| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Rebuild Indexes
Hi Jonathan
I got the script from metalink. :-(
Thanks for you advices. :-)
regards,
Senthil.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jonathan Lewis
Sent: Friday, February 13, 2004 3:05 PM
To: oracle-l_at_freelists.org
Subject: Re: Rebuild Indexes
Is this the script that comes off Metalink ?
There are a number of flaws that need to be highlighted - most significantly the warnings that point out
the impact this will have on a production system, the idiocy of changing a b-tree to a bitmap on an OLTP system, the absence of any references to partitioned indexes, the possibility that the level is exactly what is ought to be for very large indexes,
the importance of timing when considering deleted rows
Note:
The VALIDATE option effectively locks the underlying table. You can include the ONLINE option, but then the INDEX_STATS structure is not populated.
The VALIDATE command takes a long time on a large index, and will do a lot of I/O.
The suggestion that an index is a candidate for being turned into a bitmap in an OLTP system is remarkably stupid unless (a) the table is in a read-only tablespace, or (b) the table is going to have virtually no single row inserts or deletes, or updates to the indexed column. Bitmap indexes introduce massive concurrency and deadlock problems and tend to grow catastrophically if you are doing lightweight DML on the table.
If you analyze a partitioned index, the only statistics you capture in INDEX_STATS are the statistics for the last partitioned (or sub-partition) analyzed.
Although the script chooses height > 5 as a benchmark for rebuilds, and you have to have quite a large table for the 'correct' height for an index to be 5, an arbitrary choice of height is a bad idea. Think about this: if the index did not need to be rebuilt, then the mistake is a really BIG one, because it will start, and end, as a really BIG index. (In passing, for an 8K block size and an 80 byte key, you can get an index to height 6 on just over 120,000,000 rows).
If you rebuild regularly on 20% deleted rows, you could be rebuilding at just the right time for (a) wasting your effort, and (b) introducing a performance problem. For random data patterns, Oracle tends to re-use space from deleted entries quite effectively. You have to know your application to be certain that this 'magic' 20% really is space that won't be re-used, and that it really will improve performance. (There's an article of mine on www.dbazine.com that gives an example of the sort of thing that can make this a counter-productive operation). Another little drawback to the 20% rule is that it doesn't tell you whether you need to use the rebuild command, or the coalesce command, for the most effective way of cleaning things up.
I've never got round to writing a script for it, but one starting point is simply: is the index much larger than it should be. To check this:
For each B-TREE index
check columns used in user_ind_columns
check average length of columns in user_tab_columns
sum the average column lengths,
add the number of columns plus 10
multiply by the number of rows in the index
Multiply by 1/0.69, on the basis that the steady
state for a random insertion b-tree index will be
about 69% according to published papers on
fringe analysis.
Your b-tree index should be about this size
This is very much a ball-park figure, and does not cater properly for globally partitioned indexes, cluster indexes, space taken by branch blocks (typically less than 2% on an 8K block size), compression, multi-column indexes where the distribution of nulls across the columns is very variable.
For indexes which are much larger than this, you may want to spend a little time investigating how the index is used, and why it might be larger than expected, and whether a rebuild will (a) reclaim useful space or (b) improve performance.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof
March 2004 Charlotte NC OUG - CBO Tutorial
April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Hi
You can use this script.
HTH
Senthil Kumar.
REM
REM rebuild_indx.sql REM =============================================================
prompt
ACCEPT spoolfile CHAR prompt 'Output-file : ';
ACCEPT schema CHAR prompt 'Schema name (% allowed) : ';
prompt
prompt
prompt Rebuild the index when :
prompt - deleted entries represent 20% or more of the current entries
prompt - the index depth is more then 4 levels.
prompt Possible candidate for bitmap index :
prompt - when distinctiveness is more than 99%
prompt
spool &spoolfile
set serveroutput on
set verify off
declare
c_name INTEGER; ignore INTEGER; height index_stats.height%TYPE := 0; lf_rows index_stats.lf_rows%TYPE := 0;del_lf_rows index_stats.del_lf_rows%TYPE := 0; distinct_keys index_stats.distinct_keys%TYPE := 0; cursor c_indx is
('--------------- --------------------------------------- -----------------
c_name := DBMS_SQL.OPEN_CURSOR;
for r_indx in c_indx loop
DBMS_SQL.PARSE(c_name,'analyze index ' || r_indx.owner || '.' ||
r_indx.index_name || ' validate
structure',DBMS_SQL.NATIVE);
select HEIGHT, decode (LF_ROWS,0,1,LF_ROWS), DEL_LF_ROWS,
decode (DISTINCT_KEYS,0,1,DISTINCT_KEYS)
into height, lf_rows, del_lf_rows, distinct_keys
from index_stats;
end loop;
DBMS_SQL.CLOSE_CURSOR(c_name);
end;
/
spool off
set verify on
 |
 |