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 <table> 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
Requires Free Membership to View
asktom.oracle.com. I also used
information on Metalink (Oracle documentation) on how the dbms_stats stored
procedure works.
a dbms_stats.objecttab;
b dbms_stats.objecttab;
BEGIN
FOR s in (select distinct(owner) schema
from dba_tables
where owner not in ('SYS','SYSTEM'))
LOOP
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
loop
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
loop
dbms_output.put_line( 'Stats missing: '||a(i).objType );
dbms_output.put_line( 'Object Name: '||a(i).objName );
end loop;
End Loop;
END;
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 8.1.7.0.0.
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 first published in November 2002
Join the conversationComment
Share
Comments
Results
Contribute to the conversation