Analyzing all tables in schema

Here is a dynamic SQL script that analyzes all tables excluding the temporary table in a 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.

This was first published in April 2004

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close