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

Last updated on MARCH 15, 2022

Applies to:

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:XXXX
Name:P_po_num_to Value:XXXX
...
Name:P_REGENERATE_DOCUMENT Value:Y
ORGID:<ORG_ID>
m_withTerms : Y
Executing the query for header id
doc Identifier: PO
Setting the store flag to Y
Document ID:XXXXXX
Revision Num1
Document SubTypeSTANDARD
Document TypePO
GenerateDocument::2nd Constructor()
setFileName- m_documentType:PO, m_documentId:XXXX
setFileName- m_fileName:XX.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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.