Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

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:
 

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.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational