slow request

[latifa]

Hi Erman,

The customer has launched a request that takes long time, more than 5 hours.

Looking for the log file, I found the following oracle error:

<< glstmt() 11-JAN-2018 15:23:57

FND stat BEGIN
  FND_STATS.gather_table_stats('GL',
 'GL_SUMMARY_INTERIM_0' ,
   20);
   END;

ORA-01403: no data found


FND stat BEGIN
  FND_STATS.gather_table_stats('GL',
 'GL_SUMMARY_INTERIM_2' ,
   20);
   END;

ORA-01403: no data found

SHRD0117: 67 records inserted in  GL_ACCOUNT_HIERARCHIES

Is this behavior normal ?
what should I do to reduce duration of this request.

Thanks in advance for your help.

Regards,
Latifa

[latifa]

it finished at :

<< glsiah() 11-JAN-2018 20:38:00

[ErmanArslansOracleBlog]

What is the official name of this concurrent program?

[latifa]

Posting: Single Ledger

short name:  GLPPOSS

[ErmanArslansOracleBlog]

Did you check this note? -> GLPPOSS: Posting Has Poor Performance And is Hanging at INSERT INTO GL_ACCOUNT_HIERARCHIES (Doc ID 746560.1)

[latifa]

Very nice Note :) however, It's about 11.5.10.2 ? Is it ok for r12.2.4 ?

[ErmanArslansOracleBlog]

This is for 11.5.10.2 and later.

It redirects you to -> How To Drop and Recreate Summary Templates in Oracle General Ledger (Doc ID 241090.1)

You can implement it on TEST instance first..

However, lets leave it for a while..

First, enable trace for that concurrent program and check the trace of it..
See where it waits for 5 hours.. See the exact sql statement. Report it to me, then we will look again..

[latifa]

Hi Erman,

no trace file generated after enabling trace for this program !! I think it was not enough.
and the request submited by the customer finshed again after more than five hours.

The solution to suppress the templates didn't persuade the customer because it's not definitive.

Regards,
Latifa

[latifa]

Oh ... Sorry I didn't enable trace in a good way:

- to Set the Profile Concurrent: Allow Debugging to YES.
- to ask customer before Submitting the Concurrent Program in Submit Request screen in option like Debug Options to click on that button and select the SQL Trace and enable Trace with binds.


Anyway I will do that in a next time :(

regards,
Latifa

[ErmanArslansOracleBlog]

good.
update me with the outcome.

[ahajami]

Hi,

please refer to the following note (Doc ID 960769.1).

While it is related to 'GL_SUMMARY_INTERIM_2' I suggest to add the Option Profile "GL_SUMMARY_HINT_INS_ACCT_HIER" with the hint "leading(bal parent_cc pcc) use_nl(bal parent_cc pcc) index(bal(period_name))"

Hope this help
Kind regards
Anass