Order Purge Selection Performance Issue (Doc ID 1912622.1)

Last updated on APRIL 04, 2025

Applies to:

Symptoms

On : 12.1.2 version, Transaction Payment

ACTUAL BEHAVIOR
---------------
We are running the request Order Purge Selection from 10-DEC-2013 16:34:04 and don't finished yet
The parameters are the next for only one month

Find performance issue from CURSOR c_oe_ope_invoice
FUNCTION Check_Open_Invoiced_Orders in PACKAGE BODY OE_ORDER_PURGE_PVT

sql id b69r1b6bcqajn needs to be reviewed

SQL_ID EXECUTIONS FIRST_LOAD_TIME USERS_EXECUTING AVGSECONDS
------------- ---------- -------------------- --------------- ----------
b69r1b6bcqajn 1308 2014-01-16/04:07:12 1 323.43

SQL_ID b69r1b6bcqajn, child number 0
-------------------------------------
SELECT 'Open invoices for this sales order' FROM
RA_CUSTOMER_TRX_LINES_ALL RCTL, RA_CUSTOMER_TRX RCT WHERE
RCTL.INTERFACE_LINE_ATTRIBUTE1 = :B2 AND RCTL.INTERFACE_LINE_ATTRIBUTE2
= :B1 AND RCTL.INTERFACE_LINE_CONTEXT = 'ORDER ENTRY' AND
RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID AND RCT.COMPLETE_FLAG = 'N'

Plan hash value: 1945516594

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 31 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| RA_CUSTOMER_TRX_LINES_ALL | 1 | 19 | 6 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | XXALP_RA_CUSTOMER_TRX_LINES_N2 | 1 | | 5 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 1 | | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL | 1 | 12 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

EXPECTED BEHAVIOR
-----------------------
Expect better performance

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. using order management responsibility
2. submit Order Purge Selection
The parameters are the next for only one month

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
We need evaluate the result for this purge for test and execute after in production

Cause

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