Re: INDEX SPACE UTILIZATION

From: Brian P. Mac Lean <brian.maclean_at_teldta.com>
Date: 1996/06/28
Message-ID: <31D4D083.55A1_at_teldta.com>


Roger Harris wrote:
>
> mkulkarn_at_na.stratus.com wrote:
> >
> > Is there a way to find how much of allocated space the indexes are
> > really using. One way I have tried is to issue command "analyse index
> > validate structure" and then query from index_stats table
> > (btree_space,used_space and blocks). But for some reason this table
> > holds only one row at a time. So when I analyse another index, the
> > data for previous index is lost. I would like to write a script which
> > will run every morning and give me list of indexes which have utilized
> > more than 80% of the allocated space so that I can take appropriate
> > action.
>
> I wrote a PL/SQL procedure that uses the DBMS_DDL supplied package to analyze
> each index in "Estimate" mode. I then select the leaf_blocks column from
> user_indexes. Add one for the root block, and there you have it.
>
> Send e-mail if you want more details.
> --
> Roger Harris
> Oracle DBA
> GE Credit Corp
> Danbury, CT

The index_stats table will/does/always has held 1 row at a time. I have handled this by dynamically creating/executing a *.sql script (sql from sql) and after each analyze execute “insert into my_index_stats select ‘index_name’, a.* from index_stats a”. Once that was done I had a second script that reported whatever I wanted from the "my_index_stats" table. The following may give you some ideas on what’s possible.....

/* BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN */ set verify off
set pagesize 23
set linesize 80
set pause on
set pause 'Hit enter to continue'
set feedback off
set showmode off
set echo off

set space 0
set heading off

set termout off pause off
column blsk new_value BLOCK_SIZE_K
select value / 1024 blsk
from v$parameter
where name = 'db_block_size';
set termout on pause on

PROMPT
ACCEPT USER_INPUT1 CHAR PROMPT 'Please enter a index to analyze:>' ACCEPT USER_INPUT2 CHAR PROMPT 'Please enter a owner to analyze:>' PROMPT
PROMPT Note: an index of 20,000 records takes about 10 seconds PROMPT
PROMPT Working, Please wait.....
PROMPT analyze index &USER_INPUT2..&USER_INPUT1 validate structure;

col ROWS_PER_KEY          format 999.99     heading '      '
col BLKS_GETS_PER_ACCESS  format 99,999.99  heading '         '

select

'**************************************************************************',
'                                    Name of the index = ' || NAME,
'                                 Height of the b-tree = ' || HEIGHT,
'                        Blocks allocated to the index = ' || BLOCKS,
'            Number of leaf rows (values in the index) = ' || LF_ROWS,
'                  Number of leaf blocks in the b-tree = ' || LF_BLKS,
'              Sum of the lengths of all the leaf rows = ' || LF_ROWS_LEN,
'                        Useable space in a leaf block = ' || LF_BLK_LEN,
'                                Number of branch rows = ' || BR_ROWS,
'                Number of branch blocks in the b-tree = ' || BR_BLKS,
'Sum of lengths of all the branch blocks in the b-tree = ' || BR_ROWS_LEN,
'                      Useable space in a branch block = ' || BR_BLK_LEN,
'             Number of deleted leaf rows in the index = ' || DEL_LF_ROWS,
'        Total length of all deleted rows in the index = ' || DEL_LF_ROWS_LEN,
'                 Number of distinct keys in the index = ' || DISTINCT_KEYS,
'     How many times the most repeated key is repeated = ' || MOST_REPEATED_KEY,
'        Total space currently allocated in the b-tree = ' || BTREE_SPACE,
'Totl space that is currently being used in the b-tree = ' || USED_SPACE,
'% of space allocated in the b-tree that is being used = ' || PCT_USED,
'              Average number of rows per distinct key = ', ROWS_PER_KEY,
'Expected number of consistent mode block gets per row = ', BLKS_GETS_PER_ACCESS, ' ' from index_stats;
col NAME                 format a30       fold_after
col dummy_col_0                           fold_after
col dummy_col_1                           fold_after
col dummy_col_2                           fold_after
col LF_ROWS              format 9,999,990            heading '         '
col DEL_LF_ROWS          format 9,999,990            heading '         '
col BR_ROWS              format 9,999,990            heading '         '
col DISTINCT_KEYS        format 9,999,990            heading '         '
col MOST_REPEATED_KEY    format 9,999,990            heading '         '
col ROWS_PER_KEY         format 9,999,990            heading '         '
col HEIGHT               format 0         fold_after heading '          '
col BLKS_GETS_PER_ACCESS format 99,999.99 fold_after heading '         '
col LF_BLK_LEN           format 9,990     fold_after heading '     '
col lf_row_size          format 990       fold_after heading '      '
col lf_row_per_blk       format 990       fold_after heading '      '
col BR_BLK_LEN           format 9,990     fold_after heading '     '
col br_row_size          format 990       fold_after heading '      '
col br_row_per_blk       format 990       fold_after heading '      '

col meg                  format 999.90    fold_after heading '      '
col lf_meg               format 999.90               heading '      '
col lf_meg_pct           format 99.90     fold_after heading '      '
col br_meg               format 999.90               heading '      '
col br_meg_pct           format 99.90     fold_after heading '      '
col uu_meg               format 999.90               heading '      '
col uu_meg_pct           format 99.90     fold_after heading '      '
col bt_meg               format 999.90               heading '      '
col bt_meg_pct           format 99.90     fold_after heading '      '
col btu_meg              format 999.90               heading '      '
col btu_meg_pct          format 99.90     fold_after heading '      '
col btuu_meg             format 999.90               heading '      '
col btuu_meg_pct         format 99.90     fold_after heading '      '
col btuub_meg            format 999.90               heading '      '
col btuub_meg_pct        format 99.90     fold_after heading '      '

select

 '************************************************************************' dummy_col_0,
 'Index Name.........', NAME, ' ' dummy_col_1,
 'Leaf Rows..........', LF_ROWS, '     ',
 'Leaf Block Size........', LF_BLK_LEN,
 'Deleted Leaf Rows..', DEL_LF_ROWS, '     ',
 'Leaf Row Size..........', LF_ROWS_LEN / LF_ROWS lf_row_size,
 'Branch Rows........', BR_ROWS, '     ',
 'Leaf Rows Per Block....', LF_BLK_LEN / (LF_ROWS_LEN / LF_ROWS) lf_row_per_blk,
 'Distinct Keys......', DISTINCT_KEYS, '     ',
 'Branch Block Size......', BR_BLK_LEN,
 'Max Common Key.....', MOST_REPEATED_KEY, '     ',
 'Branch Row Size........', BR_ROWS_LEN / (BR_ROWS + 1) br_row_size,
 'Avg Common Key.....', ROWS_PER_KEY, '     ',
 'Branch Rows Per Block..', BR_BLK_LEN / ((BR_ROWS_LEN / (BR_ROWS + 1)) + 1) br_row_per_blk,
 'Height Of B-Tree...', HEIGHT,
 'Reads Per Access...', BLKS_GETS_PER_ACCESS,
 ' ' dummy_col_2,
 'Index Meg................', (BLOCKS * &BLOCK_SIZE_K) / 1024 meg,
 'Leaf Meg/Pct.............', (LF_BLKS * &BLOCK_SIZE_K) / 1024 lf_meg, ' /',
                              (LF_BLKS / BLOCKS) * 100 lf_meg_pct,
 'Branch Meg/Pct...........', (BR_BLKS * &BLOCK_SIZE_K) / 1024 br_meg, ' /',
                              (BR_BLKS / BLOCKS) * 100 br_meg_pct,
 'Unused Meg/Pct...........', ((BLOCKS - (LF_BLKS + BR_BLKS)) * &BLOCK_SIZE_K) / 1024 uu_meg, ' /',
                              ((BLOCKS - (LF_BLKS + BR_BLKS)) / BLOCKS) * 100 uu_meg_pct,
 'B-Tree Meg/Pct...........', ((LF_BLKS + BR_BLKS) * &BLOCK_SIZE_K) / 1024 bt_meg, ' /',
                              ((LF_BLKS + BR_BLKS) / BLOCKS) * 100 bt_meg_pct,
 'B-Tree Used Meg/Pct......', (USED_SPACE / 1024) / 1024 btu_meg, ' /',
                              PCT_USED btu_meg_pct,
 'B-Tree UnUsed Meg/Pct....', ((BTREE_SPACE - USED_SPACE) / 1024) / 1024 btuu_meg, ' /',
                              100 - PCT_USED btuu_meg_pct,
 'B-Tree UnUsable Meg/Pct..', (DEL_LF_ROWS_LEN / 1024) / 1024 btuub_meg, ' /',
                              (((DEL_LF_ROWS_LEN / 1024) / 1024) /
                              (LF_BLKS + BR_BLKS)) * 100 btuub_meg_pct
from index_stats;

exit;
/* END END END END END END END END END */ brian.maclean_at_teldta.com Received on Fri Jun 28 1996 - 00:00:00 CEST

Original text of this message