REM ========================================================================
REM ANALYZE SCRIPT
REM Copyright 1999 by Yuval Oren (yuval@bluecast.com).
REM
REM This software is released under the terms of the GNU Lesser General
REM Public License, available at http://www.gnu.org/licenses/lgpl.html.
REM
REM This script analyzes all tables and indexes and computes
REM or estimates statistics. This is necessary in order for Oracle's
REM cost-based optimizer to correctly decide on the best way to 
REM execute queries.
REM
REM The script first writes the SQL commands to /tmp/analyze_[user].sql
REM and then executes it. 
REM
REM
REM ========================================================================
col bytes noprint
col byte_size format a79

prompt Analyze objects in what schema? 
define user = &&1

set echo off feedb off termout off pause off pages 0 doc off verify off
whenever sqlerror exit 1

-- ============================================================
-- Compute statistics for all indexes
-- ============================================================
select 'prompt Computing statistics for index ' || i.index_name || '...' || chr(10) ||
'execute sys.dbms_ddl.analyze_object(' || '''' || 'INDEX' || '''' || ', '
|| '''' || s.owner || '''' || ', -' || chr(10) || '''' ||
i.index_name || '''' || ', ' || '''' ||
'COMPUTE' || '''' || ');' 
, '/* ' || s.bytes/1024 || ' kbytes ' || s.bytes/1024/1024 || 
' mbytes */' byte_size, s.bytes
from dba_segments s, dba_indexes i
where s.owner = i.owner
and s.segment_name = i.index_name
and s.owner = upper('&&user')
and s.segment_type = 'INDEX'
--
-- ============================================================
-- Compute statistics for all tables smaller than 10MB
-- ============================================================
union
select 'prompt Computing statistics for table ' || segment_name || '...' || chr(10) ||
'execute sys.dbms_ddl.analyze_object(' || '''' || 'TABLE' || '''' || ', '
|| '''' || owner || '''' || ', -' || chr(10) || '''' ||
segment_name || '''' || ', ' || '''' ||
'COMPUTE' || '''' || ', null, null, ' || '-' || chr(10) || '''' ||
'for table for all indexed columns' || '''' ||
');' , '/* ' || bytes/1024 || ' kbytes ' || bytes/1024/1024 || ' mbytes */' byte_size, bytes
from dba_segments
where 
segment_type = 'TABLE'
and owner = upper('&&user')
and bytes < 10 * 1024 * 1024 /* 10M */
--
-- ============================================================
-- Estimate statistics for all larger tables
-- ============================================================
union
select 'prompt Estimating statistics for table ' || segment_name || '...' || chr(10) ||
'execute sys.dbms_ddl.analyze_object(' || '''' || 'TABLE' || '''' || ', '
|| '''' || owner || '''' || ', -' || chr(10) || '''' ||
segment_name || '''' || ', ' || '''' ||
'ESTIMATE' || '''' || ', ' || 'NULL' || ', ' || '1' || ', ' 
|| '-' || chr(10) || '''' || 
'FOR TABLE for all indexed columns' || '''' || ');'
, '/* ' || bytes/1024 || ' kbytes ' || bytes/1024/1024 || ' mbytes */' byte_size, bytes
from dba_segments
where 
segment_type = 'TABLE'
and owner = upper('&&user')
and bytes >= 10 * 1024 * 1024 /* 10M */
order by bytes

spool /tmp/analyze_&&user..sql
prompt set pause off echo off termout on time on timing on doc off pages 0 feedb off verify off
prompt whenever sqlerror exit 1
prompt
rem prompt col curr_time format a8
rem prompt
rem prompt
rem set sqlterminator ^
rem prompt select to_char(sysdate, 'HH:MI:SS') curr_time
rem prompt from dual;
rem prompt
rem set sqlterminator ;
/
rem set sqlterminator ^
rem prompt
rem prompt select to_char(sysdate, 'HH:MI:SS') curr_time
rem prompt from dual;
rem prompt 
rem set sqlterminator ;
prompt
prompt exit
spool off
set pause off echo on termout on time on timing on doc off pages 0 feedb on 
@/tmp/analyze_&&user
exit