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