SQL Performance Tuning Scripts

Analyze all table and index partitions individually

set feed off echo off head off trimspool on line 500 spool /tmp/analyze$$.sql select ‘ANALYZE TABLE ‘||table_owner||’.’||table_name||’ partition (‘|| partition_name||’) estimate statistics;’ from sys.dba_tab_partitions / select ‘ANALYZE INDEX ‘||index_owner||’.’||index_name||’ partition (‘|| partition_name||’) estimate statistics;’ from sys.dba_ind_partitions / spool off set feed on @/tmp/analyze$$.sql ! rm /tmp/analyze$$.sql

List all indexes for a given table

break on index_name skip 1 col index_name format a30 col uniuenes format a12 col column_name format a30 prompt Indexes for table: &&1 select c.index_name, i.uniqueness, c.column_name from user_indexes i, user_ind_columns c where i.index_name = c.index_name and i.table_name = upper(‘&&1’) order by c.index_name, c.column_position / undef 1

List analyzed tables with not-analyzed indexes

— select distinct ‘analyze table ‘||i.table_name|| — ‘ estimate statistics sample 25 percent;’ select ‘Index ‘||i.index_name||’ not analyzed but table ‘|| i.table_name||’ is.’ from user_tables t, user_indexes i where t.table_name = i.table_name and t.num_rows is not null and i.distinct_keys is null /

Explain SQL execution plan

DELETE FROM plan_table WHERE statement_id = ‘XXX’; COMMIT; EXPLAIN PLAN SET STATEMENT_ID = ‘XXX’ FOR select * from dual —–< Insert your SQL statement here