Watch out though! From the 8.1.6
documentation:
"Validating the structure of an object prevents SELECT, INSERT, UPDATE, and
DELETEstatements from concurrently accessing the object. Therefore, do not
use this clause on thetables, clusters, and indexes of your production
applications during periods of highdatabase activity."
I didn't realize that until users started complaining when I tried this
a while back.
Stephen
>>> mark_at_cool-tools.co.uk 10/26/01 04:10AM
>>>Alex,This script is based around an analyze index .....
validate structure asmentined by
Deepak-------------------------------------------------------set
verify offset pagesize 35set linesize 132set pause onset pause
'Hit enter to continue'set feedback offset showmode offset echo
offset space 0set heading offset termout off pause
offcolumn blsk new_value BLOCK_SIZE_Kselect value / 1024 blskfrom
v$parameterwhere name = 'db_block_size';set termout on pause
offPROMPTACCEPT USER_INPUT1 CHAR PROMPT 'Please enter a index to
analyze:>'ACCEPT USER_INPUT2 CHAR PROMPT 'Please enter a owner to
analyze:>'PROMPTPROMPT Working, Please
wait.....PROMPTanalyze 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_ACCESSfrom
index_stats;col
NAME
format a30 fold_aftercol
dummy_col_0
fold_aftercol
dummy_col_1
fold_aftercol
dummy_col_2
fold_aftercol
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 onselect 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) / 1024bt_meg,
'
/',
((LF_BLKS + BR_BLKS) / BLOCKS) * 100bt_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) / 1024btuu_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_pctfrom
index_stats;-------------------------------------------------------HTHMark-----Original
Message-----ThapliyalSent: Thursday, October 25, 2001 22:16To:
Multiple recipients of list ORACLE-Lhi @lexone of the good ways
to check this is to do a analyzeindex .. validate structure and look at the
statisticsfrom index_statsDeepak--- Alexander Ordonez
<aordonez_at_ccss.sa.cr> wrote:> hi gurus, how check the fragmentation
on index...??> ahy idea???> please i need your
help!!>>
@lex>------------------------------------------------------------>
Lic. Alexander Ordsqez Arroyo> Caja Costarricense del Seguro
Social>> Soporte Ticnico - Divisisn de
Informatica>> Telefono: 295-2004, San Josi, Costa
Rica>>
Aordonez_at_ccss.sa.cr Icq#
30173325>>------------------------------------------------------------>
The true is out there in WWW>>> --> Please see the
official ORACLE-L FAQ:> <A
href="">
http://www.orafaq.com> -->
Author: Alexander Ordonez> INET:
aordonez_at_ccss.sa.cr>> 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).__________________________________________________Do
You Yahoo!?Make a great connection at Yahoo! Personals.<A
href="">
http://personals.yahoo.com--Please
see the official ORACLE-L FAQ: <A
href="">
http://www.orafaq.com--Author: Deepak
Thapliyal INET: deepakthapliyal_at_yahoo.comFat City Network
Services -- (858) 538-5051 FAX: (858) 538-5051San
Diego, California -- Public Internet
access / Mailing
Lists--------------------------------------------------------------------To
REMOVE yourself from this mailing list, send an E-Mail messageto:
ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list
you want to be removed from). You mayalso 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.ukFat City
Network Services -- (858) 538-5051 FAX: (858)
538-5051San Diego, California --
Public Internet access / Mailing
Lists--------------------------------------------------------------------To
REMOVE yourself from this mailing list, send an E-Mail messageto:
ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list
you want to be removed from). You mayalso send the HELP command for
other information (like subscribing).
Received on Fri Oct 26 2001 - 11:05:42 CDT
