Analyzing all tables in schema

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;
/

Reader Comments

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.