|
Watch out though! From the 8.1.6
documentation:
"Validating the structure of an object prevents SELECT, INSERT, UPDATE, and
DELETE
statements from concurrently accessing the object. Therefore, do not use this clause on the tables, clusters, and indexes of your production applications during periods of high database activity." I didn't realize that until users started complaining when I tried this a while back. Stephen
>>> [EMAIL PROTECTED] 10/26/01 04:10AM >>> 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, ' 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 ' ' 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, '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) * &BLOCK_SIZE_K) / 1024)) * 100 btuub_meg_pct from index_stats; ------------------------------------------------------- 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 --- Alexander Ordonez <[EMAIL PROTECTED]> 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 > > [EMAIL PROTECTED] Icq# 30173325 > > ------------------------------------------------------------ > The true is out there in WWW > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Alexander Ordonez > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). |
- i need check fragmentation of index Alexander Ordonez
- Re: i need check fragmentation of index Deepak Thapliyal
- RE: i need check fragmentation of index Mark Leith
- Stephen Andert
