My Oracle Support Banner

Performance Issue Due To High Elapsed Time In ATP Post Plan Processing Program (Doc ID 1570550.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Global Order Promising - Version 11.5.10 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.

Symptoms

On : 11.5.10.2 version, Memory Based Planner

ACTUAL BEHAVIOR
---------------
This program runs 2-3 times a day in Billy and takes 150+ minutes to complete .There are several SQL statements which are getting bad plans due to the table msc_atp_detail_peg_temp. Full table scans are being used when it should be using the index on the item id. Since these SQLs are executed inside a loop, the full scan time adds up. This is a simple fix and we can fix it by adding hints to the cursor. This is standard product code, so you should also open a bug.

EXPECTED BEHAVIOR
-----------------------
Expect to perform better

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
Trigger Plan after completion of Planner it will trigger ATP post plan. Navigation - ASC->Launch

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot .

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
 For 11.5.10
 For 12.1.3  apply the following patch
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.