Script:To Report Information on Indexes

Reports index fragmentation statistics:

==========
Script #1:
==========

SET ECHO off
REM NAME:   TFSIFRAG.SQL
REM USAGE:"@path/tfsifrag schema_name index_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT on INDEX_STATS
REM ------------------------------------------------------------------------
REM PURPOSE:
REM    Reports index fragmentation statistics
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM                     Index Fragmentation Statistic
REM
REM    index name        S_EMP_USERID_UK
REM    leaf rows deleted            0
REM    leaf rows in use            25
REM    index badness            0.000
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
def ownr  = &&1
def name  = &&2

ttitle -
  center 'Index Fragmentation Statistic'   skip 2

set heading off

col name                 newline
col lf_blk_rows          newline
col del_lf_rows          newline
col ibadness newline

validate index &ownr..&name;

select
  'index name        '||name,
  'leaf rows deleted '||to_char(del_lf_rows,'999,999,990')  del_lf_rows,
  'leaf rows in use  '||to_char(lf_rows-del_lf_rows,'999,999,990')  lf_blk_rows,
  'index badness     '||to_char(del_lf_rows/(lf_rows+0.00001),'999,990.999') ibadness
from
  index_stats
/

undef ownr
undef name
set verify on


==============
Sample Output:
==============

                         Index Fragmentation Statistic


index name                   S_EMP_USERID_UK
leaf rows deleted            0
leaf rows in use             25
index badness                0.000




==========
Script #2:
==========

SET ECHO off
REM NAME:   TFSISTAT.SQL
REM USAGE:"@path/tfsistat schema_name index_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT on INDEX_STATS
REM ------------------------------------------------------------------------
REM PURPOSE:
REM    Report index statistics.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM                                Index Statistics
REM
REM    S_EMP_USERID_UK
REM    ----------------------------------------------------------
REM    height                          1
REM    blocks                          5
REM    del_lf_rows                     0
REM    del_lf_rows_len                 0
REM    distinct_keys                  25
REM    most_repeated_key               1
REM    btree_space                 1,876
REM    used_space                    447
REM    pct_used                       24
REM    rows_per_key                    1
REM    blks_gets_per_access            2
REM    lf_rows                        25            br_rows               0
REM    lf_blks                         1            br_blks               0
REM    lf_rows_len                   447            br_rows_len           0
REM    lf_blk_len                  1,876            br_blk_len            0
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
def ownr        = &&1
def name        = &&2

ttitle -
  center  'Index Statistics'  skip 2

set heading off

col name   newline
col headsep              newline
col height               newline
col blocks               newline
col lf_rows              newline
col lf_blks        	 newline
col lf_rows_len          newline
col lf_blk_len           newline
col br_rows              newline
col br_blks              newline
col br_rows_len          newline
col br_blk_len           newline
col del_lf_rows          newline
col del_lf_rows_len      newline
col distinct_keys        newline
col most_repeated_key    newline
col btree_space          newline
col used_space    	 newline
col pct_used             newline
col rows_per_key         newline
col blks_gets_per_access newline

validate index &ownr..&name;

select
  name,
  '----------------------------------------------------------'    headsep,
  'height               '||to_char(height,     '999,999,990')     height,
  'blocks               '||to_char(blocks,     '999,999,990')     blocks,
  'del_lf_rows          '||to_char(del_lf_rows,'999,999,990')     del_lf_rows,
  'del_lf_rows_len      '||to_char(del_lf_rows_len,'999,999,990') del_lf_rows_len,
  'distinct_keys        '||to_char(distinct_keys,'999,999,990')   distinct_keys,
  'most_repeated_key    '||to_char(most_repeated_key,'999,999,990') most_repeated_key,
  'btree_space          '||to_char(btree_space,'999,999,990')       btree_space,
  'used_space           '||to_char(used_space,'999,999,990')        used_space,
  'pct_used                     '||to_char(pct_used,'990')          pct_used,
  'rows_per_key         '||to_char(rows_per_key,'999,999,990')      rows_per_key,
  'blks_gets_per_access '||to_char(blks_gets_per_access,'999,999,990') blks_gets_per_access,
  'lf_rows      '||to_char(lf_rows,    '999,999,990')||'        '||+
  'br_rows      '||to_char(br_rows,    '999,999,990')                  br_rows,
  'lf_blks      '||to_char(lf_blks,    '999,999,990')||'        '||+
  'br_blks      '||to_char(br_blks,    '999,999,990')                  br_blks,
  'lf_rows_len  '||to_char(lf_rows_len,'999,999,990')||'        '||+
  'br_rows_len  '||to_char(br_rows_len,'999,999,990')                  br_rows_len,
  'lf_blk_len   '||to_char(lf_blk_len, '999,999,990')||'        '||+
  'br_blk_len   '||to_char(br_blk_len, '999,999,990')                br_blk_len
from
  index_stats
/

undef ownr
undef name
set verify on


==============
Sample Output:
==============

                                Index Statistics
S_EMP_USERID_UK
----------------------------------------------------------
height                          1
blocks                          5
del_lf_rows                     0
del_lf_rows_len                 0
distinct_keys                  	25
most_repeated_key               1
btree_space                 	1,876
used_space                    	447
pct_used                       	24
rows_per_key                    1
blks_gets_per_access            2
lf_rows                		25
br_rows                 	0
lf_blks				1
br_blks                 	0
lf_rows_len           		447
br_rows_len            		0
lf_blk_len          		1,876
br_blk_len              	0




==========
Script #3:
==========

SET ECHO off
REM NAME:   TFSIKEYS.SQL
REM USAGE:"@path/tfsikeys idx_owner table_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT on DBA_IND_COLUMNS and DBA_INDEXES
REM ------------------------------------------------------------------------
REM PURPOSE:
REM Shows the index keys for a particular table.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM             Index Keys Summary
REM
REM    Uniqueness                Index Name                    Column Name
REM    ---------- ----------------------------------------  ------------------
REM    UNIQUE                    SCOTT.S_EMP_ID_PK               ID
REM
REM    UNIQUE                    SCOTT.S_EMP_USERID_UK           USERID
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
def ixowner	= &&1
def tabname	= &&2

ttitle -
   center  'Index Keys Summary'  skip 2

col uniq    format a10 heading 'Uniqueness'  justify c trunc
col indname format a40 heading 'Index Name'  justify c trunc
col colname format a25 heading 'Column Name' justify c trunc

break -
  on indname skip 1 -
  on uniq

select
  ind.uniqueness                  uniq,
  ind.owner||'.'||col.index_name  indname,
  col.column_name                 colname
from
  dba_ind_columns  col,
  dba_indexes      ind
where
  ind.owner = upper('&ixowner')
    and
  ind.table_name = upper('&tabname')
    and
  col.index_owner = ind.owner
    and
  col.index_name = ind.index_name
order by
  col.index_name,
  col.column_position
/

undef ixowner
undef tabname
set verify on


==============
Sample Output:
==============


         Index Keys Summary


Uniqueness                Index Name                    Column Name
---------- ---------------------------------------- ----------------------
UNIQUE                SCOTT.S_EMP_ID_PK                        ID

UNIQUE                SCOTT.S_EMP_USERID_UK                    USERID

Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *