Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

Monitoring Index Statistics 

Oracle Tips by Burleson 

Chapter 8 Oracle Index Internal Scripting

       SYS_IL0000001597C00001$ LOB    SYSTEM DEF$_TEMP$LOB TABLE U SYSTEM
       SYS_IL0000001588C00006$ LOB    SYSTEM DEF$_LOB      TABLE U SYSTEM
       SYS_IL0000001588C00004$ LOB    SYSTEM DEF$_LOB      TABLE U SYSTEM
       SYS_IL0000001597C00003$ LOB    SYSTEM DEF$_TEMP$LOB TABLE U SYSTEM
       AQ$_QUEUES_CHECK        NORMAL SYSTEM AQ$_QUEUES    TABLE U SYSTEM    NAME
       AQ$_QUEUES_PRIMARY      NORMAL SYSTEM AQ$_QUEUES    TABLE U SYSTEM     OID
       BM_TEST_BITMAP          BITMAP SYSTEM TEST_BITMAP   TABLE N SYSTEM    TEST_COL1

Monitoring Index Statistics  

Under Oracle7, the dba_indexes view was extended to include B-tree level, number of leaf blocks, number of distinct keys, average number of leaf blocks per key, average number of data blocks per key, and the index clustering factor. Under Oracle8 and Oracle8i, columns covering partitions, domain indexes, and function-based indexes where added. Under Oracle9i, columns for index types and join indexes where added, along with a column to show the index status, either DIRECT LOAD or VALID. The TYPE column specifies whether the index is NORMAL, an IOT, an LOB, or a BITMAP index. This is essentially the only indicator for BITMAP-type indexes.

Index statistics generated from the ANALYZE command are stored in the index_stats view. The major

limitation of the index_stats view is that it shows only the index analyzed most recently.

Run the script below if results from all the indexes in a particular schema are needed. An example of a report follows the script.

brown.sql

rem
rem NAME: brown.sql
rem FUNCTION: Analyze indexes and produce stat report
rem FUNCTION: Including browning indicator
rem
rem HISTORY: MRA 6/15/97 Created
rem
COL del_lf_rows_len FORMAT 999,999,999 HEADING 'Deleted Bytes'
COL lf_rows_len     FORMAT 999,999,999 HEADING 'Filled Bytes'
COL browning        FORMAT 999.90      HEADING 'Percent|Browned'
COL height          FORMAT 999,999     HEADING 'Height'

COL blocks          FORMAT 999,999     HEADING 'Blocks'
COL disti                                                                      
nct_keys   FORMAT 999,999,999 HEADING '#|Keys'
COL most_repeated_key FORMAT 999999999 HEADING 'Most|Repeated|Key'
COL used_space      FORMAT 999999999   HEADING 'Used|Space'
COL rows_per_key    FORMAT 999999      HEADING 'Rows|Per|Key'
ACCEPT owner PROMPT 'Enter table owner name: '

SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF RECSEP OFF
SET PAGES 0
TTITLE OFF
DEFINE cr='CHR(10)'
SPOOL index_sz.sql
SELECT
  'CREATE TABLE stat_temp AS SELECT * FROM index_stats;'||&&cr||
  'TRUNCATE TABLE stat_temp;' 
FROM dual;
SELECT     
'ANALYZE INDEX '||owner||'.'||index_name||
' VALIDATE STRUCTURE;'||&&cr||
     'INSERT INTO stat_temp SELECT * FROM index_stats;'||&&cr||
     'COMMIT;'
FROM
     dba_indexes
SEE CODE DEPOT FOR FULL SCRIPT
SPOOL OFF
PROMPT 'Analyzing Indexes'

SET FEEDBACK OFF TERMOUT OFF LINES 132 VERIFY OFF
START index_sz.sql
SET TERMOUT ON FEEDBACK ON VERIFY ON LINES 132 PAGES 58
ttitle "Index Statistics Report"
SPOOL browning.lst
SELECT
     name,
     del_lf_rows_len,
     lf_rows_len,
(del_lf_rows_len/
DECODE((lf_rows_len+del_lf_rows_len),0,1,lf_rows_len+del_lf_rows_len))*100 browning,
     height,
     blocks,
     distinct_keys,
     most_repeated_key,
     used_space,
     rows_per_key
FROM
     stat_temp
SEE CODE DEPOT FOR FULL SCRIPT
SPOOL OFF
SET FEEDBACK ON TERMOUT ON LINES 80 VERIFY ON
HOST del stat_temp

 


 


 

The Oracle script collection contains more than 600 working Oracle scripts.  You can download them immediately at this link:
 


 

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks