Re: Rebuild Indexes
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 13 Feb 2004 09:34:56 -0000
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
----- Original Message -----
From: "Senthil Kumar" <senthilkumard@xxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, February 13, 2004 8:59 AM
Subject: RE: Rebuild Indexes
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
select owner, table_name, index_name
from dba_indexes
where owner like upper('&schema')
and owner not in ('SYS','SYSTEM');
begin
dbms_output.enable (1000000);
dbms_output.put_line ('Owner Index Name
% Deleted Entries Blevel Distinctiveness');
dbms_output.put_line
('--------------- --------------------------------------- -----------------
------ ---------------');
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);
ignore := DBMS_SQL.EXECUTE(c_name);
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;
--
-- Index is considered as candidate for rebuild when :
-- - when deleted entries represent 20% or more of the current entries
-- - when the index depth is more then 4 levels.(height starts counting
from 1 so > 5)
-- Index is (possible) candidate for a bitmap index when :
-- - distinctiveness is more than 99%
--
if ( height > 5 ) OR ( (del_lf_rows/lf_rows) > 0.2 ) then
dbms_output.put_line (rpad(r_indx.owner,16,' ') ||
rpad(r_indx.index_name,40,' ') ||
lpad(round((del_lf_rows/lf_rows)*100,3),17,' ') ||
lpad(height-1,7,' ') ||
lpad(round((lf_rows-distinct_keys)*100/lf_rows,3),16,' '));
end if;
end loop;
DBMS_SQL.CLOSE_CURSOR(c_name);
end;
/
spool off
set verify on
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
