Free script to very quickly and cheaply estimate the size of an index if it were to be rebuilt
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 11.2.0.3. -- -- Inspired on blog posts from Richard Foote and Connor MacDonald: -- http://richardfoote.wordpress.com/2014/04/24/estimate-index-size-with-explain-plan-i-cant-explain/#comment-116966 -- http://connormcdonald.wordpress.com/2012/05/30/index-size/ -- -- 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; DEF exclusion_list = "('ANONYMOUS','APEX_030200','APEX_040000','APEX_SSO','APPQOSSYS','CTXSYS','DBSNMP','DIP','EXFSYS','FLOWS_FILES','MDSYS','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS')"; DEF exclusion_list2 = "('SI_INFORMTN_SCHEMA','SQLTXADMIN','SQLTXPLAIN','SYS','SYSMAN','SYSTEM','TRCANLZR','WMSYS','XDB','XS$NULL')"; 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); SET SERVEROUT ON; DECLARE sql_text CLOB; BEGIN 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') LOOP BEGIN 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; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(i.owner||'.'||i.index_name||': '||SQLERRM); DBMS_OUTPUT.PUT_LINE(DBMS_LOB.substr(sql_text)); END; END LOOP; END; / SET SERVEROUT OFF; WITH indexes AS ( SELECT pt.object_owner, pt.object_name, 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 GROUP BY owner, segment_name ), list_bytes AS ( SELECT (s.actual_bytes - i.estimated_bytes) actual_minus_estimated, s.actual_bytes, i.estimated_bytes, i.object_owner, i.object_name 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 ORDER BY 1 DESC, object_owner, object_name / DELETE plan_table WHERE statement_id IN (:random1, :random2); UNDEF owner table_name index_name exclusion_list exclusion_list2; SPO OFF;
Hi Carlos,
I am not getting output after running script , please see below
Am i doing something incorrect
SQL> @estimate_index_size.sql
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
0 rows deleted.
Enter value for owner: MGTAPPO8
old 7: WHERE idx.owner = NVL(UPPER(TRIM(‘&&owner.’)), idx.owner) — optional schema owner name
new 7: WHERE idx.owner = NVL(UPPER(TRIM(‘MGTAPPO8’)), idx.owner) — optional schema owner name
Enter value for table_name: CUSTOMER
old 8: AND idx.table_name = NVL(UPPER(TRIM(‘&&table_name.’)), idx.table_name) — optional table name
new 8: AND idx.table_name = NVL(UPPER(TRIM(‘CUSTOMER’)), idx.table_name) — optional table name
Enter value for index_name: CUSTOMER_1IX
old 9: AND idx.index_name = NVL(UPPER(TRIM(‘&&index_name.’)), idx.index_name) — optional index name
new 9: AND idx.index_name = NVL(UPPER(TRIM(‘CUSTOMER_1IX’)), idx.index_name) — optional index name
old 10: AND idx.owner NOT IN &&exclusion_list. — exclude non-application schemas
new 10: AND idx.owner NOT IN (‘ANONYMOUS’,’APEX_030200′,’APEX_040000′,’APEX_SSO’,’APPQOSSYS’,’CTXSYS’,’DBSNMP’,’DIP’,’EXFSYS’,’FLOWS_FILES’,’MDSYS’,’OLAPSYS’,’ORACLE_OCM’,’ORDDATA’,’ORDPLUGINS’,’ORDSYS’,’OUTLN’,’OWBSYS’) — exclude non-application schemas
old 11: AND idx.owner NOT IN &&exclusion_list2. — exclude more non-application schemas
new 11: AND idx.owner NOT IN (‘SI_INFORMTN_SCHEMA’,’SQLTXADMIN’,’SQLTXPLAIN’,’SYS’,’SYSMAN’,’SYSTEM’,’TRCANLZR’,’WMSYS’,’XDB’,’XS$NULL’) — exclude more non-application schemas
PL/SQL procedure successfully completed.
old 16: WHERE owner = NVL(UPPER(TRIM(‘&&owner.’)), owner) — optional schema owner name
new 16: WHERE owner = NVL(UPPER(TRIM(‘MGTAPPO8’)), owner) — optional schema owner name
old 17: AND segment_name = NVL(UPPER(TRIM(‘&&index_name.’)), segment_name) — optional index name
new 17: AND segment_name = NVL(UPPER(TRIM(‘CUSTOMER_1IX’)), segment_name) — optional index name
old 18: AND owner NOT IN &&exclusion_list. — exclude non-application schemas
new 18: AND owner NOT IN (‘ANONYMOUS’,’APEX_030200′,’APEX_040000′,’APEX_SSO’,’APPQOSSYS’,’CTXSYS’,’DBSNMP’,’DIP’,’EXFSYS’,’FLOWS_FILES’,’MDSYS’,’OLAPSYS’,’ORACLE_OCM’,’ORDDATA’,’ORDPLUGINS’,’ORDSYS’,’OUTLN’,’OWBSYS’) — exclude non-application schemas
old 19: AND owner NOT IN &&exclusion_list2. — exclude more non-application schemas
new 19: AND owner NOT IN (‘SI_INFORMTN_SCHEMA’,’SQLTXADMIN’,’SQLTXPLAIN’,’SYS’,’SYSMAN’,’SYSTEM’,’TRCANLZR’,’WMSYS’,’XDB’,’XS$NULL’) — exclude more non-application schemas
no rows selected
0 rows deleted.
SQL>
Regards,
Mayank
Mayank
October 14, 2014 at 7:29 am
You may want to remove the “DELETE plan_table” command near the bottom of the script and verify if anything is written into this Table.
Carlos Sierra
October 14, 2014 at 7:42 pm
Does this assume if index stats are current ?
Rocky
October 16, 2014 at 2:48 pm
yes
Carlos Sierra
October 16, 2014 at 3:08 pm
[…] time back I blogged about an easy way to estimate the size of an index. It turns out there is an API that also uses the plan_table under the hood in order to estimate […]
Script to identify index rebuild candidates on 12c | Carlos Sierra's Tools and Tips
July 12, 2017 at 11:03 am
Hello Dear, When I run the script am getting the below error:
ERROR at line 25:
ORA-06550: line 25, column 58:
PLS-00382: expression is of wrong type
ORA-06550: line 25, column 40:
PL/SQL: Statement ignored
old 16: WHERE owner = NVL(UPPER(TRIM(‘&&owner.’)), owner) — optional schema owner name
new 16: WHERE owner = NVL(UPPER(TRIM(‘RMSPRD’)), owner) — optional schema owner name
old 17: AND segment_name = NVL(UPPER(TRIM(‘&&index_name.’)), segment_name) — optional index name
new 17: AND segment_name = NVL(UPPER(TRIM(”)), segment_name) — optional index name
old 18: AND owner NOT IN &&exclusion_list. — exclude non-application schemas
new 18: AND owner NOT IN (‘ANONYMOUS’,’APEX_030200′,’APEX_040000′,’APEX_SSO’,’APPQOSSYS’,’CTXSYS’,’DBSNMP’,’DIP’,’EXFSYS’,’FLOWS_FILES’,’MDSYS’,’OLAPSYS’,’ORACLE_OCM’,’ORDDATA’,’ORDPLUGINS’,’ORDSYS’,’OUTLN’,’OWBSYS’) — exclude non-application schemas
old 19: AND owner NOT IN &&exclusion_list2. — exclude more non-application schemas
new 19: AND owner NOT IN (‘SI_INFORMTN_SCHEMA’,’SQLTXADMIN’,’SQLTXPLAIN’,’SYS’,’SYSMAN’,’SYSTEM’,’TRCANLZR’,’WMSYS’,’XDB’,’XS$NULL’) — exclude more non-application schemas
no rows selected
Nagendra
August 18, 2019 at 11:02 pm
Not sure what is line 25 on the version of eDB360 you are using. Be sure you are on latest version as per SQLdb360, then if still an issue please pase the entire SQL text from the error you get. Also let me know the version of DB you are in.
Carlos Sierra
September 3, 2019 at 6:28 am