Important update:
The rules for identification of candidates for index coalescing/rebuilding
depend on your specific index state. See MOSC notes 989186.1,
122008.1, 989093.1 for Oracle's suggestions on when to coalesce/rebuild
indexes.
Also see my
updated notes on index rebuilding and note that this DBA has demonstrated the
characteristics indexes that benefits from
scheduled oracle index rebuilding.
Don Burleson recently sent out to one of his Oracle DBA
mailing lists some information about
rebuilding indices (or is it indexes?) His information is very
valuable, and I have a few more observations from my experience at my
previous job.
Burleson notes that Oracle recommends rebuilding
indexes when the b-level is more than four or the percent deleted is
greater than 20.
At my previous employer, the hardware budget was
always very tight. We couldn't always lay out the
tablespaces in the ideal manner to optimize performance, but I think we
did a pretty good job within the constraints we had. We
rarely hit a b-level higher than three, but we discovered that in Oracle
Versions 8i, 9i, and 10g, the larger the table the more noticeable the
performance degradation on index reads as the percentage of deletes goes
up.
On larger tables the performance degradation was
much quicker and more noticeable than on smaller tables. A
large table would see noticeable performance degradation on as few as 5%
delete, whereas on smaller tables it would need to hit 10-15% before the
degradation became noticeable. These were not minor
differences, measured in milliseconds. The performance
changes were dramatic, highly noticeable to end users. For
the purposes of this article, a smaller table is from one hundred
thousand to one million rows, a medium size table could be up to five
million rows, and a large table is more than five million rows.
Obviously this sort of statistic is highly dependent on hardware, but
I suspect there are plenty of Oracle shops that have seen similar
results.
I eventually wrote a simple query that generates a
list of candidates for index rebuilds, and the commands necessary to
rebuild the indexes once the tables reached a point where it was
necessary. The query reads a table we built called
TABLE_MODIFICATIONS that we loaded each night from DBA_TAB_MODIFICATIONS
before we ran statistics. Monitoring must be turned on to use the
DBA_TAB_MODIFICATIONS table.
select
'exec analyzedb.reorg_a_table4('||""||rtrim(t.table_owner)||""||','||""||
rtrim(t.table_name)||""||');',
t.table_owner||'.'||t.table_name name,
a.num_rows,
sum(t.inserts) ins,
sum(t.updates) upd,
sum(t.deletes) del,
sum(t.updates)+sum(t.inserts)+sum(t.deletes) tot_chgs,
to_char((sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0,'999999.99′)
per_del,
round(((sum(t.updates)+sum(t.inserts)+sum(t.deletes))/(decode(a.num_rows,0,1,a.num_rows))
*100.0),2) per_chg
from analyzedb.table_modifications t,
all_tables a
where t.timestamp >= to_date('&from_date','dd-mon-yyyy') and
t.table_owner = a.owner and t.table_owner not in ('SYS','SYSTEM') and
t.table_name=a.table_name
having (sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0 >=5
group by t.table_owner, t.table_name, a.num_rows
order by num_rows desc, t.table_owner, t.table_name;
Of course, your mileage will vary. Reorg_a_table4 was
the stored procedure that actually generated the DDL. It
just reads the data dictionary to get the index name and tablespace name
and generates the alter index rebuild command.
This was a site with a traditional one night per
week operations window, and we usually didn't have enough time to
rebuild everything, so the list was sorted so that those tables most in
need of work were listed first.
and include the URL for the page.
