Oracle 10g 10.2 performance problem

[DLab]

Hi,

We recently upgraded from oracle 9i to oracle 10g release 10.2.0.3.0. I am having serious performance issues with pl/sql developer release 7.1.4.1389. The code assistant is performing very poorly. When looking at the session sql, I see this query everytime I call up the column list (when pressing .)

SELECT /*+rule*/ S.TABLE_OWNER, S.TABLE_NAME FROM ALL_SYNONYMS S WHERE S.SYNONYM_NAME = :B1 AND S.OWNER = 'PUBLIC' AND ROWNUM = 1

This query takes 6 seconds to run. However, when I remove the rule optimizer hint, it runs under 0.02 seconds. As of oracle 10g 10.2, the rule optimizer has been deprecated, so it should not be used anymore as performance are pretty bad.

I am now back to using version 7.0.3.1123 as this release doesn't seem to be affected by this issue. (it must not be using the optimizer hint).

Please advise.

 

[Gustavo]

Try to change your shortcut to something like:

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

The NoQueryHints parameter removes some hints from queries to the dictionary.

 

[DLab]

Thanks, this is working better

 

[Ron E]

Does anybody have any other suggestions? We have tried this parameter on many PCs and the code assistant doesn't seem to pop up any faster on 10G. We upgraded the Oracle client, thinking that it might help, but it only ran about 1 second faster, which isn't much out of a 8-30 second wait time.

Thanks,

Ron

 

[Paul Thornton]

Gustavo - that's great - much, much faster!

Ron E - In Prefrences/Options try setting "Use DBA Views" to off (un-ticked)