Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Free script to very quickly and cheaply estimate the size of an index if it were to be rebuilt

leave a comment »

A good friend of mine recently asked me if edb360 included a section showing indexes that would benefit of a rebuild. I replied “not yet” and basically committed to add something. This topic of the needs versus the implications of rebuilding an index has been recently discussed in Richard Foote’s Blog. In my opinion, if you want to know more about indexes, Richard’s blog is one of the first stops. To my surprise and delight, I learned that we can actually use a little trick of the EXPLAIN PLAN FOR command and actually use the CBO to estimate what would be the size of an index if we were to create (or rebuild) it. In this latter blog posting Richard explains and demonstrates how it can be done.

My blog posting today is about encapsulating this cool method to quickly and cheaply estimate the size of an index if it were to be rebuilt, and put it on a free script for the Oracle community to use. So, feel free to use script below, but I need to remind you that before you jump to conclusions that this or that index should be rebuilt, read first about the actual need of doing so. You may want to include in your reading Richard Foote’s numerous postings on this topic, and also a couple of Oracle MOS notes: 989093.1 and 989186.1

-- File name:   estimate_index_size.sql
-- Purpose:     Reports Indexes with an Actual size > Estimated size for over 1 MB
-- Author:      Carlos Sierra
-- Version:     2014/07/18
-- Description: Script to very quickly and cheaply estimate the size of an index if it
--              were to be rebuilt. It uses EXPLAIN PLAN FOR CREATE INDEX technique.
--              It can be used on a single index, or all the indexes on a table, or
--              a particular application schema, or all application schemas. It does not
--              lock indexes and only updates the plan_table, which is usually a global
--              temporary table.
-- Usage:       Connect to SQL*Plus as SYS or DBA account and execute without parameters.
--              It will ask for optional schema owner, table name and index name. If all
--              3 are given null values then it acts on all application schemas. It
--              generates a simple text report with the indexes having an estimated size
--              of at least 1 MB over their actual size.
-- Example:     @estimate_index_size.sql
-- Notes:       Developed and tested on
--              Inspired on blog posts from Richard Foote and Connor MacDonald:
--              If considering index rebuilds based on the output of this script, read
--              first Richard Foote's numerous blog postings about this topic. Bottom
--              line: there are only a few cases where you actually need to manually
--              rebuild an index.
--              This method to estimated size of an index is far from perfect, please
--              scrutinize this script before using it. You may also want to read
--              Oracle MOS notes: 989093.1 and 989186.1 on this topic.
SPO estimate_index_size.txt;
UNDEF owner table_name index_name exclusion_list exclusion_list2;
VAR random1 VARCHAR2(30);
VAR random2 VARCHAR2(30);
EXEC :random1 := DBMS_RANDOM.string('A', 30);
EXEC :random2 := DBMS_RANDOM.string('X', 30);
DELETE plan_table WHERE statement_id IN (:random1, :random2);

  sql_text CLOB;
  FOR i IN (SELECT idx.owner, idx.index_name
              FROM dba_indexes idx,
                   dba_tables tbl
             WHERE idx.owner = NVL(UPPER(TRIM('&&owner.')), idx.owner) -- optional schema owner name
               AND idx.table_name = NVL(UPPER(TRIM('&&table_name.')), idx.table_name) -- optional table name
               AND idx.index_name = NVL(UPPER(TRIM('&&index_name.')), idx.index_name) -- optional index name
               AND idx.owner NOT IN &&exclusion_list. -- exclude non-application schemas
               AND idx.owner NOT IN &&exclusion_list2. -- exclude more non-application schemas
               AND idx.index_type IN ('NORMAL', 'FUNCTION-BASED NORMAL', 'BITMAP', 'NORMAL/REV') -- exclude domain and lob
               AND idx.status != 'UNUSABLE' -- only valid indexes
               AND idx.temporary = 'N'
               AND tbl.owner = idx.table_owner
               AND tbl.table_name = idx.table_name
               AND tbl.last_analyzed IS NOT NULL -- only tables with statistics
               AND tbl.num_rows > 0 -- only tables with rows as per statistics
               AND tbl.blocks > 128 -- skip small tables
               AND tbl.temporary = 'N')
      sql_text := 'EXPLAIN PLAN SET STATEMENT_ID = '''||:random1||''' FOR '||REPLACE(DBMS_METADATA.get_ddl('INDEX', i.index_name, i.owner), CHR(10), ' ');
      -- cbo estimates index size based on explain plan for create index ddl
      EXECUTE IMMEDIATE sql_text;
      -- index owner and name do not fit on statement_id, thus using object_owner and object_name, using statement_id as processing state
      DELETE plan_table WHERE statement_id = :random1 AND (other_xml IS NULL OR NVL(DBMS_LOB.instr(other_xml, 'index_size'), 0) = 0);
      UPDATE plan_table SET object_owner = i.owner, object_name = i.index_name, statement_id = :random2 WHERE statement_id = :random1;
        DBMS_OUTPUT.PUT_LINE(i.owner||'.'||i.index_name||': '||SQLERRM);

indexes AS (
SELECT pt.object_owner,
       TO_NUMBER(EXTRACTVALUE(VALUE(d), '/info')) estimated_bytes
  FROM plan_table pt,
       TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(pt.other_xml), '/*/info'))) d
 WHERE pt.statement_id = :random2
   AND pt.other_xml IS NOT NULL -- redundant
   AND DBMS_LOB.instr(pt.other_xml, 'index_size') > 0 -- redundant
   AND EXTRACTVALUE(VALUE(d), '/info/@type') = 'index_size' -- grab index_size type
segments AS (
SELECT owner, segment_name, SUM(bytes) actual_bytes
  FROM dba_segments
 WHERE owner = NVL(UPPER(TRIM('&&owner.')), owner) -- optional schema owner name
   AND segment_name = NVL(UPPER(TRIM('&&index_name.')), segment_name) -- optional index name
   AND owner NOT IN &&exclusion_list. -- exclude non-application schemas
   AND owner NOT IN &&exclusion_list2. -- exclude more non-application schemas
   AND segment_type LIKE 'INDEX%'
HAVING SUM(bytes) > POWER(2, 20) -- only indexes with actual size > 1 MB
list_bytes AS (
SELECT (s.actual_bytes - i.estimated_bytes) actual_minus_estimated,
  FROM indexes i,
       segments s
 WHERE i.estimated_bytes > POWER(2, 20) -- only indexes with estimated size > 1 MB
   AND s.owner = i.object_owner
   AND s.segment_name = i.object_name
SELECT ROUND(actual_minus_estimated / POWER(2, 20)) actual_minus_estimated,
       ROUND(actual_bytes / POWER(2, 20)) actual_mb,
       ROUND(estimated_bytes / POWER(2, 20)) estimated_mb,
       object_owner owner,
       object_name index_name
  FROM list_bytes
 WHERE actual_minus_estimated > POWER(2, 20) -- only differences > 1 MB
       1 DESC,

DELETE plan_table WHERE statement_id IN (:random1, :random2);
UNDEF owner table_name index_name exclusion_list exclusion_list2;

Written by Carlos Sierra

July 18, 2014 at 9:45 am

Leave a Reply

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

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s


Get every new post delivered to your Inbox.

Join 1,510 other followers

%d bloggers like this: