Toad World® Forums

SQL Nav 6.4 lock up

After a switch to v6.x I’ve noticed that SQL navigator keep locking up when I try to click on table names. I tried to run SQL tracing and here’s the query it tries to run when I do the click:

select USER owner, a.object_name, a.object_id, a.created, a.last_ddl_time,decode(a.status,‘VALID’,0,‘INVALID’,1,2) status, decode(b.partitioned,‘YES’,‘Y’,‘NO’,‘N’) partitioned, ‘N’ object_table, ‘N’ external_table, decode(b.nested,‘YES’,‘Y’,‘N’) nested, decode(b.IOT_Type,‘IOT’,1,‘IOT_OVERFLOW’,2,0) IOT_Type, b.IOT_Name, b.temporary
from sys.user_objects a,sys.user_tables b
where b.table_name like ‘%’
and a.object_name=b.table_name
and a.object_type=‘TABLE’
and b.dropped=‘NO’

I tried this query on several of our 11g databases and it takes forever to get any result (I waited for 15+ minutes, then had to terminate it). There were ~650 objects with type = ‘TABLE’ and ~600 tables with dropped = ‘NO’. I tried it against 9g database and it completed relatively fast.

I wonder, why it uses a wildcard in “b.table_name like ‘%’”? The same query with an actual table names takes less than a second to execute.

At this point I’m ready to give up SQL Nav, it’s been less than satisfying experience with 6.x and Oracle 11g. :frowning:

Hi Alexey,

Thank you for your feedback. We will fix the performance issue in the next 6.6 beta release; unfortunately, it’s too late to include the fix in 6.5 - it goes GA very soon.