My Oracle Support Banner

Buyer Work Center - Poor Performance When Selecting Seeded and Saved Filters - Stuck Threads Reported ORDERSSUMMARYHEADERSPG (Doc ID 2867648.1)

Last updated on MAY 06, 2022

Applies to:

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

Symptoms

Using Purchasing, Version 12.2.9

Actual:
Buyers are choosing various filters for locating purchasing documents in the buyer work center, and the performance is poor and many times the page never returns.


Expected:
The proper data should be returned for the filter being used, and there should not be any errors or timeouts.

Errors:

As noted by the User after waiting an extended period of time.
Failure of Web Server bridge
No backend server available for connection: timed out after ## seconds or idempotent set to OFF or method not idempotent
This was the message after 10 minutes time - 600 seconds - as that was the timeout session set for the OA Core.

Error seen in the OA Core Logs:
WatchRule: (SEVERITY = 'Error') AND ((MSGID = 'WL-000337') OR (MSGID = 'BEA-000337'))
WatchData: DATE = Dec 30, 2021 8:19:40 AM CST SERVER = oacore_server27 MESSAGE = [STUCK] ExecuteThread: '7' for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for "618" seconds working on the request "Workmanager: default, Version: 0, Scheduled=true, Started=true, Started time: 618160 ms
[
POST /OA_HTML/OA.jsp?page=/oracle/apps/po/document/order/webui/OrdersSummaryLinesPG&OA_SubTabIdx=1&searchType=advancedSearch&fwkQBSearchTypeSource=/oracle/apps/po/document/order/webui/OrdersSummaryLinesPG__LinesQueryRN__201&_ti=169686982&retainAM=Y&

GET /OA_HTML/OA.jsp?page=/oracle/apps/po/document/order/webui/OrdersSummaryHeadersPG&OAHP=PO_BUYER_HOME_PAGE&OASF=PO_ORDERS_SUMMARY&language_code=US&searchType=customize&fwkQBSearchTypeSource=/oracle/apps/po/document/order/webui/OrdersSummaryHeadersPG__HeaderQueryRN__201&_ti=51371870&oapc=2&retainAM=N&addBreadCrumb=N&OAPB=PO_BRAND&oas=ebh726_i2gU_PIqs7PgiKA.. HTTP/1.1

GET /OA_HTML/OA.jsp?page=/oracle/apps/po/document/order/webui/OrdersSummaryHeadersPG&OAHP=PO_BUYER_HOME_PAGE&OASF=PO_ORDERS_SUMMARY&language_code=US&searchType=customize&fwkQBSearchTypeSource=/oracle/apps/po/document/order/webui/OrdersSummaryHeadersPG__HeaderQueryRN__201&_ti=1877481713&oapc=38&retainAM=N&addBreadCrumb=N&OAPB=PO_BRAND&oas=7856bRIcslSgRdur_mGLaQ.. HTTP/1.1

From trace - and awr reports -
select * from ( SELECT
POH.ACCEPTANCE_DUE_DATE ACKNOWLEDGE_BY,
NVL(POH.ACCEPTANCE_REQUIRED_FLAG, 'N') ACCEPTANCE_REQUIRED_FLAG,
DECODE(POH.APPROVED_FLAG, 'Y', POH.APPROVED_DATE, NULL) APPROVED_DATE,
POH.BILL_TO_LOCATION_ID,
POH.AGENT_ID,
NVL(POH.CANCEL_FLAG, 'N') HEADER_CANCEL_FLAG,
POH.CLOSED_DATE,
NVL(POH.CLOSED_CODE, 'OPEN') HEADER_CLOSED_CODE,
...

NVL(psp.security_position_structure_id,-1)
)
)
)
)
)
AND 'Y' = Decode(Nvl(FND_PROFILE.Value('PO_ENFORCE_PROJ_SECURITY'),'N'),'Y',
PA_PO_INTEGRATION_UTILS.PA_USER_PO_ACCESS_CHECK(poh.po_header_id,:10,'VIEW'),'Y')
) QRSLT ORDER BY ....

Also this sql was noted as being executed over and over:

SELECT DECODE(:B4 , 'USER', USER_ENABLED_FLAG, 'RESP', RESP_ENABLED_FLAG,
'APPL', APP_ENABLED_FLAG, 'SERVER', SERVER_ENABLED_FLAG, 'ORG',
ORG_ENABLED_FLAG, 'SERVRESP', SERVERRESP_ENABLED_FLAG, 'SITE',
SITE_ENABLED_FLAG)
FROM
FND_PROFILE_OPTIONS WHERE PROFILE_OPTION_NAME = :B3 AND PROFILE_OPTION_ID =
:B2 AND APPLICATION_ID = :B1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 980341 106.94 279.59 0 0 0 0
Fetch 980341 2.69 6.80 0 2941023 0 980341
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1960682 109.63 286.40 0 2941023 0 980341



Replication Steps:
The issue can be possibly be reproduced at will with the following steps:
1. Buyer Work Center - Orders Tab
2. Choose a saved filter or even one of the seeded filters 'My Open Orders'
3. Proceed with the search and find it is taking a very long time.



Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References


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