--------------------------------------------------------------------------
-- 1 - Make a utility table to hold the index statistics;
-- this is necessary because Oracle overwrites the
-- index_stats information for each index analysis.
-- The user is given the choice. The default is 'Y'
-- because remaking the table does no harm. If 'Y',
-- a temporary file is filled with the drop and create
-- commands and executed. If 'N', the same file is
-- replaced with no output (made zero-length). The file
-- is crdidxst.sql (Create InDeX Statistics Table).
--
-- 2 - Refresh the utility table with current index
-- statistics. Again the user is given the choice, with
-- default 'Y', and a temporary file is made. The file
-- is anidstt.sql (ANalyze InDex STaTistics).
--
-- 3 - Generate a report on the index statistics.
-- The user is given three options, each one setting
-- a limit. The first is the minimum percentage of
-- deleted leaf nodes to report, which defaults to 20.
-- The second is the minimum index node height to report,
-- which defaults to 4. The third is the minimum number
-- of block gets per access to report, which defaults to
-- 10. If any of these limits is equalled or exceeded,
-- the index appears in the report. The report is spooled
-- to a temporary file, repidxst.lst.
--
-- Author : David Clement
-- Date : 01-JUN-2004
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Initialize
---------------------------------------------------------------------
set verify off
set head off
set feedback off
set pagesize 0
-- These columns will hold the date and time; they have to have their very
-- own query because there is no other single-row query without output
-- to a file.
column dtcol new_value tdy noprint
column tmcol new_value tim noprint
select to_char (sysdate, 'YYYY-MON-DD') dtcol,
to_char (sysdate, 'HH24:MI') tmcol
from dual;
------------------------------------------------------------------
-- Part 1: Make the index statistics utility table.
------------------------------------------------------------------
-- Prompt the user about making the index stats utility table.
accept yn char -
prompt "Do you want to create the index stats utility table? (Y|N) " -
default 'Y'
prompt
-- Make the index stats utility table.
-- First put the appropriate commands in a temporary file...
set termout off
spool crdidxst.sql
select 'drop table utl_idx_stats;' || chr (10)
|| 'create table utl_idx_stats ' || chr (10)
|| ' storage (initial 32K next 32K maxextents 2' || chr (10)
|| ' pctincrease 0) ' || chr (10)
|| ' tablespace user_data '|| chr (10)
|| ' as select name, most_repeated_key, distinct_keys, ' || chr (10)
|| ' del_lf_rows, lf_rows, height, blks_gets_per_access ' || chr (10)
|| ' from index_stats ' || chr (10)
|| ' where rownum<1;'
from dual
where 'Y'='&yn'
/
spool off
-- ...and then run the commands from the temporary file.
@crdidxst.sql
set termout on
prompt Done.
prompt
----------------------------------------------------------------------
-- Part 2: Refresh the utility table with current index statistics.
----------------------------------------------------------------------
-- Prompt the user about refreshing the index stats for Oracle
-- as a whole and for the index stats utility table in particular.
undef yn
accept yn char -
prompt "Do you want to refresh the index stats? (Y|N) " -
default 'Y'
prompt
-- Populate the index stats utility table.
-- First put the appropriate commands in a temporary file...
set termout off
spool anidstt.sql
select 'analyze index ' || owner || '.' || index_name || chr (10)
|| ' validate structure;' || chr (10)
|| 'insert into utl_idx_stats ' || chr (10)
|| '(name, most_repeated_key, distinct_keys, del_lf_rows, ' || chr (10)
|| 'lf_rows, height, blks_gets_per_access) ' || chr (10)
|| 'select name, most_repeated_key, distinct_keys, del_lf_rows, ' || chr (10)
|| 'lf_rows, height, blks_gets_per_access ' || chr (10)
|| 'from index_stats; ' || chr (10)
from dba_indexes
where owner not in ('SYS', 'SYSTEM')
and 'Y'='&yn'
/
spool off
-- ...and then run the commands from the temporary file.
@anidstt.sql
set termout on
prompt Done.
prompt
----------------------------------------------------------------------
-- Part 3: Generate the report.
-----------------------------------------------------------------------
prompt The following settings will determine what to report.
prompt Each has a default value: press to accept it.
prompt
-- Prompt the user about the threshold value for deleted leaf nodes.
accept dlflimit number -
prompt "What is the lowest percentage for deleted leaf nodes? (20) " -
default 20
prompt
-- Prompt the user about the threshold value for index node height.
accept hlimit number -
prompt "What is the lowest index node height? (4) " -
default 4
prompt
-- Prompt the user about the threshold value for block gets per access.
accept bgpalimit number -
prompt "What is the lowest number of block gets per access? (10) " -
default 10
prompt
set head on
set pagesize 55
-- Define the page titles.
ttitle center 'INDEX STATISTICAL REPORT' skip -
center 'Identifies Indexes to Consider Rebuilding' skip -
center '-------------------------------------------------------' skip -
center 'Date ' format a12 &tdy ' Time ' format a12 &tim-
' Page ' format 99 sql.pno skip -
center '-------------------------------------------------------' skip
btitle center '-------------------------------------------------------' skip -
left 'Criteria for selection are:' skip -
left 'Percentage of leaf nodes deleted >= '&dlflimit' (default is 20)' skip -
left 'Height of index nodes >= '&hlimit' (default is 4)' skip -
left 'Block gets per access >= '&bgpalimit' (default is 10)' skip -
-- Format the report columns. The "like " construct is used for
-- uniformity.
column nm format a30 heading "Index Name"
column mrk format 999,999 heading "Most|Repeated|Key"
column dk like mrk heading "Distinct|Keys"
column dlf format 999.99 heading "Percent|Leaf Rows|Deleted"
column h like mrk heading "Height"
column bgpa like dlf heading "Block|Gets Per|Access"
-- Execute the query that produces the report.
spool repidxst.lst
select name nm, most_repeated_key mrk, distinct_keys dk,
decode (lf_rows, 0, 0, (del_lf_rows / lf_rows) * 100) dlf,
height h, blks_gets_per_access bgpa
from utl_idx_stats
where
(decode (lf_rows, 0, 0, (del_lf_rows / lf_rows) * 100) >= &dlflimit
or height >= &hlimit
or blks_gets_per_access >= &bgpalimit)
/
spool off
set pagesize 23
set feedback on
set verify on
set termout on