| 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: Index fragmentation
This old script might work:
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,
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_pctfrom index_stats;
-----Original Message-----
From: Seema Singh [mailto:oracledbam_at_hotmail.com]
Sent: Tuesday, May 29, 2001 3:06 PM
To: Multiple recipients of list ORACLE-L
Subject: Index fragmentation
Hi Gurus
How I can know about Index fragmentation?
Thanks in advance for reply
-Seema
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: oracledbam_at_hotmail.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).Received on Tue May 29 2001 - 19:12:15 CDT
 |
 |