This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Aug 30, 2013 2:13 PM by Jan-Marten Spit RSS

bind variables and expression evaluation

Jan-Marten Spit Explorer
Currently Being Moderated

Hi friends,

 

i have been looking at a query that uses a wrong plan. db=11.2.0.3 with user bind peeking enabled. this is a simplified version demonstrating the problem:

 

select egp.bsn

,      egp.klantnummer       as "Persoonsnummer"

,      egp.samengesteldenaam as "Samengesteldenaam"

,      egp.geboortedatum     as "Geboortedatum"

from   pr_eigenschappen2      egp

where

(egp.bsn               = :b1 or :b2 is null)

and rownum < 51

 

egp.bsn is varchar2(10) and has high selectivity (almost unique), and is btree-indexed. table and index have adequate statistics.

 

when run with b1:=928818 and b2:=928818  (both bound as varchar2(10)) a full table scan+filter is used on pr_eigenschappen2.

 

if the query is changed to

 

select egp.bsn

,      egp.klantnummer       as "Persoonsnummer"

,      egp.samengesteldenaam as "Samengesteldenaam"

,      egp.geboortedatum     as "Geboortedatum"

from   pr_eigenschappen2      egp

where

(egp.bsn               = :b1 or 928818 is null)

and rownum < 51


the index on bsn is used, and the query is not taking 3.9 seconds but 1 millisecond.


if i would have a choice, the query would be different. i don't want to talk about the raison d'etre of the query, i would like to know why the optimizer is not using the index in the first case.



  • 1. Re: bind variables and expression evaluation
    Hoek Guru
    Currently Being Moderated

    I can reproduce.

    Apparently it seems to switch the OR, the :b IS NULL part suddenly appears first in the plan?

    Looks kind of buggy to me....did find some nvl/index related bugs on Metalink as well, perhaps this is related:

     

     

    SQL> create table t as select level col, dbms_random.string('a', 512) str
      2  from dual connect by level <= 500000;

    Table created.

    SQL> create index t_idx on t(col);

    Index created.

    SQL> exec dbms_stats.gather_table_stats(user, 'T', cascade => true);

    PL/SQL procedure successfully completed.

    SQL> select /*+ gather_plan_statistics */
      2  .
    SQL> var b number
    SQL> exec :b := 125

    PL/SQL procedure successfully completed.

    SQL> select /*+ gather_plan_statistics */
      2         col
      3  ,      str
      4  from   t
      5  where ( col = :b or :b is null )
      6  and rownum < 51;

           COL
    ----------
    STR
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
           125
    lfNxyQgFeiewXxTagYVpMmCqyxLTUcIUlfmRJeUPneeGSXctluJsDgzkcZQTtpZPORNgboqTCzweJejksULXJIEOFDlCTufCvsnuLSNnuFXMLjUUucrDxddTUyiuiVVyrjYVxwXmajkVrYizfcFRYJVQqWouojlmowiIgsHUMbYpCMSAboMHerOiFNQlgJYOgSeYQqUsDdHaVoRSKBBSaAUtKytMCfjstCNTCnseRThAffAPOSDHKtIqQYkTVDkxgUuYbKxc


    1 row selected.

    SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  apvzpah54ug1b, child number 0
    -------------------------------------
    select /*+ gather_plan_statistics */        col ,      str from   t
    where ( col = :b or :b is null ) and rownum < 51

    Plan hash value: 508354683

    ----------------------------------------------------------------------------------------------
    | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
    ----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |   38475 |  38462 |
    |*  1 |  COUNT STOPKEY     |      |      1 |        |      1 |00:00:00.01 |   38475 |  38462 |
    |*  2 |   TABLE ACCESS FULL| T    |      1 |     51 |      1 |00:00:00.01 |   38475 |  38462 |
    ----------------------------------------------------------------------------------------------

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

       1 - filter(ROWNUM<51)
       2 - filter((:B IS NULL OR "COL"=:B))


    21 rows selected.

    SQL> select /*+ gather_plan_statistics */
      2         col
      3  ,      str
      4  from   t
      5  where ( col = :b or 125 is null )
      6  and rownum < 51;

           COL
    ----------
    STR
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
           125
    lfNxyQgFeiewXxTagYVpMmCqyxLTUcIUlfmRJeUPneeGSXctluJsDgzkcZQTtpZPORNgboqTCzweJejksULXJIEOFDlCTufCvsnuLSNnuFXMLjUUucrDxddTUyiuiVVyrjYVxwXmajkVrYizfcFRYJVQqWouojlmowiIgsHUMbYpCMSAboMHerOiFNQlgJYOgSeYQqUsDdHaVoRSKBBSaAUtKytMCfjstCNTCnseRThAffAPOSDHKtIqQYkTVDkxgUuYbKxc


    1 row selected.

    SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  1jy9jmsxqnkw1, child number 0
    -------------------------------------
    select /*+ gather_plan_statistics */        col ,      str from   t
    where ( col = :b or 125 is null ) and rownum < 51

    Plan hash value: 2869526954

    ---------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
    ---------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |       |      1 |        |      1 |00:00:00.01 |       5 |      1 |
    |*  1 |  COUNT STOPKEY               |       |      1 |        |      1 |00:00:00.01 |       5 |      1 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| T     |      1 |      1 |      1 |00:00:00.01 |       5 |      1 |
    |*  3 |    INDEX RANGE SCAN          | T_IDX |      1 |      1 |      1 |00:00:00.01 |       4 |      0 |
    ---------------------------------------------------------------------------------------------------------

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

       1 - filter(ROWNUM<51)
       3 - access("COL"=:B)


    22 rows selected.

    SQL> select banner from v$version;

    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE    11.2.0.3.0      Production
    TNS for Linux: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production

    5 rows selected.

    SQL>

  • 2. Re: bind variables and expression evaluation
    Hoek Guru
    Currently Being Moderated

    And using NVL I get:

     

     

    SQL> select /*+ gather_plan_statistics */
      2         col
      3  ,      str
      4  from   t
      5  where  nvl(:b, col) = col
      6  and rownum < 51;

           COL
    ----------
    STR
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
           125
    lfNxyQgFeiewXxTagYVpMmCqyxLTUcIUlfmRJeUPneeGSXctluJsDgzkcZQTtpZPORNgboqTCzweJejksULXJIEOFDlCTufCvsnuLSNnuFXMLjUUucrDxddTUyiuiVVyrjYVxwXmajkVrYizfcFRYJVQqWouojlmowiIgsHU


    1 row selected.

    SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  5cys33bh9agw6, child number 0
    -------------------------------------
    select /*+ gather_plan_statistics */        col ,      str from   t
    where  nvl(:b, col) = col and rownum < 51

    Plan hash value: 4272883126

    ---------------------------------------------------------------------------------------------------
    | Id  | Operation                       | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |       |      1 |        |      1 |00:00:00.01 |       5 |
    |*  1 |  COUNT STOPKEY                  |       |      1 |        |      1 |00:00:00.01 |       5 |
    |   2 |   CONCATENATION                 |       |      1 |        |      1 |00:00:00.01 |       5 |
    |*  3 |    FILTER                       |       |      1 |        |      0 |00:00:00.01 |       0 |
    |*  4 |     FILTER                      |       |      1 |        |      0 |00:00:00.01 |       0 |
    |*  5 |      TABLE ACCESS FULL          | T     |      0 |    500K|      0 |00:00:00.01 |       0 |
    |*  6 |    FILTER                       |       |      1 |        |      1 |00:00:00.01 |       5 |
    |*  7 |     FILTER                      |       |      1 |        |      1 |00:00:00.01 |       5 |
    |   8 |      TABLE ACCESS BY INDEX ROWID| T     |      1 |      1 |      1 |00:00:00.01 |       5 |
    |*  9 |       INDEX RANGE SCAN          | T_IDX |      1 |      1 |      1 |00:00:00.01 |       4 |
    ---------------------------------------------------------------------------------------------------

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

       1 - filter(ROWNUM<51)
       3 - filter(ROWNUM<51)
       4 - filter(:B IS NULL)
       5 - filter("COL" IS NOT NULL)
       6 - filter(ROWNUM<51)
       7 - filter(:B IS NOT NULL)
       9 - access("COL"=:B)


    33 rows selected.

    SQL>


    Not sure if this is a plan we'd expect or can predict...oh heck, I totally did not expect this plan.....

  • 3. Re: bind variables and expression evaluation
    Jan-Marten Spit Explorer
    Currently Being Moderated

    thanks for your replies.

     

    in the meantime, when i rewrite

     

    (egp.bsn               = :b1 or :b2 is null)


    as


    egp.bsn=decode(:b1,NULL,egp.bsn,:b1)


    this specific application problem is solved (is using the index) and uses only halve the bind vars.


    the trick seems to get rid of the OR here.


    as for "the plan you did not expect", it is using OR expansion into concatenation, so effectively moving the OR up and replacing it with a CONCAT of two distinct result sets (as if it is a union)

    (which for the simplified case one can enforce with the USE_CONCAT hint).


    still, my question remains is it realy a bug or do i not understand user bind peeking fully


  • 4. Re: bind variables and expression evaluation
    Hoek Guru
    Currently Being Moderated

    To be more specific, when using nvl, I didn't expect to see 'rownum<51' appearing three times.  Thanks for reminding me of OR expansion

    Regarding your remaining question: I played a bit with different settings for optimizer_features_enable, but Optimizer didn't (really) change it's mind:

    When set to 10.2.0.1 you'll get the exact sample plan, and for lower versions you'll get:

     

    SQL> alter session set optimizer_features_enable = '9.2.0';

    Session altered.

     

    SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------
    SQL_ID  apvzpah54ug1b, child number 3
    -------------------------------------
    select /*+ gather_plan_statistics */        col ,      str from   t
    where ( col = :b or :b is null ) and rownum < 51

    Plan hash value: 508354683

    ----------------------------------------------------------------------------------------------
    | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
    ----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |   38475 |  37926 |
    |*  1 |  COUNT STOPKEY     |      |      1 |        |      1 |00:00:00.01 |   38475 |  37926 |
    |*  2 |   TABLE ACCESS FULL| T    |      1 |  25001 |      1 |00:00:00.01 |   38475 |  37926 |
    ----------------------------------------------------------------------------------------------

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

       1 - filter(ROWNUM<51)
       2 - filter(("COL"=:B OR :B IS NULL))

    Note
    -----
       - cpu costing is off (consider enabling it)


    25 rows selected.

     

     

    Perhaps your thread will draw the attention of Mr. Lewis and he might expand on this

    I'm a bit confused myself now, on one hand I tend to think it's a bug, since the bind variable is not null, Opimizer should know that, however, on the other hand the Optimizer needs to take into account the fact that the bind var might be null and then a full scan is needed....

  • 5. Re: bind variables and expression evaluation
    Jan-Marten Spit Explorer
    Currently Being Moderated

    "Perhaps your thread will draw the attention of Mr. Lewis and he might expand on this "

     

    that was sort of the intention

     

    thx!

  • 6. Re: bind variables and expression evaluation
    Martin Preiss Expert
    Currently Being Moderated

    I think that the CBO is able to understand that <928818 is null> can savely be ignored since it's false all the time. So I made a CBO trace with Hoek's example (in 11.1.0.7) assuming that the "Final query after transformations" would exclude this part of the condition - but this is not the case. In the trace I don't see an explanation for the decision but it's clear that the CBO does not even consider to use the index for the first query:

     

    -- col = :b or :b is null

    SINGLE TABLE ACCESS PATH

      Single Table Cardinality Estimation for T[T]

      Table: T  Alias: T

        Card: Original: 50000.000000  Rounded: 2501  Computed: 2500.95  Non Adjusted: 2500.95

      Access Path: TableScan

        Cost:  1960.01  Resp: 1960.01  Degree: 0

          Cost_io: 1960.00  Cost_cpu: 38819700

          Resp_io: 1960.00  Resp_cpu: 38819700

      ****** trying bitmap/domain indexes ******

      ****** finished trying bitmap/domain indexes ******

      Best:: AccessPath: TableScan

             Cost: 1960.01  Degree: 1  Resp: 1960.01  Card: 2500.95  Bytes: 0

     

    -- col = :b or 125 is null

    SINGLE TABLE ACCESS PATH

      Single Table Cardinality Estimation for T[T]

      Table: T  Alias: T

        Card: Original: 50000.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00

      Access Path: TableScan

        Cost:  1960.01  Resp: 1960.01  Degree: 0

          Cost_io: 1960.00  Cost_cpu: 37894700

          Resp_io: 1960.00  Resp_cpu: 37894700

      Access Path: index (AllEqRange)

        Index: T_IDX

        resc_io: 2.00  resc_cpu: 15463

        ix_sel: 0.000020  ix_sel_with_filters: 0.000020

        Cost: 2.00  Resp: 2.00  Degree: 1

      Best:: AccessPath: IndexRange

      Index: T_IDX

             Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.00  Bytes: 0

  • 7. Re: bind variables and expression evaluation
    Martin Preiss Expert
    Currently Being Moderated

    and a little example to support my interpretation:

     

    select /*+ gather_plan_statistics */ * from t where 125 is null;

     

    SQL_ID  gy91bdw0vhv0x, child number 0

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

    select /*+ gather_plan_statistics */ * from t where 125 is null

     

    Plan hash value: 1322348184

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

    | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   |

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

    |   0 | SELECT STATEMENT   |      |      1 |        |      0 |00:00:00.01 |

    |*  1 |  FILTER            |      |      1 |        |      0 |00:00:00.01 |

    |   2 |   TABLE ACCESS FULL| T    |      0 |  50000 |      0 |00:00:00.01 |

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

     

    Predicate Information (identified by operation id):

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

       1 - filter(NULL IS NOT NULL)

     

    The CBO estimates 50000 rows but it (or the runtime engine) is able to decide that it's useless to do the FTS at all. In this case the additional predicate - filter(NULL IS NOT NULL) - is a better indicator (beeing discribed by Maria Colgan - http://optimizermagic.blogspot.de/2008/06/why-are-some-of-tables-in-my-query.html).

     

    Regards

     

    Martin

  • 8. Re: bind variables and expression evaluation
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    The "rownum < 51" has to appear 3 times because the optimizer has to produce a generic plan that is always correct irrespective of the values in the bind variables. This means that both inner parts of the concat have to have the predicate so that they stop as soon as possible, and the outer part has to have the predicate because (generically) a concat with rownum has to ensure that it doesn't do "real" concatenation and get more then the required number of rows.  Of course in this particular case we know that the concat will only run one of the two sub-plans because of the way the two filter predicates split cover the only options with no overlap.

     

    Regards

    Jonathan Lewis


  • 9. Re: bind variables and expression evaluation
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    Your final comment is correct, and explains a lot.

     

    The optimizer can see whether the bind variable is null or not for the first peek - but it has to produce a plan that will produce the correct answer for every other possible input, so it has to be a plan that gets the right answer whether or not the variable is null or not.

     

    Regards

    Jonathan Lewis


  • 10. Re: bind variables and expression evaluation
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    I'm a little surprised that the plan doesn't automatically use concatenation (with one subplan filtering on :b2 is null and the other filtering on :b2 is not null). I'd have to check whether this can happen automatically, though.  The rownum < 51 pushes the optimizer into First_K optimisation - and that does have some buggy bits and some surprising emergent effects. You might compare the costs of the plans for "select where col = :b1 and rownum <51" and  "select where rownum < 51" - if the latter is cheaper than the former that might explain the full scan plan you've got; you could also check whether or not you get concatenation if you remove the rownum predicate.

     

    I'd say the plan is "not irrational", but is "arithmetically surprising".

     

    Regards

    Jonathan Lewis


  • 11. Re: bind variables and expression evaluation
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    You need to be careful with that rewrite - it will exclude rows where bsn is null.

    I've written a couple of notes on this entire topic, one here: Conditional SQL | Oracle Scratchpad

     

    Regards

    Jonathan Lewis


  • 12. Re: bind variables and expression evaluation
    Hoek Guru
    Currently Being Moderated

    And "arithmetically surprising" != "bug"?

  • 13. Re: bind variables and expression evaluation
    Hoek Guru
    Currently Being Moderated

    Thanks for clearing my doubts, Jonathan!

  • 14. Re: bind variables and expression evaluation
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    Hoek wrote:

     

    And "arithmetically surprising" != "bug"?

     

    Not necessarily - for example, you may have a query where you know that the use of a particular index makes sense, but Oracle uses a different index. There may be something about the statistics that means that Oracle's choice is logically correct, but mechanically unsuitable.  Picking the "wrong" index would be a surprise, but not a bug.

     

    In the original example, though, I (quickly) can't think of a simple data set where applying the correct logic would produce this plan.

     

    Regards

    Jonathan Lewis

1 2 Previous Next

Legend

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