This script is used to determine which indexes require a re-build to assist with system performance. A ratio of...
10-20% deleted lf rows to lf rows may be candidates. The script will attempt the used of dynamic SQL to execute the DDL commands required to execute the analyze table commands directly. Script will then re-build the indexes above a specified level of deleted rows. This script uses a table to store table/index modification details table format:
DMOHAR.TEMP_INDEX_DATA_MED(V_ID number, V_DATA VARCHAR2(100) )
The script also requires access to the Package DBMS_UTILITY and must be run under SYS user.
set linesize 200 set long 200 set pagesize 1000 set serveroutput on size 1000000 DECLARE v_table_name dba_tables.table_name%TYPE; v_index_name dba_indexes.index_name%TYPE; v_owner dba_indexes.owner%TYPE; v_lf_count number; v_del_lf_count number; v_ratio number; v_cursor INTEGER; v_count PLS_INTEGER; v_del_lf_percentage NUMBER; v_time1 NUMBER; v_time2 NUMBER; v_run_date DATE; CURSOR get_indexes IS SELECT index_name gi_index_name, owner gi_owner, table_name gi_table_name FROM dba_indexes WHERE owner = 'CIS' AND table_name not like 'C_RPTWORK%' AND table_name like 'C_%'; --AND table_name like 'C_SU%'; BEGIN DBMS_OUTPUT.ENABLE(1000000); DBMS_OUTPUT.PUT_LINE('Starting dynamic SQL to Analyze index structures'); DBMS_OUTPUT.PUT_LINE('This MUST be run under SYS'); v_time1 := DBMS_UTILITY.GET_TIME; -- DBMS_OUTPUT.PUT_LINE(to_char(to_date(v_time1),'yyyymmdd hh24:mi'); DBMS_OUTPUT.PUT_LINE((v_time1)); FOR cur_index IN get_indexes LOOP v_owner := cur_index.gi_owner; v_index_name := cur_index.gi_index_name; v_cursor := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(v_cursor, 'ANALYZE INDEX '||v_owner||'.'||v_index_name|| ' VALIDATE STRUCTURE',dbms_sql.native); v_count := DBMS_SQL.EXECUTE(v_cursor); DBMS_SQL.CLOSE_CURSOR(v_cursor); /*** IF v_count = 0 THEN DBMS_OUTPUT.PUT_LINE('command executed'); ELSE DBMS_OUTPUT.PUT_LINE('NOT executed'); END IF; ***/ SELECT LF_ROWS,DEL_LF_ROWS INTO v_lf_count,v_del_lf_count FROM index_stats; IF v_lf_count > 0 AND v_del_lf_count > 0 THEN SELECT round((v_del_lf_count/v_lf_count)*100,4) INTO v_del_lf_percentage FROM index_stats; SELECT SYSDATE INTO v_run_date FROM DUAL; IF v_del_lf_percentage > 10 THEN DBMS_OUTPUT.PUT_LINE(to_char(v_run_date,'yyyymmdd hh24:mi') ||' '|| v_owner||'.'||v_index_name||' LF ROWS : '||v_lf_count|| ' Del lf rows: '|| v_del_lf_count||' percentage : '||v_del_lf_percentage); INSERT INTO DMOHAR.TEMP_INDEX_DATA_MED VALUES (93932211, RPAD(to_char(v_run_date,'yyyymmdd hh24:mi')||','|| v_owner||'.'||v_index_name||','|| v_lf_count||','|| v_del_lf_count||','|| v_del_lf_percentage,100) ); COMMIT; -- Now re-build the ineffecient indexes. v_cursor := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(v_cursor,'ALTER INDEX '||v_owner||'.'||v_index_name|| ' REBUILD',DBMS_SQL.NATIVE); v_count := DBMS_SQL.EXECUTE(v_cursor); DBMS_SQL.CLOSE_CURSOR(v_cursor); DBMS_OUTPUT.PUT_LINE('Index '||v_index_name||' Re-built'); END IF; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE(' SELECT LF_ROWS,DEL_LF_ROWS, (DEL_LF_ROWS'|| '/LF_ROWS)*100 FROM INDEX_STATS;'); v_time2 := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE(to_char((v_time2-v_time1)/100) ||' seconds '); END; /
Sanjay R. writes: IMHO index rebuilds are justified only if there are regular deletes and inserts of more than 20-30% of the records in the table.
This is what I read in a note from an Oracle Expert: I'm a big believer that indexes do not need to be rebuilt except in very particular cases. 99% of index rebuilds are a waste of time. Some people look at the number of deleted entries in the index and rebuild based on that (analyze index index_name validate structure and then query index_stats to see statistics on the index such as deleted entries and such). I say that might be misleading. If 50% of the entries are deleted (leaving "holes" in the index), should we rebuild? Maybe -- maybe not. depends on how the index is used and what data it indexes.
create table emp ( empno int, ename varchar2(25) ); create index emp_idx1 on emp(empno); create index emp_idx2 on emp(ename)
Now, suppose empno is filled in via a sequence. It's always increasing and we never reuse a value. In our system, we actually delete rows when we fire someone (a real HR system would never do that).
So, emp_idx1 develops "holes" over time that will never be reused. It we find it to be X% "empty" over time, it might be a candidate for a rebuild. We'll never reuse that deleted space (unless we end up deleting all index entries on a block).
But, the index on ename -- that's a little more chaotic. Say we have employees:
Bill Bob Mary
Say we fire Bob, now we have:
Bill X-Bob (deleted entry) Mary
Should we rebuild an index like that? Probably NOT. The reason: we are going to hire Hillary (Bill Hillary Mary). Hillary will reuse Bobs slot. There is no reason to get rid of that slot only to have to rebuild it again later. If the data you are indexing has a good probability of reusing a slot like that, rebuilding can actually slow you down over time (it takes time to split a block with empty entries -- the chances we need to split are reduced. If you rebuild, they'll go up; if you unsplit everything, got rid of the space. Now we have to re-split again, every time you rebuild). I find most indexes hit a steady state -- if you rebuild them, they'll go back into that state over time. So rebuilding is somewhat self-defeating in that case.
Make sure to BENCHMARK yourself here. Before rebuilding, capture statistics, performance metrics, IO's, etc against that index. After rebuilding, compare the results. If you got nothing back -- no increased performance, decreased IO, etc -- don't rebuild it again later, it doesn't buy you anything.
For More Information
- What do you think about this tip? E-mail the Editor at firstname.lastname@example.org with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.