REM
/*
 * StretchedTables.Sql (c) Tim Onions 2007
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
 */

SET SERVEROUT ON SIZE 90000
SET VERIFY OFF ECHO OFF

DECLARE
--
	CURSOR CU_tabl IS
	SELECT	owner owner,table_name table_name
	FROM 	all_tables
	WHERE	owner LIKE UPPER('&&1')
	AND	table_name LIKE UPPER('&&2')
	AND	tablespace_name is not null
	UNION ALL
	SELECT	table_owner owner,table_name||' ('||partition_name||')' table_name
	FROM 	all_tab_partitions
	WHERE	table_owner LIKE UPPER('&&1')
	AND	table_name LIKE UPPER('&&2')
	AND	tablespace_name is not null
	ORDER BY 1,2;
--
	R_tabl CU_tabl%ROWTYPE;
--
SEGMENT_OWNER                  VARCHAR2(30);        
SEGMENT_NAME                   VARCHAR2(30);        
SEGMENT_TYPE                   VARCHAR2(20);       
TOTAL_BLOCKS                   NUMBER      ;    
TOTAL_BYTES                    NUMBER      ;    
UNUSED_BLOCKS                  NUMBER      ;    
UNUSED_BYTES                   NUMBER      ;    
LAST_USED_EXTENT_FILE_ID       NUMBER      ;    
LAST_USED_EXTENT_BLOCK_ID      NUMBER      ;    
LAST_USED_BLOCK                NUMBER      ; 
--
FREELIST_GROUP_ID              NUMBER   ;
FREE_BLKS                      NUMBER   ;
SCAN_LIMIT                     NUMBER   ; 
--
L_stretch_lim NUMBER(3) DEFAULT &&3;
L_temp NUMBER;
L_min_blocks PLS_INTEGER DEFAULT 10;
L_act_perf BOOLEAN DEFAULT FALSE;
--
BEGIN

	FOR R_tabl IN CU_tabl LOOP
--
		DBMS_SPACE.UNUSED_SPACE(R_tabl.owner,R_tabl.table_name,'TABLE',
		TOTAL_BLOCKS,
		TOTAL_BYTES,
		UNUSED_BLOCKS,
		UNUSED_BYTES,
		LAST_USED_EXTENT_FILE_ID,
		LAST_USED_EXTENT_BLOCK_ID,
		LAST_USED_BLOCK);
--
		freelist_group_id := 0; -- alter this is you are using multiple freelists
--                                         to the particular freelist you are interested in
		scan_limit :=999999;
		DBMS_SPACE.FREE_BLOCKS(R_tabl.owner,R_tabl.table_name,'TABLE',
		FREELIST_GROUP_ID,
		FREE_BLKS,
		SCAN_LIMIT);
--
		L_temp := (L_stretch_lim/100) *(TOTAL_BLOCKS-UNUSED_BLOCKS-1);
--
		IF 	TOTAL_BLOCKS > L_min_blocks 
		 AND	(TOTAL_BLOCKS-unused_blocks-1) > 0 
		 AND 	free_blks >= L_temp 
--		 AND unused_blocks=0 
		THEN 
			dbms_output.put_line('.');
			dbms_output.put_line('Table '||R_tabl.owner||'.'||R_tabl.table_name||' is stretched!');
			dbms_output.put_line('Total blocks             ='||TO_CHAR(TOTAL_BLOCKS,'999,999'));
			dbms_output.put_line('Total bytes(k)           ='||TO_CHAR(TOTAL_BYTES/1024,'999,999'));
			dbms_output.put_line('Unused bytes(k)          ='||TO_CHAR(UNUSED_BYTES/1024,'999,999'));
			dbms_output.put_line('Blocks above HWM         ='||TO_CHAR(UNUSED_BLOCKS,'999,999'));
			dbms_output.put_line('Blocks below HWM         ='||TO_CHAR(TOTAL_BLOCKS-UNUSED_BLOCKS-1,'999,999'));
			dbms_output.put_line('Free blocks              ='||TO_CHAR(FREE_BLKS,'999,999'));
			dbms_output.put_line('%free wrt used blocks    ='||TO_CHAR(free_blks/(TOTAL_BLOCKS-1) * 100,'999,999')||'%');
			L_act_perf := TRUE;
--
		end if;
	END LOOP;
--
	IF NOT L_act_perf THEN
		dbms_output.put_line('.');
		dbms_output.put_line('No tables found that have more than '||L_min_blocks||' blocks of which '||L_stretch_lim||'% or more are on the freelist');
	END IF;
END;
/

undef 1
undef 2
undef 3

/*
 * A stretched table is one that has had, at one time, a lot of rows in it but no longer does.
 * Oracle records, via the High Water Mark (HWM), the last block ever used to hold data.
 * When a full table scan (FTS) is used to query data from the table Oracle scans all blocks
 * up to the HWM - this can (and does) slow down query performance. This script uses the 
 * DBMS_SPACE built-in package to try and locate tables with HWM problems.
 * HWM problems can be fixed (in 7.3.n) only by either dropping and recreating the table
 * or via the TRUNCATE command. Remember, though, that if data remains in the table you
 * must take steps to secure it before a drop or truncate.
 *
 * NB Stretched tables will have blocks below the HWM and free blocks close together
 *    For the purposes of this script a table is stretched if more nn% of blocks below
 *    HWM are free (80-90% is a good starting point).
 *    At present ignores tables with blocks above the HWM which may not be right.
 *
 * This script will request 3 parameters if not specified on the command line:
 *    1 = owner of tables to check  (wild cards allowable)
 *    2 = name of table to check (wild cards allowable)
 *    3 = % of free blocks considered as being worth reporting on
 *
 * The second part of the script (which uses DBMS_SPACE.FREE_BLOCKS) only works on tables
 * that are not in ASSM tablespaces (Automatic Segment Space Managed) - as such tablespaces
 * do away with freelists. The following error is returned if ASSM is used:
 *  ORA-10618: Operation not allowed on this segment
 *  ORA-06512: at "SYS.DBMS_SPACE", line 152
 *  ORA-06512: at line 54
 *
 * That part must be removed if you want to use the script on ASSM controlled tables.
 *
 * For example if you wish to locate those tables where 90% or more of blocks
 * are "free" (ie on the free list - they may not be empty) set parameter #3 to 90.
 *
 * Example output for a Designer/2000 r1.3.2 repository using 90% is shown below:
 * 
 * Table OWNER1.RM$DEFERRED_CHECKS is stretched!
 * Total blocks             =     117
 * Total bytes(k)           =     234
 * Unused bytes(k)          =       0
 * Blocks above HWM         =       0
 * Blocks below HWM         =     116
 * Free blocks              =     116
 * %free wrt used blocks    =     100%
 * .
 * Table OWNER1.RM$DEFERRED_QUERIES is stretched!
 * Total blocks             =     247
 * Total bytes(k)           =     494
 * Unused bytes(k)          =       0
 * Blocks above HWM         =       0
 * Blocks below HWM         =     246
 * Free blocks              =     246
 * %free wrt used blocks    =     100%
 * 
 * (c) Tim Onions 2007
 * Script used at the database owners own risk.
 */
REM