Re: INDEX SPACE UTILIZATION
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_pctfrom 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