Slow to open tree in Oracle11g databases

[aeowyn]

I am trying to troubleshoot an issue where PL/SQL Developer is extremely slow to expand any of the trees (such as tables) when connect to an 11g database. This can take as long as 4 minutes. I have no issues when doing so if connected to a 9i database on the same server. This happens across all servers when connected to 11g. I have had this issue with a 9i client. This morning I install 11g instant client to see if it would clear up, but it is not working any better.

Any suggestions would be greatly appreciated.

 

[Marco Kalter]

As a test, can you modify the PL/SQL Developer and add the QueryHints parameter? For example:

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

This will apply a /*+rule*/ hint to the all_objects query, which can help.

Let me know how this turns out.

 

[Worker]

I have noticed the same problem as aeowyn, and I can say that the QueryHints parameter nicely speeds things up in an 11g database, and seems to have no side-effects in an 8i database.

 

[aeowyn]

Excellent! The QueryHints option fixed this issue.

Thanks!!

 

[Garrett]

Thanks!!!

 

[da bull]

Not to sound too stupid, but could I please get a better explination of where I am supposed to be adding QueryHints? Is this a file I need to download from somewhere? Right now, it takes over 1 minute to expand either my Table or my View folders in Object Browser.
thank you

 

[Worker]

Assuming you start PL/SQL Developer from a shortcut or the start menu, right-click that shortcut or menu item and choose "Properties". In the "Target" field it should say something like

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

Change that to

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

 

[da bull]

Thank YOU!!! you have just made my weekend. It expanded the tree so fast it almost scared me :~)

 

[dilbongo]

I tried this and it has not helped at all. We upgraded a few weeks ago to Oracle 10.2.0.4 and i went from almost instant tree expansion to it taking 65-80 seconds to expand a node with about 150 objects. Any other suggestions?

 

[Marco Kalter]

All I can suggest is that you use the "My objects" filter, or any other filter that has the owner in the where clause.

 

[Scott Rice]

for 10g you need to use NoQueryHints option