 |
|
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:
|
