--------------------------------------------------------------------------
--			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