From StephenAndert@firsthealth.com Fri, 26 Oct 2001 09:05:42 -0700 From: "Stephen Andert" Date: Fri, 26 Oct 2001 09:05:42 -0700 Subject: RE: i need check fragmentation of index Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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@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 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@ccss.sa.cr        Icq# 30173325>>------------------------------------------------------------> The true is out there in WWW>>> --> Please see the official ORACLE-L FAQ:> http://www.orafaq.com> --> Author: Alexander Ordonez>   INET: aordonez@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@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.http://personals.yahoo.com--Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: Deepak Thapliyal  INET: deepakthapliyal@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@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@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@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).