Find indexes browning, rebuild
                                                 Last update (2008-10-13 11:20:44)
                                                                                                                   Date added (2008-02-29 09:22:26)

Summary
Index browning is the process where Btree leaf nodes are deleted leaving holes in the index. Under the Btree structure the vacancies left by data deletion aren't filled. Using the analogy of a tree it is as if the leaf has turned brown because it is dead.

If an index shows excessive browning it will impact performance due to the large number of transversals required to find the required values.

A browned index should be rebuilt.

How to find them?
The concept is very simple, we will use the:analyze index INDEX_NAME validate structure;
command to find the required information, and will be stored in a custom table.

Create the custom table
CREATE TABLE helpdesk.index_usage
(owner       VARCHAR2(30),
NAME        VARCHAR2(30)
,btree_space NUMBER(12)
,pct_used    NUMBER(3)
,del_len     NUMBER(12)
,dt          DATE
,height      number(3)
,lf_rows     number(12)
,del_lf_rows number(12)
,size_mb     number(12)
) 
TABLESPACE helpdesk;
Connect as sys to sqlplus and run the script, for example for AR schema indexes
DECLARE
v_stmt  VARCHAR2(100);
v_count INTEGER(1);
v_size  NUMBER(12);
CURSOR c1 IS 
SELECT owner,index_name FROM dba_indexes WHERE owner = 'AR';
BEGIN 
FOR line IN c1 
LOOP
   SELECT COUNT(*) INTO v_count FROM helpdesk.index_usage WHERE owner=line.owner 
   AND NAME=line.index_name AND btree_space!=-1;
      IF v_count=0 THEN
       v_stmt := 'analyze index '||line.owner||'.'||line.index_name|| ' validate structure';
       BEGIN
       EXECUTE IMMEDIATE v_stmt;
        EXCEPTION
         WHEN OTHERS --If index is unavailable, set btree_space=-1 
                     --as a warning that it was not analyzed and continue to the next one...
            THEN
            INSERT INTO helpdesk.index_usage(owner,NAME,btree_space,dt) 
            VALUES (line.owner,line.index_name,-1,SYSDATE);
        END;
       SELECT SUM(bytes)/1024/1024 INTO v_size FROM dba_segments 
       WHERE owner=line.owner AND segment_name=line.index_name; 
       INSERT INTO helpdesk.index_usage(owner,NAME,btree_space,pct_used,del_len,dt, height,lf_rows,del_lf_rows)
       SELECT line.owner,NAME,btree_space,pct_used,del_lf_rows_len,SYSDATE,height,lf_rows,del_lf_rows 
       FROM index_stats;
       UPDATE helpdesk.index_usage SET size_mb=v_size WHERE owner=line.owner AND NAME=line.index_name;
   END IF;
   IF MOD(c1%rowcount,100)=0 THEN
     COMMIT;
   END IF;
END LOOP;
COMMIT;

END;
/
Now the table HELPDESK.INDEX_USAGE has all the information about analyzed indexes. To find which of them need rebuild execute for example.
SELECT a.* 
FROM helpdesk.index_usage a 
WHERE height>3;

AND

SELECT * FROM helpdesk.index_usage a
WHERE (A.DEL_LF_ROWS/a.lf_rows)> .3
AND A.DEL_LF_ROWS>0
AND A.LF_ROWS>0
AND btree_space > :block_size;

Where :block_size is you database block size (for example 8192)

Posted by: Eleutherios Tsekouras
Reviews
Categories
Filters
Search