Problem solve Get help with specific problems with your technologies, process and projects.

Keeping Optimizer statistics up to date

This script can be scheduled to run regularly to monitor table usage and keep statistics on schema objects current.

In order for Oracle to use the Cost Based Optimizer (CBO) effectively, schema objects must have current statistics....

Instead of analyzing all user objects regularly, which is costly, in 8i and above you can have Oracle monitor table usage. Utilizing the dbms_stats package you then only need to analyze the table when there has been significant change. The following script can be scheduled to run regularly. Note: You must gather statistics initially and issued the "alter

monitoring;" statement for this to work. Also, when issuing the statement in SQL*Plus remember to set serveroutput on.

I based the framework (cycling through all non-sys and system schemas) on an article on I also used information on Metalink (Oracle documentation) on how the dbms_stats stored procedure works.

a dbms_stats.objecttab;
  b dbms_stats.objecttab;
 FOR s in (select distinct(owner) schema
             from dba_tables
             where owner not in ('SYS','SYSTEM'))
        dbms_output.put_line('Analyzing the entire '||s.schema||' schema');
--List tables with stale statistics
        dbms_stats.gather_schema_stats(ownname => s.schema,
                                        options => 'LIST STALE',
                                        objlist => b);
        for i in 1 .. b.count
             dbms_output.put_line( 'Stale Stats: '||b(i).objName );
         end loop;
-- Analyze objects with stale statistics
        dbms_stats.gather_schema_stats(ownname => s.schema,
                                        method_opt => 'FOR ALL INDEXED COLUMNS',
                                        degree => 4,
                                        cascade => TRUE,
                                        options => 'GATHER STALE');
--List any objects with missing statistics
        dbms_stats.gather_schema_stats(ownname => s.schema,
                                        options => 'LIST EMPTY',
                                        objlist => a);
        for i in 1 .. a.count
             dbms_output.put_line( 'Stats missing: '||a(i).objType );
             dbms_output.put_line( 'Object Name: '||a(i).objName );
          end loop;
   End Loop;

Reader Feedback

Brett F. writes: It should be fairly obvious that this tip won't work plugged directly into SQL*PLUS. I tried it anyway just in case I was being dense and got a slew of errors. It is not clear from the context how this should be run, although the author seems to have intended it to be run in this way as he mentions that you should "set serveroutput on". Here are the errors I received when I ran it "as is":

ERROR at line 10:
ORA-06550: line 10, column 52:
PLS-00201: identifier 'B' must be declared
ORA-06550: line 8, column 9:
PL/SQL: Statement ignored
ORA-06550: line 11, column 23:
PLS-00201: identifier 'B.COUNT' must be declared
ORA-06550: line 11, column 9:
PL/SQL: Statement ignored
ORA-06550: line 25, column 52:
PLS-00201: identifier 'A' must be declared
ORA-06550: line 23, column 9:
PL/SQL: Statement ignored
ORA-06550: line 26, column 23:
PLS-00201: identifier 'A.COUNT' must be declared
ORA-06550: line 26, column 9:
PL/SQL: Statement ignored

I'm running Oracle

Jiayong S. writes regarding the question above: It will work perfectly if you just add DECLARE at the beginning when executing it in SQL*PLUS.

The author. responds: I should have added that the script is the text for a stored procedure. Therefore, the following line should be added to the beginning of the script:

create or replace procedure GET_STATS  as

To execute in SQL*Plus after you create the procedure:

set serveroutput on
exec dbms_output.enable(100000);  --may be added to the beginning of the script 
                                  --to ensure a large enough output buffer
exec get_stats;

This will then provide you with like this:

Analyzing the entire USER1 schema
Stale Stats: TABLE1
Stale Stats: TABLE2
Stale Stats: TABLE3
Stats missing: INDEX
Object Name: TABLE4_PK
Stats missing: INDEX
Object Name: TABLE5_PK
Analyzing the entire USER2 schema
Analyzing the entire USER3 schema
Stale Stats: TABLE1
Analyzing the entire USER4 schema

I am using OEM Jobs to run this every day on my system. I developed this in 8.1.7 so it should work for Brett. He just needs to be aware that in 8.1.7, it takes about 4 hours for SMON to mark table stats as stale. This has been improved to 15 minutes in 9i. One other thing to note is that if you get the following error:

ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 91
ORA-06512: at "SYS.DBMS_OUTPUT", line 58
ORA-06512: at "DGIBBS.GET_STATS", line 15
ORA-06512: at line 1

You need to enable a larger buffer (though there is a max size, I just can't remember off the top of my head -- it's in the manual).

Sorry I wasn't clear enough.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was last published in November 2002

Dig Deeper on Oracle and SQL



Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.