First K Rows Optimization : Query Performance issue with ROWNUM predicate

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 :

  1. 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.
  2. 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.

Advertisements

About Vivek Sharma
I am an Oracle Professional from Mumbai (India). I blog on the issues that I feel is Interesting for my readers. Some of these are my real life examples, which I hope, you would find interesting. Comments are always a welcome. The Technical Observations & Views here are my own and not necessarily those of Oracle or its affiliates. These are purely based on my understandings, learnings and resolutions of various customer issues.

2 Responses to First K Rows Optimization : Query Performance issue with ROWNUM predicate

  1. Shyamakanta says:

    Nice posting.
    Good to know abt this bug.

    Like

  2. Atul Tripathi says:

    Really interesting, facing same issue in my project. This reference will help me to sortout my production issue.
    Thanks a lot Vivek.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s