First K Rows Optimization : Query Performance issue with ROWNUM predicate
October 8, 2009 2 Comments
Post 10g Upgrade, have you ever come across of a Performance Issue of a Query with ROWNUM predicate ? Last few weeks, I was busy with two back to back issues, reported by two different customers, of a Query Performance Issue due to ROWNUM predicates. Since this was reported by two different customers, the problem reported were different, but ended up as what seemed to be a First K Rows Optimization Issue. The issue reported were :
- Performance of a Query, on UAT, degraded after creating a new Index. This new Index is created as a part of new deployment and is impacting a query already deployed sucessfully on Production.
- Performance of a Query, on production, degraded after upgrading to 10.2.0.3 from 9.2.0.8. Further, upgrading to 10.2.0.4 has also not helped as yet and it continues to consume most of the resources.
Prior to Oracle 9i, the only two cost-based optimizer modes available were either ALL_ROWS and FIRST_ROWS. The former was either explicitly specified, by way of initialization parameter or hint, or was triggered when the following two conditions were met: optimizer_mode=CHOOSE and statistics on any of the tables in the query were gathered. FIRST_ROWS optimization was triggered, either by way of parameter setting or Hint.
The Optimization Rules and Calculations of “First K Rows Optimization” is analogous to First_Rows_n, but does not require any specific parameter settings or Hints and is automatically invoked when some of the conditions are met. One such condition is a ROWNUM predicate. If a query block contains a ROWNUM predicate, then First K Rows optimization will be automatically invoked, where the value of K is set to the value specified to a ROWNUM predicate. For example, if a query block contains a predicate ROWNUM=1 or ROWNUM=10, then the value of K, for First K Rows Optimization, will be evaluated to 1 or 10, as a result, First_rows_n optimization will be triggered.
First K Rows Optimization working is similar to First_Rows_n. With this, the optimizer first estimates the number of rows that will be returned by completely analyzing the first Join Order. Once this is done, and the estimated rows to be processed is computed, the entire optimization is redone to find the execution plan that minimizes the resource consumption to process the query. It is the first step of calculatng estimated number of rows that drives whether the final execution plan generated will be optimal or sub-optimal.
ISSUE 1
In this case, the query has been deployed sucessfully on production and is running perfectly fine. On the UAT, a New Index was created for deployment of new module. While the creation of this new index optimized the to-be-deployed module, it impacted another module and this concern was raised by the customer. In this case, the better option was to drop the new index and re-optimize the to-be-deployed module, in such a way, that this new Index is not required.
Query and Analysis
SELECT 'X' FROM NBFC_PMNT_DTL
WHERE TXNADVICEID IN
(SELECT (TXNADVICEID + 0 ) FROM NBFC_TXN_ADVICE_DTL
WHERE CASEID = :b1
AND STATUS = 'A'
AND TXNTYPE IN ( 'A','P' )
AND CHARGEID IN
(SELECT CHARGEID FROM NBFC_CHARGES_M WHERE CHARGECODEID = 2 ))
AND STATUS IS NULL
AND ROWNUM = 1
FROM UAT
========
-------------------------------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
-------------------------------------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 4 | | | |
| 1 | COUNT STOPKEY | | | | | | | |
| 2 | FILTER | | | | | | | |
| 3 | PARTITION RANGE ALL | | 1 | 9 | 2 | 00:00:01 | 1 | 21 |
| 4 | TABLE ACCESS FULL | NBFC_PMNT_DTL | 1 | 9 | 2 | 00:00:01 | 1 | 21 |
| 5 | NESTED LOOPS | | 1 | 32 | 2 | 00:00:01 | | |
| 6 | PARTITION RANGE SINGLE | | 1 | 21 | 1 | 00:00:01 | KEY | KEY |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID | NBFC_TXN_ADVICE_DTL | 1 | 21 | 1 | 00:00:01 | KEY | KEY |
| 8 | INDEX RANGE SCAN | NBFC_TXN_ADVICE_DTL_IDX5| 1 | | 1 | 00:00:01 | KEY | KEY |
| 9 | TABLE ACCESS BY INDEX ROWID | NBFC_CHARGES_M | 1 | 11 | 1 | 00:00:01 | | |
| 10 | INDEX UNIQUE SCAN | NBFC_CHARGES_PK | 1 | | 1 | 00:00:01 | | |
-------------------------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
1 - filter(ROWNUM=1)
2 - filter( IS NOT NULL)
4 - filter("STATUS" IS NULL)
7 - filter(("TXNTYPE"='A' OR "TXNTYPE"='P'))
8 - access("STATUS"='A' AND "CASEID"=:B1)
8 - filter("TXNADVICEID"+0=:B1)
9 - filter("CHARGECODEID"=2)
10 - access("CHARGEID"="CHARGEID")
FROM PRODUCTION
===============
--------------------------------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
--------------------------------------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 7 | | | |
| 1 | COUNT STOPKEY | | | | | | | |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID | NBFC_PMNT_DTL | 1 | 9 | 1 | 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 22 | 7 | 00:00:01 | | |
| 4 | VIEW | VW_NSO_1 | 1 | 13 | 4 | 00:00:01 | | |
| 5 | HASH UNIQUE | | 1 | 31 | | | | |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID | NBFC_TXN_ADVICE_DTL | 1 | 21 | 3 | 00:00:01 | | |
| 7 | NESTED LOOPS | | 1 | 31 | 4 | 00:00:01 | | |
| 8 | TABLE ACCESS BY INDEX ROWID | NBFC_CHARGES_M | 1 | 10 | 1 | 00:00:01 | | |
| 9 | INDEX RANGE SCAN | NBFC_CHARGES_M_IDX1 | 1 | | 1 | 00:00:01 | | |
| 10 | PARTITION RANGE SINGLE | | 39 | | 1 | 00:00:01 | KEY | KEY |
| 11 | INDEX RANGE SCAN | NBFC_TXN_ADVICE_DTL_IDX3| 39 | | 1 | 00:00:01 | KEY | KEY |
| 12 | PARTITION RANGE ITERATOR | | 1 | | 1 | 00:00:01 | KEY | KEY |
| 13 | INDEX RANGE SCAN | NBFC_PMNT_DTL_IDX1 | 1 | | 1 | 00:00:01 | KEY | KEY |
--------------------------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
1 - filter(ROWNUM=1)
2 - filter("STATUS" IS NULL)
6 - filter(("STATUS"='A' AND INTERNAL_FUNCTION("TXNTYPE")))
9 - access("CHARGECODEID"=2)
11 - access("CASEID"=:B1 AND "CHARGEID"="CHARGEID")
13 - access("TXNADVICEID"="$nso_col_1")
NBFC_TXN_ADVICE_DTL_IDX5 index, used on UAT, is a new Index. The query on production uses another index i.e.NBFC_TXN_ADVICE_DTL_IDX3. Though the statistics on production and uat are different, these were enough to get to the root cause of the issue. With ROWNUM=1 predicate, both the queries were optimized using First K Rows optimization, but the estimated number of rows, at the very first step played a major role in generating the final execution plan. On UAT, the New index is on STATUS,CASEID columns, while on production, the index IDX3 is only on CASEID. We all know that First_Row_n optimization means, fetch the Initial N rows as fast as possible. On UAT, since the Index is on two columns, based on the Distinct Keys (#DK), the optimizer estimates only 1 row (#Rows/#DK), while on Production, with IDX3, it estimates 39 Rows. As soon as the New Index (IDX5) on UAT was dropped, the estimated cardinality increased to 9 and this restored the optimized plan similar to production.
PRODUCTION Stats for NBFC_TXN_ADVICE_DTL
========================================
Table Stats::
Table: NBFC_TXN_ADVICE_DTL Alias: NBFC_TXN_ADVICE_DTL (Using composite stats)
#Rows: 893378705 #Blks: 985102 AvgRowLen: 162.00
Index Stats::
Index: NBFC_TXN_ADVICE_DTL_IDX3 Col#: 28 3
USING COMPOSITE STATS
LVLS: 3 #LB: 3341430 #DK: 23223154 LB/K: 1.00 DB/K: 22.00 CLUF: 531943755.00
UAT Stats for NBFC_TXN_ADVICE_DTL
==================================
Table Stats::
Table: NBFC_TXN_ADVICE_DTL Alias: NBFC_TXN_ADVICE_DTL (Using composite stats)
#Rows: 20847998 #Blks: 8756 AvgRowLen: 164.00
Index Stats::
Index: NBFC_TXN_ADVICE_DTL_IDX3 Col#: 28 3
USING COMPOSITE STATS
LVLS: 2 #LB: 52180 #DK: 2236889 LB/K: 1.00 DB/K: 5.00 CLUF: 12030805.00
Index: NBFC_TXN_ADVICE_DTL_IDX5 Col#: 17 28 27 1
USING COMPOSITE STATS
LVLS: 2 #LB: 78305 #DK: 16932120 LB/K: 1.00 DB/K: 1.00 CLUF: 11248060.00
ISSUE 2
Fortunately, I could spend more time on this issue and the analysis in this case, I feel, should be of interest to you. This was an Apps seeded query and the performance of this query degraded post 10g Upgrade (10.2.0.3). The issue again was due to ROWNUM=1 predicate, and I could confirm this by comparing the response time and consistent gets of the query after and before removing ROWNUM=1 predicate and the difference was huge. This query is a part of a critical transactions, that use to take 4-5 seconds in 9208, but takes 20-30 minutes after the upgrade. On 10.2.0.3, the query performance improves after setting Optimizer_features_enable (OFE) to 9.2.0.8 or 10.1.0, but this would not be a feasible solution as it required testing of the entire application. The Query and the plan with OFE as default and set to 9.2.0.8 is pasted below :
SQL_ID SQL_TEXT BUFFER_GETS EXECUTIONS
------------- ------------------------------------------------------------ ----------- ----------
a15w9fc43c6gr SELECT UOM.UOM_CODE FROM MTL_UNITS_OF_MEASURE_VL UOM WHERE U 2352410716 55
OM.UNIT_OF_MEASURE = ( SELECT PL.UNIT_MEAS_LOOKUP_CODE FROM
PO_LINES_ALL PL WHERE (PL.PO_HEADER_ID,PL.PO_LINE_ID) = ( SE
LECT PD.PO_HEADER_ID, PD.PO_LINE_ID FROM PO_DISTRIBUTIONS_AL
L PD WHERE PD.PO_DISTRIBUTION_ID IN ( SELECT DISTINCT RT.PO_
DISTRIBUTION_ID FROM RCV_TRANSACTIONS RT WHERE RT.WIP_ENTITY
_ID = :B4 AND NVL(RT.WIP_REPETITIVE_SCHEDULE_ID, -1) = NVL(:
B3 , -1) AND RT.ORGANIZATION_ID = :B2 AND RT.WIP_OPERATION_S
EQ_NUM = :B1 AND RT.PO_DISTRIBUTION_ID IS NOT NULL ) AND ROW
NUM = 1 ) )
## Run time BAD Plan
--------------------
Plan hash value: 455173600
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID | MTL_UNITS_OF_MEASURE_TL | 1 | 16 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | MTL_UNITS_OF_MEASURE_TL_U1 | 1 | | 1 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID | PO_LINES_ALL | 1 | 16 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PO_LINES_U1 | 1 | | 1 (0)| 00:00:01 |
|* 5 | COUNT STOPKEY | | | | | |
| 6 | NESTED LOOPS SEMI | | 2 | 80 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | PO_DISTRIBUTIONS_ALL | 1275K| 19M| 2 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID| RCV_TRANSACTIONS | 1 | 24 | 1 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | RCV_TRANSACTIONS_N10 | 1 | | 1 (0)| 00:00:01 |
|* 10 | COUNT STOPKEY | | | | | |
| 11 | NESTED LOOPS SEMI | | 2 | 80 | 3 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | PO_DISTRIBUTIONS_ALL | 1275K| 19M| 2 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | RCV_TRANSACTIONS | 1 | 24 | 1 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | RCV_TRANSACTIONS_N10 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("UNIT_OF_MEASURE"= AND "LANGUAGE"=USERENV('LANG'))
3 - filter("PL"."PO_HEADER_ID"=)
4 - access("PL"."PO_LINE_ID"=)
5 - filter(ROWNUM=1)
8 - filter(("RT"."PO_DISTRIBUTION_ID" IS NOT NULL AND "RT"."ORGANIZATION_ID"=:B2 AND
NVL("RT"."WIP_REPETITIVE_SCHEDULE_ID",(-1))=NVL(:B3,(-1)) AND
"PD"."PO_DISTRIBUTION_ID"="RT"."PO_DISTRIBUTION_ID"))
9 - access("RT"."WIP_ENTITY_ID"=:B4 AND "RT"."WIP_OPERATION_SEQ_NUM"=:B1)
filter("RT"."WIP_OPERATION_SEQ_NUM"=:B1)
10 - filter(ROWNUM=1)
13 - filter(("RT"."PO_DISTRIBUTION_ID" IS NOT NULL AND "RT"."ORGANIZATION_ID"=:B2 AND
NVL("RT"."WIP_REPETITIVE_SCHEDULE_ID",(-1))=NVL(:B3,(-1)) AND
"PD"."PO_DISTRIBUTION_ID"="RT"."PO_DISTRIBUTION_ID"))
14 - access("RT"."WIP_ENTITY_ID"=:B4 AND "RT"."WIP_OPERATION_SEQ_NUM"=:B1)
filter("RT"."WIP_OPERATION_SEQ_NUM"=:B1)
## With OFE set to 9208
------------------------
------------------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 9 | |
| 1 | TABLE ACCESS BY INDEX ROWID | MTL_UNITS_OF_MEASURE_TL | 1 | 16 | 2 | 00:00:01 |
| 2 | INDEX UNIQUE SCAN | MTL_UNITS_OF_MEASURE_TL_U1| 1 | | 2 | 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | PO_LINES_ALL | 1 | 16 | 2 | 00:00:01 |
| 4 | INDEX UNIQUE SCAN | PO_LINES_U1 | 1 | | 2 | 00:00:01 |
| 5 | COUNT STOPKEY | | | | | |
| 6 | NESTED LOOPS | | 1 | 40 | 4 | 00:00:01 |
| 7 | SORT UNIQUE | | 1 | 24 | 2 | 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | RCV_TRANSACTIONS | 1 | 24 | 2 | 00:00:01 |
| 9 | INDEX RANGE SCAN | RCV_TRANSACTIONS_N10 | 1 | | 2 | 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | PO_DISTRIBUTIONS_ALL | 1 | 16 | 2 | 00:00:01 |
| 11 | INDEX UNIQUE SCAN | PO_DISTRIBUTIONS_U1 | 1 | | 2 | 00:00:01 |
| 12 | COUNT STOPKEY | | | | | |
| 13 | NESTED LOOPS | | 1 | 40 | 4 | 00:00:01 |
| 14 | SORT UNIQUE | | 1 | 24 | 2 | 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | RCV_TRANSACTIONS | 1 | 24 | 2 | 00:00:01 |
| 16 | INDEX RANGE SCAN | RCV_TRANSACTIONS_N10 | 1 | | 2 | 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | PO_DISTRIBUTIONS_ALL | 1 | 16 | 2 | 00:00:01 |
| 18 | INDEX UNIQUE SCAN | PO_DISTRIBUTIONS_U1 | 1 | | 2 | 00:00:01 |
------------------------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("UNIT_OF_MEASURE"= AND "LANGUAGE"=USERENV('LANG'))
3 - filter("PL"."PO_HEADER_ID"=)
4 - access("PL"."PO_LINE_ID"=)
5 - filter(ROWNUM=1)
8 - filter(("RT"."PO_DISTRIBUTION_ID" IS NOT NULL AND "RT"."ORGANIZATION_ID"=:B2 AND NVL("RT"."WIP_REPETITIVE_SCHEDULE_ID",(-1))=NVL(:B3,(-1))))
9 - access("RT"."WIP_ENTITY_ID"=:B4 AND "RT"."WIP_OPERATION_SEQ_NUM"=:B1)
9 - filter("RT"."WIP_OPERATION_SEQ_NUM"=:B1)
11 - access("PD"."PO_DISTRIBUTION_ID"="RT"."PO_DISTRIBUTION_ID")
12 - filter(ROWNUM=1)
15 - filter(("RT"."PO_DISTRIBUTION_ID" IS NOT NULL AND "RT"."ORGANIZATION_ID"=:B2 AND NVL("RT"."WIP_REPETITIVE_SCHEDULE_ID",(-1))=NVL(:B3,(-1))))
16 - access("RT"."WIP_ENTITY_ID"=:B4 AND "RT"."WIP_OPERATION_SEQ_NUM"=:B1)
16 - filter("RT"."WIP_OPERATION_SEQ_NUM"=:B1)
18 - access("PD"."PO_DISTRIBUTION_ID"="RT"."PO_DISTRIBUTION_ID")
The BAD plan does Full Table Scan of PO_DISTRIBUTIONS_ALL, while the Good Plan, does an Unique Index Scan of this table. The Difference in I/O is 32 Million Logical Reads as against 22 Logical Reads. For my analysis, as I know the issue was with ROWNUM predicate, I removed the outer query block and retained only the relevant portion. The final query, that I worked on is pasted below :
SELECT PD.PO_HEADER_ID, PD.PO_LINE_ID FROM apps.PO_DISTRIBUTIONS_ALL PD WHERE PD.PO_DISTRIBUTION_ID IN (SELECT DISTINCT RT.PO_DISTRIBUTION_ID FROM apps.RCV_TRANSACTIONS RT WHERE RT.WIP_ENTITY_ID = :B4 AND NVL(RT.WIP_REPETITIVE_SCHEDULE_ID, -1) = NVL(:B3 , -1) AND RT.ORGANIZATION_ID = :B2 AND RT.WIP_OPERATION_SEQ_NUM = :B1 AND RT.PO_DISTRIBUTION_ID IS NOT NULL ) AND ROWNUM = 1 ;
First K Row Optimization is not invoked, if the query block does not contain ROWNUM predicate, or, if you provide ALL_ROWS hint to the query, or if you provide PARALLEL hint into the query. Therefore, to make sure that this is a First K Row Optimization Issue, I executed the queries without ROWNUM predicate, with ALL_ROWS hint and with PARALLEL hint, and all these ran perfectly fine. With this analysis, I was sure that this seems to be an Issue with First K Rows Optimization. Unfortunately, there is no way to disable first k row optimization for this particular query, because of rownum=1. 10053 trace of the query reveals that the computed cardinality of the inner query on RCV_TRANSACTIONS is 1. See the bold portion below :
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#49): WIP_ENTITY_ID(NUMBER)
AvgLen: 2.00 NDV: 100442 Nulls: 8235076 Density: 9.9560e-06 Min: 20 Max: 1901958
Column (#51): WIP_REPETITIVE_SCHEDULE_ID(NUMBER)
AvgLen: 22.00 NDV: 0 Nulls: 8996812 Density: 0.0000e+00 Min: 0 Max: 0
Column (#46): ORGANIZATION_ID(NUMBER)
AvgLen: 4.00 NDV: 72 Nulls: 0 Density: 0.013889 Min: 10 Max: 703
Column (#52): WIP_OPERATION_SEQ_NUM(NUMBER)
AvgLen: 2.00 NDV: 169 Nulls: 8235076 Density: 0.0059172 Min: 1 Max: 2055
Table: RCV_TRANSACTIONS Alias: RT
Card: Original: 8996812 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00
The computed cardinality of 1, alongwith the ROWNUM=1 predicate in the outer query block seems to be an issue. To get back to the 9i plan, I had to fake the statistics (NUM_ROWS) of RCV_TRANSACTIONS table, to a value, such that the computed cardinality increases from 1 to 65. Once done, the query performance improved and the transaction response time was restored from 20-30 minutes to 4-5 seconds.
In my opinion, the generation of sub-optimal execution plan with First K Rows Optimization seems to be a BUG. Therefore, for this issue (Issue no.2), the customer has raised an SR and I am currently working closed with the SR Analysts to get a fix of this issue. To reproduce this issue inhouse, I prepared a test case and with default optimizer settings, I could reproduce it on my local database. The test case is pasted below. If you are not able to reproduce the issue, do send a comment and I shall take this up for further investigation.
drop table po_distributions_all ;
drop table RCV_TRANSACTIONS;
exec dbms_random.seed(0);
create table po_distributions_all as
select rownum po_distribution_id, round(dbms_random.value(1,100000),0) po_header_id,
round(dbms_random.value(100001,1000000),0) po_line_id,
rpad(dbms_random.string('A',20),100,'*') po_name
from all_objects
where rownum<=10000;
create unique index po_distributions_u1 on po_distributions_all(po_distribution_id);
drop table rcv_transactions;
create table rcv_transactions as
select round(dbms_random.value(1,10000),0) po_distribution_id, round(dbms_random.value(1000,10000000),0) wip_entity_id,
100000 wip_repetitive_schedule_id, 1 wip_line_id, mod(rownum,72) organization_id,
mod(rownum,169) wip_operation_seq_num
from all_objects
where rownum<=10000;
update rcv_transactions set wip_repetitive_schedule_id=null;
create index rcv_transactions_n10 on rcv_transactions(wip_entity_id, wip_line_id, wip_operation_seq_num);
exec dbms_stats.gather_table_stats(user,'rcv_transactions',cascade=>true,method_opt=>'for all columns size 1');
exec dbms_stats.set_table_stats(user,'po_distributions_all',numrows=>1394862,numblks=>42951);
exec dbms_stats.set_column_stats(user,'po_distributions_all',colname=>'po_distribution_id',DISTCNT=>1394862,density=>1/1394862,nullcnt=>0);
exec dbms_stats.set_table_stats(user,'rcv_transactions',numrows=>8996812,numblks=>452191);
exec dbms_stats.set_column_stats(user,'rcv_transactions',colname=>'wip_entity_id',DISTCNT=>100442,density=>1/100442,nullcnt=>8235076);
exec dbms_stats.set_column_stats(user,'rcv_transactions',colname=>'wip_line_id',DISTCNT=>1,density=>1,nullcnt=>round(8996812*95/100,0));
exec dbms_stats.set_column_stats(user,'rcv_transactions',colname=>'wip_operation_seq_num',DISTCNT=>169,density=>1/169,nullcnt=>8235076);
exec dbms_stats.set_column_stats(user,'rcv_transactions',colname=>'po_distribution_id',DISTCNT=>1057573,density=>1/1057573,nullcnt=>6792851);
variable b1 number;
variable b2 number;
variable b3 number;
variable b4 number;
exec :b1:=5;
exec :b2:=5;
exec :b3:=null;
exec :b4:=4449100;
SELECT /*+ VIVEK_ORIGINAL */ PD.PO_HEADER_ID, PD.PO_LINE_ID
FROM PO_DISTRIBUTIONS_ALL PD
WHERE PD.PO_DISTRIBUTION_ID IN
( SELECT DISTINCT RT.PO_DISTRIBUTION_ID
FROM RCV_TRANSACTIONS RT
WHERE RT.WIP_ENTITY_ID = :B4
AND NVL(RT.WIP_REPETITIVE_SCHEDULE_ID, -1) = NVL(:B3 , -1)
AND RT.ORGANIZATION_ID = :B2
AND RT.WIP_OPERATION_SEQ_NUM = :B1
AND RT.PO_DISTRIBUTION_ID IS NOT NULL )
AND ROWNUM = 1;
## Fake Statistics
exec dbms_stats.set_table_stats(user,'rcv_transactions',numrows=>1118996812,numblks=>452191);
SELECT /*+ VIVEK_FAKE */ PD.PO_HEADER_ID, PD.PO_LINE_ID
FROM PO_DISTRIBUTIONS_ALL PD
WHERE PD.PO_DISTRIBUTION_ID IN
( SELECT DISTINCT RT.PO_DISTRIBUTION_ID
FROM RCV_TRANSACTIONS RT
WHERE RT.WIP_ENTITY_ID = :B4
AND NVL(RT.WIP_REPETITIVE_SCHEDULE_ID, -1) = NVL(:B3 , -1)
AND RT.ORGANIZATION_ID = :B2
AND RT.WIP_OPERATION_SEQ_NUM = :B1
AND RT.PO_DISTRIBUTION_ID IS NOT NULL )
AND ROWNUM = 1;
To Summarize, First K Rows Optimization, invoked automatically with ROWNUM predicate, can generate a sub-optimal execution plan, if the statistics of the inner query computes a cardinality of less number of rows or 1. In this case, you may need to either re-write a query or fake the statistics. There are few bugs reported with this optimization and, if relevant for your case, the fixes provided as a BUG fix can be safely applied. In the case above, the fix is still awaited and I will keep you posted on this.