This discussion is archived
13 Replies Latest reply: Sep 20, 2009 4:08 AM by Jonathan Lewis RSS

oracle rownum wrong explain plan

655554 Newbie
Currently Being Moderated
SCOTT@oracle10g>create table t as select * from dba_objects;

Table created.

SCOTT@oracle10g>alter table t modify CREATED date not null;

Table altered.

SCOTT@oracle10g>insert into t select * from t;

50416 rows created.

SCOTT@oracle10g>insert into t select * from t;

100832 rows created.

SCOTT@oracle10g>insert into t select * from t;

201664 rows created.

SCOTT@oracle10g>commit;

Commit complete.

SCOTT@oracle10g>create index t_created on t(created) nologging;

Index created.

SCOTT@oracle10g>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production


SCOTT@oracle10g>set autot trace 
SCOTT@oracle10g>select t.owner,t.object_name   from 
  2  (select rid from (
  3  select rownum rn,rid from 
  4  (select rowid rid from t order by created)
  5  where rownum<100035)
  6  where rn>100000) h, t
  7  where t.rowid=h.rid;

34 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3449471415

--------------------------------------------------------------------------------
---------

| Id  | Operation           | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| T
ime     |

--------------------------------------------------------------------------------
---------

|   0 | SELECT STATEMENT    |           |   100K|    11M|       |  4776   (2)| 0
0:00:58 |

|*  1 |  HASH JOIN          |           |   100K|    11M|  3616K|  4776   (2)| 0
0:00:58 |

|*  2 |   VIEW              |           |   100K|  2442K|       |  1116   (2)| 0
0:00:14 |

|*  3 |    COUNT STOPKEY    |           |       |       |       |            |
        |

|   4 |     VIEW            |           |   440K|  5157K|       |  1116   (2)| 0
0:00:14 |

|   5 |      INDEX FULL SCAN| T_CREATED |   440K|  9024K|       |  1116   (2)| 0
0:00:14 |

|   6 |   TABLE ACCESS FULL | T         |   440K|    39M|       |  1237   (2)| 0
0:00:15 |

--------------------------------------------------------------------------------
---------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T".ROWID="RID")
   2 - filter("RN">100000)
   3 - filter(ROWNUM<100035)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5814  consistent gets
          0  physical reads
          0  redo size
       1588  bytes sent via SQL*Net to client
        422  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         34  rows processed    

here ,oracle don't choose the best explain plan ,I think becase  oracle compute cadinality 100k ,so it don't choose nest loop,why oracle can't compute cardinality 35 here ??

|*  2 |   VIEW              |           |   100K|  2442K|       |  1116   (2)| 0



SCOTT@oracle10g>select  t.owner,t.object_name   from t where rowid in 
  2      (select rid from (
  3      select rownum rn,rid from 
  4      (select rowid rid from t order by created)
  5      where rownum<100035)
  6      where rn>100000) 
  7  
SCOTT@oracle10g>/

34 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1566335206

--------------------------------------------------------------------------------
---------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
ime     |

--------------------------------------------------------------------------------
---------

|   0 | SELECT STATEMENT            |           |     1 |   107 |  1586   (2)| 0
0:00:20 |

|   1 |  NESTED LOOPS               |           |     1 |   107 |  1586   (2)| 0
0:00:20 |

|   2 |   VIEW                      | VW_NSO_1  |   100K|  1172K|  1116   (2)| 0
0:00:14 |

|   3 |    HASH UNIQUE              |           |     1 |  2442K|            |
        |

|*  4 |     VIEW                    |           |   100K|  2442K|  1116   (2)| 0
0:00:14 |

|*  5 |      COUNT STOPKEY          |           |       |       |            |
        |

|   6 |       VIEW                  |           |   440K|  5157K|  1116   (2)| 0
0:00:14 |

|   7 |        INDEX FULL SCAN      | T_CREATED |   440K|  9024K|  1116   (2)| 0
0:00:14 |

|   8 |   TABLE ACCESS BY USER ROWID| T         |     1 |    95 |     1   (0)| 0
0:00:01 |

--------------------------------------------------------------------------------
---------


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("RN">100000)
   5 - filter(ROWNUM<100035)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        301  consistent gets
          0  physical reads
          0  redo size
       1896  bytes sent via SQL*Net to client
        422  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         34  rows processed

SCOTT@oracle10g>select /*+ordered use_nl(t)*/ t.owner,t.object_name   from 
  2  (select rid from (
  3  select rownum rn,rid from 
  4  (select rowid rid from t order by created)
  5  where rownum<100035)
  6  where rn>100000) h, t
  7  where t.rowid=h.rid;

34 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3976541160

--------------------------------------------------------------------------------
---------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
ime     |

--------------------------------------------------------------------------------
---------

|   0 | SELECT STATEMENT            |           |   100K|    11M|   101K  (1)| 0
0:20:16 |

|   1 |  NESTED LOOPS               |           |   100K|    11M|   101K  (1)| 0
0:20:16 |

|*  2 |   VIEW                      |           |   100K|  2442K|  1116   (2)| 0
0:00:14 |

|*  3 |    COUNT STOPKEY            |           |       |       |            |
        |

|   4 |     VIEW                    |           |   440K|  5157K|  1116   (2)| 0
0:00:14 |

|   5 |      INDEX FULL SCAN        | T_CREATED |   440K|  9024K|  1116   (2)| 0
0:00:14 |

|   6 |   TABLE ACCESS BY USER ROWID| T         |     1 |    95 |     1   (0)| 0
0:00:01 |

--------------------------------------------------------------------------------
---------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("RN">100000)
   3 - filter(ROWNUM<100035)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        304  consistent gets
          0  physical reads
          0  redo size
       1588  bytes sent via SQL*Net to client
        422  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         34  rows processed   
  • 1. Re: oracle rownum wrong explain plan
    655554 Newbie
    Currently Being Moderated
    who can help me ????
  • 2. Re: oracle rownum wrong explain plan
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    jinyu wrote:
    who can help me ????
    (You are asking this of volunteers - on a Saturday night? Can I encourage a review of "[How To Ask Questions The Smart Way|http://catb.org/~esr/faqs/smart-questions.html]")

    I did not see gathering of any statistics. Did I miss that? Dynamic sampling is generally sketchy at best.
  • 3. Re: oracle rownum wrong explain plan
    655554 Newbie
    Currently Being Moderated
    yes,I forgot to gather statistics,but it's not primary reason.
  • 4. Re: oracle rownum wrong explain plan
    698786 Newbie
    Currently Being Moderated
    Looks to me the bottom of line is can Oracle have stats on a view?

    If you have a table t having 1000 rows,then your create a view

    create t_view
    as
    select * from t
    where rownum <20;

    Does Oracle know you have only 19 rows in t_view?

    - Denis

    Edited by: denissun on Sep 19, 2009 11:52 PM
  • 5. Re: oracle rownum wrong explain plan
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    In my quick test of variation 1 (XE on Win XPP/SP2), I come up with similar results before and after stats. Look at the pothers if I get a chance.

    Before gathering stats
    -----------------------------------------------------------------------------------------
    | Id  | Operation           | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |           |   100K|    11M|       |  1660   (2)| 00:00:20 |
    |*  1 |  HASH JOIN          |           |   100K|    11M|  3616K|  1660   (2)| 00:00:20 |
    |*  2 |   VIEW              |           |   100K|  2442K|       |   327   (2)| 00:00:04 |
    |*  3 |    COUNT STOPKEY    |           |       |       |       |            |          |
    |   4 |     VIEW            |           |   141K|  1655K|       |   327   (2)| 00:00:04 |
    |   5 |      INDEX FULL SCAN| T_CREATED |   141K|  2897K|       |   327   (2)| 00:00:04 |
    |   6 |   TABLE ACCESS FULL | T         |   141K|    12M|       |   433   (2)| 00:00:06 |
    -----------------------------------------------------------------------------------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("T".ROWID="RID")
       2 - filter("RN">100000)
       3 - filter(ROWNUM<100035)
    
    Note
    -----
       - dynamic sampling used for this statement
    
    
    Statistics
    ----------------------------------------------------------
             14  recursive calls
              0  db block gets
           2161  consistent gets
            265  physical reads
              0  redo size
           1689  bytes sent via SQL*Net to client
            406  bytes received via SQL*Net from client
              4  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
             34  rows processed
    
    SQL> connect / as sysdba
    Connected.
    SQL> EXEC DBMS_STATS.gather_schema_stats('TEST');
    
    PL/SQL procedure successfully completed.
    after gather stats
    SQL> connect test/test
    Connected.
    SQL>select t.owner,t.object_name   from 
      2      (select rid from (
      3      select rownum rn,rid from
      4      (select rowid rid from t order by created)
      5      where rownum<100035)
      6      where rn>100000) h, t
      7      where t.rowid=h.rid;
    SQL> set autot trace
    SQL> select t.owner,t.object_name   from
      2          (select rid from (
      3          select rownum rn,rid from
      4          (select rowid rid from t order by created)
      5          where rownum<100035)
      6          where rn>100000) h, t
      7          where t.rowid=h.rid;
    
    34 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3449471415
    
    -----------------------------------------------------------------------------------------
    | Id  | Operation           | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |           |   100K|  6252K|       |  1235   (2)| 00:00:15 |
    |*  1 |  HASH JOIN          |           |   100K|  6252K|  3616K|  1235   (2)| 00:00:15 |
    |*  2 |   VIEW              |           |   100K|  2442K|       |   326   (2)| 00:00:04 |
    |*  3 |    COUNT STOPKEY    |           |       |       |       |            |          |
    |   4 |     VIEW            |           |   121K|  1419K|       |   326   (2)| 00:00:04 |
    |   5 |      INDEX FULL SCAN| T_CREATED |   121K|  2365K|       |   326   (2)| 00:00:04 |
    |   6 |   TABLE ACCESS FULL | T         |   121K|  4613K|       |   432   (2)| 00:00:06 |
    -----------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("T".ROWID="RID")
       2 - filter("RN">100000)
       3 - filter(ROWNUM<100035)
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
           1843  consistent gets
              0  physical reads
              0  redo size
           1689  bytes sent via SQL*Net to client
            406  bytes received via SQL*Net from client
              4  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
             34  rows processed
  • 6. Re: oracle rownum wrong explain plan
    655554 Newbie
    Currently Being Moderated
    oracle must be know the result of the query has no more than 20 rows,so when the view join other tables,the row source of view no more than 20
  • 7. Re: oracle rownum wrong explain plan
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    jinyu wrote:
    oracle must be know the result of the query has no more than 20 rows,so when the view join other tables,the row source of view no more than 20
    Why MUST Oracle know ? Just because a pattern is obvious to you it doesn't mean that recognition of that pattern has been coded into the optimizer.

    I agree with you that this looks like such an important and common coding strategy that the optimizer ought to be coded to handle it. I'd even say that I'm a little surprised that it doesn't already do so. But it seems that the optimizer loses all knowledge of the values of the generated "rn" columns as soon it has generated the data. There's [*a posting on my blog*|http://jonathanlewis.wordpress.com/2008/05/09/manual-optimisation-2/] that discusses the technique you've used in your code.

    At the moment I just live with the need to control this type of code with hints - but you might like to raise an SR on it, passing in your test case to make the point.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 8. Re: oracle rownum wrong explain plan
    655554 Newbie
    Currently Being Moderated
    Thanks for your great reply and posting ,could you tell me why subquery has the least cost here ??
    SCOTT@oracle10g>select  t.owner,t.object_name   from t where rowid in 
      2      (select rid from (
      3      select rownum rn,rid from 
      4      (select rowid rid from t order by created)
      5      where rownum<100035)
      6      where rn>100000) 
      7  
    SCOTT@oracle10g>/ 
     
    34 rows selected.
     
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1566335206
     
    --------------------------------------------------------------------------------
    ---------
     
    | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
    ime     |
     
    --------------------------------------------------------------------------------
    ---------
     
    |   0 | SELECT STATEMENT            |           |     1 |   107 |  1586   (2)| 0
    0:00:20 |
     
    |   1 |  NESTED LOOPS               |           |     1 |   107 |  1586   (2)| 0
    0:00:20 |
     
    |   2 |   VIEW                      | VW_NSO_1  |   100K|  1172K|  1116   (2)| 0
    0:00:14 |
     
    |   3 |    HASH UNIQUE              |           |     1 |  2442K|            |
            |
     
    |*  4 |     VIEW                    |           |   100K|  2442K|  1116   (2)| 0
    0:00:14 |
     
    |*  5 |      COUNT STOPKEY          |           |       |       |            |
            |
     
    |   6 |       VIEW                  |           |   440K|  5157K|  1116   (2)| 0
    0:00:14 |
     
    |   7 |        INDEX FULL SCAN      | T_CREATED |   440K|  9024K|  1116   (2)| 0
    0:00:14 |
     
    |   8 |   TABLE ACCESS BY USER ROWID| T         |     1 |    95 |     1   (0)| 0
    0:00:01 |
     
    --------------------------------------------------------------------------------
    ---------
     
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       4 - filter("RN">100000)
       5 - filter(ROWNUM<100035)
     
    Note
    -----
       - dynamic sampling used for this statement
  • 9. Re: oracle rownum wrong explain plan
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    jinyu wrote:
    Thanks for your great reply and posting ,could you tell me why subquery has the least cost here ??

    -----------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -----------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 107 | 1586 (2)| 00:00:20 |
    | 1 | NESTED LOOPS | | 1 | 107 | 1586 (2)| 00:00:20 |
    | 2 | VIEW | VW_NSO_1 | 100K| 1172K| 1116 (2)| 00:00:14 |
    | 3 | HASH UNIQUE | | 1 | 2442K| | |
    |* 4 | VIEW | | 100K| 2442K| 1116 (2)| 00:00:14 |
    |* 5 | COUNT STOPKEY | | | | | |
    | 6 | VIEW | | 440K| 5157K| 1116 (2)| 00:00:14 |
    | 7 | INDEX FULL SCAN | T_CREATED | 440K| 9024K| 1116 (2)| 00:00:14 |
    | 8 | TABLE ACCESS BY USER ROWID| T | 1 | 95 | 1 (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------->
    You'll notice that as a result of a "driving" IN subquery Oracle has done a hash unique operation (line 3) on the rowids produced by the subquery. At this point the optimizer has lost all knowledge of the number of distinct values for that data column in the subquery and come back with the cardinality of one. The re-appearance of 100K as the cardinality in line 2 is an error, but I don't think the optimizer has used that value in later arithmetic.

    Given the cardinality of one, the obvious path into the T table is a nested loop.

    The same type of probelm appears when you use the table() operator in joins - you can use the cardinality() hint to try an tell Oracle how many rows the table() will produce, but that doesn't tell it how many distinct values there are in join columns - and that's an important detail when you work out the join cardinality and method).

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 10. Re: oracle rownum wrong explain plan
    655554 Newbie
    Currently Being Moderated
    SCOTT@oracle10g>alter session set "_optimizer_cost_model"=io;
    
    会话已更改。
    
    SYS@oracle10g>select  t.owner,t.object_name   from t where rowid in     (select rid from (
      2      select rownum rn,rid from 
      3      (select rowid rid from t order by created)
      4      where rownum<100035)
      5      where rn>100000)
      6  /
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 1566335206
    
    -------------------------------------------------------------------------
    | Id  | Operation                   | Name      | Rows  | Bytes | Cost  |
    -------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |           |     1 |    53 |  1382 |
    |   1 |  NESTED LOOPS               |           |     1 |    53 |  1382 |
    |   2 |   VIEW                      | VW_NSO_1  |   100K|  1172K|  1070 |
    |   3 |    HASH UNIQUE              |           |     1 |  2442K|       |
    |*  4 |     VIEW                    |           |   100K|  2442K|  1070 |
    |*  5 |      COUNT STOPKEY          |           |       |       |       |
    |   6 |       VIEW                  |           |   403K|  4726K|  1070 |
    |   7 |        INDEX FULL SCAN      | T_CREATED |   403K|  7483K|  1070 |
    |   8 |   TABLE ACCESS BY USER ROWID| T         |     1 |    41 |     1 |
    -------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - filter("RN">100000)
       5 - filter(ROWNUM<100035)
    
    Note
    -----
       - cpu costing is off (consider enabling it)
    if the cardinality=1,the cost may be 1070 +1*1=1071 ,here the cost is 1382
  • 11. Re: oracle rownum wrong explain plan
    655554 Newbie
    Currently Being Moderated
    From 10053 trace,I find out table cost is 1381 not 1070
    ***************
    Now joining:  T[T]#0
    ***************
    NL Join
      Outer table: Card: 1.00  Cost: 1381.00  Resp: 1380.50  Degree: 1  Bytes: 12
      Inner table:  T  Alias: T
      Access Path: TableScan
        NL Join:  Cost: 1912.50  Resp: 1912.50  Degree: 0
          Cost_io: 1912.50  Cost_cpu: 0
          Resp_io: 1912.50  Resp_cpu: 0
      Inner table:  T  Alias: T
      Access Path: Rowid
        NL Join:  Cost: 1381.50  Resp: 1381.50  Degree: 0
          Cost_io: 1381.50  Cost_cpu: 0
          Resp_io: 1381.50  Resp_cpu: 0
      Best NL cost: 1382.00
              resc: 1381.50 resc_io: 1381.50 resc_cpu: 0
              resp: 1381.50 resp_io: 1381.50 resp_cpu: 0
    Join Card:  1.00 = outer (1.00) * inner (403336.00) * sel (2.4793e-006)
    Join Card - Rounded: 1 Computed: 1.00
  • 12. Re: oracle rownum wrong explain plan
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    From the investigation you've done in the last couple of posts, you can see that the entire set of figures in line 2 of the plan (and not just the 100K for cardinality) must have been reported incorrectly from some other part of the calculation. The cost of that VIEW (and the hash unique in line 3) should be the 1380 that you found in the trace file, leading (with rounding) to the NL cost of 1382 on line 1.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 13. Re: oracle rownum wrong explain plan
    655554 Newbie
    Currently Being Moderated
    Thanks very very very much

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points