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. 

ORA-7445:[qksfmKillTest]


Okay.. this one was a killer...  Shivers down the spine when i saw this first in the alert log. Data corruption or segment corruption ? no clue. Checked on metalink , no direct info.. so first things first , whenever you get this error ( or ORA-0600) raise a SEV1 SR. Oracle can tell you the best what to do in these cases. 

So i raised a SEV 1 SR with Oracle. Oracle guys asked for the alert log and the trace files generated. They scanned it and told me that it was an issue with an automatic tuning task and this can be safely ignored. its not a corruption issue. 

Something had killed the automatic tuning task. Oracle suggested to safely ignore this error for the time being and watch if it occurs again. 

They also suggested if i wish to cancel the sql tuning advisor , but since we have been using it often we decided not to. However , it can be disabled by using the query below :

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;

/


Cheers
Orafolks:)

Random children going into 'inactive'-'nomanager'

Ever happened that a request is fired which spawns many children and boom , one or two of them go into 'inactive' -'nomanager'. You have no clue why is that happening because when you see , you still have many managers available to cater to those requests.

Well , the reason is simple , an FND view needs to be rebuild. Without saying more , i suggest you go through the below metalink note :

Concurrent Program Spawns Child That is Going to Inactive No Manager (Doc ID 1621537.1)

Cheers

Orafolks :)

Performance Recommendations for an EBS system

We all are happy working on our EBS Setups.. But do we really know if its setup in the best way ?

The least we can do is to check if the tables are fragmented , if yes how do we defrag them?

Refer to the notes below to get a quick help :

Various Aspects of Fragmentation (Doc ID 186826.1)
Script to Detect Tablespace Fragmentation (Doc ID 1020182.6)
Script to Report Table Fragmentation (Doc ID 1019716.6)



Cheers
Orafolks :)

Integrating SOA gateway with Oracle EBS R12 ( 12.1.3) 

                                     Master note : 556540.1 

Well we find scientists everywhere... even if not in the real sense.. but some nasty developer who has no clue what to do , just running after you to go help in implementing SOA gateway.

First thing he shares is : A error as below :

______________________________________________________________________

IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -v -g ar:patch/115/sql=XXSOA_GTWY_POC.pls
Can't locate Class/MethodMaker.pm in @INC (@INC contains: /u01/appluat/apps/apps_st/appl/fnd/12.0.0/perl /u01/appluat/apps/apps_st/appl/fnd/12.0.0/xml/orc115 /u01/appluat/apps/apps_st/appl/fnd/12.0.0/perl/FND/irep/repo /u01/appluat/apps/apps_st/appl/fnd/12.0.0/bin/ /u01/appluat/apps/tech_st/10.1.3/perl/lib/5.8.3/i686-linux-thread-multi /u01/appluat/apps/tech_st/10.1.3/perl/lib/5.8.3 /u01/appluat/apps/tech_st/10.1.3/perl/lib/site_perl/5.8.3/i686-linux-thread-multi /u01/appluat/apps/tech_st/10.1.3/perl/lib/site_perl/5.8.3 /u01/appluat/apps/apps_st/appl/au/12.0.0/perl /u01/appluat/apps/tech_st/10.1.3/Apache/Apache/mod_perl/lib/site_perl/5.8.3/i686-linux-thread-multi /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/5.8.3/i686-linux-thread-multi /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/5.8.3 /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/site_perl/5.8.3/i686-linux-thread-multi /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/site_perl/5.8.3 /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/site_perl .) at /u01/appluat/apps/apps_st/appl/fnd/12.0.0/perl/FND/irep/repo/Rep/Parser.pm line 24.
BEGIN failed--compilation aborted at /u01/appluat/apps/apps_st/appl/fnd/12.0.0/perl/FND/irep/repo/Rep/Parser.pm line 24.
Compilation failed in require at /u01/appluat/apps/apps_st/appl/fnd/12.0.0/bin/irep_parser.pl line 37.
BEGIN failed--compilation aborted at /u01/appluat/apps/apps_st/appl/fnd/12.0.0/bin/irep_parser.pl line 37.

_______________________________________________________________________________________________

So I scratch my head and google google google and first thing I find is a metalink note : 

When Attempting to Create a Custom Integration Repository Loader (*.ildt) File, the "$FND_TOP/bin/irep_parser.pl" Command Fails "Can't Locate Class/MethodMaker.pm" (Doc ID 1079218.1)

Patch number : 9004712

Applied it right away on the dev box and then since I am on linux box i had do a little extra work : Bug 9060041 : README IN GENERIC PATCH 9004712 PORT SPECIFIC FOR LINUX

Well , it still did not work for me .. However , now the above error was gone , but I was getting an error when we guys were trying to generate a wsdl file . So then , it was very obvious that a lot of setup pre-req things were just missing. First thing and the last thing when implementing anything  -- log an service request with Oracle. 

Oracle team told me to apply the following pre req  patches on my EBS r12.1.3 system:

1. Please apply the following application pre-requisite patches on my 12.1.3 instance ( you may have to add 1-2 here and there depending on your version- refer to master note):

13347633
13957925
14063221
9139673
13516999
14741766
9157774
9070077
9462433
11688301

Also read :

1. For patch 9371120, please review the following note for instructions:

How Does One Apply The Pre-requisite For Patch 9371120 (Web Service Framework Takes More Time for Giving the Response) As Mentioned In Note 556540.1? (Doc ID 1333789.1)

2. For patch 15914125, please review the following note for instructions:

What To Do With Pcapps.rar Delivered In Patch 15914125? (Doc ID 1586876.1)

2. Please apply the following patches on your 10.1.3 home:

13800972
9371120
7366746
15914125

Please note the 10.1.3 patches are to be applied on the application tier after sourcing the env files for 10.1.3 and using the opatch utility.  Also the 10.1.3 version should be 10.1.3.5 ( anything less than that needs another patch to make it 10.1.3.5).-- doc id 454811.1


Well , it still did not work. So while Oracle suggested the next action plan , i had already searched enough on the metalink understanding that the asadmin user needs to be checked. So i followed the steps below :

Login from the sysadmin and then select user management , unlock the asadmin user and set a password for that user. then next step is to set the password in the backend also by doing the below steps :

Reset the "ASADMIN" password in the file system after "ASADMIN" user is enabled from inside EBS by updating the file:

vi $ORA_CONFIG_HOME/10.1.3/j2ee/oafm/config/system-jazn-data.xml

<user>
<name>ASADMIN</name>
<display-name>Default Apps SOA User</display-name>
<description>Used by SOAProvider for DB connection</description>
<credentials>!<NEW PASSWORD></credentials>
</user>

NOTE: The password should be preceded by a '!' (Exclamation) so that when OAFM is started, it gets encrypted. For example, if the password is "welcome", then you have to change it to "!welcome"


I was somewhat feeling that it might just work now.. but alas , nope. Needed to go through more of the meta link. So another note ( and SR) suggested me to run the below queries( and the expected output below) : 

select substr(user_name, 1, 30) name, substr(role_name, 1, 30) role
from wf_local_user_roles
where user_name='ASADMIN';

and 
select substr(user_name, 1, 30) name, substr(role_name, 1, 30) role
from wf_user_roles
where user_name='ASADMIN';

if the output is not as below :

NAME
--------------------------------------------------------------------------------
ROLE
--------------------------------------------------------------------------------
ASADMIN
ASADMIN

ASADMIN
UMX|APPS_SCHEMA_CONNECT 

You need to perform the following steps :

1. Please run the concurrent program " Workflow Directory Services User/Role Validation" in two batches.

Once with the following parameters:

Fix Dangling User/Roles

Default Batchsize=10000
Fix Dangling User/Roles=Yes
Add Missing User/Role Assignments=No
Update WHO columns=Yes

2. After it completes, run it with the following parameters:

Default Batchsize=10000
Fix Dangling User/Roles=No
Add Missing User/Role Assignments=Yes
Update WHO columns=Yes


Post that I was able to get the required output and the test link to confirm if the SOA gateway is integrated properly with the EBS box.


http://myhost.com:port/webservices/SOAProvider/?testquery

and finally ask the scientist (The Developer) to check from his seeded api .

Good luck.

More on this below :

http://www.oracle.com/technetwork/testcontent/oracle-integrated-soa-gateway-data--131065.pdf

Cheers !
OraFolks :)