slow dba_tables query response
Recently I have discovered the dba_tables reponse is almost 30 times slower than other databases.The quick autotrace on the query shows the query on this 10.2.0.4 database is runnign FTS on the obj$, tab$ & seg$. However when i tested the same trace on other 10.2.0.5 database the plan was quite different and so also the performance of the query. See below
I refreshed the stats on the dictionary and sys schema still the results are not quite differnt.
10.2.0.4 database
============================
10:48:18 SQL> set autotrace on
10:48:48 SQL> select count(1) from dba_tables;
COUNT(1)
----------
17728
Elapsed: 00:00:25.89
Execution Plan
----------------------------------------------------------
Plan hash value: 2610396835
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
0