Loading a package in program window taking more than 2 minutes

[DPS#1]

Loading a package in program window takes more than 2 minutes after moving Oracle 10g standalone database to Oracle 10g RAC. The PL/SQL Developer version is 8.0.1.1502. This package is about 14700 lines which used to open within a few seconds but now taking anywhere between 2 and 5 minutes. Are there any settings that I need to make in the PL/SQL Developer. Any help is appreciated. Thank you.

 

[Marco Kalter]

Seems like an Oracle Dictionary performance issue with the sys.all_source or sys.dba_source view. 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.

 

[DPS#1]

Thanks for your reply. Could you please tell me where to send the debug.txt file that has been generated. I did not see any tag for the attachment. Thanks

 

[DPS#1]

The output file, Debug.txt has been sent to your id. FYI

 

[Marco Kalter]

I don't think we received it. Can you resend it?

 

[DPS#1]

The file has been emailed to the support.

 

[DPS#1]

Do you have any update on why it is taking so long to load a package? I did provide the debug information to you earlier. I even tried with QueryHints but that did not help either. Let me know if you need any additional information. Thanks

 

[Marco Kalter]

This query takes too much time on your database:

select text
  from sys.dba_source
 where owner = :object_owner
   and name  = :object_name
   and type  = :object_type
 order by line

Perhaps you can let a DBA check why the query is so slow when using bind variables compared to the same query using character literals?

You can also try to disable the "Use DBA views if available" option. This way the query will use the sys.all_source view.