Script to Analyze All Schema Tables and Indexes in Oracle Database

This script to generate the script to analyze all the tables in the specified schema(s) 

Method 1. 

Using dbms_stats

SET PAGES 10000 
SET LINES 444
SET HEAD OFF 
SET FEED OFF
spool analyze_table.sql
select 'exec dbms_stats.gather_table_stats(ownname => '''||owner||''',tabname => '''||table_name ||''', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE,method_opt => '' FOR ALL COLUMNS SIZE AUTO'', degree => 8);' 
from dba_tables 
where owner='TEST';
spool off

advertisements

 

Method 2. 

Analyze table command

SET PAGES 10000 
SET LINES 444
SET HEAD OFF 
SET FEED OFF
spool analyze_table.sql
select 'ANALYZE TABLE ' || owner || '.' || table_name ||
' ESTIMATE STATISTICS SAMPLE 10 PERCENT;'
from dba_tables
where  owner ='TEST';
spool off

Script to create analyze index script

SET PAGES 10000 
SET LINES 444
SET HEAD OFF 
SET FEED OFF
spool analyze_index.sql
select 'ANALYZE index ' || owner || '.' || index_name ||
' ESTIMATE STATISTICS SAMPLE 10 PERCENT;'
from dba_indexes
where  owner ='TEST';