| rebuild index [message #555672] |
Sun, 27 May 2012 00:09  |
 |
priteshranjan.preet
Messages: 131
Registered: April 2012
Location: delhi
|
Senior Member |
|
|
In which condition it is required to rebuild the index and how can identified it?
thanks & regards
pritesh ranjan
|
|
|
|
| Re: rebuild index [message #555673 is a reply to message #555672] |
Sun, 27 May 2012 00:44  |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
There are only few reasons (and the worst one you'll find somewhere is to do it every X days).
2 of these reasons for instance:
- When you did a bulk delete and don't want to reinsert data (archiving or purging)
- When some direct path load invalidated them
Regards
Michel
[Updated on: Sun, 27 May 2012 01:12] Report message to a moderator |
|
|
|
|
|
|
|
| Re: rebuild index [message #555910 is a reply to message #555745] |
Tue, 29 May 2012 20:04 |
 |
SSharma
Messages: 17
Registered: July 2011
Location: INDIA
|
Junior Member |
|
|
Fragmentation in index/Index is growing in sizes and the deleted space is not reused :
Occurs when there are frequent deletes/inserts on a table . As a result its possible that leaf entries are deleted and they are not reused . A position comes when this ratio (del_lf_rows/lf_rows)*100 exceeds 30 . At that time you need to rebuild index
[Updated on: Mon, 04 June 2012 13:15] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: rebuild index [message #559101 is a reply to message #556489] |
Thu, 28 June 2012 14:04 |
 |
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
I followed all the recommendations by Oracle as to when to rebuild indexes but found out that if you rebuild all indexes online and rerun statistics on them, that some queries will run more optimal, reducing the load on the db.
SQL > alter index scott.pk_emp rebuild nologging online;
Index altered.
SQL > alter index scott.pk_emp logging;
Index altered.
SQL > execute dbms_stats.gather_index_stats('SCOTT','PK_EMP',estimate_percent=>100)
PL/SQL procedure successfully completed.
I use the following query to monitor how much memory I am processing per day to see how much benefit the index rebuilds were:
ECSCDAP1P > @dba_hist_seg_stat_AVERAGE_LOGICAL_READS_PER_HOUR.sql
DATE AVG_GIG_PER_MIN
-------------------- ---------------
2012-06-19 Tuesday 818.2
2012-06-20 Wednesday 779.9 All indexes rebuilt at midnight.
2012-06-21 Thursday 369.3 Usage the next day drops
2012-06-22 Friday 343.8 Remains low the following days.
2012-06-23 Saturday 322.7
2012-06-24 Sunday 321.7
2012-06-25 Monday 367.4
2012-06-26 Tuesday 356.3
2012-06-27 Wednesday 358.5
9 rows selected.
ECSCDAP1P > list
1 select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",
2 sum(b.LOGICAL_READS_DELTA)*8192/1024/1024/1024/60/24 Avg_GIG_PER_min
3 from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
4 where a.object_id=b.OBJ#
5 and c.snap_id=b.snap_id
6 and c.begin_interval_time > trunc(sysdate-29)
7 and c.begin_interval_time < trunc(sysdate)
8 and b.instance_number=c.instance_number
9 group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day')
10* order by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day')
By monitoring the logical gigabytes pressed per minute, I can see the usage grow over several months and I know that I have to rebuild ALL the application indexes.
I have used snapshots of v$segment_statistics when cpu was too high to see which objects were being processed the most, rebuilt all the indexes on the tables being processed the most, ran stats on these indexes and the cpu usage dropped back to normal.
SQL > select owner, index_name from dba_indexes where table_name='EMP';
OWNER INDEX_NAME
------------------------------ ------------------------------
SCOTT PK_EMP
Alan
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
