Forms use a bad explain plan but same sql from sqlplus uses the good plan
I am working on a particular performance issue in the ASCP Workbench in EBS 12.1.3.
When open Supply/Demand form the following sql is run
SELECT 1
FROM MSC_FULL_PEGGING
WHERE PLAN_ID = :b1 AND TRANSACTION_ID = :b2
That sql uses the following explain plan which is really bad.
Plan hash value: 3219769060
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | | | 0 |00:00:05.07 | 4604K| | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 48 | 480 | 2 (0)| 00:00:01 | KEY | KEY | 0 |00:00:05.07 | 4604K| | | |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| MSC_FULL_PEGGING | 1 | 48 | 480 | 2 (0)| 00:00:01 | KEY | KEY | 0 |00:00:05.07 | 4604K| | | |
|* 3 | INDEX RANGE SCAN | MSC_FULL_PEGGING_N1 | 1 | 1 | | 2 (0)| 00:00:01 | KEY | KEY | 5156K|00:00:00.96 | 21092 | 1025K| 1025K| |