DBA Tips Archive for Oracle

  


Data Dictionary Performance Problems After Running DBMS_STATS

by Jeff Hunter, Sr. Database Administrator

Overview

When collecting statistics using analyze or DBMS_STATS, ensure you DO NOT gather statistics for anything owned by the SYS user. (e.g. the Data Dictionary). Many users have run DBMS_STATS.GATHER_DATABASE_STATS and have seen queries against the data dictionary from from < 1 second to over a minute.

Even a query like the following:

  SELECT
       a2.table_name
    ,  a1.constraint_name 
  FROM
      dba_constraints a1
    , dba_constraints a2 
  WHERE
        a1.constraint_name = a2.r_constraint_name
    AND a1.table_name = UPPER('EMP') 
  ORDER BY 1;
could take around 45 seconds if statistics were to be gathered on the Data Dictionary

Dropping Statistics for SYS Objects

The data dictionary is optimized for the rules based optimizer and if you put statistics on them AND your optimizer mode is choose, it will go to the cost based optimizer, which can cause some serious performance problems in the data dictionary.

To corrrect this issue, go back and drop the statistics for anything owned by SYS.

From a privledeged account, you can run the following SQL script, which will give you all the DELETE STATISTICS commands. Simply spool the output of the following query to a file and then run it.

  SPOOL delete_stats_sys.sql

  SELECT 'ANALYZE TABLE "'
         || owner || '"."' || table_name
         || '" DELETE STATISTICS;' 
  FROM   dba_tables 
  WHERE  owner = 'SYS'; 

  SPOOL off

  @delete_stats_sys


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Tuesday, 09-Jan-2007 13:24:30 EST
Page Count: 15174