Home » RDBMS Server » Performance Tuning » When to rebuild indexes ...
| When to rebuild indexes ... [message #162376] |
Thu, 09 March 2006 23:40 |
tridentadm
Messages: 142
Registered: March 2006
|
Senior Member |
|
|
I just read this article..
Hope it will help you all.
ORACLE DATABASE ADMINISTRATOR
Which indexes require a re-build?
Danny Mohar
22 Jan 2002
Rating: -3.17- (out of 5)
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;
/
Reader Feedback
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.
Consider this:
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.
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 14:51:26 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
