PO Output For Communication Performance Issues But SQL Trace Show No Performance Problems (Doc ID 1575175.1)

Last updated on JUNE 12, 2017

Applies to:

Oracle Purchasing - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

When many PO Output For Communications process are submitted the process takes a long time to complete.

A trace of the process will show the SQL has no performance issue and the small PO's complete in a few seconds.

The log file show the process took many minutes from the
start time (Current system time is 12-JUN-2013 05:14:24)
to the completion time (completed successfully Current system time is 12-JUN-2013 05:25:06)


POXPOPDF module: PO Output for Communication
+---------------------------------------------------------------------------+
Current system time is 12-JUN-2013 05:14:24
+---------------------------------------------------------------------------+

Name:P_report_type Value:R
Name:P_agent_id Value:
Name:P_po_num_from Value:48264
Name:P_po_num_to Value:48264
...
Name:P_REGENERATE_DOCUMENT Value:Y
ORGID:162
m_withTerms : Y
Executing the query for header id
doc Identifier: PO
Setting the store flag to Y
Document ID:67119
Revision Num1
Document SubTypeSTANDARD
Document TypePO
GenerateDocument::2nd Constructor()
setFileName- m_documentType:PO, m_documentId:67119
setFileName- m_fileName:PO_162_48264_1_US.pdf
GenerateDocument::Finished 3rd Constructor()
Setting PO template Code
Setting OKC template Code
Before calling genDocWed Jun 12 05:24:56 EDT 2013
.....

+---------------------------------------------------------------------------+
Concurrent request completed successfully
Current system time is 12-JUN-2013 05:25:06
+---------------------------------------------------------------------------+

The log file show the time to generate the document to be a few seconds.
This is shown by the time stamp and the completion time
  Before calling genDocWed Jun 12 05:24:56
  Current system time is 12-JUN-2013 05:25:06

You will see the jobs are getting queued up in CM queue.

The following select will show the "seconds to run" will be small for the first few jobs then will increase

select program, request_id, to_char(request_date, 'dd-mon-yyyy hh24:mi:ss') request_date,
               to_char(requested_start_date, 'dd-mon-yyyy hh24:mi:ss') requested_start_date,
               to_char(actual_start_date, 'dd-mon-yyyy hh24:mi:ss') actual_start_date,
               round ((actual_start_date - requested_start_date)*24*60*60,2) "seconds to start" ,
               to_char(actual_completion_date, 'dd-mon-yyyy hh24:mi:ss') actual_completion_date,
               round ((actual_completion_date -actual_start_date )*24*60*60,2) "seconds to run"
from FND_CONC_REQ_SUMMARY_V
where program_short_name = 'POXPOPDF'
and trunc(sysdate) = trunc(actual_start_date);




Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms