Systems Engineering and RDBMS

Index Browning

Posted by decipherinfosys on March 28, 2008

Index Browning is a terminology that is sometimes used in the Oracle world to refer to fragmentation of the indexes. It is essentially a process where the btrieve leaf nodes are deleted thus leaving holes in an index structure. The name comes from the analogy of a tree where when a leaf turns brown after it is dead. Excessive index browning can cause performance issues since one would then need to traverse through a lot more data for range scans of the data to find the data. Such indexes should be rebuilt. Here is a script to help identify such indexes:

Set Doc Off
/*****************************************************************************************
*
* Purpose:  To generate statistics for indexes and get the “browning” report.

* NOTE:  Please plan on some downtime during this activity, as this operation could result in locking the indexes

* Usage:

* 1. Get the list of the indexes for a given table for which you need to run the brown report.

Ex: SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = ‘<table>’

* 2. Run Analyze with Validate Structure option on these indexes. Use the following steps :

Create a temp table to store the index statistics. *
* CREATE TABLE TEMP_IND_STATS AS *
* SELECT * FROM INDEX_STATS WHERE 1=2; *
* *
* i. ANALYZE INDEX <ind_1> VALIDATE STRUCTURE; *
* ii. INSERT INTO TEMP_IND_STATS SELECT * FROM INDEX_STATS; *
* iii. COMMIT; *
* *
* Repeat the above i, ii and iii steps for all the indexes, you are interested in and *
* then run the following SQL to generate the browning report *
* *
* Explanation of ResultSet: *
* Index Name: *
* Name of the Index *
* Deleted Bytes in MB: *
* Total length of all deleted rows in Index measure in MegaBytes *
* Filled Bytes in MB: *
* Percent Browned: *
* Height: *
* Blocks: *
* # Of Keys: *
* Most Repeated Key: *
* Used Space in MB: *
* Rows Per Key: *
* *
*****************************************************************************************/
Set Pages 100 Feedback Off Verify Off Lines 120
COLUMN name FORMAT a30 HEADING ‘Index|Name’
COLUMN deleted_bytes FORMAT 9,999.99 HEADING ‘Deleted|Bytes|in MB’
COLUMN filled_bytes FORMAT 9,999.99 HEADING ‘Filled|Bytes|in MB’
COLUMN browning FORMAT 999.99 HEADING ‘Percent|Browned’
COLUMN height FORMAT 999,999 HEADING ‘Height’
COLUMN blocks FORMAT 999,999 HEADING ‘Blocks’
COLUMN distinct_keys FORMAT 999,999,999 HEADING ‘# Of|Keys’
COLUMN most_repeated_key FORMAT 999999999 HEADING ‘Most|Repeated|Key’
COLUMN used_space FORMAT 9,999.99 HEADING ‘Used|Space|in MB’
COLUMN rows_per_key FORMAT 999999 HEADING ‘Rows|Per|Key’
TTITLE “Index Statistics Report”
SELECT Name,
Del_Lf_Rows_Len/1048576 As deleted_bytes,
Lf_Rows_Len/1048576 As filled_bytes,
(Del_Lf_Rows_Len/Decode((Lf_Rows_Len+Del_Lf_Rows_Len),0,1,Lf_Rows_Len+Del_Lf_Rows_Len))*100 Browning,
Height,
Blocks,
Distinct_Keys,
Most_Repeated_Key,
Used_Space/1048576 As Used_Space,
Rows_Per_Key
FROM Temp_Ind_Stats
WHERE Rows_Per_Key > 0
/
TTITLE OFF
CLEAR COLUMNS
Set Feedback On Verify On

Sorry, the comment form is closed at this time.

 
%d bloggers like this: