When to rebuild indexes and when to defrag : oracle EBS r12.1.3

Well , yet again , i have been working on multiple SR's with oracle for performance on my EBS R12.1.3 system. 

The focus was on performance of seeded programs , reducing time for Gather Schema Statistics and to check if the tables have high level of fragmentation and to rebuild indexes if need be. 

On checking with oracle below were the findings :


1. When and what to defrag : 
To identify object which are highly fragmented you can use segment advisor (tablespace level). 

Segment advisor can run 3 levels 

a) Segment level : Advice is generated for a single segment, such as an unpartitioned table, a partition or subpartition of a partitioned table, an index, or a LOB column. 

b) Object level—Advice is generated for an entire object, such as a table or index. If the object is partitioned, advice is generated on all the partitions of the object. 

c) Tablespace level—Advice is generated for every segment in a tablespace. 



Run the segment advisor in tablespace level. It may take some time to complete depending upon number of objects and size of objects 


variable id number; 
begin 
declare 
name varchar2(100); 
descr varchar2(500); 
obj_id number; 
begin 
name:='<taskname>'; --------------> Replace with task name : Give any name 
descr:='<task desc>'; -----------> Give some description 

dbms_advisor.create_task ( 
advisor_name => 'Segment Advisor', 
task_id => :id, 
task_name => name, 
task_desc => descr); 

dbms_advisor.create_object ( 
task_name => name, 
object_type => 'TABLESPACE', 
attr1 => '<tablespace name>', =====> replace with tablespace name 
attr2 => NULL, 
attr3 => NULL, 
attr4 => NULL, 
attr5 => NULL, 
object_id => obj_id); 

dbms_advisor.set_task_parameter( 
task_name => name, 
parameter => 'recommend_all', 
value => 'TRUE'); 

dbms_advisor.execute_task(name); 
end; 
end; 
/ 


For more information please refer 


http://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm#ADMIN11602 

This will generate a tuning set , now we need to see the recommendations from above , this can be either done using a txt file or a formatted html file. Oracle prefers a formatted html file and hence i was asked to run the below command :

set markup html on spool on 
SPOOL ADVISOR.HTML 
set pagesize 200 
set echo on 


select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message 
from dba_advisor_findings af, dba_advisor_objects ao 
where ao.task_id = af.task_id 
and ao.object_id = af.object_id; 

select tablespace_name, segment_name, segment_type, partition_name, 
recommendations, c1 from 
table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE')); 

spool off 
set markup html off spool off 



This will generate a file by the name advisor.html.. This is an html file . find out the tables which have high level of fragmentation and post that follow the steps below to remove the fragmentation :

1.Find the indexes associated with the table.

2. alter table < tablename>  move; 

3.index  rebuild online; 


2. When to rebuild : 

How to Determine When an Index Should be Rebuilt? (Doc ID 1373415.1) 


Hope this helps. Do revert with your queries.