Seeded Program of AutoReconciliation taking too much time

It was bright sunny day and i was listening to music and performing my UAT cloning when i see a mail from an end user who was complaining about the poor performance of the AutoReconciliation program. 

Since this was a seeded program , yet again i had opened an SR with oracle. 

Oracle asked for multiple traces , logs and tkprofs and finally gave me an action plan, which was to run gss at 100% for certain seeded tables. The AutoReconciliation program took 6-7 hours for an X amount of records. After running the GSS , the program completion time came down to 15 mins for the same X amount of records. 

The steps we followed were :

exec fnd_stats.gather_table_stats('CE','CE_BANK_ACCOUNTS',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('CE','CE_BANK_ACCT_USES_ALL',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('CE','CE_CASHFLOWS',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('CE','CE_CASHFLOW_ACCT_H',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('CE','CE_PAYMENT_TRANSACTIONS',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('CE','CE_SECURITY_PROFILES_GT',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('CE','CE_STATEMENT_RECONCILS_ALL',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('CE','CE_SYSTEM_PARAMETERS',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('CE','CE_TRXNS_SUBTYPE_CODES',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('APPLSYS','FND_CURRENCIES',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('APPLSYS','FND_LOOKUP_VALUES',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('GL','GL_DAILY_CONVERSION_TYPES',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('GL','GL_LEDGERS',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('AR','HZ_CODE_ASSIGNMENTS',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('AR','HZ_CONTACT_POINTS',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('AR','HZ_ORGANIZATION_PROFILES',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('AR','HZ_PARTIES',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('AR','HZ_RELATIONSHIPS',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('IBY','IBY_DOCS_PAYABLE_ALL',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('IBY','IBY_PAYMENTS_ALL',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('SYS','TAB$',PERCENT=>100,cascade=>True) ; 
exec fnd_stats.gather_table_stats('XLE','XLE_ENTITY_PROFILES',PERCENT=>100,cascade=>True) ; 

Cheers

Speed up your Gather Schema Statistics process

Well , again to get the best of performance in the system , we had observed that the gather schema statistics , with time , had started to take almost 15 hours. The DB size was about 1700 Gb growing daily by 4-5 GB. 

Most of us generally run Gather Schema Statistics on the weekend with gather option , however we are not aware of the gather_auto option. Gather_auto is the new and recommended way of running the GSS in the EBS system. 

Have adopted the same .Requires a patch :

16410424:R12.FND.B as per Note 1586374.1. 


Post application of this patch , the GSS now completes within 7-8 hours on weekends. 

And yes , since we are always worried about performance , this did not cause any performance harm. None of the end users came back with any issues.


Cheers

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.