Logo

SQL Script

Three ways to Analyze Tables

 Tested on Oracle 8.0 Tested on Oracle 7

Updated: 6-April-1999
Version : 2.0

Method 1 - DBMS_UTILITY Package

Description

There are 2 Oracle supplied packages procedures which can be used to analyze tables. Always make sure you read the latest release notes for you platform before running these procedures.

PROCEDURE dbms_utility.analyze_schema

Argument Name Type In/Out Default ?
SCHEMA VARCHAR2 IN
METHOD VARCHAR2 IN
ESTIMATE_ROWS NUMBER IN
ESTIMATE_PERCENT NUMBER IN
METHOD_OPT VARCHAR2 IN

METHOD_OPT is one of ' FOR TABLE','FOR ALL COLUMNS [SIZE n]','FOR ALL INDEXED COLUMNS [SIZE n]','FOR ALL INDEXES'.

PROCEDURE dbms_utility.analyze_database

Argument Name Type In/Out Default ?
METHOD VARCHAR2 IN
ESTIMATE_ROWS NUMBER IN
ESTIMATE_PERCENT NUMBER IN
METHOD_OPT VARCHAR2 IN

SQL Source

To run these procedures, from the SQL prompt type

SQL> execute dbms_utility.analyze_schema('SCOTT','ESTIMATE')

Alternatively use,

SQL> execute dbms_utility.analyze_database('COMPUTE')

Method 2 - Dynamic SQL Method

Description

This is a simple technique of using SQL to generate SQL. The main benefit of using this technique as oppose to Method 1 is that since it is a SQL query, you have control down to table level about which tables you analyze. It is not uncommon that in a single schema some tables will need to be analyzed on a more frequent basis that other tables in the same schema For example, frequently changing OLTP tables may need analyzing daily whereas static/historical/archive data will only need analyzing once just after population.

SQL Source

spool analyze.lst
set pages 0
set verify off
select 'ANALYZE TABLE  '||owner||'.'||table_name||' ESTIMATE STATISTICS;'
from dba_tables
where owner not in ('SYS','SYSTEM')
/
spool off
start analyze.lst 

Method 3 - Dynamic SQL using DBMS_DDL Method    

Description

The third method combines an Oracle supplied package and dynamic SQL using the DBMS_DDL.ANALYZE_OBJECT package procedure. Therefore, we can use this procedure to create a new dynamic analyze table script, which can be easily tailored to individual databases requirements.

PROCEDURE dbms_ddl.analyze_object

Argument Name Type In/Out Default
TYPE VARCHAR2 IN  
SCHEMA VARCHAR2 IN  
NAME VARCHAR2 IN  
METHOD VARCHAR2 IN  
ESTIMATE_ROWS NUMBER IN Y
ESTIMATE_PERCENT NUMBER IN Y
METHOD_OPT VARCHAR2 IN Y

TYPE is 'TABLE','INDEX','CLUSTER'. If SCHEMA is NULL then the procedure assumes that its the current schema.

SQL Source

spool analyze.lst
set pages 0
set verify off
select 'execute dbms_ddl.analyze_object('||chr(39)||segment_type||chr(39)||
					','||chr(39)||owner||chr(39)||
					','||chr(39)||segment_name||chr(39)||
					','||chr(39)||'ESTIMATE'||chr(39)||
					')'
from dba_segments
where owner not in ('SYS','SYSTEM')
and segment_type in ('TABLE','INDEX','CLUSTER')
/
spool off
start analyze.lst 

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz
Legal

Logo