: Lists All Indexes that Benefit from a Rebuild

:
 Script. Lists All Indexes that Benefit from a Rebuild
 
 文档 ID:
 122008.1
 类型:
 SCRIPT
 Modified Date:
 11-MAR-2008
 状态:
 PUBLISHED

Abstract
 
This script. determines whether an index is a good candidate for a rebuild or for a bitmap index.  All indexes for a given schema or for a subset of schema's are analyzed (except indexes under SYS and SYSTEM)
 
Product Name, Product Version
 Oracle Server, 7.3.x to 10g
 
Platform
 Platform. Independent
 
Date Created
 15-Feb-2001
 
Instructions
 
Execution Environment:

    

Access Privileges:

     Requires DBA privileges in order to be executed.
Usage:

     sqlplus / @ind_an

Instructions:

     Copy the script. into the file ind_an.sql. Execute the script. from SQL*Plus connected
     with a user with DBA privileges.  The script. requires to parameters:
     1. Name of the output file where the report while be generated
     2. Name of the SCHEMA to be analyzed.

PROOFREAD THIS SCRIPT. BEFORE USING IT! Due to differences in the way text

editors, e-mail packages, and operating systems handle text formatting (spaces,

tabs, and carriage returns), this script. may not be in an executable state

when you first receive it. Check over the script. to ensure that errors of

this type are corrected.
 
 
Description
 
This script. determines whether an index is a good candidate for a rebuild or for a bitmap index.  All indexes for a given schema or for a subset of schema's are

analyzed (except indexes under SYS and SYSTEM).

Sample Output

-------------

 

Owner           Index Name                % Deleted Entries Blevel Distinctivenes

--------------- ------------------------- ----------------- ------ -------------

SMS             PLATMAP_I1                           31.504      1        32.927

SMS             PLATMAP_I2                           27.682      1        29.399

SMS             PLATMAP_I3                           31.237      1        31.237

SMS             PRODMAP_I4                            8.765      5          99.9

SMS             SB_CR_BOM_ITEMS_I1                   34.496      2        97.356

SMS             SB_CR_OS_VERSIONS_I1                 51.942      1        68.063

SMS             SB_CR_RELEASES_I1                    34.584      1        18.426

SMS             TAR_HEAD_I2                          21.728      5        22.344

 

Rebuild the index when:
- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.

Possible candidate for bitmap index:
- when distinctiveness is more than 99%
 
References
  SQL Reference Guide
 
Script
 
REM =============================================================
REM
REM                         rebuild_indx.sql
REM
REM  Copyright (c) Oracle Software, 1998 - 2000
REM
REM  Author  : Jurgen Schelfhout
REM
REM  The sample program in this article is provided for educational
REM  purposes only and is NOT supported by Oracle Support Services.
REM  It has been tested internally, however, and works as documented.
REM  We do not guarantee that it will work for you, so be sure to test
REM  it in your environment before relying on it.
REM
REM  This script. will analyze all the indexes for a given schema
REM  or for a subset of schema's. After this the dynamic view
REM  index_stats is consulted to see if an index is a good
REM  candidate for a rebuild or for a bitmap index.
REM
REM  Database Version : 7.3.X and above.
REM
REM  NOTE:  If running this on 10g, you must exclude the
REM  objects in the Recycle Bin
REM        cursor c_indx is
REM          select owner, table_name, index_name
REM            from dba_indexes
REM           where owner like upper('&schema')
REM             and table_name not like 'BIN$%'
REM             and owner not in ('SYS','SYSTEM');
REM
REM  Additional References for Recycle Bin functionality:
REM  Note.265254.1 Flashback Table feature in Oracle Database 10g
REM  Note.265253.1 10g Recyclebin Features And How To Disable it(_recyclebin)
REM
REM =============================================================

prompt
ACCEPT spoolfile CHAR prompt 'Output-file : ';
ACCEPT schema CHAR prompt 'Schema name (% allowed) : ';
prompt
prompt
prompt Rebuild the index when :
prompt   - deleted entries represent 20% or more of the current entries
prompt   - the index depth is more then 4 levels.
prompt Possible candidate for bitmap index :
prompt   - when distinctiveness is more than 99%
prompt
spool &spoolfile

set serveroutput on
set verify off
declare
 c_name        INTEGER;
 ignore        INTEGER;
 height        index_stats.height%TYPE := 0;
 lf_rows       index_stats.lf_rows%TYPE := 0;
 del_lf_rows   index_stats.del_lf_rows%TYPE := 0;
 distinct_keys index_stats.distinct_keys%TYPE := 0;
 cursor c_indx is
  select owner, table_name, index_name
 from dba_indexes
  where owner like upper('&schema')
    and owner not in ('SYS','SYSTEM');
begin
 dbms_output.enable (1000000);
 dbms_output.put_line ('Owner           Index Name                              % Deleted Entries Blevel Distinctiveness');
 dbms_output.put_line ('--------------- --------------------------------------- ----------------- ------ ---------------');
 c_name := DBMS_SQL.OPEN_CURSOR;
 for r_indx in c_indx loop
  DBMS_SQL.PARSE(c_name,'analyze index ' || r_indx.owner || '.' ||
                 r_indx.index_name || ' validate structure',DBMS_SQL.NATIVE);
  ignore := DBMS_SQL.EXECUTE(c_name);
  select HEIGHT, decode (LF_ROWS,0,1,LF_ROWS), DEL_LF_ROWS,
         decode (DISTINCT_KEYS,0,1,DISTINCT_KEYS)
         into height, lf_rows, del_lf_rows, distinct_keys
  from index_stats;
--
-- Index is considered as candidate for rebuild when :
--   - when deleted entries represent 20% or more of the current entries
--   - when the index depth is more then 4 levels.(height starts counting from 1 so > 5)
-- Index is (possible) candidate for a bitmap index when :
--   - distinctiveness is more than 99%
--
  if ( height > 5 ) OR ( (del_lf_rows/lf_rows) > 0.2 ) then
    dbms_output.put_line (rpad(r_indx.owner,16,' ') || rpad(r_indx.index_name,40,' ') ||
                         lpad(round((del_lf_rows/lf_rows)*100,3),17,' ') ||
                          lpad(height-1,7,' ') || lpad(round((lf_rows-distinct_keys)*100/lf_rows,3),16,' '));
  end if;
 end loop;

 DBMS_SQL.CLOSE_CURSOR(c_name);
end;
/

spool off

set verify on
 
Disclaimer
 EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS ALL WARRANTIES OF ANY KIND, WHETHER  EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.
ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY TIME WITHOUT NOTICE.

Limitation of Liability
 IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20542911/viewspace-622666/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20542911/viewspace-622666/