1 2 Previous Next 17 Replies Latest reply on Nov 12, 2013 2:31 PM by Nikolay Savvinov

    Bind Variable Peeking Not Working

    cmit0227


      Hello,

       

      I'm having an issue with a query I'm running in a stored proc.

       

      The BILLING_TRANS_LOG table is partitioned on orig_process_date and when I run the query with literals the plan makes use of the partitions, but when using values from the procedure parameters or bind variables the plan does not use them and takes a long time to return.

       

        SELECT btl.billing_trans_id 

      FROM compas.billing_trans_log btl, compas.billing_trans_log_alloc btla 

      WHERE

      btl.billing_trans_id = btla.billing_trans_id 

      AND btl.bank_routing_number = '123456789' 

      AND btl.bank_account_number = '222222222''

      --AND btl.orig_process_date BETWEEN '01-sep-2012' AND '07-sep-2012' 

      AND btl.orig_process_date BETWEEN :p_start_date AND :p_stop_date 

      AND btla.total_amount = 214.5;

       

      Does anyone know what I might do to force bind variable peeking so that the plan uses the partitions every time, or some other approach that would ensure the use of them?

       

      Thanks,

      Christine

        • 1. Re: Bind Variable Peeking Not Working
          Azhar Husain

          or some other approach that would ensure the use of them?

           

          SELECT * FROM <table_name> PARTITION (partition_name) WHERE ....;
          • 2. Re: Bind Variable Peeking Not Working
            Hoek

            If orig_process_date is of DATE datatype, then shouldn't you also TO_DATE your p_start_date and p_stop_date parameters?

            Never compare a date to a string.

            • 3. Re: Bind Variable Peeking Not Working
              Nikolay Savvinov

              Hi,

               

              I think the plan should use PARTITION RANGE (ITERATOR) regardless to bind peeking.

              Can you post the actual plan you are getting (not the explain plan)? Could it be that your problem

              might be related to something other than partition pruning not taking place?

               

              Best regards,

                Nikolay

              • 4. Re: Bind Variable Peeking Not Working
                cmit0227

                Hi - I can't specify the partition - it could be any one.

                 

                AzharHusain wrote:

                 

                or some other approach that would ensure the use of them?

                 

                SELECT * FROM <table_name> PARTITION (partition_name) WHERE ....;
                • 5. Re: Bind Variable Peeking Not Working
                  cmit0227

                  That was just for quick testing - it's a date parameter that used in the stored proc, but that still doesn't affect the fact that it's not using the partition.

                   

                  Hoek wrote:

                   

                  If orig_process_date is of DATE datatype, then shouldn't you also TO_DATE your p_start_date and p_stop_date parameters?

                  Never compare a date to a string.

                  • 6. Re: Bind Variable Peeking Not Working
                    cmit0227

                    Hi - I'm not sure what plan you are referring to, if not the explain plan.  Will you please clarify?  Thanks....

                     

                    NikolaySavvinov wrote:

                     

                    Hi,

                     

                    I think the plan should use PARTITION RANGE (ITERATOR) regardless to bind peeking.

                    Can you post the actual plan you are getting (not the explain plan)? Could it be that your problem

                    might be related to something other than partition pruning not taking place?

                     

                    Best regards,

                      Nikolay

                    • 7. Re: Bind Variable Peeking Not Working
                      cmit0227

                      In the meantime (while I wait to hear what plan you're referring to), here are the explain plans:

                       

                      USING BIND VARIABLES:

                       

                       

                      Plan hash value: 2446918835

                       

                      --------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                            | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
                      --------------------------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT                     |                         |     1 |    48 |  1338K  (1)| 04:27:41 |       |       |
                      |*  1 |  FILTER                              |                         |       |       |            |          |       |       |
                      |   2 |   NESTED LOOPS                       |                         |       |       |            |          |       |       |
                      |   3 |    NESTED LOOPS                      |                         |     1 |    48 |  1338K  (1)| 04:27:41 |       |       |
                      |*  4 |     TABLE ACCESS FULL                | BILLING_TRANS_LOG_ALLOC |  1011 | 12132 |  1335K  (1)| 04:27:04 |       |       |
                      |*  5 |     INDEX UNIQUE SCAN                | PK_BILLING_TRANS_LOG    |     1 |       |     2   (0)| 00:00:01 |       |       |
                      |*  6 |    TABLE ACCESS BY GLOBAL INDEX ROWID| BILLING_TRANS_LOG       |     1 |    36 |     3   (0)| 00:00:01 | ROWID | ROWID |
                      --------------------------------------------------------------------------------------------------------------------------------

                       

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

                       

                         1 - filter(TO_DATE(:P_START_DATE)<=TO_DATE(:P_STOP_DATE))
                         4 - filter("BTLA"."TOTAL_AMOUNT"=214.5)
                         5 - access("BTL"."BILLING_TRANS_ID"="BTLA"."BILLING_TRANS_ID")
                         6 - filter("BTL"."BANK_ACCOUNT_NUMBER"='222222222' AND "BTL"."BANK_ROUTING_NUMBER"='123456789' AND
                                    "BTL"."ORIG_PROCESS_DATE">=:P_START_DATE AND "BTL"."ORIG_PROCESS_DATE"<=:P_STOP_DATE)


                      USING LITERALS:

                       

                       

                       

                       

                      Plan hash value: 1651242598

                       

                      ----------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                    | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
                      ----------------------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT             |                             |     1 |    48 | 11939   (1)| 00:02:24 |       |       |
                      |   1 |  NESTED LOOPS                |                             |       |       |            |          |       |       |
                      |   2 |   NESTED LOOPS               |                             |     1 |    48 | 11939   (1)| 00:02:24 |       |       |
                      |   3 |    PARTITION RANGE SINGLE    |                             |     1 |    36 | 11935   (1)| 00:02:24 |   111 |   111 |
                      |*  4 |     TABLE ACCESS FULL        | BILLING_TRANS_LOG           |     1 |    36 | 11935   (1)| 00:02:24 |   111 |   111 |
                      |*  5 |    INDEX RANGE SCAN          | IDX_BILLING_TRANS_LOG_ALLOC |     1 |       |     3   (0)| 00:00:01 |       |       |
                      |*  6 |   TABLE ACCESS BY INDEX ROWID| BILLING_TRANS_LOG_ALLOC     |     1 |    12 |     4   (0)| 00:00:01 |       |       |
                      ----------------------------------------------------------------------------------------------------------------------------

                       

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

                       

                         4 - filter("BTL"."BANK_ACCOUNT_NUMBER"='222222222' AND "BTL"."BANK_ROUTING_NUMBER"='123456789' AND
                                    "BTL"."ORIG_PROCESS_DATE"<=TO_DATE(' 2012-09-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
                                    "BTL"."ORIG_PROCESS_DATE">=TO_DATE(' 2012-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
                         5 - access("BTL"."BILLING_TRANS_ID"="BTLA"."BILLING_TRANS_ID")
                         6 - filter("BTLA"."TOTAL_AMOUNT"=214.5)

                      • 8. Re: Bind Variable Peeking Not Working
                        Nikolay Savvinov

                        Hi,

                         

                        1) just like I thought -- bind variables don't prevent partition pruning. I.e. the two plans differ not because in one case non-used paritions are eliminated and in the other they aren't. Look carefully -- they are driven from different tables

                        2) posted plans don't prove that bind peeking is not taking place, because you cannot observe bind peeking with explain plans. An explain plan is the optimizer's prediction of the plan which may differ from reality for a number of reasons, the most common being bind peeking. You can obtain the actual plan from a trace file or from dbms_xlan.display_cursor after the statement is completed.

                        I'm sure that if you flush the shared pool, then run your statement with binds (using the values above) you'll get the same plan you were getting for literals

                        3) note though that this plan will only be efficient for a small subset of possible bind variable values (when they are close to each other), for other cases it will be very inefficient

                        4) with bind variables you depend on which values are peeked by the optimizer when hard parsing the statement, i.e. you cannot control the plan. For instance, you flush the shared pool, run the statement for the first time with values that ensure the plan you chose. So far so good. Then after a while, your cursor gets aged out of the shared pool (or invalidated), and the statement gets hard-parsed again. You don't know which bind values would be used at that time

                        5) so in order to ensure the optimal plan the best solution would be not to use binds at all (unless you run the statement very often and use new values of parameters every time you do so -- then you'll be chosing between two evils: wasting performance on parsing and getting instable plans). or you can force the desired plan with one of plan stability techniques, such as SQL profiles or baselines

                        6) in 11g Oracle introduced so-called "adaptive cursor sharing" which is supposed to take care of situations like your -- i.e. it checks if the cached plan can be effiient for the given value of the bind variables. Unfortunately, this feature doesn't always work smoothly, but in many cases it solves the problem "out of the box"

                         

                        Best regards,

                          Nikolay

                        • 9. Re: Bind Variable Peeking Not Working
                          cmit0227

                          Hi Nikolay,

                           

                          Very interesting.  All of this is new to me so I really appreciate the information you've shared.  Our App DBA lead has suggested that I try to use dynamic SQL instead, in order to avoid the use of bind variables.  Do you think with this approach I could still run into the same problem over time?

                           

                          Thanks again,

                          Christine

                          • 10. Re: Bind Variable Peeking Not Working
                            Hoek

                            Your Apps DBA is in fact suggesting to make your application vulnerable for SQL Injection.

                            • 11. Re: Bind Variable Peeking Not Working
                              Nikolay Savvinov

                              Hi Christine,

                               

                              I don't like dynamic SQL -- in additional to being prone to SQL Injection as mentioned by Hoek it's also more difficult to maintain and troubleshoot.

                              I'm not sure what is the best option here -- it depends on how often this SQL will run, how often various combinations of input parameters will be used

                              and what are performance requirements. For example if you can be sure that the date interval will only span 1-2 partitions then you can fix the optimal

                              plan using hints, outlines or profiles.

                               

                              Best regards,

                                Nikolay

                              • 12. Re: Bind Variable Peeking Not Working
                                Sven W.

                                Hi Christine,

                                 

                                the main problem is that the CBO will not use peeked values when doing ranged search. It does not depend on previous peeked values. It will simply not use it. That means comparison operations like >= or BETWEEN (as in your case) will usually get the wrong selectivity estimation by the CBO. That in turn leads to a wrong execution plan. If you use literals then the values will be considered and the CBO will make a good guess regarding cardinalities.

                                 

                                If you use bind variables the CBO will estimate 5% for a BETWEEN and some higher percentage for an >= comparison (I vaguely remember 10 or 15%).

                                If I remember correctly then Randolph Geist wrote an excellent article about this behaviour. Unfortunatly I didn't find it at the moment.

                                 

                                 

                                Possible solutions:

                                1) Use literals. This often means rewrite the query into dynamic SQL. Remember that you can use the quoted writing to avoid duplication all aposthrophes (e.g.: q['SQLstring containing ' ' )

                                 

                                2) I think that maybe dynamic sampleing would help to get better estimations. I'm far from sure about it, but you can try to enforce it with a hint /*+ dynamic_sampling(3) */

                                 

                                3) If you know the cardinality yourself you could enforce it with an hint /* cardinality (table alias, number of filtered rows) */

                                 

                                4) Rewrite BETWEEN into >= and <= (bad solution). This wont solve the real problem, but since the cardinalities will be different it might work out in your case.

                                 

                                5) Rewrite the BETWEEN comparison into an EQUAL comparison. Sometimes it is possible useing an FBI for that, or create partitions or subpartitions so that you can to a equal search instead of a ranged search. Bind peeking and histograms should work then.

                                 

                                Regards

                                Sven

                                 

                                Some update on FBI: An FBI will create a virtual hidden column on the table. You then need to collect statistics on the table for that column.

                                It is possible to do this with dbms_stats and the method_opt=> 'FOR ALL HIDDEN COLUMNS' without changing other statistics.

                                • 13. Re: Bind Variable Peeking Not Working
                                  Dom Brooks

                                  How about a local index on (BANK_ACCOUNT_NUMBER, BANK_ROUTING_NUMBER)?

                                  • 14. Re: Bind Variable Peeking Not Working
                                    Mohamed Houri

                                    Dominic,

                                     

                                    Your suggestion of local non prefixed index seems interesting. I have tested it

                                     

                                    create table billing_trans_log

                                         (billing_trans_id     number

                                         ,orig_process_date    date

                                         ,bank_routing_number  number

                                         ,bank_account_number  number

                                         )

                                    PARTITION BY RANGE (orig_process_date)

                                    (

                                    PARTITION P_20121201 VALUES LESS THAN (TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,

                                    PARTITION P_20121202 VALUES LESS THAN (TO_DATE(' 2012-12-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,

                                    PARTITION P_20121203 VALUES LESS THAN (TO_DATE(' 2012-12-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,

                                    PARTITION P_20121204 VALUES LESS THAN (TO_DATE(' 2012-12-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 

                                    );

                                     

                                    alter table billing_trans_log add constraint PK_BILLING_TRANS_LOG primary key (billing_trans_id);


                                    create table billing_trans_log_alloc (billing_trans_id number,total_amount number);

                                    create index IDX_BILLING_TRANS_LOG_ALLOC on billing_trans_log_alloc(billing_trans_id);

                                     

                                    SELECT btl.billing_trans_id

                                    FROM billing_trans_log btl

                                      , billing_trans_log_alloc btla

                                    WHERE

                                    1. btl.billing_trans_id = btla.billing_trans_id

                                    AND btl.bank_routing_number = '123456789'

                                    AND btl.bank_account_number = '222222222'

                                    AND btl.orig_process_date BETWEEN to_date('01122012','ddmmyyyy') AND to_date('02122012','ddmmyyyy')

                                    AND btla.total_amount = 214.5;

                                     

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

                                    | Id  | Operation                    | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

                                    |   0 | SELECT STATEMENT             |                             |       |       |     2 (100)|          |       |       |

                                    |   1 |  NESTED LOOPS                |                             |       |       |            |          |       |       |

                                    |   2 |   NESTED LOOPS               |                             |     1 |    74 |     2   (0)| 00:00:01 |       |       |

                                    |   3 |    PARTITION RANGE ITERATOR  |                             |     1 |    48 |     2   (0)| 00:00:01 |     2 |     3 |

                                    |*  4 |     TABLE ACCESS FULL        | BILLING_TRANS_LOG           |     1 |    48 |     2   (0)| 00:00:01 |     2 |     3 |

                                    |*  5 |    INDEX RANGE SCAN          | IDX_BILLING_TRANS_LOG_ALLOC |     1 |       |     0   (0)|          |       |       |

                                    |*  6 |   TABLE ACCESS BY INDEX ROWID| BILLING_TRANS_LOG_ALLOC     |     1 |    26 |     0   (0)|          |       |       |

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

                                     

                                    Predicate Information (identified by operation id):

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

                                       4 - filter(("BTL"."BANK_ROUTING_NUMBER"=123456789 AND "BTL"."BANK_ACCOUNT_NUMBER"=222222222 AND

                                                  "BTL"."ORIG_PROCESS_DATE"<=TO_DATE(' 2012-12-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

                                       5 - access("BTL"."BILLING_TRANS_ID"="BTLA"."BILLING_TRANS_ID")

                                       6 - filter("BTLA"."TOTAL_AMOUNT"=214.5)

                                     

                                    Lets create the suggested local non prefixed index and re-execute the same query again

                                     

                                    create index local_non_prefixed_mho_ind on billing_trans_log (bank_routing_number,bank_account_number) local;

                                     

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

                                    | Id  | Operation                            | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

                                    |   0 | SELECT STATEMENT                     |                             |       |       |     1 (100)|          |       |       |

                                    |   1 |  NESTED LOOPS                        |                             |       |       |            |          |       |       |

                                    |   2 |   NESTED LOOPS                       |                             |     1 |    74 |     1   (0)| 00:00:01 |       |       |

                                    |   3 |    PARTITION RANGE ITERATOR          |                             |     1 |    48 |     1   (0)| 00:00:01 |     2 |     3 |

                                    |*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| BILLING_TRANS_LOG           |     1 |    48 |     1   (0)| 00:00:01 |     2 |     3 |

                                    |*  5 |      INDEX RANGE SCAN                | LOCAL_NON_PREFIXED_MHO_IND  |     1 |       |     1   (0)| 00:00:01 |     2 |     3 |

                                    |*  6 |    INDEX RANGE SCAN                  | IDX_BILLING_TRANS_LOG_ALLOC |     1 |       |     0   (0)|          |       |       |

                                    |*  7 |   TABLE ACCESS BY INDEX ROWID        | BILLING_TRANS_LOG_ALLOC     |     1 |    26 |     0   (0)|          |       |       |

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

                                     

                                    Predicate Information (identified by operation id):

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

                                       4 - filter("BTL"."ORIG_PROCESS_DATE"<=TO_DATE(' 2012-12-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

                                       5 - access("BTL"."BANK_ROUTING_NUMBER"=123456789 AND "BTL"."BANK_ACCOUNT_NUMBER"=222222222)

                                       6 - access("BTL"."BILLING_TRANS_ID"="BTLA"."BILLING_TRANS_ID")

                                       7 - filter("BTLA"."TOTAL_AMOUNT"=214.5)

                                     

                                     

                                    It seems to be a  better plan.


                                    The local non prefixed index is suitable here because the query itself is doing a partition pruning (see this article for details about the attention one should make when creating a local non prefixed index).

                                     

                                    I pushed a little bit the test further and replaced the local non prefixed index by a local prefixed one and re-executed the query again

                                     

                                    drop index local_non_prefixed_mho_ind;


                                    create index local_prefixed_mho_ind on billing_trans_log

                                       (orig_process_date, bank_routing_number,bank_account_number) local;

                                     

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

                                    | Id  | Operation                            | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

                                    |   0 | SELECT STATEMENT                     |                             |     1 |    74 |     0   (0)| 00:00:01 |       |       |

                                    |   1 |  NESTED LOOPS                        |                             |       |       |            |          |       |       |

                                    |   2 |   NESTED LOOPS                       |                             |     1 |    74 |     0   (0)| 00:00:01 |       |       |

                                    |   3 |    PARTITION RANGE ITERATOR          |                             |     1 |    48 |     0   (0)| 00:00:01 |     2 |     3 |

                                    |   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| BILLING_TRANS_LOG           |     1 |    48 |     0   (0)| 00:00:01 |     2 |     3 |

                                    |*  5 |      INDEX RANGE SCAN                | LOCAL_PREFIXED_MHO_IND      |     1 |       |     0   (0)| 00:00:01 |     2 |     3 |

                                    |*  6 |    INDEX RANGE SCAN                  | IDX_BILLING_TRANS_LOG_ALLOC |     1 |       |     0   (0)| 00:00:01 |       |       |

                                    |*  7 |   TABLE ACCESS BY INDEX ROWID        | BILLING_TRANS_LOG_ALLOC     |     1 |    26 |     0   (0)| 00:00:01 |       |       |

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

                                     

                                    Predicate Information (identified by operation id):

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

                                       5 - access("BTL"."ORIG_PROCESS_DATE">=TO_DATE(' 2012-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

                                                  "BTL"."BANK_ROUTING_NUMBER"=123456789 AND "BTL"."BANK_ACCOUNT_NUMBER"=222222222 AND "BTL"."ORIG_PROCESS_DATE"<=TO_DATE('

                                                  2012-12-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

                                           filter("BTL"."BANK_ROUTING_NUMBER"=123456789 AND "BTL"."BANK_ACCOUNT_NUMBER"=222222222)

                                       6 - access("BTL"."BILLING_TRANS_ID"="BTLA"."BILLING_TRANS_ID")

                                       7 - filter("BTLA"."TOTAL_AMOUNT"=214.5)

                                     

                                    What I gained here is that there is no filter on the table BILLING_TRANS_LOG. The index became very precise having only an access predicate  

                                     

                                    Of course the test should be conducted under meaningful data volume to have an exact feeling about the new indexes

                                     

                                    Best regards

                                    Mohamed Houri

                                    1 2 Previous Next