Title: Reporting Index Storage Usage
Author:Steven Orr, a principal consultant for TechWise Consulting, Inc, in San Jose, California. Reporting on table storage is easily accomplished by analyzing statistics by schema and querying the DBA_TABLES view (or by other means). Reporting index storage is not so easy. The DBA_INDEXES view does not contain storage information like the DBA_TABLES view. However, Oracle does supply the DBMS_SPACE package to get index storage information. Unfortunately this package only reports storage for an individual index. The script below is provided to report on all indexes for a schema or for the entire database. The output of this script will help to identify which indexes have improper storage parameters and should be rebuilt using the "SQL> ALTER INDEX REBUILD" command. To analyze storage growth over time (trend analysis) you may want to capture the output in a table. I capture statistics weekly with the same user/schema that I use for Oracle Enterprise Manager. If you want to use a different schema or table name be sure to modify the create procedure code accordingly.Source/Text/Comments
Here's how to implement:
0) First verify you have implemented the necessary DBMS_UTILITY, DBMS_SPACE and UTL_FILE packages from Oracle. Also verify you have implemented the path for UTL_FILE in the init.ora (utl_file_dir=).
1) Create the table to capture the results. (Optional)
drop table oem.oem_index_hist cascade constraints;
create table oem.oem_index_hist
(sample_id number not null,
sample_date date default sysdate not null,
owner varchar2(30) not null,
index_name varchar2(30) not null,
total_blocks number,
unused_blocks number,
used_blocks number,
total_extents number,
initial_extent_blocks number,
next_extent_blocks number,
pct_increase number,
max_extents number,
ini_trans number,
max_trans number,
freelists number,
freelist_groups number,
blevel number,
leaf_blocks number,
distinct_keys number,
avg_leaf_blocks_per_key number,
avg_data_blocks_per_key number,
clustering_factor number)
pctfree 5 pctused 75
tablespace oem
storage (initial 1M next 64K pctincrease 0);
alter table oem.oem_index_hist add primary key
(sample_id, owner, index_name);
drop sequence oem.index_sample_id;
create sequence oem.index_sample_id
increment by 1 start with 1 nomaxvalue nocycle;
2) Create the procedure to report on index storage.
create or replace
procedure idxusage (s_path IN varchar2,
s_filename IN varchar2,
s_schema IN varchar2,
s_output_flag IN varchar2) as
--
-- idxusage stored procedure.
-- Author: Steven K. Orr, TechWise Consulting, Inc.
-- email: SteveOrr@TechWise.com
-- Date: 3/1/99.
-- Purpose: Report and/or capture index storage usage statistics.
--
-- Usage:
-- 1) To report on all indexes (other than for sys or system)
-- specify 'ALL' for s_schema.
-- 2) The values for s_output_flag should be:
-- 'TableOnly'; 'FileOnly'; or 'Both'.
-- If choosing TableOnly pass empty strings for first 2 parms.
-- 3) Example:
-- SQL> execute idxusage('/utl_file_dir','idx2.txt','USER','Both');
--
-- Dependencies:
-- 1) This procedure uses the DBMS_SPACE package from Oracle.
-- The account executing this procedure must have the
-- "ANALYZE ANY" and 'SELECT ANY' system privileges.
-- 2) This procedure uses the UTL_FILE package from Oracle.
-- s_path MUST be specified in the init.ora,
-- i.e. utl_file_dir =
-- 3) To capture the results you must have privileges for the oem
-- tableand sequence referenced below. (DDL provided separately.)
-- 4) You should analyze statistics prior to this executing
-- procedure.
-- Generally this procedure should be executed on weekends just
-- after analyzing statistics by schema.
--
--
-- Declaratons:
db_blk_size number;
initblks number;
nextblks number;
sampleID number;
rowsCommited number := 0 ;
commitPoint number := 1000 ;
hdrBreak varchar2(30) := 'FirstRow' ;
dbname varchar2(30);
maxext varchar2(5);
hndOutFile UTL_FILE.FILE_TYPE;
s_owner varchar2(30);
sample_date DATE := sysdate ;
InvalidOutputOption EXCEPTION ;
-- Unused space parameters for DBMS_SPACE.UNUSED_SPACE procedure...
USP1 number;
USP2 number;
USP3 number;
USP4 number;
USP5 number;
USP6 number;
USP7 number;
-- Declare cursors...
CURSOR schema_idx_cur IS
select dba_indexes.owner ownr,
dba_indexes.index_name inam,
dba_segments.blocks blks,
dba_segments.extents exts,
dba_indexes.initial_extent init,
dba_indexes.next_extent nxt,
dba_indexes.pct_increase pcti,
dba_indexes.max_extents maxe,
dba_indexes.ini_trans itrans,
dba_indexes.max_trans mtrans,
dba_indexes.freelists freelis,
dba_indexes.freelist_groups freelg,
dba_indexes.blevel btreelev,
dba_indexes.leaf_blocks leafblks,
dba_indexes.distinct_keys distkey,
dba_indexes.avg_leaf_blocks_per_key albpk,
dba_indexes.avg_data_blocks_per_key adbpk,
dba_indexes.clustering_factor clufac
from dba_indexes, dba_segments
where dba_segments.owner = dba_indexes.owner
and dba_segments.segment_name = dba_indexes.index_name
and dba_segments.segment_type = 'INDEX'
and dba_indexes.owner not in ('SYS','SYSTEM')
and dba_indexes.owner LIKE s_owner
order by ownr, blks desc, inam
;
BEGIN
-- Validate output option...
IF ((s_output_flag <> 'TableOnly')
AND (s_output_flag <> 'FileOnly')
AND (s_output_flag <> 'Both'))
THEN RAISE InvalidOutputOption ;
END IF ;
-- Get db_block_size and db_name...
select value into db_blk_size from v$parameter
where name = 'db_block_size';
select value into dbname from v$parameter where name = 'db_name';
-- Set the owner parameter...
s_owner := upper(s_schema) ;
IF s_owner = 'ALL' THEN
s_owner := '%' ;
ELSE
s_owner := s_owner||'%';
END IF ;
-- Open the output file and create a header...
IF s_output_flag <> 'TableOnly' THEN
hndOutFile := UTL_FILE.FOPEN(s_path, s_filename, 'W');
UTL_FILE.PUTF(hndOutFile,
'INDEX USAGE REPORT: DATABASE '||dbname||'. Reported on '||sample_date||
'\n(Storage numbers in database blocks of '||db_blk_size||' bytes.)');
END IF ;
-- Get unique ID from the sequence...
IF s_output_flag <> 'FileOnly' THEN
select oem.index_sample_ID.nextval into sampleID from sys.dual;
END IF ;
-- ------------------ LOOP ------------------------
-- Loop through the cursor...
FOR irow IN schema_idx_cur LOOP
-- Do a header break on the index owner...
IF hdrBreak <> irow.ownr THEN
hdrBreak := irow.ownr ;
UTL_FILE.PUTF(hndOutFile,
'\n\nINDEXES ON SCHEMA '||hdrBreak||'\n'||
'Index Total Unused Used Total Init Next Max Pct\n'||
'Name Blocks Blocks Blocks Extent Extent Extent Ext Inc\n'||
'------------------------------ ------ ------ ------ ------ ------ ------ ----- ---\n');
END IF ;
-- Get space usage on the index...
DBMS_SPACE.UNUSED_SPACE(irow.ownr,irow.inam,'INDEX',
USP1,USP2,USP3,USP4,USP5,USP6,USP7);
-- Calculate/Format the results...
initblks := irow.init/db_blk_size ;
nextblks := irow.nxt/db_blk_size ;
IF irow.maxe > 99999 THEN
maxext := '>100K' ;
ELSE
maxext := irow.maxe ;
END IF ;
-- Output the results...
IF s_output_flag <> 'TableOnly' THEN
UTL_FILE.PUT_LINE(hndOutFile,
RPAD(irow.inam,30)|| LPAD(USP1,7) || LPAD(USP3,7) ||
LPAD(USP1-USP3,7) || LPAD(irow.exts,7) || LPAD(initblks,7) ||
LPAD(nextblks,7) || LPAD(maxext,6) || LPAD(irow.pcti,4));
END IF ;
-- Save the results...
IF s_output_flag <> 'FileOnly' THEN
INSERT INTO oem.oem_index_hist
(sample_id,
sample_date,
owner,
index_name,
total_blocks,
unused_blocks,
used_blocks,
total_extents,
initial_extent_blocks,
next_extent_blocks,
pct_increase,
max_extents,
ini_trans,
max_trans,
freelists,
freelist_groups,
blevel,
leaf_blocks,
distinct_keys,
avg_leaf_blocks_per_key,
avg_data_blocks_per_key,
clustering_factor)
VALUES
(sampleID,
sample_date,
irow.ownr,
irow.inam,
USP1,
USP3,
USP1-USP3,
irow.exts,
initblks,
nextblks,
irow.pcti,
irow.maxe,
irow.itrans,
irow.mtrans,
irow.freelis,
irow.freelg,
irow.btreelev,
irow.leafblks,
irow.distkey,
irow.albpk,
irow.adbpk,
irow.clufac );
-- COMMIT every commitPoint rows...
rowsCommited := rowsCommited + 1;
IF (MOD(rowsCommited, commitPoint) = 0 ) THEN
COMMIT;
END IF;
END IF ;
-- -------------- END LOOP ------------------------
END LOOP;
-- Close the output file.
IF s_output_flag <> 'TableOnly' THEN
UTL_FILE.FCLOSE(hndOutFile);
END IF ;
COMMIT ; -- commit the remaining rows if any.
EXCEPTION
WHEN InvalidOutputOption THEN
dbms_output.put_line(s_output_flag ||
' is not a valid output option.');
dbms_output.put_line(
'Valid output options are: TableOnly; FileOnly; or Both');
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('There are no indexes for that schema.');
WHEN OTHERS THEN
dbms_output.put_line('IDXUSAGE Exception:');
dbms_output.put_line(SQLERRM);
END;
/
3) Analyze statistics
SQL> execute DBMS_UTILITY.ANALYZE_SCHEMA('USERNAME','COMPUTE');
4) Execute the procedure.
SQL> execute idxusage('/utl_file_dir','idx2.txt','USER','Both');