Oracle Database Administration: The Essential Refe

Script to automate the ANALYZE process

The last few pages have been designed to convince you that if you are using the cost-based optimizer, you have to analyze your tables, indexes, and some of your columns. However, you don’t want to generate and maintain a script that goes out and analyzes every table, index, and column. The following SQL*Plus script takes as a parameter a username. It then generates another SQL script that will:

• Analyze all tables owned by the passed username

• Analyze all indexes owned by the passed username

• Analyze all previously analyzed columns owned by the passed username

SET HEAD OFF
SET VERI OFF
SET FEED OFF
SET ECHO OFF
SPOOL &1..sql
SELECT 'ANALYZE TABLE '||owner||'.'||table_name||'
COMPUTE STATISTICS FOR TABLE;'
FROM dba_tables WHERE owner = '&1';

SELECT 'ANALYZE TABLE '||owner||'.'||table_name||'
COMPUTE STATISTICS FOR ALL INDEXES;'
FROM dba_tables WHERE owner = '&1';

Select 'ANALYZE TABLE '||owner||'.'||table_name||'
COMPUTE STATISTICS FOR COLUMNS '||column_name||' size 254;'
FROM dba_histograms
WHERE owner = '&1'
GROUP BY owner,table_name,column_name;

SPOOL OFF
SET HEAD ON
SET VERI ON
SET FEED ON
SET ECHO ON
@&1..sql