SQL In Quality Module Has Bad Performance
(Doc ID 2450533.1)
Last updated on FEBRUARY 10, 2021
Applies to:
Oracle Quality - Version 12.1.3 to 12.2.8 [Release 12.1 to 12.2]Information in this document applies to any platform.
Symptoms
ACTUAL BEHAVIOR
---------------
The following SQL has bad performance.
EXPECTED BEHAVIOR
-----------------------
Better performance.
STEPS
-----------------------
The following SQL is causing the problem:
WITH PARENT_QPCR (PARENT_PLAN_ID, PARENT_COLLECTION_ID, PARENT_OCCURRENCE, CHILD_PLAN_ID, CHILD_COLLECTION_ID, CHILD_OCCURRENCE, HIERLEVEL, CHILD_TXN_HEADER_ID) AS ( SELECT PARENT_PLAN_ID, PARENT_COLLECTION_ID, PARENT_OCCURRENCE, CHILD_PLAN_ID, CHILD_COLLECTION_ID, CHILD_OCCURRENCE, 1 HIERLEVEL, CHILD_TXN_HEADER_ID FROM QA_PC_RESULTS_RELATIONSHIP WHERE PARENT_PLAN_ID = :B1 UNION ALL SELECT CHILD.PARENT_PLAN_ID, CHILD.PARENT_COLLECTION_ID, CHILD.PARENT_OCCURRENCE, CHILD.CHILD_PLAN_ID, CHILD.CHILD_COLLECTION_ID, CHILD.CHILD_OCCURRENCE, PARENT.HIERLEVEL + 1, CHILD.CHILD_TXN_HEADER_ID FROM QA_PC_RESULTS_RELATIONSHIP CHILD JOIN PARENT_QPCR PARENT ON ( PARENT.CHILD_PLAN_ID = CHILD.PARENT_PLAN_ID AND PARENT.CHILD_COLLECTION_ID = CHILD.PARENT_COLLECTION_ID AND PARENT.CHILD_OCCURRENCE = CHILD.PARENT_OCCURRENCE ) ) SELECT 0 CHILD_PLAN_ID, 0 CHILD_COLLECTION_ID, 0 CHILD_OCCURRENCE, PLAN_ID PARENT_PLAN_ID, COLLECTION_ID PARENT_COLLECTION_ID, OCCURRENCE PARENT_OCCURRENCE, 0 LEVELS FROM QA_RESULTS QR WHERE QR.PLAN_ID = :B1 AND QR.TXN_HEADER_ID = :B2 UNION ALL SELECT CHILD_PLAN_ID, CHILD_COLLECTION_ID, CHILD_OCCURRENCE, PARENT_PLAN_ID, PARENT_COLLECTION_ID, PARENT_OCCURRENCE, HIERLEVEL FROM PARENT_QPCR WHERE CHILD_TXN_HEADER_ID = :B2
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 |