Different Execution Plans when Converting SQL from SELECT to UPDATE … IN SELECT, ROWNUM Related Bug in 11.2.0.2

28 06 2012

June 28, 2012 (Modified June 28, 2012)

Sometimes it is possible to build a very effective test case to reproduce a particular problem.  Sometimes the test case drifts a bit offtrack, in the process revealing other information that is not directly related to the original problem that the test case attempted to simulate.  You will see several examples of test cases scripts used in various articles on this blog, including one that attempts to describe how to build useful test cases.  It is not uncommon to see test cases used to demonstrate various types of behavior, and there are several well known people in the Oracle Database community that use test cases to not only demonstrate problems, but also to teach methods of avoiding those problems.

The FAQ (Frequently Asked Questions) for the SQL and PL/SQL OTN forum includes links to two OTN threads that everyone posting to the forum about performance problems should read carefully:

When your query takes too long…
How to post a SQL statement tuning request

A handful of the good test cases have been posted to the OTN forums over the last couple of years that have then sparked an interesting article on this blog.  Take, for example, this OTN thread that provided a test case script to show that a poor execution plan was generated by the Oracle Database optimizer when ROWNUM was included in the WHERE clause.  Prior to Oracle Database 11.2.0.1, the cardinality calculation for steps in the execution plan that could not possibly be short-circuited early (such as a hash join with a subsequent ORDER BY) were affected by the ROWNUM predicate in the WHERE clause (of a parent query block).  That test case resulted in at least one article on this blog, which showed that with a bit of modification to the SQL statement, that it was possible to use the ROW_NUMBER analytic function as a replacement for ROWNUM, thus avoiding the automatic switch to a FIRST_ROWS(n) optimizer mode for the SQL statement.  This particular problem was determined to be a bug, and corrected with the release of Oracle Database 11.2.0.1.

Fast forward two and a half years, and we see another interesting, and oddly related test case in a thread on the OTN forum.  The OP (original poster) in the OTN forum thread generated the test case, posting his results from Oracle Database 10.2.0.5 (Standard Edition), while I posted my observed results from Oracle Database 11.2.0.2 in an attempt to find a more efficient method for updating rows that match specific criteria (other people helped also, possibly executing the test case script in other Oracle Database release versions).

What do we learn from the OTN test case script?  Here is a start:

  • When hinting a SQL statement to generate an expected execution plan, it is important to be as specific as possible with the hints, without boxing the SQL statement into a corner as the data volume increases in the various tables accessed by the SQL statement.  The hints also must appear in the correct section of a complex SQL statement with one or more inline views or subqueries.
  • If you use ROWNUM in the WHERE clause of a SQL statement, and then convert that SELECT statement to a UPDATE tablex WHERE coly IN (SELECT…) statement, you may find that the execution plan for the SELECT portion of the UPDATE statement changes dramatically from what was seen when just the SELECT statement was executed.  With Oracle Database 11.2.0.2, you may find that the first execution is efficient, and on the second execution find that cardinality feedback triggered a new hard parse of the SQL statement, resulting in a significantly less efficient execution plan (or possibly significantly more efficient execution plan).
  • AUTOTRACE in SQL*Plus lies about the execution plan sometimes, such as when cardinality feedback triggers the generation of a new execution plan.
  • Using a UNION ALL in an inline view will likely not permit the Oracle runtime engine to short-circuit an operation in the inline view when a ROWNUM condition is present in the parent SQL statement, even if the query optimizer’s generated execution plan indicates that this short-circuit will happen.  If possible, rewrite the SQL statement to avoid the UNION ALL operation, if both sections of the UNION ALL are accessing the same tables.
  • The GATHER_PLAN_STATISTICS hint, when added to a SQL statement, permits the use of DBMS_XPLAN.DISPLAY_CURSOR with the ALLSTATS LAST format parameter.  An execution plan generated with that format parameter will indicate whether or not short-circuiting of steps in an execution plan took place.  This short-circuiting will be indicated in the A-Rows column, the Starts column, and/or the Predicate Information section of the execution plan.
  • Within an inline view, an index-driven nested loop join that permits the elimination of an ORDER BY clause can be short-circuited by a ROWNUM condition in the parent portion of the SQL statement, while the same is not true for hash or sort-merge joins.
  • The ROWNUM cardinality calculation bug is still present in Oracle Database 11.2.0.2 if the inline view contains a UNION ALL clause – this bug is most apparent when cardinality feedback triggers the generation of a new execution plan on the second execution of the SQL statement.
  • In Oracle Database 11.2.0.2, query blocks in a SQL statement that cannot be short-circuited by a parent query block’s ROWNUM predicate are still optimized with an automatic FIRST_ROWS(n) optimizer mode (where n is the number associated with the parent operation’s ROWNUM predicate).  Through an apparent bug in the query optimizer, this automatic optimization may cause odd cost calculation problems, such as dropping the cost of a full table scan from 35.01 to 2.02 (with the cost of the corresponding hash join dropping from 56.76 to just 23.83); at the same time, due to cardinality feedback, the cost of an efficient index access path with a nested loops join (that may be short-circuited by the runtime engine) at the same point in the execution plan may increase from a cost of 41.32 to 2947.89.  As a result of the costing change, the nested loop join with index access path is likely to be replaced with a hash join and full table scan.

What else may we learn from the latest OTN test case thread?

Late Addition to the Article (June 28, 2012)

A side-by-side comparison of a portion of the 10053 trace file (showing the possible bug related to ROWNUM and cardinality feedback) that was generated by the following script (using the OP’s sample data):

DROP INDEX IND_DTL_TEST;

SET AUTOTRACE OFF
SET LINESIZE 140
SET PAGESIZE 1000

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET TRACEFILE_IDENTIFIER='TESTING 1';

select /*+ GATHER_PLAN_STATISTICS */
  dtlid
from
  (select
     dtlid
   from
     (select
        dtlid,
        process_date
      from
        dtl
      where
        hdrid is null
        and process_date < (sysdate + 30)
        and process_ind = 'NEW'
      UNION ALL
      select
        dtl.dtlid,
        dtl.process_date
      from
        dtl,
        hdr
      where
        dtl.hdrid = hdr.hdrid
        and dtl.process_date < (sysdate + 30)
        and dtl.process_ind = 'NEW'
        and hdr_ind in (0, 2))
   order by
     dtlid,
     process_date)
where
  rownum <= 10;

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

ALTER SESSION SET TRACEFILE_IDENTIFIER='TESTING 2';

select /*+ GATHER_PLAN_STATISTICS */
  dtlid
from
  (select
     dtlid
   from
     (select
        dtlid,
        process_date
      from
        dtl
      where
        hdrid is null
        and process_date < (sysdate + 30)
        and process_ind = 'NEW'
      UNION ALL
      select
        dtl.dtlid,
        dtl.process_date
      from
        dtl,
        hdr
      where
        dtl.hdrid = hdr.hdrid
        and dtl.process_date < (sysdate + 30)
        and dtl.process_ind = 'NEW'
        and hdr_ind in (0, 2))
   order by
     dtlid,
     process_date)
where
  rownum <= 10;

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'; 

The generated execution plan, before cardinality feedback:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4dwv2ktktv1ck, child number 1
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */   dtlid from   (select      dtlid
  from      (select         dtlid,         process_date       from
   dtl       where         hdrid is null         and process_date <
(sysdate + 30)         and process_ind = 'NEW'       UNION ALL
select         dtl.dtlid,         dtl.process_date       from
dtl,         hdr       where         dtl.hdrid = hdr.hdrid         and
dtl.process_date < (sysdate + 30)         and dtl.process_ind = 'NEW'
      and hdr_ind in (0, 2))    order by      dtlid,      process_date)
where   rownum <= 10

Plan hash value: 2201656677

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |          |      1 |        |     10 |00:00:00.05 |   31500 |       |       |          |
|*  1 |  COUNT STOPKEY                     |          |      1 |        |     10 |00:00:00.05 |   31500 |       |       |          |
|   2 |   VIEW                             |          |      1 |     22 |     10 |00:00:00.05 |   31500 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY           |          |      1 |     22 |     10 |00:00:00.05 |   31500 |  2048 |  2048 | 2048  (0)|
|   4 |     VIEW                           |          |      1 |     22 |   1367 |00:00:00.04 |   31500 |       |       |          |
|   5 |      UNION-ALL                     |          |      1 |        |   1367 |00:00:00.04 |   31500 |       |       |          |
|*  6 |       TABLE ACCESS FULL            | DTL      |      1 |     11 |    160 |00:00:00.01 |    1117 |       |       |          |
|   7 |       NESTED LOOPS                 |          |      1 |        |   1207 |00:00:00.07 |   30383 |       |       |          |
|   8 |        NESTED LOOPS                |          |      1 |     11 |   2838 |00:00:00.04 |   27566 |       |       |          |
|   9 |         TABLE ACCESS BY INDEX ROWID| DTL      |      1 |     32 |  15034 |00:00:00.02 |   15068 |       |       |          |
|* 10 |          INDEX RANGE SCAN          | DTL_IDX1 |      1 |        |  15034 |00:00:00.01 |      56 |       |       |          |
|* 11 |         INDEX UNIQUE SCAN          | HDR_PK   |  15034 |      1 |   2838 |00:00:00.01 |   12498 |       |       |          |
|* 12 |        TABLE ACCESS BY INDEX ROWID | HDR      |   2838 |      1 |   1207 |00:00:00.01 |    2817 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   6 - filter(("HDRID" IS NULL AND "PROCESS_IND"='NEW' AND "PROCESS_DATE"<SYSDATE@!+30))
  10 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
  11 - access("DTL"."HDRID"="HDR"."HDRID")
  12 - filter(("HDR_IND"=0 OR "HDR_IND"=2)) 

After cardinality feedback:

SQL_ID  4dwv2ktktv1ck, child number 2
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */   dtlid from   (select      dtlid
  from      (select         dtlid,         process_date       from
   dtl       where         hdrid is null         and process_date <
(sysdate + 30)         and process_ind = 'NEW'       UNION ALL
select         dtl.dtlid,         dtl.process_date       from
dtl,         hdr       where         dtl.hdrid = hdr.hdrid         and
dtl.process_date < (sysdate + 30)         and dtl.process_ind = 'NEW'
      and hdr_ind in (0, 2))    order by      dtlid,      process_date)
where   rownum <= 10

Plan hash value: 986254152

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |      1 |        |     10 |00:00:00.01 |    2437 |       |       |          |
|*  1 |  COUNT STOPKEY          |      |      1 |        |     10 |00:00:00.01 |    2437 |       |       |          |
|   2 |   VIEW                  |      |      1 |   2104 |     10 |00:00:00.01 |    2437 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY|      |      1 |   2104 |     10 |00:00:00.01 |    2437 |  2048 |  2048 | 2048  (0)|
|   4 |     VIEW                |      |      1 |   2104 |   1367 |00:00:00.01 |    2437 |       |       |          |
|   5 |      UNION-ALL          |      |      1 |        |   1367 |00:00:00.01 |    2437 |       |       |          |
|*  6 |       TABLE ACCESS FULL | DTL  |      1 |    160 |    160 |00:00:00.01 |    1117 |       |       |          |
|*  7 |       HASH JOIN         |      |      1 |   1944 |   1207 |00:00:00.01 |    1320 |  1517K|  1517K| 1398K (0)|
|*  8 |        TABLE ACCESS FULL| HDR  |      1 |   6667 |   4285 |00:00:00.01 |     203 |       |       |          |
|*  9 |        TABLE ACCESS FULL| DTL  |      1 |  15034 |  15034 |00:00:00.01 |    1117 |       |       |          |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   6 - filter(("HDRID" IS NULL AND "PROCESS_IND"='NEW' AND "PROCESS_DATE"<SYSDATE@!+30))
   7 - access("DTL"."HDRID"="HDR"."HDRID")
   8 - filter(("HDR_IND"=0 OR "HDR_IND"=2))
   9 - filter(("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30))

Note
-----
   - cardinality feedback used for this statement 

In the above example, cardinality feedback helped to reduce the execution time.  Notice in the first plan that the E-Rows column shows that the ROWNUM predicate was pushed too far into the inline view’s cardinality calculations.  So, cardinality feedback helped in the above example.  Now, let’s look at what happens to my optimized version of the SQL statement that removes the UNION ALL:

select
  dtlid
from
  (select
     dtlid
   from
     (select /*+ INDEX(DTL) INDEX(HRD) USE_NL(DTL HDR) */
        dtl.dtlid,
        dtl.process_date
      from
        dtl,
        hdr
      where
        dtl.hdrid = hdr.hdrid(+)
        and dtl.process_date < (sysdate + 30)
        and dtl.process_ind = 'NEW'
        and (
            (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL)
            OR (dtl.hdrid IS NULL)))
   order by
     dtlid,
     process_date) 

The execution plan before cardinality feedback:

SQL_ID  5d8128vjhm1j0, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */   dtlid from   (select      dtlid
  from      (select         dtl.dtlid,         dtl.process_date
from         dtl,         hdr       where         dtl.hdrid =
hdr.hdrid(+)         and dtl.process_date < (sysdate + 30)         and
dtl.process_ind = 'NEW'         and (             (hdr_ind in (0, 2)
and dtl.hdrid IS NOT NULL)             OR (dtl.hdrid IS NULL)))
order by      dtlid,      process_date) where   rownum <= 10

Plan hash value: 1187173211

----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |      1 |        |     10 |00:00:00.01 |      80 |
|*  1 |  COUNT STOPKEY                  |              |      1 |        |     10 |00:00:00.01 |      80 |
|   2 |   VIEW                          |              |      1 |     11 |     10 |00:00:00.01 |      80 |
|*  3 |    FILTER                       |              |      1 |        |     10 |00:00:00.01 |      80 |
|   4 |     NESTED LOOPS OUTER          |              |      1 |     11 |     19 |00:00:00.01 |      80 |
|   5 |      TABLE ACCESS BY INDEX ROWID| DTL          |      1 |  19635 |     19 |00:00:00.01 |      52 |
|*  6 |       INDEX FULL SCAN           | IND_DTL_TEST |      1 |     16 |     19 |00:00:00.01 |      49 |
|   7 |      TABLE ACCESS BY INDEX ROWID| HDR          |     19 |      1 |     18 |00:00:00.01 |      28 |
|*  8 |       INDEX UNIQUE SCAN         | HDR_PK       |     19 |      1 |     18 |00:00:00.01 |      10 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS
              NULL))
   6 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
       filter(("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30))
   8 - access("DTL"."HDRID"="HDR"."HDRID") 

The execution plan after cardinality feedback:

SQL_ID  5d8128vjhm1j0, child number 1
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */   dtlid from   (select      dtlid
  from      (select         dtl.dtlid,         dtl.process_date
from         dtl,         hdr       where         dtl.hdrid =
hdr.hdrid(+)         and dtl.process_date < (sysdate + 30)         and
dtl.process_ind = 'NEW'         and (             (hdr_ind in (0, 2)
and dtl.hdrid IS NOT NULL)             OR (dtl.hdrid IS NULL)))
order by      dtlid,      process_date) where   rownum <= 10

Plan hash value: 672193791

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |        |      1 |        |     10 |00:00:00.02 |    5315 |       |       |          |
|*  1 |  COUNT STOPKEY                   |        |      1 |        |     10 |00:00:00.02 |    5315 |       |       |          |
|   2 |   VIEW                           |        |      1 |      9 |     10 |00:00:00.02 |    5315 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY         |        |      1 |      9 |     10 |00:00:00.02 |    5315 |  2048 |  2048 | 2048  (0)|
|*  4 |     FILTER                       |        |      1 |        |   1374 |00:00:00.02 |    5315 |       |       |          |
|   5 |      NESTED LOOPS OUTER          |        |      1 |      9 |  15115 |00:00:00.02 |    5315 |       |       |          |
|*  6 |       TABLE ACCESS FULL          | DTL    |      1 |     19 |  15115 |00:00:00.01 |    1117 |       |       |          |
|   7 |       TABLE ACCESS BY INDEX ROWID| HDR    |  15115 |      1 |   2853 |00:00:00.01 |    4198 |       |       |          |
|*  8 |        INDEX UNIQUE SCAN         | HDR_PK |  15115 |      1 |   2853 |00:00:00.01 |    1345 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   4 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL))
   6 - filter(("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30))
   8 - access("DTL"."HDRID"="HDR"."HDRID")

Note
-----
   - cardinality feedback used for this statement 

In the above, notice that the reported A-Time increased from 0.01 seconds (the actual time could be must faster than that) to 0.02 seconds.  The number of consistent gets also increased from 80 to 5,315.  Cardinality feedback will NOT automatically change the execution plan back to the more efficient execution plan during later executions of the SQL statement.


Actions

Information

6 responses

28 06 2012
Dom Brooks

> When hinting a SQL statement to generate an expected execution plan, it is important to be as specific as possible with the hints, without boxing the SQL statement into a corner as the data volume increases in the various tables accessed by the SQL statement

For once, I’m not sure I agree with you, Charles. I don’t want to take the thread off topic but …

If we were just talking about test cases and trying to reproduce a particular behaviour then I’d have nothing further to say.

But you mention giving the optimizer options as volumes change, so we must be talking about permanently embedded hints in application code, right?

Now, it must depend on what sort of hints we’re talking about and I’ve had some thoughts before about “acceptable” hints.

But, certainly in any recent version of Oracle, if we’re not talking about this small subset of statistical adjustments and other such hints as per above but we’re talking about hinting join mechanisms and access paths then I think you have to make a pretty much black and white choice.

You either have to provide a full set of hints for a specific plan or you don’t hint.

Particularly as the optimizer gets more complex and query transformation gets more sophisticated, not boxing the SQL statement into a corner is a performance threat and I see the threat in action time and time again, especially related to upgrades.

If you can fix an execution plan issue via statistical adjustments using cardinality, dynamic_sampling or even opt_estimate then great, otherwise I think you’ve a tough choice.

Alternatively, if you want to best of both worlds – specific plans now with the flexibility to evolve then we’re talking baselines.

Cheers,
Dominic

28 06 2012
Charles Hooper

Dominic,

Thanks for the comment and for supplying a link to one of your articles. You make a good point. Rational disagreement is of course encouraged when necessary – there are of course a lot of extremely knowledgeable people out there, and when I am wrong, I hope that those people will offer kind assistance.

The first of the above bullet points has (at least) two completely different interpretations:

    * If you as the developer know that table T1 will expand from 1,000 rows in your test environment to 10,000,000,000 rows once the application is in service for a couple of years (with index clustering factor values expected to become closer to the number or rows in the table than it is to the number of blocks in the table), while the other tables accessed by the SQL statement will remain at roughly 1,000 rows, it might make sense to specify the access path for one of the tables, while allowing the optimizer to dynamically adjust the access path, join method, and automatic query transformation for the remainder of the query. I believe that this is the interpretation envisioned when you read the first bullet point, and might very well be the same interpretation that many other people have also.

    * When you as the developer are developing an application, the sample data that is used for development may be much smaller than what your customers will see – that 1,000 row table that has beautifully built rows that are inserted in a perfect order for the indexes built on the table, may in fact be a 10,000,000,000 row table with rows inserted in a nearly random order into the table’s blocks, with the table having 10,000 partitions in an ASSM tablespace, and with access to parallel query. The statement “without boxing the SQL statement into a corner as the data volume increases in the various tables accessed by the SQL statement” in this case means – think about the big picture, what will the table’s data volume look like in a month, year, 10 years, etc. If the developer boxes the optimizer into a corner with hints that produce efficient execution plans in the small sample database with OPTIMIZER_INDEX_COST_ADJ set to 1, for instance, that same SQL statement and execution plan could cause horrendous performance problems when the code is released into production environments with several years’ worth of data. In short, if you do not know what you are doing with hints, and do not understand how the data will transform over time, maybe it is best to leave the hints out of the SQL statement all together.

    * Consider how the data and supporting structures might mature over time. If as a developer, you fully hint an execution plan to take advantage of specific indexes, access paths, join methods, etc. and the query executes efficiently – great. But, what happens if the definition of “efficient” changes at some point in the future? What if the developer learns that normal B*tree indexes can contain NULLs (http://hoopercharles.wordpress.com/2012/02/28/repeat-after-me-null-values-are-not-stored-in-indexes ) and determines that a new composite index would generate a better plan, or simply adding a NOT NULL contraint to a table definition provides the optimizer with enough information that it can not make use of an index access path that was previously unavailable. What if a hint specifies a specific index, and that index is no longer accessible to the optimizer (maybe the index is marked as UNUSABLE, maybe it was altered to be hidden, maybe it was just a redundant index and dropped, maybe it was an index with a system generated SYS_ name that does not survive an export-import) – the optimizer in an Oracle Database is quite clever at times, and may arrive at a completely stupid execution plan when trying to follow the demands specified by the remaining valid hints. If the developer had boxed in the optimizer with very specific hints, it could be difficult to fix the application code to take advantage of the more efficient access paths (I understand that there are extensive procedures that must be followed in some industries, for instance drug manufacturing, any time the source code of an application changes).

The different possible interpretations, in this case, was somewhat intentional – the second of the above bullet points was the original target of the bullet point statement, but the other interpretations are present to encourage discussion with readers who have a lot of technical experience in this area.

Anyone else have an opinion on this matter, or any of the other bullet pointed items in the article?

29 06 2012
Log Buffer #276, A Carnival of the Vanities for DBAs | The Pythian Blog

[...] test case to reproduce a particular problem.  Sometimes the test case drifts a bit offtrack. Charles Hooper shares his unique [...]

1 07 2012
Narendra

Charles,

I believe I have managed to simulate the data pattern more closer to the data in original tables (BTW, I am the OP of that OTN thread).
And I am again stuck at the stage where the SELECT part of the UPDATE, when executed on its own, uses the nice NESTED LOOP join with short-circuit. But, as expected, the UPDATE does not use the particular index and the join changes to HASH JOIN. I strongly suspect that the UPDATE could benefit from using the access path used by the standalone SELECT but can’t figure out how to make it happen.
If I have not already managed to annoy you, I can post the complete test case, either here or on OTN thread.

1 07 2012
Charles Hooper

Narendra,

You may be able to control the access path by using hints (keep in mind the comment made by Dominic).

This is an example that I put in that OTN thread where hints were used in the SELECT portion of the UPDATE statement:

update dtl set process_ind = 'PROCESSED' where dtlid in (
select
  dtlid
from
  (select
     dtlid
   from
     (select /*+ INDEX(DTL) INDEX(HRD) NO_USE_HASH(HDR) NO_USE_MERGE(HDR) */
        dtl.dtlid,
        dtl.process_date
      from
        dtl,
        hdr
      where
        dtl.hdrid = hdr.hdrid(+)
        and dtl.process_date < (sysdate + 30)
        and dtl.process_ind = 'NEW'
        and (
            (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL)
            OR (dtl.hdrid IS NULL)))
   order by
     dtlid,
     process_date)
where
  rownum <= 10);

I think that if the DTL table is not selected as the first table in the join, the NO_USE_HASH and NO_USE_MERGE hints might not have any effect. So, we probably need to use a LEADING hint to make certain that the optimizer starts with the DTL table:

update dtl set process_ind = 'PROCESSED' where dtlid in (
select
  dtlid
from
  (select
     dtlid
   from
     (select /*+ LEADING(DTL) INDEX(DTL) INDEX(HRD) NO_USE_HASH(HDR) NO_USE_MERGE(HDR) */
        dtl.dtlid,
        dtl.process_date
      from
        dtl,
        hdr
      where
        dtl.hdrid = hdr.hdrid(+)
        and dtl.process_date < (sysdate + 30)
        and dtl.process_ind = 'NEW'
        and (
            (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL)
            OR (dtl.hdrid IS NULL)))
   order by
     dtlid,
     process_date)
where
  rownum <= 10);

You could probably replace the NO_USE_HASH and NO_USE_MERGE hints with just a USE_NL(HDR) hint:

update dtl set process_ind = 'PROCESSED' where dtlid in (
select
  dtlid
from
  (select
     dtlid
   from
     (select /*+ LEADING(DTL) INDEX(DTL) INDEX(HRD) USE_NL(HDR) */
        dtl.dtlid,
        dtl.process_date
      from
        dtl,
        hdr
      where
        dtl.hdrid = hdr.hdrid(+)
        and dtl.process_date < (sysdate + 30)
        and dtl.process_ind = 'NEW'
        and (
            (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL)
            OR (dtl.hdrid IS NULL)))
   order by
     dtlid,
     process_date)
where
  rownum <= 10);

Since the SELECT statement is optimized with a FIRST_ROWS(n) optimizer mode, there is a chance (probably a small chance) that a FIRST_ROWS(n) hint may override the automatic ALL_ROWS optimization:

update dtl set process_ind = 'PROCESSED' where dtlid in (
select
  dtlid
from
  (select
     dtlid
   from
     (select /*+ FIRST_ROWS(10) */
        dtl.dtlid,
        dtl.process_date
      from
        dtl,
        hdr
      where
        dtl.hdrid = hdr.hdrid(+)
        and dtl.process_date < (sysdate + 30)
        and dtl.process_ind = 'NEW'
        and (
            (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL)
            OR (dtl.hdrid IS NULL)))
   order by
     dtlid,
     process_date)
where
  rownum <= 10);

If you are able to solve the problem, I would like to see what worked. Definitely update the OTN thread when you find a solution. If the hint does not work out, you could post the updated test case so that people are able to better reproduce the problem that you are experiencing.

3 07 2012
Charles Hooper

Narendra,

The OTN forums crashed as I tried to post the following response (sorry to all of the users of the OTN forums), so I thought that I would post it here.

Your analysis of an increasing number of rows needing to be fetched from the DTL table for each execution of the UPDATE statement is correct (I think that I mentioned this issue early in this thread). So, what is happening?

First, let’s make it easy for people to create your latest version of the test case tables:

drop table dtl purge ;
drop table hdr purge ;
create table hdr (hdrid number(10), hdr_ind number(2) not null, hpad varchar2(100), constraint hdr_pk primary key (hdrid)) ;
create table dtl (dtlid number(10), process_ind varchar2(10) not null, process_date date not null, hdrid number(10), type_id number(2), cat_id number(2), dpad varchar2(100), 

constraint dtl_pk primary key (dtlid)) ;
create index dtl_idx1 on dtl(process_ind, process_date) nologging ;
create index dtl_idx2 on dtl(process_ind, decode(type_id, 1, 99, type_id), process_date) nologging ;
insert into hdr select level, case when mod(level, 3) = 0 then 0 when mod(level, 7) = 0 then 2 else 6 end, dbms_random.string('a', 100) from dual connect by level <= 10000 ;
commit ;
drop sequence dtl_seq ;
create sequence dtl_seq cache 1000 ;
insert into dtl select dtl_seq.nextval, 'NEW', sysdate, case mod(rownum, 500) when 0 then null else hdrid end, case mod(rownum, 100) when 0 then 2 else 1 end, 1, 

dbms_random.string('a', 10) from (select hdrid from hdr where rownum <= 3000),(select level from dual connect by level <= 2) ;
commit ;
 
begin
for i in (select level rn from dual connect by level <= 20 order by dbms_random.random)
loop
insert into dtl select dtl_seq.nextval, 'NEW', (sysdate + i.rn), case mod(rownum, 500) when 0 then null else hdrid end, case mod(rownum, 100) when 0 then 2 else 1 end, 1, 

dbms_random.string('a', 10) from (select hdrid from hdr where rownum <= 5000),(select level from dual connect by level <= 20) ;
commit;
end loop;
end;
/
 
exec dbms_stats.gather_table_stats(user, 'DTL', cascade=>true);
exec dbms_stats.gather_table_stats(user, 'HDR', cascade=>true);

With those tables freshly created, we can start experimenting. What if we execute the update statement 20 times, outputting the execution plan each time, and executing the following SQL statement before the first update statement, and then after every 5 executions of the update statement:

select /*+ gather_plan_statistics */
  V.*,
  COUNT(decode(INCLUDE,'Y','Y',NULL)) OVER (ORDER BY ROWNUM) C,
  ROWNUM RN
from
(    select /*+ NO_EXPAND LEADING(dtl hdr) USE_NL(hdr) INDEX(dtl DTL_IDX2) */
 dtl.dtlid,
  hdr_ind,
  dtl.hdrid,
  dtl.cat_id,
  dtl.type_id,
  DTL.process_date,
  decode(dtl.hdrid,null,'Y',decode(hdr.hdr_ind,0,'Y',2,'Y','N')) INCLUDE
    from dtl,
         hdr
    where dtl.hdrid = hdr.hdrid(+)
          and dtl.process_date < sysdate
          and dtl.process_ind = 'NEW'
          AND dtl.cat_id = 1
    order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date) V
where
  rownum<=2800;

The above output will show you the rows that are making it out of the DTL table, which are then used to probe the primary key index on the HDR table. The C column indicates the number of rows that match the WHERE clause in your UPDATE statement, after those rows are pulled from the DTL table (once the C column reaches 100, the Oracle runtime engine is able to short-circuit the execution plan). The RN column provides a running counter of the number of rows from the DTL table.

Before the first update, notice that the first row that will be returned (based on your WHERE clause) is the first row printed, and that the 100th row that will be returned is the 211th row that is printed:

     DTLID    HDR_IND      HDRID     CAT_ID    TYPE_ID PROCESS_D I          C         RN
---------- ---------- ---------- ---------- ---------- --------- - ---------- ----------
       600          0        600          1          2 03-JUL-12 Y          1          1
       700          2        700          1          2 03-JUL-12 Y          2          2
       800          6        800          1          2 03-JUL-12 N          2          3
       100          6        100          1          2 03-JUL-12 N          2          4
       200          6        200          1          2 03-JUL-12 N          2          5
       300          0        300          1          2 03-JUL-12 Y          3          6
       400          6        400          1          2 03-JUL-12 N          3          7
...
       669          0        669          1          1 03-JUL-12 Y         98        205
       670          6        670          1          1 03-JUL-12 N         98        206
       671          6        671          1          1 03-JUL-12 N         98        207
       672          0        672          1          1 03-JUL-12 Y         99        208
       673          6        673          1          1 03-JUL-12 N         99        209
       674          6        674          1          1 03-JUL-12 N         99        210
       675          0        675          1          1 03-JUL-12 Y        100        211

The execution plan, notice the A-Rows value for the DTL table is 211, the same value as the RN value above.

SQL_ID  a2g3h48p54pa2, child number 0
-------------------------------------
update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
where dtlid in (   select dtlid   from (     select /*+ NO_EXPAND
LEADING(dtl hdr) USE_NL(hdr) INDEX(dtl DTL_IDX2) */ dtl.dtlid     from
dtl,          hdr     where dtl.hdrid = hdr.hdrid(+)           and
dtl.process_date < sysdate           and dtl.process_ind = 'NEW'                        
   and dtl.cat_id = 1           and (                (hdr_ind in (0, 2)                 
and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)               )                       
order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date                       
      )     where rownum <= 100)    
 
Plan hash value: 3052733514         
 
-------------------------------------------------------------------------------------------------------------------------------------       
| Id  | Operation                           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |       
-------------------------------------------------------------------------------------------------------------------------------------       
|   0 | UPDATE STATEMENT                    |          |      1 |        |      0 |00:00:00.01 |    1863 |       |       |          |       
|   1 |  UPDATE                             | DTL      |      1 |        |      0 |00:00:00.01 |    1863 |       |       |          |       
|   2 |   NESTED LOOPS                      |          |      1 |    100 |    100 |00:00:00.01 |     502 |       |       |          |       
|   3 |    VIEW                             | VW_NSO_1 |      1 |    100 |    100 |00:00:00.01 |     415 |       |       |          |       
|   4 |     SORT UNIQUE                     |          |      1 |    100 |    100 |00:00:00.01 |     415 | 73728 | 73728 |          |       
|*  5 |      COUNT STOPKEY                  |          |      1 |        |    100 |00:00:00.01 |     415 |       |       |          |       
|   6 |       VIEW                          |          |      1 |  63619 |    100 |00:00:00.01 |     415 |       |       |          |       
|*  7 |        FILTER                       |          |      1 |        |    100 |00:00:00.01 |     415 |       |       |          |       
|   8 |         NESTED LOOPS OUTER          |          |      1 |  63619 |    211 |00:00:00.01 |     415 |       |       |          |       
|*  9 |          TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95524 |    211 |00:00:00.01 |     152 |       |       |          |       
|* 10 |           INDEX RANGE SCAN          | DTL_IDX2 |      1 |  95524 |    211 |00:00:00.01 |     116 |       |       |          |       
|  11 |          TABLE ACCESS BY INDEX ROWID| HDR      |    211 |      1 |    199 |00:00:00.01 |     263 |       |       |          |       
|* 12 |           INDEX UNIQUE SCAN         | HDR_PK   |    211 |      1 |    199 |00:00:00.01 |      64 |       |       |          |       
|* 13 |    INDEX UNIQUE SCAN                | DTL_PK   |    100 |      1 |    100 |00:00:00.01 |      87 |       |       |          |       
-------------------------------------------------------------------------------------------------------------------------------------       
 
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(ROWNUM<=100)          
   7 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL)) 
   9 - filter("DTL"."CAT_ID"=1)     
  10 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!)             
       filter("DTL"."PROCESS_DATE"<SYSDATE@!)      
  12 - access("DTL"."HDRID"="HDR"."HDRID")         
  13 - access("DTLID"="DTLID")      

Before the sixth execution – notice that the C column does not advance from 0 until the 645th row, and then runs for about 230 rows until hitting the 100th row that will be returned:

     DTLID    HDR_IND      HDRID     CAT_ID    TYPE_ID PROCESS_D I          C         RN
---------- ---------- ---------- ---------- ---------- --------- - ---------- ----------
      1093          6       1093          1          1 03-JUL-12 N          0        643
      1094          6       1094          1          1 03-JUL-12 N          0        644
      1095          0       1095          1          1 03-JUL-12 Y          1        645
      1096          6       1096          1          1 03-JUL-12 N          1        646
      1097          6       1097          1          1 03-JUL-12 N          1        647
      1098          0       1098          1          1 03-JUL-12 Y          2        648
      1099          2       1099          1          1 03-JUL-12 Y          3        649
      1101          0       1101          1          1 03-JUL-12 Y          4        650
...
      1840          0       1269          1          1 03-JUL-12 Y         98        868
      1841          6       1270          1          1 03-JUL-12 N         98        869
      1842          6       1271          1          1 03-JUL-12 N         98        870
      1843          0       1272          1          1 03-JUL-12 Y         99        871
      1844          6       1273          1          1 03-JUL-12 N         99        872
      1845          2       1274          1          1 03-JUL-12 Y        100        873

The execution plan for the 6th execution of the update statement, notice that the DTL table is returning 873 rows, just as the above predicted:

-------------------------------------------------------------------------------------------------------------------------------------       
| Id  | Operation                           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |       
-------------------------------------------------------------------------------------------------------------------------------------       
|   0 | UPDATE STATEMENT                    |          |      1 |        |      0 |00:00:00.01 |    2482 |       |       |          |       
|   1 |  UPDATE                             | DTL      |      1 |        |      0 |00:00:00.01 |    2482 |       |       |          |       
|   2 |   NESTED LOOPS                      |          |      1 |    100 |    100 |00:00:00.01 |    1134 |       |       |          |       
|   3 |    VIEW                             | VW_NSO_1 |      1 |    100 |    100 |00:00:00.01 |    1096 |       |       |          |       
|   4 |     SORT UNIQUE                     |          |      1 |    100 |    100 |00:00:00.01 |    1096 | 73728 | 73728 |          |       
|*  5 |      COUNT STOPKEY                  |          |      1 |        |    100 |00:00:00.01 |    1096 |       |       |          |       
|   6 |       VIEW                          |          |      1 |  63619 |    100 |00:00:00.01 |    1096 |       |       |          |       
|*  7 |        FILTER                       |          |      1 |        |    100 |00:00:00.01 |    1096 |       |       |          |       
|   8 |         NESTED LOOPS OUTER          |          |      1 |  63619 |    873 |00:00:00.01 |    1096 |       |       |          |       
|*  9 |          TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95524 |    873 |00:00:00.01 |     155 |       |       |          |       
|* 10 |           INDEX RANGE SCAN          | DTL_IDX2 |      1 |  95524 |    873 |00:00:00.01 |     123 |       |       |          |       
|  11 |          TABLE ACCESS BY INDEX ROWID| HDR      |    873 |      1 |    873 |00:00:00.01 |     941 |       |       |          |       
|* 12 |           INDEX UNIQUE SCAN         | HDR_PK   |    873 |      1 |    873 |00:00:00.01 |      68 |       |       |          |       
|* 13 |    INDEX UNIQUE SCAN                | DTL_PK   |    100 |      1 |    100 |00:00:00.01 |      38 |       |       |          |       
-------------------------------------------------------------------------------------------------------------------------------------       
 
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(ROWNUM<=100)          
   7 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL)) 
   9 - filter("DTL"."CAT_ID"=1)     
  10 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!)             
       filter("DTL"."PROCESS_DATE"<SYSDATE@!)      
  12 - access("DTL"."HDRID"="HDR"."HDRID")         
  13 - access("DTLID"="DTLID")      

Just before the 11th update, the first row returned will begin with the 1312th row printed, and end with the 1544th row printed (again running for about 230 rows once the first usable row is found):

     DTLID    HDR_IND      HDRID     CAT_ID    TYPE_ID PROCESS_D I          C         RN
---------- ---------- ---------- ---------- ---------- --------- - ---------- ----------
      2791          6       2791          1          1 03-JUL-12 N          0       1310
      2792          6       2792          1          1 03-JUL-12 N          0       1311
      2793          0       2793          1          1 03-JUL-12 Y          1       1312
      2794          6       2794          1          1 03-JUL-12 N          1       1313
      2795          6       2795          1          1 03-JUL-12 N          1       1314
      2796          0       2796          1          1 03-JUL-12 Y          2       1315
      2797          6       2797          1          1 03-JUL-12 N          2       1316
      2798          6       2798          1          1 03-JUL-12 N          2       1317
...
      3024          0         24          1          1 03-JUL-12 Y         98       1540
      3025          6         25          1          1 03-JUL-12 N         98       1541
      3026          6         26          1          1 03-JUL-12 N         98       1542
      3027          0         27          1          1 03-JUL-12 Y         99       1543
      3028          2         28          1          1 03-JUL-12 Y        100       1544

You can probably guess what the execution plan looks like at this point:

SQL_ID  a2g3h48p54pa2, child number 0              
-------------------------------------              
update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'                  
where dtlid in (   select dtlid   from (     select /*+ NO_EXPAND                       
LEADING(dtl hdr) USE_NL(hdr) INDEX(dtl DTL_IDX2) */ dtl.dtlid     from                  
dtl,          hdr     where dtl.hdrid = hdr.hdrid(+)           and                      
dtl.process_date < sysdate           and dtl.process_ind = 'NEW'                        
   and dtl.cat_id = 1           and (                (hdr_ind in (0, 2)                 
and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)               )                       
order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date                       
      )     where rownum <= 100)    
 
Plan hash value: 3052733514         
 
-------------------------------------------------------------------------------------------------------------------------------------       
| Id  | Operation                           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |       
-------------------------------------------------------------------------------------------------------------------------------------       
|   0 | UPDATE STATEMENT                    |          |      1 |        |      0 |00:00:00.01 |    3172 |       |       |          |       
|   1 |  UPDATE                             | DTL      |      1 |        |      0 |00:00:00.01 |    3172 |       |       |          |       
|   2 |   NESTED LOOPS                      |          |      1 |    100 |    100 |00:00:00.01 |    1841 |       |       |          |       
|   3 |    VIEW                             | VW_NSO_1 |      1 |    100 |    100 |00:00:00.01 |    1815 |       |       |          |       
|   4 |     SORT UNIQUE                     |          |      1 |    100 |    100 |00:00:00.01 |    1815 | 73728 | 73728 |          |       
|*  5 |      COUNT STOPKEY                  |          |      1 |        |    100 |00:00:00.01 |    1815 |       |       |          |       
|   6 |       VIEW                          |          |      1 |  63619 |    100 |00:00:00.01 |    1815 |       |       |          |       
|*  7 |        FILTER                       |          |      1 |        |    100 |00:00:00.01 |    1815 |       |       |          |       
|   8 |         NESTED LOOPS OUTER          |          |      1 |  63619 |   1544 |00:00:00.01 |    1815 |       |       |          |       
|*  9 |          TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95524 |   1544 |00:00:00.01 |     168 |       |       |          |       
|* 10 |           INDEX RANGE SCAN          | DTL_IDX2 |      1 |  95524 |   1544 |00:00:00.01 |     129 |       |       |          |       
|  11 |          TABLE ACCESS BY INDEX ROWID| HDR      |   1544 |      1 |   1544 |00:00:00.01 |    1647 |       |       |          |       
|* 12 |           INDEX UNIQUE SCAN         | HDR_PK   |   1544 |      1 |   1544 |00:00:00.01 |     103 |       |       |          |       
|* 13 |    INDEX UNIQUE SCAN                | DTL_PK   |    100 |      1 |    100 |00:00:00.01 |      26 |       |       |          |       
-------------------------------------------------------------------------------------------------------------------------------------       
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter(ROWNUM<=100)          
   7 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL)) 
   9 - filter("DTL"."CAT_ID"=1)     
  10 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!)             
       filter("DTL"."PROCESS_DATE"<SYSDATE@!)      
  12 - access("DTL"."HDRID"="HDR"."HDRID")         
  13 - access("DTLID"="DTLID")      

The execution plan for the 20th update:

-------------------------------------------------------------------------------------------------------------------------------------       
| Id  | Operation                           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |       
-------------------------------------------------------------------------------------------------------------------------------------       
|   0 | UPDATE STATEMENT                    |          |      1 |        |      0 |00:00:00.01 |    4468 |       |       |          |       
|   1 |  UPDATE                             | DTL      |      1 |        |      0 |00:00:00.01 |    4468 |       |       |          |       
|   2 |   NESTED LOOPS                      |          |      1 |    100 |    100 |00:00:00.01 |    3120 |       |       |          |       
|   3 |    VIEW                             | VW_NSO_1 |      1 |    100 |    100 |00:00:00.01 |    3094 |       |       |          |       
|   4 |     SORT UNIQUE                     |          |      1 |    100 |    100 |00:00:00.01 |    3094 | 73728 | 73728 |          |       
|*  5 |      COUNT STOPKEY                  |          |      1 |        |    100 |00:00:00.01 |    3094 |       |       |          |       
|   6 |       VIEW                          |          |      1 |  63619 |    100 |00:00:00.01 |    3094 |       |       |          |       
|*  7 |        FILTER                       |          |      1 |        |    100 |00:00:00.01 |    3094 |       |       |          |       
|   8 |         NESTED LOOPS OUTER          |          |      1 |  63619 |   2743 |00:00:00.01 |    3094 |       |       |          |       
|*  9 |          TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95524 |   2743 |00:00:00.01 |     192 |       |       |          |       
|* 10 |           INDEX RANGE SCAN          | DTL_IDX2 |      1 |  95524 |   2743 |00:00:00.01 |     141 |       |       |          |       
|  11 |          TABLE ACCESS BY INDEX ROWID| HDR      |   2743 |      1 |   2743 |00:00:00.01 |    2902 |       |       |          |       
|* 12 |           INDEX UNIQUE SCAN         | HDR_PK   |   2743 |      1 |   2743 |00:00:00.01 |     159 |       |       |          |       
|* 13 |    INDEX UNIQUE SCAN                | DTL_PK   |    100 |      1 |    100 |00:00:00.01 |      26 |       |       |          |       
-------------------------------------------------------------------------------------------------------------------------------------       
 
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(ROWNUM<=100)          
   7 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL)) 
   9 - filter("DTL"."CAT_ID"=1)     
  10 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!)             
       filter("DTL"."PROCESS_DATE"<SYSDATE@!)      
  12 - access("DTL"."HDRID"="HDR"."HDRID")         
  13 - access("DTLID"="DTLID") 

After the 20th update, the 21st update would need to go well beyond the 2800th row printed (probably close to the 2900th row):

     DTLID    HDR_IND      HDRID     CAT_ID    TYPE_ID PROCESS_D I          C         RN
---------- ---------- ---------- ---------- ---------- --------- - ---------- ----------
      4109          6       1109          1          1 03-JUL-12 N          0       2643
      4111          6       1111          1          1 03-JUL-12 N          0       2644
      4112          6       1112          1          1 03-JUL-12 N          0       2645
      4113          0       1113          1          1 03-JUL-12 Y          1       2646
      4114          6       1114          1          1 03-JUL-12 N          1       2647
      4115          6       1115          1          1 03-JUL-12 N          1       2648
      4116          0       1116          1          1 03-JUL-12 Y          2       2649
      4117          6       1117          1          1 03-JUL-12 N          2       2650
      4118          6       1118          1          1 03-JUL-12 N          2       2651
...
      4266          6       1837          1          1 03-JUL-12 N         66       2798
      4267          6       1838          1          1 03-JUL-12 N         66       2799
      4268          0       1839          1          1 03-JUL-12 Y         67       2800
...

Now, I think you know what you need to do to optimize this SQL statement, if your test case correctly replicates the product environment (if it is not obvious yet, look at the output of the SELECT SQL statement that returns 2800 rows, after the 20th update).

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




Follow

Get every new post delivered to your Inbox.

Join 137 other followers

%d bloggers like this: