-------------------------------------------------------------------------------
--			1 - Make a temporary table so that the actual
--			row count can be compared to the statistics.
--			Since the row count is always different, a 
--			permanent table would not be much use. The 
--			table is called tmp_table_stats.
--
--			2 - Present a report of the results on-screen
--			so that the operator can decide which tables (if
--			any) to analyze.
--
--			3 - Give the user the option to run the 'analyze'
--			command on tables with out of date statistics.
--			These are reported as tables with actual row
--			counts 20 percent higher or lower than the 
--			row count in the statistics table in the Data
--			Dictionary (as reported by user_tables) and 
--			with at least 500 rows (anything smaller is not
--			worth the worry). The threshold values (20 percent
--			and 500) are hard-coded in this script. 
--
--			4 - Make a utility table to hold the table 
--			statistics. This is for uniformity with the 
--			index statistics script; it makes it easier to 
--			write jobs to save both utility tables for auditing 
--			purposes, if desired. The user is given the choice
--			to make the table. The default is 'Y'. The 
--			temporary file used to make the utility table is
--			crdtabst.sql (CReate TABle STatistics).
--			On creation, the table is populated with the 
--			blocks allocated and high water marks.
--
--			5 - Present a report on-screen of the tables with
--			a high water mark greater than the number of blocks
--			in use, sorted by the difference between the two 
--			figures. Such tables take longer to scan, because
--			the blocks no longer in use have to be traversed as
--			well as the blocks that still contain data. (The
--			difference will be mainly the result of deletion,
--			and to a lesser extent the result of row
--			migration.)
--
-- Author		: David Clement
-- Date				: 04-JUN-2004
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- Initialize.
-------------------------------------------------------------------------------
set feedback off
set head off
set verify off

-- These columns will hold the date and time. They have their own query 
-- because there is no single-row query outside a PL/SQL block before their
-- values are needed.
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: Identify tables with out of date stats.
-------------------------------------------------------------------------------

-- Try to remove the temporary table, so that the 'create' will not fail.
set termout off
set feedback off
drop table tmp_table_stats
/
set termout on

-- Create the temporary table without any data in it.
create global temporary table tmp_table_stats
	(tblname varchar2 (30),
	realrowcnt number,
	statrowcnt number)
on commit delete rows
/

-- Populate the temporary table with actual counts of rows and with the
-- row numbers derived from the last 'analyze table' command.
declare
	nRrc number := 0;
	nSrc number := 0;
begin
	for r in 
		(select table_name 
		from user_tables
		where table_name not in 
			('TMP_TABLE_STATS', 
			'UTL_INDEX_STATS',
			'UTL_TABLE_STATS') )
	loop
		execute immediate 'select count (*) '
		|| 'from ' || r.table_name
		into nRrc;
		execute immediate 'select nvl (num_rows, 0) '
		|| 'from user_tables '
		|| 'where table_name = ''' || r.table_name || ''''
		into nSrc;
		insert into tmp_table_stats 
			(tblname, realrowcnt, statrowcnt)
		values (r.table_name, nRrc, nSrc);
	end loop;
end;
/

----------------------------------------------------------------------------
-- Part 2: Report the results to the script operator.
----------------------------------------------------------------------------

-- Terminal settings.
set pagesize 24
set linesize 80
set termout on
set head on

-- Column formatting.
column tn format a30 heading "Table Name"
column rr format 9,999,999 heading "Actual|Rows"
column sr like rr heading "Rows in |Stats"

-- Report titles.
ttitle center 'TABLE MAINTENANCE REPORT #1' skip -
	center 'Identifies Tables to Consider Analyzing' skip -
	center '---------------------------------------------------' skip -
	center 'Date ' format a12 &tdy ' Time ' format a12 &tim -
	'                     Page ' format 99 sql.pno skip -
	center '---------------------------------------------------' skip 

btitle center '----------------------------------------------------' skip -
       	center 'Tables listed above have over 20 percent more' skip -
       	center 'or less rows than the statistics show and have' skip -
	center 'at least 500 rows.' skip

select tblname tn, realrowcnt rr, statrowcnt sr
from tmp_table_stats
where realrowcnt > 500 
and (realrowcnt < statrowcnt * 0.8
	or realrowcnt > statrowcnt * 1.2)
/

-- Turn titles and columns off.
btitle off
ttitle off
set head off

-------------------------------------------------------------------------------
-- Part 3: Run 'analyze' on the tables the user chooses.
-------------------------------------------------------------------------------

-- Get the list of tables to analyze from the user.
prompt
prompt Enter any tables to analyze. To enter a list, separate the tables with 
accept tablist char prompt "quote-comma-quote (',') --> "
prompt

-- Get the percentage to use when estimating statistics.
accept pct number -
prompt "Enter the percentage to use when estimating statistics (30) --> " -
default 30
prompt

-- Put the appropriate commands in a temporary file.
set verify off
set feedback off
spool anodtbls.sql
select 'analyze table ' || tblname 
	|| ' estimate statistics sample ' || &pct || ' percent ' || chr (10)
  || '/' || chr (10)
from tmp_table_stats
where tblname in ('&tablist')
/
spool off

-- Run the commands from the temporary file.
set feedback on
@anodtbls
set feedback off

-----------------------------------------------------------------------------
-- Part 4: Make a utility table to hold the calculated table statistics.
-----------------------------------------------------------------------------

-- Prompt the user about making the table stats table.
accept yn char -
prompt "Create and load the table stats table? (Y|N) --> " -
default 'Y'
prompt

-- Make the table stats table. First use SQL to write SQL into
-- a temporary file.
set termout off
spool crdtabst.sql
select 'drop table utl_table_stats' || chr (10)
|| '/' || chr (10)
|| 'create table utl_table_stats ' || chr (10)
|| ' storage (initial 64K next 64K maxextents unlimited ' || chr (10) 
|| ' pctincrease 0) ' || chr (10)
|| ' tablespace user_data ' || chr (10)
|| ' as select table_name as name, blocks as highwatermark, ' || chr (10)
|| ' empty_blocks as unusedblocks, ' || chr (10)
|| ' blocks + empty_blocks + 1 as allocatedblocks, ' || chr (10)
|| ' 0 as usedblocks ' || chr (10)
|| 'from user_tables ' || chr (10)
|| '/'
from dual 
where 'Y' = '&yn'
/
spool off

-- Run the commands from the temporary file.
-- NOTE A side-effect of the implied 'commit' in the DDL is to
-- empty the global temporary table tmp_table_stats. 
set feedback on
@crdtabst.sql
set feedback off

-----------------------------------------------------------------------------
-- Part 4: Populate the utility table with the current actual block usage.
-----------------------------------------------------------------------------
begin
	for r in 
		(select table_name 
		from user_tables
		where table_name not in 
			('TMP_TABLE_STATS', 
			'UTL_INDEX_STATS',
			'UTL_TABLE_STATS') )
	loop
		execute immediate 'update utl_table_stats '
		|| 'set usedblocks = (select count (distinct '
		|| '	dbms_rowid.rowid_block_number (rowid) '
		|| '	|| dbms_rowid.rowid_relative_fno (rowid) ) '
		|| '	from ' || r.table_name || ') '
		|| 'where name = ' || r.table_name;
	end loop;
end;
/

set termout on
prompt Done.
prompt

-----------------------------------------------------------------------------
-- Step 5: Report the tables with high water marks above actual block
-- usage.
-----------------------------------------------------------------------------

-- Terminal setting.
set head on

-- Column formatting. The 'tn' format previously defined will be re-used.
column hwm format 9,999,999 heading "High|Water|Mark"
column nub like hwm heading "Unused|Blocks"
column ab like hwm heading "Allocated|Blocks"
column ub like hwm heading "Used|Blocks"

-- Report titles.
ttitle center 'TABLE MAINTENANCE REPORT #2' skip -
	center 'Identifies Tables to Consider Truncating' skip -
	center '---------------------------------------------------' skip -
	center 'Date ' format a12 &tdy ' Time ' format a12 &tim -
	'                     Page ' format 99 sql.pno skip -
	center '---------------------------------------------------' skip 

btitle center '----------------------------------------------------' skip -
       	center 'High water mark + unused blocks + 1 = allocated blocks.' skip -
	center 'High water mark - used blocks = the scanning overhead  ' skip -
	center 'that truncating would eliminate.' skip

-- Execute the query that produces the report.
spool reptblhw.lst
select name as tn, 
	highwatermark as hwm,
	unusedblocks as nub,
	allocatedblocks as ab,
	usedblocks as ub
from utl_table_stats
where highwatermark > 0
order by highwatermark - usedblocks desc
/
spool off

-----------------------------------------------------------------------------
-- Leave a clean campsite.
-----------------------------------------------------------------------------

-- Clear the column and title definitions.
clear columns
btitle off
ttitle off

-- Reset terminal controls to conventional values.
set pagesize 14
set linesize 80
set feedback on
set verify on