R12 Performance Issue With PRC: Generate Intercompany Invoices For A Range Of Projects (Doc ID 2147679.1)

Last updated on JANUARY 31, 2024

Applies to:

Symptoms

Performance issue with PRC: Generate Intercompany Invoices for a Range of Projects on 12.2.4



STEPS
-----------------------
The issue can be reproduced at will with the following steps:

      Run PRC: Generate Intercompany Invoices for a Range of Projects

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) OPT_ESTIMATE(@"innerQuery", INDEX_FILTER, "PA_DRAFT_REVENUES_ALL", "PA_DRAFT_REVENUES_N5", ROWS=0) OPT_ESTIMATE(@"innerQuery", INDEX_FILTER, "PA_DRAFT_REVENUES_ALL", "PA_DRAFT_REVENUES_N13", ROWS=0) OPT_ESTIMATE(@"innerQuery", INDEX_FILTER, "PA_DRAFT_REVENUES_ALL", "PA_DRAFT_REVENUES_N12", ROWS=0) */ C1, C2, C3 FROM (SELECT /*+ qb_name("innerQuery") INDEX( "PA_DRAFT_REVENUES_ALL" "PA_DRAFT_REVENUES_U1") */ COUNT(*) AS C1, 4294967295 AS C2, COUNT(*) AS C3 FROM "PA"."PA_DRAFT_REVENUES_ALL" "PA_DRAFT_REVENUES_ALL" WHERE ("PA_DRAFT_REVENUES_ALL"."PROJECT_ID"=:B1) AND ("PA_DRAFT_REVENUES_ALL"."DRAFT_REVENUE_NUM"<= (SELECT MAX("PA_CUST_EVENT_RDL_ALL"."DRAFT_REVENUE_NUM") "MAX(RDL.DRAFT_REVENUE_NUM)" FROM "PA"."PA_CUST_EVENT_RDL_ALL" "PA_CUST_EVENT_RDL_ALL","PA"."PA_EVENT_TYPES" "ET","PA"."PA_DRAFT_INVOICE_ITEMS" "DII","PA"."PA_EVENTS" "E","PA"."PA_EVENT_TYPES" "ET","PA"."PA_CUST_EVENT_RDL_ALL" "PA_CUST_EVENT_RDL_ALL" WHERE "PA_CUST_EVENT_RDL_ALL"."PROJECT_ID"=:B2 AND NVL("DII"."EVENT_TASK_ID",(-99))=NVL("PA_CUST_EVENT_RDL_ALL"."TASK_ID",(-99)) AND "DII"."EVENT_NUM"="PA_CUST_EVENT_RDL_ALL"."EVENT_NUM" AND "DII"."PROJECT_ID"="PA_CUST_EVENT_RDL_ALL"."PROJECT_ID" AND NVL("E"."TASK_ID",(-99))=NVL("PA_CUST_EVENT_RDL_ALL"."TASK_ID",(-99)) AND "E"."EVENT_NUM"="PA_CUST_EVENT_RDL_ALL"."EVENT_NUM" AND "E"."PROJECT_ID"="PA_CUST_EVENT_RDL_ALL"."PROJECT_ID" AND "PA_CUST_EVENT_RDL_ALL"."ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')) AND "ET"."EVENT_TYPE_CLASSIFICATION"='AUTOMATIC' AND "E"."EVENT_TYPE"="ET"."EVENT_TYPE" AND "DII"."DRAFT_INVOICE_NUM"=:B3 AND "DII"."PROJECT_ID"=:B4 AND "DII"."EVENT_NUM" IS NOT NULL AND "E"."PROJECT_ID"=:B5 AND "E"."EVENT_TYPE"="ET"."EVENT_TYPE" AND "ET"."EVENT_TYPE_CLASSIFICATION"='AUTOMATIC' AND "DII"."EVENT_NUM"="PA_CUST_EVENT_RDL_ALL"."EVENT_NUM" AND "DII"."PROJECT_ID"="PA_CUST_EVENT_RDL_ALL"."PROJECT_ID" AND "PA_CUST_EVENT_RDL_ALL"."ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')) AND "PA_CUST_EVENT_RDL_ALL"."PROJECT_ID"=:B6 AND NVL("DII"."EVENT_TASK_ID",(-99))=NVL("PA_CUST_EVENT_RDL_ALL"."TASK_ID",(-99)) AND NVL("E"."TASK_ID",(-99))=NVL("PA_CUST_EVENT_RDL_ALL"."TASK_ID",(-99)) AND "E"."EVENT_NUM"="PA_CUST_EVENT_RDL_ALL"."EVENT_NUM"))) innerQuery

Changes

Cause

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