Index Health Check

I was recently watching this SQL “Tune-Off” webinar that Jonathan Lewis and Kyle Hailey put together a few years back. As part of this webinar, Jonathan briefly mentions a script he has for looking at the number of keys that exist per leaf block of an index. The idea being that if the majority of your index leaf blocks are mostly empty, then it is a good candidate for being rebuilt (or coalesced or shrunk.. for info on which to choose, check out this post by Richard Foote). Jonathan being his normal generous self, shared this script in the presentation. This is a huge reason why I love the Oracle community. Jonathan could have just as easily kept his script to himself as a “competitive advantage”, but he is more interested in helping others learn. There are a huge number of people in the Oracle community who spend a large amount of time with the majority of the benefit for people they will never meet. For this generosity, I will never be able to thank them enough!

I have taken this script and made it a little easier for me to use. I can pass this an Owner and Table and it will loop through all the Indexes and print the results for each via DBMS_OUTPUT (which can be easily changed to a different method). There is a 3rd parameter as well, where you can optionally pass just a single index if you only care about one.

The script looks like:

DECLARE
    PROCEDURE check_index_health
    (
        owner_in IN VARCHAR2,
        table_in IN VARCHAR2,
        index_in IN VARCHAR2 := null
    )
    IS
        TYPE rec_t IS RECORD(keys_per_leaf PLS_INTEGER, blocks PLS_INTEGER);
        TYPE tbl_t IS TABLE OF rec_t;

        v_predicate VARCHAR2(3000);
        v_cnt PLS_INTEGER := 0;

        FUNCTION indx_health (
            owner_in IN VARCHAR2,
            tbl_in IN VARCHAR2,
            predicate_in IN VARCHAR2,
            object_id_in IN VARCHAR2
        )
        RETURN TBL_T
        IS
            /* Helper function that gets details for index specified by object_id_in */
            tbl TBL_T; 
        BEGIN
            execute immediate '
                select
                    keys_per_leaf, count(*) blocks
                from (
                    select
                        sys_op_lbid('||object_id_in||',''L'',t.rowid) block_id,
                        count(*) keys_per_leaf
                    from
                        '||owner_in||'.'||tbl_in||' t
                    where
                        '||predicate_in||'
                    group by
                        sys_op_lbid('||object_id_in||',''L'',t.rowid)
                )
                group by keys_per_leaf
                order by keys_per_leaf'
            BULK COLLECT INTO
                tbl;

            RETURN tbl;
        END indx_health;
    
        PROCEDURE p (
            text_in IN VARCHAR2
        )
        IS
            /* 
                Helper procedure to output single line of text.  Proceduralized in
                case want to switch to logging to file, etc. 
            */
        BEGIN
            dbms_output.put_line(text_in);
        END p;

        PROCEDURE output (
            index_name_in IN VARCHAR2,
            tbl_in IN TBL_T
        )
        IS
            /*
                Helper procedure to output details for a single index.
                Proceduralized in case want to switch to insert to table, etc.
             */
        BEGIN
            p('Index: '||index_name_in);
            IF tbl_in.COUNT = 0
            THEN
                p('No rows in Index');
            ELSE
                p('KEYS PER LEAF          BLOCKS');
                p('-------------      ---------');
                FOR i IN tbl_in.FIRST .. tbl_in.LAST
                LOOP
                    p(rpad(tbl_in(i).keys_per_leaf,23)||tbl_in(i).blocks);
                END LOOP;
            END IF;
            p(chr(10)||chr(10));
        END output;
    BEGIN
        /* 
            Loop over each index in table, building predicate from columns in index
            and then gathering details about health of index
        */ 
        FOR rec in (
            select
                do.object_id,
                ic.index_owner,
                ic.index_name,
                ic.column_name,
                count(*) over (partition by do.object_id) cnt,
                row_number() over (partition by do.object_id order by 1) rn
            from
                dba_ind_columns ic
                inner join dba_objects do
                    on do.owner = ic.index_owner
                    and do.object_name = ic.index_name
            where
                ic.table_owner = upper(owner_in)
                and ic.table_name = upper(table_in)
                and (ic.index_name = upper(index_in) or index_in is null) 
            order by
                object_id
        )
        LOOP

            v_predicate := v_predicate||' or '||rec.column_name||' is not null'; -- add each column in index to predicate        

            IF rec.cnt = rec.rn -- Last line for index
            THEN
                v_predicate := substr(v_predicate,4); -- take off leading ' or '
                output(rec.index_name, indx_health(owner_in,table_in,v_predicate,rec.object_id)); -- get results for current index
                v_predicate := null; -- reset predicate              
                v_cnt := v_cnt + 1; -- increment index counter
            END IF;

        END LOOP;

        p('Matching indexes found: '||v_cnt);

    END check_index_health;
BEGIN
    check_index_health(
        owner_in => 'CMARTIN2',
        table_in => 'TBL1'
    );
END;

And gives output like:

Index: TBL1_IDX1
KEYS PER LEAF          BLOCKS
-------------          -----------
96                     1
170                    10
187                    1
222                    248
223                    11
224                    9
225                    12
226                    17
227                    12
228                    115
229                    40
230                    7
231                    4
232                    4
233                    2
237                    4
238                    2
239                    5
240                    9
241                    19
242                    143
243                    1258
244                    285
245                    150
246                    66
247                    5
248                    9
249                    3
250                    9



Index: TBL1_IDX2
KEYS PER LEAF          BLOCKS
-------------          -----------
167                    1
220                    1
221                    1106
222                    1056
262                    1
295                    1
296                    368

Which is telling me in the index TBL1_IDX2, there is 1 leaf block pointing to 167 rows in the table, 1 leaf block pointing to 220 rows, 1106 leaf blocks pointing to 221 rows each, etc. I have already put this to use at work and found a few indexes which had thousands of leaf blocks with less than 10 keys in them. I suspected the indexes may need to be rebuilt by the nature of what happens to the table, but this script was able to confirm it.

If you see any issues or areas for improvements, let me know!