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

    bind variables and expression evaluation

    Jan-Marten Spit

      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

          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

            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

              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

                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

                  "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

                    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

                      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

                        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

                          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

                            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

                              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

                                And "arithmetically surprising" != "bug"?

                                • 13. Re: bind variables and expression evaluation
                                  Hoek

                                  Thanks for clearing my doubts, Jonathan!

                                  • 14. Re: bind variables and expression evaluation
                                    Jonathan Lewis

                                    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