 |
|
Dictionary queries have very poor performance
Oracle Tips by Burleson Consulting |
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:
exec
dbms_stats.gather_schema_stats ('SYS', gather_fixed=>TRUE);
BEGIN dbms_stats.gather_schema_stats
('SYS', gather_fixed=>TRUE); END;
*
ERROR at line 1:
ORA-00957: duplicate column name
ORA-06512: at "SYS.DBMS_STATS", line 10070
Answer:
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
connect username/password
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,
level 4';
exec dbms_stats.gather_schema_stats ('SYS', gather_fixed=>TRUE);
select 'close the cursor' from dual;
exec dbms_session.reset_package;
alter session set events '10046 trace name context off';
quit
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
tables.
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).
delete_table_stats('SYS','X$KQLFXPL');
delete_table_stats('SYS','X$KGLOB');
Then you can take
statistics on them using gather_table_stats and check again
(elapsed time, plan).
gather_table_stats('SYS','X$KQLFXPL');
gather_table_stats('SYS','X$KGLOB');
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
change.
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
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
