Oracle Scratchpad

Index Efficiency 3

Go to July 2022 update

A quick and dirty script that you can use for fairly detailed analysis of how well index leaf blocks are used. But it reads every used index block using an index fast full scan. Note that it only reports leaf blocks which have rows in them – so it won’t show zeros for empty leaf blocks that are both in the structure and on the freelist or marked as free in the bitmap space management blocks, even though it will visit them.

(As copied by Oracle Corp. in Metalink note 989186.1)

Make sure you read the notes.

rem     Script:        index_efficiency_3.sql
rem     Author:        Jonathan Lewis
rem     Dated:         Sept 2003
rem     Purpose:       Example of how to check leaf block packing
rem	Last tested
rem	Not tested
rem	Not applicable
rem	-- no sys_op_lbid()
rem     Notes
rem     Example of analyzing index entries per leaf block.
rem	Set up the schema, table name, and index name before
rem	running the query.
rem	The code assumes you have no more than 10 columns in
rem	the index for the purposes of generating "is not null"
rem	clauses to force the necessary index-only execution path.
rem	This limit can easily be extended by cloning and editing
rem	the lines of the "setup" statement.
rem	For a simple b-tree index, the first parameter to the
rem	sys_op_lbid() function has to be the object_id of the
rem	index.
rem	The query will work with a sample clause if you are
rem	worried that the index you want to investigate is too
rem	large to analyze in a reasonable time. (I take 100 blocks
rem	per second as a conservative estimate for the I/O rate
rem	when running this script on very large indexes but have
rem	seen it running three or four times faster that.)
rem	This version of the code is suitable only for simple
rem	B-tree indexes - although that index may be reversed,
rem	have descending columns or virtual (function-based)
rem	columns, or be a global index on a partitioned table.
rem	In a recent (2022) update I added the hint /*+ use_invisible_indexes */
rem	to the main query to cater for invisible indexes (which would otherwise
rem	be ignored by the optimizer and raise ORA-00904). It also turns out that
rem	all the "is not null" predicates are now redundant

define m_owner = &m_schema
define m_table_name = &m_table
define m_index_name = &m_index

column ind_id new_value m_ind_id

        object_id ind_id
        owner       = upper('&m_owner')
and     object_name = upper('&m_index_name')
and     object_type = 'INDEX'

column col01    new_value m_col01
column col02    new_value m_col02
column col03    new_value m_col03
column col04    new_value m_col04
column col05    new_value m_col05
column col06    new_value m_col06
column col07    new_value m_col07
column col08    new_value m_col08
column col09    new_value m_col09

        nvl(max(decode(column_position, 1,column_name)),'null')        col01,
        nvl(max(decode(column_position, 2,column_name)),'null')        col02,
        nvl(max(decode(column_position, 3,column_name)),'null')        col03,
        nvl(max(decode(column_position, 4,column_name)),'null')        col04,
        nvl(max(decode(column_position, 5,column_name)),'null')        col05,
        nvl(max(decode(column_position, 6,column_name)),'null')        col06,
        nvl(max(decode(column_position, 7,column_name)),'null')        col07,
        nvl(max(decode(column_position, 8,column_name)),'null')        col08,
        nvl(max(decode(column_position, 9,column_name)),'null')        col09
where   table_owner = upper('&m_owner')
and     table_name  = upper('&m_table_name')
and     index_name  = upper('&m_index_name')
order by

break on report skip 1
compute sum of blocks on report
compute sum of row_ct on report

spool index_efficiency_3

prompt Owner &m_owner
prompt Table &m_table_name
prompt Index &m_index_name

set verify off

        rows_per_block * blocks                     row_ct,
        sum(blocks) over (order by rows_per_block)  cumulative_blocks
from    (
                count(*) blocks
        from    (
                               index_ffs(t1, &m_index_name)
                               noparallel_index(t1, &m_index_name)
                        sys_op_lbid( &m_ind_id ,'L',t1.rowid) as block_id,
                        count(*)                              as rows_per_block
                        &m_owner..&m_table_name t1
                --      &m_owner..&m_table_name sample block (5) t1
                        &m_col01 is not null
                or      &m_col02 is not null
                or      &m_col03 is not null
                or      &m_col04 is not null
                or      &m_col05 is not null
                or      &m_col06 is not null
                or      &m_col07 is not null
                or      &m_col08 is not null
                or      &m_col09 is not null
                group by
                        sys_op_lbid( &m_ind_id ,'L',t1.rowid)
        group by
order by

spool off

I’ve supplied a sample of the output, with a few observations on interpreting it, in this article on my blog.

Update July 2022

While running the version of this code that had been copied and tweaked on MOS, a user of the Oracle developer forum reported that it raised an ORA-00904: Invalid identifier error. There have been many new features in Oracle since I wrote the original code, so it’s not completely surprising that something written for 9i in 2003 should fail when run against 19c in 2022. After a quick check I found that the script would fail with this error if the index were declared invisible – so I’ve added the /*+ use_invisible_indexes */ hint to the critical query. (If the index is invisible the index_ffs() hint cannot be used, and the sys_op_lbid() function finds that the object_id that is its first parameter doesn’t match the object_id of the object it’s scanning – hence the slightly unexpected error.

I also found that the messy bit of code to generate a list of “is not null” predicates was no longer necessary, the optimizer was happy to do use an index-only path even when the index was made of columns that were all nullable. I’m fairly sure that this wasn’t the case when I first wrote the code.



  1. […] thought than just the simple “it’s two / four times the size”- and this is what the script index_efficiency_3.sql is about. Given the schema, table, and index name, it produces a report which tells you about the […]

    Pingback by Index Efficiency 3 « Oracle Scratchpad — March 3, 2010 @ 9:21 pm GMT Mar 3,2010 | Reply

  2. […] a data dictionary scan for simple B-tree indexes that may be unreasonably large for the data held Index Leaf Block scanner: a labour-intensive analysis of leaf block usage for a simple B-tree […]

    Pingback by Simple scripts « Oracle Scratchpad — March 4, 2010 @ 5:20 am GMT Mar 4,2010 | Reply

  3. Jonathan,

    What’s the point of “order by column_position” in lines 87-88?
    I’m actually quite impressed that Oracle politely ignores attempts to order a single row – I didn’t know that.


    Comment by Flado — March 4, 2010 @ 12:51 pm GMT Mar 4,2010 | Reply

    • Flado,

      Casting my mind back nearly seven years – I’ve got no idea. As I pointed out at the top of this note, and on the catalog page, these are just quick hacks I put together and they’re not intended to be polished, bullet-proof code.

      I’ll take a guess that when I wrote the introductory statement I started with a query to pull back the columns I needed in the right order, checked it was working, then did the max(decode()) trick and forgot to eliminate the “order by”.

      (Of course, if Oracle had considered that an error seven years ago, it wouldn’t be here today).

      Prompted by the thought of a seven-year-old hack, I’ve just had to ask myself what it would do if you created an index with a function-based column – without testing it I don’t think I’d know whether or not it would still work.

      Comment by Jonathan Lewis — March 5, 2010 @ 10:14 am GMT Mar 5,2010 | Reply

  4. […] you run something like my “index efficiency” code to check how well each block in the index is used, you may find something like […]

    Pingback by Index Explosion « Oracle Scratchpad — March 10, 2010 @ 10:48 pm GMT Mar 10,2010 | Reply

  5. […] this note on my website – now corrected and enhanced in the script catalog – about how to use the sys_op_lbid() function to do a detailed check on […]

    Pingback by Statistics problem « Oracle Scratchpad — March 22, 2010 @ 6:59 am GMT Mar 22,2010 | Reply

  6. […] is doing something similar to the model then it’s worth checking the related indexes (see my index efficiency note) to see if any of them are displaying the same problem as this test case. If they are you may want […]

    Pingback by ASSM wreck « Oracle Scratchpad — March 30, 2011 @ 9:22 pm BST Mar 30,2011 | Reply

  7. […] a significant variation from block to block, and I might want (on a production system) to check the state of the index leaf blocks of the date-based indexes from time to time, and the date_open index in […]

    Pingback by Massive Deletes – Part 2 – All Things Oracle — September 4, 2016 @ 9:23 am BST Sep 4,2016 | Reply

  8. […] you want to check the effect of index reversal you can take advantage of the sys_op_lbid() function to sample a little of your data – in my case I’ve queried the last 10,000 rows […]

    Pingback by Reverse Key | Oracle Scratchpad — December 16, 2018 @ 10:37 am GMT Dec 16,2018 | Reply

  9. […] so it can be quite instructive (when your system reaches “steady state” to produce a “histogram” of leaf contents – which is what the last SQL statement in my setup script is about, with the following […]

    Pingback by Shrinking indexes | Oracle Scratchpad — September 2, 2022 @ 7:22 pm BST Sep 2,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: