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!