Title: Rebalancing Indexes
Author:Graham Thornton, an Oracle administrator and
developer for Taylor Management Systems Inc., in Chicago, Illinios.
This package contains a suite of procedures and functions to automatically rebuild all
indexes for a given schema that:
Have more than 5 extents
Have more than 25% of entries deleted
Have a height greater than 4
Are in the wrong tablespace
Indexes are rebuilt with new storage parameters than reduce them to a single extent in
the tablespace specified. Because indexes are rebuilt rather than dropped and
recreated, indexes used for constraint implementation should not pose a problem.
Rebuild is invoked by calling:
SQL>exec pIndexMan.Balance( , );
Some procedure require special privilege (see source) and so should be executed from a
privileged account with use of a sizable rollback segment. For large databases this code
should not be executed during peak load times.
Code is intended as a basic tool and a basis for future improvements, which could
include:
Improving the index performance analysis
Changing indexes from recoverable to unrecoverable and vice-versa
Anything else that would be useful
Code uses the rebuild option so requires Oracle 7.3 or greater.
Disclaimer: Use on database - especially production databases - is at DBAs own risk.
Source/Text/Comments
/* Auto-Rebalancing Indexes */
/* Graham C Thornton - Feb 99 */
/* requires ANALYZE ANY, DROP ANY INDEX, CREATE ANY INDEX, ALTER ANY INDEX privs */
create or replace package pIndexMan as
function Execute( sCommand in VARCHAR2 ) return INTEGER;
procedure Validate(
sIndexName in DBA_INDEXES.INDEX_NAME%type
, sOwner in DBA_INDEXES.OWNER%type
);
procedure Rebuild(
sIndexName in DBA_INDEXES.INDEX_NAME%type
, sOwner in DBA_INDEXES.OWNER%type
, sTablespaceName in DBA_INDEXES.TABLESPACE_NAME%type
, nIndexSize in INTEGER
);
function Analyze(
sIndexName in DBA_INDEXES.INDEX_NAME%type
, sOwner in DBA_INDEXES.OWNER%type
, sTablespaceName in DBA_INDEXES.TABLESPACE_NAME%type
) return INTEGER;
procedure Balance(
sOwner in DBA_INDEXES.OWNER%type
, sTablespaceName in DBA_INDEXES.TABLESPACE_NAME%type
);
end;
/
create or replace package body pIndexMan as
function Execute( sCommand in VARCHAR2 ) return INTEGER
as
nCursor INTEGER;
nNumRows INTEGER;
begin
/* open the command cursor */
nCursor := dbms_sql.open_cursor;
if( dbms_sql.is_open( nCursor )) then
dbms_sql.parse( nCursor, sCommand, dbms_sql.NATIVE );
nNumRows := dbms_sql.execute( nCursor );
end if;
/* close the command cursor */
dbms_sql.close_cursor( nCursor );
return nNumRows;
end Execute;
procedure Validate(
sIndexName in DBA_INDEXES.INDEX_NAME%type
, sOwner in DBA_INDEXES.OWNER%type
) as
nNumRows INTEGER;
sCommand VARCHAR2(255);
begin
/* build the command string */
select 'validate index '||sOwner||'.'||sIndexName
into sCommand
from dual;
nNumRows := pIndexMan.Execute( sCommand );
end Validate;
procedure Rebuild(
sIndexName in DBA_INDEXES.INDEX_NAME%type
, sOwner in DBA_INDEXES.OWNER%type
, sTablespaceName in DBA_INDEXES.TABLESPACE_NAME%type
, nIndexSize in INTEGER
) as
nNumRows INTEGER;
sCommand VARCHAR2(255);
nNextExtent INTEGER;
begin
/* calculate the next extent size */
select round( nIndexSize / 2 ) into nNextExtent from dual;
/* build the command string */
select 'alter index '||sOwner||'.'||sIndexName||
' rebuild tablespace '||sTablespaceName||
' storage( initial '||nIndexSize||' next '||nNextExtent||
' pctincrease 0 )'
into sCommand
from dual;
/* rebuild the index */
nNumRows := pIndexMan.Execute( sCommand );
end Rebuild;
function Analyze(
sIndexName in DBA_INDEXES.INDEX_NAME%type
, sOwner in DBA_INDEXES.OWNER%type
, sTablespaceName in DBA_INDEXES.TABLESPACE_NAME%type
) return INTEGER as
nSize INTEGER;
nHeight INTEGER;
nDelRatio NUMBER;
nExtents INTEGER;
sCurrentSpace DBA_EXTENTS.TABLESPACE_NAME%type;
begin
/* default is don't rebuild */
nSize := 0;
/* calculate the index performance */
select HEIGHT, ( DEL_LF_ROWS /decode( LF_ROWS,0,1,LF_ROWS ))*100
into nHeight, nDelRatio
from INDEX_STATS;
/* calculate the number of extents */
select count(*), TABLESPACE_NAME
into nExtents, sCurrentSpace
from DBA_EXTENTS
where SEGMENT_NAME = sIndexName
and SEGMENT_TYPE = 'INDEX'
and OWNER = sOwner
group by TABLESPACE_NAME;
/* check the index performance */
if( nHeight > 4 OR nDelRatio > 25 OR nExtents > 5 OR sCurrentSpace != sTablespaceName ) then
/* calculate the size of the index in bytes */
select XS.BLOCKS * VP.VALUE
into nSize
from INDEX_STATS XS, V$PARAMETER VP
where VP.NAME = 'db_block_size';
end if;
return nSize;
end Analyze;
procedure Balance(
sOwner in DBA_INDEXES.OWNER%type
, sTablespaceName in DBA_INDEXES.TABLESPACE_NAME%type
) as
cursor NxtIdx is
select INDEX_NAME
from DBA_INDEXES
where OWNER = sOwner;
sIndexName DBA_INDEXES.INDEX_NAME%type;
nIndexSize INTEGER;
begin
/* open and fetch the index cursor */
open NxtIdx;
fetch NxtIdx into sIndexName;
/* if we cant run this job get the next one */
while( NxtIdx%FOUND ) loop
/* validate the index */
pIndexMan.Validate( sIndexName, sOwner );
/* get the size of the new index */
nIndexSize := pIndexMan.Analyze( sIndexName, sOwner, sTablespaceName );
/* check if the index needs rebuilding */
if( nIndexSize!=0 ) then
pIndexMan.Rebuild( sIndexName, sOwner, sTablespaceName, nIndexSize );
end if;
/* fetch the next index */
fetch NxtIdx into sIndexName;
end loop;
/* close the index cursor */
close NxtIdx;
end Balance;
end;
/