Problem solve Get help with specific problems with your technologies, process and projects.

Identifying fragmented indexes that should be rebuilt

This tip will help address performance issues when Oracle stops using indexes on highly volatile tables. It identifies indexes that are fragmented and should be rebuilt.

This tip was written to help address performance issues when Oracle stops using indexes on highly volatile tables....

An index is a candidate for rebuilding when a relatively high number of index leaf row deletes have occurred. Once the indexes are rebuilt, Oracle once again uses the index.

This package will identify those indexes that are fragmented and should be rebuilt. The way it is written will just create the rebuild statements to allow the DBA to rebuild when the time is right but can be easily changed to rebuild automatically. I also log information on those indexes that are identified as needing to be rebuilt so I can look for trends, but it isn't necessary for the code to work. This should run after hours as locking can occur. It has been tested on 8.1.7.4 and 9.2.0.3.

 CREATE OR REPLACE PACKAGE pkg_identify_frag_idx As ------------------------------------------------------------------------ -- PACKAGE DECLARATIONS ------------------------------------------------------------------------ PROCEDURE sp_log (owner_i IN VARCHAR2, indexname_i IN VARCHAR2, msg_i IN VARCHAR2); PROCEDURE sp_main ; END; / CREATE OR REPLACE PACKAGE BODY OPS$ORACLE.pkg_identify_frag_idx IS i_start_time varchar2(45) ; i_end_time varchar2(45) ; v_sqlerrmsg varchar2(80) := NULL ; v_counter integer :=0 ; v_rebuild_counter integer :=0 ; v_del_lf_rows_len sys.index_stats.del_lf_rows_len%TYPE ; v_lf_rows_len sys.index_stats.lf_rows_len%TYPE ; ----------------------------------------------------------------------- -- PROCEDURE sp_log ----------------------------------------------------------------------- procedure sp_log (owner_i IN VARCHAR2, indexname_i IN VARCHAR2, msg_i IN VARCHAR2) IS BEGIN INSERT INTO OPS$ORACLE.rebuild_idx_log VALUES (owner_i||'.'||indexname_i, sysdate, msg_i ) ; COMMIT ; RETURN ; EXCEPTION WHEN OTHERS THEN RETURN ; END sp_log ; ----------------------------------------------------------------------- -- Procedure: SP_MAIN ----------------------------------------------------------------------- procedure sp_main IS CURSOR index_rec IS Select owner, index_name From sys.dba_indexes Where owner Not In ('SYS','SYSTEM','DBSNMP','VLAD') ; rec index_rec%ROWTYPE ; BEGIN dbms_utility.exec_ddl_statement('Truncate Table OPS$ORACLE.rebuild_idx_log Reuse Storage') ; Select to_char(sysdate,'DD/MON/YYYY hh:mi:ss') Into i_start_time From sys.dual ; sp_log(NULL,NULL,'Index validation started at '||i_start_time||'.') ; dbms_output.put_line('Index validation started at '||i_start_time||'.') ; FOR rec IN index_rec LOOP Begin dbms_utility.exec_ddl_statement('Analyze Index '||rec.owner||'.'||rec.index_name||' Validate Structure') ; EXCEPTION WHEN OTHERS THEN sp_log(rec.owner,rec.index_name,'EXCEPTION RAISED: ' || TO_CHAR(SQLCODE) ||SUBSTR(SQLERRM, 1, 80)) ; dbms_output.put_line('EXCEPTION RAISED: ' || TO_CHAR(SQLCODE) ||': '||SUBSTR(SQLERRM, 1, 80)||' on index '||rec.owner||'.'||rec.index_name) ; End ; Select del_lf_rows_len, lf_rows_len Into v_del_lf_rows_len, v_lf_rows_len From sys.index_stats ; IF v_del_lf_rows_len <> 0 THEN IF (v_del_lf_rows_len/v_lf_rows_len)*100 > 20 THEN dbms_output.put_line('Alter Index '||rec.owner||'.'||rec.index_name||' Rebuild;') ; v_rebuild_counter := v_rebuild_counter + 1 ; END IF ; END IF ; v_counter := v_counter + 1 ; END LOOP ; Select to_char(sysdate,'DD/MON/YYYY hh:mi:ss') Into i_end_time From sys.dual ; sp_log(NULL, NULL, 'Index validation ended at '||i_end_time||'. Indexes Processed: '||v_counter||'.') ; dbms_output.put_line('Index validation ended at '||i_end_time||'.') ; dbms_output.put_line('Indexes Processed: '||v_counter||'. Indexes needed to be rebuilt: '||v_rebuild_counter||'.' ) ; EXCEPTION WHEN OTHERS THEN sp_log(rec.owner,rec.index_name,'EXCEPTION RAISED: ' || TO_CHAR(SQLCODE) ||SUBSTR(SQLERRM, 1, 80)) ; dbms_output.put_line('EXCEPTION RAISED: ' || TO_CHAR(SQLCODE) ||': '||SUBSTR(SQLERRM, 1, 80)||' on index '||rec.owner||'.'||rec.index_name) ; IF index_rec%ISOPEN THEN CLOSE index_rec ; END IF; END sp_main ; END ; /

Reader Feedback

Member Ravinder B. writes: This tip will hurt the user as it puts a lock on the table. But 9i now lets you do it online in the same way as an online rebuild, "analyze index MY_INDEX validate structure ONLINE;" and this online operation is designed purely for structure validation thus INDEX_STATS view will not be populated.

Member Dick G. writes: Tiffany's tip is pretty good, for tables that experience lots of deletes, but it misses those that undergo large amounts of updates and inserts, which cause the index to become unbalanced and consequently unusable, more so.

Member David A. writes: That script to rebuild indexes should rebuild using online (to avoid locks) and compute statistics (to gather the new statistics and avoid weird explain plans), so the sentence should be: alter index XXX rebuild online compute statistics;

This was last published in May 2004

Dig Deeper on Oracle error messages

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close