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).
