My Oracle Support Banner

Order Purge Selection Performance Issue (Doc ID 1912622.1)

Last updated on FEBRUARY 03, 2019

Applies to:

Oracle Order Management - Version 12.1.2 and later
Information in this document applies to any platform.

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

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


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.