Index Efficiency 3

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 leaf block in index order, potentially one at a time. Note that it only reports leaf blocks which have rows in them – so it won’t show anything 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
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
rem	Last tested
rem		11.1.0.7
rem		10.2.0.3
rem		 9.2.0.8
rem	Not tested
rem		11.2.0.1
rem		10.2.0.4
rem	Not applicable
rem		 8.1.7.4	-- no sys_op_lbid()
rem
rem     Notes
rem     Example of analyzing index entries per leaf block.
rem
rem	Set up the schema, table name, and index name before
rem	running the query.
rem
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
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
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
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

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

select
        object_id ind_id
from
        dba_objects
where
        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

select
        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
from
        dba_ind_columns
where   table_owner = upper('&m_owner')
and     table_name  = upper('&m_table_name')
and     index_name  = upper('&m_index_name')
order by
        column_position
;

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

select
        rows_per_block,
        blocks,
        rows_per_block * blocks                     row_ct,
        sum(blocks) over (order by rows_per_block)  cumulative_blocks
from    (
        select
                rows_per_block,
                count(*) blocks
        from    (
                select
                        /*+
                               cursor_sharing_exact
                               dynamic_sampling(0)
                               no_monitoring
                               no_expand
                               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
                from
                        &m_owner..&m_table_name t1
                --      &m_owner..&m_table_name sample block (5) t1
                where
                        &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
                rows_per_block
        )
order by
        rows_per_block
;

spool off

I’ve supplied a sample of the output, with a few observations on interpreting it, in the article referenced in comment #1 below.