Oracle Scratchpad

March 3, 2010

Index Efficiency 3

Filed under: — Jonathan Lewis @ 3:38 pm BST Mar 3,2010

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.

February 28, 2010

Index Sizing

Filed under: — Jonathan Lewis @ 3:24 pm BST Feb 28,2010

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

rem
rem     Script:     index_est_proc_2.sql
rem     Author:     Jonathan Lewis
rem     Dated:      August 2005 (updated Apr 2009)
rem     Purpose:    Fast analysis of indexes to help identify
rem                 extreme degeneration.
rem
rem     Last tested
rem             11.1.0.7
rem             10.2.0.3
rem             10.1.0.4
rem              9.2.0.8
rem              8.1.7.4
rem     Not tested
rem             11.2.0.1
rem             10.2.0.4
rem
rem     Usage:
rem     Set the values in the "define" section
rem     Log on with the privilege to see the "dba_" views
rem     using SQL*Plus and run the script.
rem
rem     Notes:
rem     This script assumes that statistics have been collected in
rem     the fairly recent past, and uses some approximations to
rem     compare the number of leaf blocks with the number of leaf
rem     blocks that ought to be needed to hold the data.
rem
rem     There are various little oddities with the way that
rem         (a) Oracle calculates average column lenght and
rem         (b) I use the available data
rem     that mean that at small sizes and in extreme cases the
rem     numbers I produce can be wrong.  In particular, for indexes
rem     where a lot of the table data has nulls (so no entry in the
rem     index), the estimated size can be significantly larger than
rem     they finally turn out to be.
rem
rem
rem     Targets
rem     =======
rem     Where the estimate is very much smaller than the actual, then
rem     you may be looking at a "FIFO" index, emptying out in the past
rem     and filling in the future. This type of index is a candidate for
rem     a regular "coalesce" - although you may want to rebuild it once
rem     to get it to the right starting size and release excess space
rem     back to the tablespace.
rem
rem     See http://jonathanlewis.wordpress.com/2008/09/26/index-analysis/
rem     for an example and discussion on this type of index.
rem
rem     Where the estimate is about half the size of the actual, then
rem     it is worth checking whether there is any special treatment of
rem     the data that is making this happen. 50% utilisation is fairly
rem     common in RAC for indexes based on a sequence with a large cache
rem     size, so it may be best to leave the indexes at that level. 
rem     However, you may find that rebuilding (perhaps just once) with
rem     a pctfree in the region of 30% may give you a slightly more efficient
rem     index in non-RAC systems.
rem
rem     If your index is running at 50% and is not strongly sequence based
rem     then you may be suffering from the concurrency/ITL bug and may want
rem     to rebuild the index and force a maxtrans setting into the index.
rem
rem     If the index is running at a fairly uniform 25%, it may be subject
rem     to side effects of both sequencing and the concurrency effects.
rem
rem     Usage:
rem     ======
rem     This script takes a username (table owner), percent usage, and
rem     scaling factor.  It reports the estimated leaf block count of
rem     all simple indexes for that schema where the size of the index
rem     would be smaller than the supplied fraction of the current size
rem     when rebuilt at the supplied percentage utilisation. Current settings
rem     are 90% (which equates to the default pctfree 10) and 0.6 which means
rem     the index would be running at about 50% empty wastage - which is the
rem     point at which it begins to be a possible target for investigation.
rem     The script does not report any index smaller than 10,000 leaf blocks,
rem     and assumes an 8KB block size.
rem
rem     Technical notes:
rem     ================
rem     Don't need to add a length byte after using dbms_stats
rem     Don't need a 'descending' byte because it's automatically included
rem     Don't need to adjust for num_nulls because it's automatically included
rem     Reverse key indexes don't affect column lengths
rem
rem     Need to worry about COMPRESSED indexes. At present compression
rem     may reduce the size of an index so that I don't notice it should
rem     still be smaller than it is.
rem
rem     Index types that can be used (with partitioned = 'NO')
rem         NORMAL
rem         NORMAL/REV
rem         FUNCTION-BASED NORMAL
rem
rem     Still needs enhancing for partitioned and subpartitioned indexes
rem     Check dba_part_indexes for locality, partitioning_type, subpartitioning_type
rem     But does handle global indexes on partitioned tables.
rem
rem     To investigate
rem         LOB
rem         IOT - TOP
rem         IOT - NESTED
rem         SECONDARY
rem         BITMAP  (and BITMAP JOIN)
rem         FUNCTION-BASED BITMAP
rem         CLUSTER
rem         ANSI ?
rem
rem     Probably not possible
rem         DOMAIN
rem         FUNCTION-BASED DOMAIN
rem
rem     Need to avoid partitioned, temporary, unusable and dropped indexes
rem

spool index_est_proc_2

set verify off
set serveroutput on size 1000000 format wrapped

define  m_owner         = '&m_schemaname'
define m_blocksize = 8192
define  m_target_use    = 90 -- equates to pctfree 10
define  m_scale_factor  = 0.6
define m_minimum = 10000
define m_overhead = 192 -- leaf block "lost" space in index_stats

declare
    m_leaf_estimate number;
begin
    for r in (
        select
            table_owner,
            table_name,
            owner       index_owner,
            index_name,
            leaf_blocks
        from
            dba_indexes
        where
            owner = upper('&m_owner')
        and index_type in (
                'NORMAL',
                'NORMAL/REV',
                'FUNCTION-BASED NORMAL'
            )
        and partitioned = 'NO'
        and temporary = 'N'
        and dropped = 'NO'
        and status = 'VALID'
        and last_analyzed is not null
        order by
            owner, table_name, index_name
    ) loop

        if r.leaf_blocks > &m_minimum then
            select
                round(
                    100 / &m_target_use *       -- assumed packing efficiency
                    (
                        ind.num_rows * (tab.rowid_length + ind.uniq_ind + 4) +
                        sum(
                            (tc.avg_col_len) *
                            (tab.num_rows)
                        )           -- column data bytes
                    ) / (&m_blocksize - &m_overhead)
                )               index_leaf_estimate
                into    m_leaf_estimate
            from
                (
                select  /*+ no_merge */
                    table_name,
                    num_rows,
                    decode(partitioned,'YES',10,6) rowid_length
                from
                    dba_tables
                where
                    table_name  = r.table_name
                and owner       = r.table_owner
                )               tab,
                (
                select  /*+ no_merge */
                    index_name,
                    index_type,
                    num_rows,
                    decode(uniqueness,'UNIQUE',0,1) uniq_ind
                from
                    dba_indexes
                where
                    table_owner = r.table_owner
                and table_name  = r.table_name
                and owner       = r.index_owner
                and index_name  = r.index_name
                )               ind,
                (
                select  /*+ no_merge */
                    column_name
                from
                    dba_ind_columns
                where
                    table_owner = r.table_owner
                and index_owner = r.index_owner
                and table_name  = r.table_name
                and index_name  = r.index_name
                )               ic,
                (
                select  /*+ no_merge */
                    column_name,
                    avg_col_len
                from
                    dba_tab_cols
                where
                    owner       = r.table_owner
                and table_name  = r.table_name
                )               tc
            where
                tc.column_name = ic.column_name
            group by
                ind.num_rows,
                ind.uniq_ind,
                tab.rowid_length
            ;

            if m_leaf_estimate < &m_scale_factor * r.leaf_blocks then

                dbms_output.put_line(
                    to_char(sysdate,'hh24:mi:ss') || ': ' ||
                    trim(r.table_name) || ' - ' ||
                    trim(r.index_name)
                );

                dbms_output.put_line(
                    'Current Leaf blocks: ' ||
                    to_char(r.leaf_blocks,'999,999,999') ||
                    '         Target size: ' ||
                    to_char(m_leaf_estimate,'999,999,999')
                );

                dbms_output.new_line;

            end if;
        end if;
    end loop;
end;
/
set verify on

spool off

set doc off
doc
A recent sample from my SYS schema (with a minimum of 100 blocks)

15:19:30: HISTGRM$ - I_H_OBJ#_COL#
Current Leaf blocks:          110         Target size:           51

15:19:30: OBJAUTH$ - I_OBJAUTH2
Current Leaf blocks:          107         Target size:           58

15:19:30: WRI$_OPTSTAT_HISTHEAD_HISTORY - I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST
Current Leaf blocks:          180         Target size:           99
#

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,268 other followers