are you working with files or code directly out of the database?
we might need the actual code to test it here
next time it freezes, grab a JSTACK and post it here
I am working in the code directly out of the database and it is definitely not the package itself, because I am currently able to open the package without Instant Client in SQL Developer and my colleague can even open it while his SQL Developer is using the Instant Client. We both used SQL Developer 4.1.5, I am currently on newest Java 64 version 111. But as said, I have this phenomenon at least since version 4.1.
I attached now three different logs
1. crashNoOCI.log - this is the JSTACK of my SQL Developer after I opened the package succesfully - NO Instant Client used
2. crashColleague.log - this is the JSTACK of my colleagues SQL Developer after he opened the package succesfully - Instant Client used
3. crashOCI.log - this is the JSTACK of my SQL Developer after I opened the package NOT succesfully - Instant Client was used. Here I pressed several times the break key. First time after it got stuck, second after I killed the session and the Package was opening the Header not fully (only showing "Create or replace packa"). Last time after it freezed again after I tried to open the body.
I hope the files are helpful.
The link appears to be broken. In addition to JStack, any unusually long executed dictionary queries in the statements log?
Let me clarify how stack information is used for troubleshooting. In the cases where you have successfully opened an editor (crashNoOCI.log and crashColleague.log), and where editor opening was unsucessful (crashOCI.log), all the background activity ceased, and stack trace conveys no information. However, when there is a deadlock, or some poorly performing sql query blocking gui, it is evident from the stack trace snapshot. Therefore, it is important to capture stack traces when hanging is occurring, and not after it.
On windows machine it is easier to capture the stack trace the following way:
1. Run sqldeveloper/bin/sqldeveloper.exe -- this version starts the application together with java console.
2. Hit <ctrl-break> when you experience hanging.
3. Witness stack trace dumped into java console.
4. Copy and paste it for examination.
Maybe I didn't clarified enough:
1. This is exactly taken as described by Vadim Tropashko-Oracle DURING it was in deadlock situation (15:28:10). But I added 2 other stacks happening in same session . One directly after killing the session and it opened a not complete Package header (15:30:03) and then another one while in deadlock during opening the Body (15:31:08).
So deadlock Stacks are (15:28:10) and (15:31:08) in CrashOCI.log. The others are just additional information because, yes, I don't know what you can read out of it and thought, it might be helpful, worst case just useless. But you should find something already in the files I attached.
You are right: there are several informative threads in that file, I failed to notice first time. Thank you for raising the issue!
there appears to be a query we're running to get the code for your plsql object to be 'hanging' - can you check to see if your session is being blocked and also look at the wait events? it could be db contention - and we're left holding the bag.
I can't see any blocked session. But when I am querying for the executed query, I get the following one:
WITH src AS (SELECT ROWNUM,LINE,TEXT,origin_con_id FROM SYS.Dba
_SOURCE WHERE TYPE = :TYPE AND OWNER = :OWNER AND NAME = :NAME)
SELECT text FROM src, (SELECT max(origin_con_id) max_orig FROM
src) WHERE origin_con_id = max_orig ORDER BY LINE
But the session is in status inactive, the BLOCKING_SESSION_STATUS is on 'NO HOLDER', WAIT CLASS on Idle and the state WAITING. Wait time was then already at 20min (confirmed with wait_time_micro). I am unfortunately not a DBA (and my DBA is currently on vacation), but I hope this helps a bit.
At least it is a weird issue, because it not always appears and my colleague can open it without an issue using Instant Client and same SQL Developer version. I will not say that it is impossible that it is caused by me and my PC settings, but it is still strange it happens on three of my PCs. (my colleagues are not using SQL developer as they are more used to TOAD or not into PLSQL)
You may have to wait for the return of your DBA for help. If I run that code against a 12c DB it runs fine in about half a second to retrieve a procedure or a small package.
WITH src AS (SELECT ROWNUM,LINE,TEXT,origin_con_id FROM SYS.Dba_SOURCE WHERE TYPE = :TYPE AND OWNER = :OWNER AND NAME = :NAME) SELECT text FROM src, (SELECT max(origin_con_id) max_orig FROM src) WHERE origin_con_id = max_orig ORDER BY LINE;
You might try using the worksheet's Explain Plan functionality in the 4.2 EA against it to see if anything unusual shows up in the plan costing, or hints.
Perhaps the DBA need only refresh some dictionary, system, or fixed_objects optimizer statistics, but this definitely merits investigation on the DB side of things.