SQL In Quality Module Has Bad Performance (Doc ID 2450533.1)

Last updated on FEBRUARY 10, 2021

Applies to:

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

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