Dictionary queries have very poor performance
Oracle Database Tips by Donald Burleson
Question: The CBO began switching to using FTS a couple
of weeks back (I'm on 9iR2). More and more queries are taking a
long time to execute (tens of minutes to hour+) where they were
either under a minute or just over a couple of weeks back. I?m
seeing some truly awful SQL that I?m throwing back to the
developers - but why did it go sideways when ?nothing has
changed?? I?m getting this error and I suspect dictionary
corruption, or a problem analyzing the SYS schema:
dbms_stats.gather_schema_stats ('SYS', gather_fixed=>TRUE);
('SYS', gather_fixed=>TRUE); END;
ERROR at line 1:
ORA-00957: duplicate column name
ORA-06512: at "SYS.DBMS_STATS", line 10070
Duplicate rows in dbms_stats - There is one solved bug
for a duplicate column name in a fixed table in 9.2, however,
the fix went into the main code line whichat the time was 10.2
and no one has required a patch in 9.2 for it. I will need to
see a 10046 of the dbms_stats session in order to see if it is
the same issue. If so, I think I can offer some workaround
options at least. Here are the steps for the 10046 trace I need
to see, when done please upload the trace file generated in the
user dump destination for this session.
$ sqlplus /nolog
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever,
exec dbms_stats.gather_schema_stats ('SYS', gather_fixed=>TRUE);
select 'close the cursor' from dual;
alter session set events '10046 trace name context off';
Poor dictionary query performance - While it is supported
in 9.2 to gather statistics on the data dictionary and fixed
views, doing so isn't the norm.
There is a bug fixed only in 10gR2 (not expected to be
back-ported to 9.2) that caused this error. The fix is - don't
generate statistics against SYS - especially not the Fixed
For this query, let's see if we can
get a better plan by removing statistics or by getting better statistics, or if
we need to do something else to tune it. Take the SYS statistics as before, but
with gather_fixed => false.
I would like for you to test first
by deleting the statistics on these two X$ tables and see how the query runs
(elapsed time, plan).
Then you can take
statistics on them using gather_table_stats and check again
(elapsed time, plan).
The issue with this
is that the contents of these fixed views, particularly
x$kqlfxpl, can change dramatically. Gathering fixed object
statistics may help now and cause problems later as the contents
Warning, this is a bit dangerous due
to latch contention, see the following note. I've supported a couple of very
busy systems that were completely halted for a time due to latch contention on
x$kglob due to monitoring queries (particularly on v$open_cursors).
Note.4339128.8 Ext/Pub Bug 4339128 -
Heavy latch contention from queries against library cache views.
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.