This procedure is dynamic SQL that calls the dbms_stats package to analyze all tables (excluding the temporary table) in the caller's schema. It has been tested on 8.1.6 and 9.2.
Calling the dbms_stats package to analyze a table benefits the cost-based optimizer, which Oracle recommends you routinely analyze. It will help speed up the analysis if you just analyze 1% of tables. Also, I've found that using dbms_stats.gather_table_stats can give better statistics than Oracle's recommended dbms_stats.gather_schema_stats.
CREATE OR REPLACE procedure analyze_table2 as ojn varchar2(100); user_name varchar2(20); dml_str VARCHAR2(200); CURSOR c1 IS SELECT table_name FROM user_tables WHERE temporary <> 'Y'; BEGIN select user into user_name from dual; open c1; loop fetch c1 into ojn; dbms_output.put_line ('Now '|| ojn ||' is analyzing '); exit when c1%NOTFOUND; dml_str := 'begin dbms_stats.gather_table_stats( '||''''||user_name||'''' ||','|| ''''||ojn ||'''' || ',null,1) ;end;'; dbms_output.put_line (dml_str); execute immediate dml_str; END LOOP; END; /
Dennis D. writes: Why use dynamic SQL? The faster option is dbms_stats.gather_table_stats, which can be called directly.
Andrew K. writes: If using dbms_stats, you might as well use dbms_stats.gather_schema_stats, which is provided in the stats package.
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 applications, SQL, database administration, 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 April 2004