| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: i need check fragmentation of index
Alex,
This script is based around an analyze index ..... validate structure as mentined by Deepak
set verify off
set pagesize 35
set linesize 132
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 off
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 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,'Expected number of consistent mode block gets per row = ' || BLKS_GETS_PER_ACCESS
' 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,
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 ' '
set pause on
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,
((LF_BLKS + BR_BLKS) / BLOCKS) * 100
bt_meg_pct,
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) * &BLOCK_SIZE_K) /
1024)) * 100 btuub_meg_pct
HTH Mark
-----Original Message-----
Thapliyal
Sent: Thursday, October 25, 2001 22:16
To: Multiple recipients of list ORACLE-L
hi @lex
one of the good ways to check this is to do a analyze
index .. validate structure and look at the statistics
from index_stats
Deepak
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: deepakthapliyal_at_yahoo.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: mark_at_cool-tools.co.uk Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Oct 26 2001 - 05:00:37 CDT
 |
 |