Slow Response in Object Browser with Oracle 12c

[LindaS]

Hi

We are experiencing extremely slow response when accessing objects via the Object Browser window in 12c. It can take minutes to open a small trigger or package for view or edit, or to query the data in a table. We did not have this same experience with prior Oracle Versions. I have tested the same objects in Oracle's SQL Developer and the response it just fine. Is there some configuration we need to change to get PL/SQL Developer to work with Oracle 12c? I see this issue was raised a year ago, but I did not see any resolution to the problem of slow performance. As it is now, we cannot continue to use this tool. We are on Version 11.0.6.1776

Thank you.

 

[Marco Kalter]

To obtain some more diagnostic information, can you modify the shortcut and add the DebugSQL parameter? For example:

"C:\Program Files\PLSQL Developer\plsqldev.exe" DebugSQL

Reproduce the problem and send me the debug.txt file that is generated in the PL/SQL Developer directory or in the %APPDATA%\PLSQL Developer directory (e.g. C:\Users\\AppData\Roaming\PLSQL Developer).

 

[LindaS]

I wanted to update my post with the solution that worked for us. We were having terrible performance when using the Object Browser window in 12C. Viewing code for a trigger could take over an hour (if I let the tool keep running). It was inconsistent, too, sometimes taking just several minutes (still too long). It took some time, but our DBA traced it down to an Oracle parameter called, optimizer_use_sql_plan_baselines. It was set to TRUE, when it was set to FALSE, PL/SQL Developer worked efficiently again.

I don't know if this solution would work for all databases, but it did solve our very frustrating problem.

Thanks.