Do the same thing for standard interface after gathering stats for the Schema involved. You will need the trace file for any SR anyway.
Thanks for the reply.
So you mean that we need to raise an SR?
Apart from this can't we do anything at our end?
1 person found this helpful
The easiest thing to do is gather schema statistics at 100% for the schema involved. If you are doing an invoice interface run it for AP schema for example. If it is still slow, ask the DBAs to verify all cost based optimizer parameters are set correctly:
bde_chk_cbo.sql - EBS initialization parameters - Healthcheck (Doc ID 174605.1)
After that, I would calibrate the I/O system and ensure the database is running as fast as possible.
Gather a trace for the interface job and sort by elapsed and fetch times sort=exeela,fchela. Then look at the top SQLs and query Oracle Support for the objects involved. If you open the concurrent request log, you can copy and paste the interface name into Oracle Support and the word performance to see if there are any known patches for your interface job that you need to apply.
Check which SQL is taking time. and sqltrpt.sql to see the performance tuning report for that sql id. Oracle always gives best recommendations in the performance report. It could be gather stats of few tables, creating indexes..etc But first analyze the performance turning report.