Using a 10g client and database, SQL Navigator is very slow in connecting to database. Using a 9i database, connection to database has a much better performance.
SQL Monitor will show that the background query taking a longer to time to execute has to do with SYS.ALL_ARGUMENTS view. Oracle 10gR2 has a new ALL_ARGUMENTS view, refer to Oracle MetaLink Note 377037.1 and MetaLink Note 429877.1 for reported performance issues on this view.
Change the OPTIMIZER_SECURE_VIEW_MERGING setting to FALSE in the Oracle Parameters, by default, this setting is set to TRUE.
1. Go to C:\Program Files\Quest Software\SQL Navigator 6.0 folder
2. Run SQLMonitor.exe and check the SQL Navigator check box on the left side
3. Minimize this window, and run SQL Navigator
4. Connect to your 10g database in SQL Navigatgor
5. Once it is connected, open SQL Monitor from the bottom of screen and you will see a series of SQLs on the right hand side
The below query is the query causing the performance to slow down:
select /*+ ALL_ROWS */ distinct a.object_name,decode(sum(decode(a.position,0,1,0)),0,0,1) entry_type
from sys.all_arguments a,sys.all_objects o
where o.owner=:owner and o.object_name=:name and a.object_id=o.object_id
group by a.object_name,a.overload order by a.object_name
owner = 'SYS'
name = 'STANDAR