14 Replies Latest reply: Jul 16, 2009 12:09 AM by 637538 RSS

    dynamic sampling and bind variables

    637538
      Hi,

      is dynamic_sampling used with bind variable ? why it's not getting the good cardinality with binds ?
      when using literals i can see that dynamic sampling occurs and get a good cardinality .
      when using binds (without stats on the tables) i can see that dynamic sampling occurs but the cradinality is the same as without dynamic sampling .

      is dynamic sampling not working good with binds ?

      Zvika
        • 1. Re: dynamic sampling and bind variables
          Hoek
          Hi Zvika,

          This might be interesting to you: http://www.oracle.com/technology/oramag/oracle/09-jan/o19asktom.html
          • 2. Re: dynamic sampling and bind variables
            637538
            10x for the reply.

            i already read it ( as always Tom explanations are amazing)

            but i still didn't get the answer for the binds .

            i get the same cardinality when using dynamic sampling and not using dynamic sampling.

            is dynamic sampling not works for binds ?
            • 3. Re: dynamic sampling and bind variables
              Hoek
              is dynamic sampling not works for binds ?
              Please post a test scenario, else we're (at least: I am ;) ) a on a guess.
              Some things make a difference, allowing for saying 'it depends':
              - Do you have an OLTP or a datawarehouse system?
              - On what base are statistics gathered?
              - Have you experimented with different levels of dynamic sampling?
              - Why in the first place you think you need the hint at all?
              - How is your data distributed, how are tables indexed?

              I have had one query which favored from using the hint, but only when cranking up the level to 7.
              That made the difference, it used a couple of bind variables, I remember...


              You might like this as well, by the way: http://jonathanlewis.wordpress.com/2009/05/06/philosophy-1/
              • 4. Re: dynamic sampling and bind variables
                William Robertson
                Dynamic sampling just means the optimizer actively samples the data when first parsing the query and generating the execution plan. From then on the standard behaviour applies, so dynamic sampling gives you no special advantage in handling bind variables.
                • 5. Re: dynamic sampling and bind variables
                  637538
                  10x

                  here is a complete test case that shows that the CBO is giving 5% cardinality if i use bind both for dynamic sampling and not.
                  17:38:23 TEST DS >set echo on time on timing on lines 190
                  17:38:23 TEST DS >
                  17:38:23 TEST DS >
                  17:38:23 TEST DS >exec dbms_stats.gather_table_stats('XXX','z1_oc');
                  
                  PL/SQL procedure successfully completed.
                  
                  17:38:24 TEST DS >
                  17:38:24 TEST DS >
                  17:38:24 TEST DS >var x varchar2(50)
                  17:38:24 TEST DS >
                  17:38:24 TEST DS >exec     :x := '4250107%'
                  
                  PL/SQL procedure successfully completed.
                  
                  Elapsed: 00:00:00.01
                  17:38:24 TEST DS >
                  17:38:24 TEST DS >print x
                  
                  X
                  --------------------------------------------------------------------------------------------------------------------------------
                  4250107%
                  
                  17:38:24 TEST DS >
                  17:38:24 TEST DS >select count(*) from z1_oc ;
                  
                    COUNT(*)
                  ----------
                        1000
                  
                  Elapsed: 00:00:00.00
                  17:38:24 TEST DS >
                  17:38:24 TEST DS >
                  17:38:24 TEST DS >select count(*) from z1_oc where IMSI  LIKE  :x ;
                  
                    COUNT(*)
                  ----------
                         476
                  
                  Elapsed: 00:00:00.01
                  17:38:24 TEST DS >
                  17:38:24 TEST DS >
                  17:38:24 TEST DS >select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE
                  17:38:24   2  from dba_tab_col_statistics
                  17:38:24   3  where owner = 'XXX'
                  17:38:24   4  AND TABLE_NAME = 'Z1_OC'
                  17:38:24   5  AND COLUMN_NAME = 'IMSI' ;
                  
                  TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT LOW_VALUE
                  ------------------------------ ------------------------------ ------------ ----------------------------------------------------------------
                  HIGH_VALUE                                                          DENSITY  NUM_NULLS NUM_BUCKETS LAST_ANALYZED       SAMPLE_SIZE
                  ---------------------------------------------------------------- ---------- ---------- ----------- ------------------- -----------
                  Z1_OC                          IMSI                                    648 343235303130313030313934373839
                  3939393939393939                                                  .00154321         12           1 12/07/2009 17:38:24         988
                  
                  
                  Elapsed: 00:00:00.01
                  17:38:24 TEST DS >
                  17:38:24 TEST DS >set autot traceonly
                  17:38:28 TEST DS >
                  17:38:28 TEST DS >prompt BIND WITH DYNAMIC SAMPLING LEVEL4
                  BIND WITH DYNAMIC SAMPLING LEVEL4
                  17:38:28 TEST DS >
                  17:38:28 TEST DS >select  /*+  dynamic_sampling (z1_oc 4) */ * from z1_oc
                  17:38:28   2  where IMSI  LIKE     :x ;
                  
                  476 rows selected.
                  
                  Elapsed: 00:00:00.19
                  
                  Execution Plan
                  ----------------------------------------------------------
                  Plan hash value: 4181598061
                  
                  -----------------------------------------------------------
                  | Id  | Operation         | Name  | Rows  | Bytes | Cost  |
                  -----------------------------------------------------------
                  |   0 | SELECT STATEMENT  |       |    49 | 23422 |     3 |
                  |*  1 |  TABLE ACCESS FULL| Z1_OC |    49 | 23422 |     3 |
                  -----------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     1 - filter("IMSI" LIKE :X)
                  
                  Note
                  -----
                     - cpu costing is off (consider enabling it)
                  
                  
                  17:38:28 TEST DS >
                  17:38:28 TEST DS >
                  17:38:28 TEST DS >prompt BIND AND NO DS
                  BIND AND NO DS
                  17:38:28 TEST DS >
                  17:38:28 TEST DS >select  * from z1_oc
                  17:38:28   2  where IMSI  LIKE     :x ;
                  
                  476 rows selected.
                  
                  Elapsed: 00:00:00.19
                  
                  Execution Plan
                  ----------------------------------------------------------
                  Plan hash value: 4181598061
                  
                  -----------------------------------------------------------
                  | Id  | Operation         | Name  | Rows  | Bytes | Cost  |
                  -----------------------------------------------------------
                  |   0 | SELECT STATEMENT  |       |    49 | 23422 |     3 |
                  |*  1 |  TABLE ACCESS FULL| Z1_OC |    49 | 23422 |     3 |
                  -----------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     1 - filter("IMSI" LIKE :X)
                  
                  Note
                  -----
                     - cpu costing is off (consider enabling it)
                  
                  
                  17:38:28 TEST DS >
                  17:38:28 TEST DS >prompt LITERAL WITH DYNAMIC SAMPLING
                  LITERAL WITH DYNAMIC SAMPLING
                  17:38:28 TEST DS >
                  17:38:28 TEST DS >select /*+       dynamic_sampling (z1_oc 4) */ *  from z1_oc
                  17:38:28   2  where IMSI  LIKE     '4250107%';
                  
                  476 rows selected.
                  
                  Elapsed: 00:00:00.17
                  
                  Execution Plan
                  ----------------------------------------------------------
                  Plan hash value: 4181598061
                  
                  -----------------------------------------------------------
                  | Id  | Operation         | Name  | Rows  | Bytes | Cost  |
                  -----------------------------------------------------------
                  |   0 | SELECT STATEMENT  |       |   476 |   222K|     3 |
                  |*  1 |  TABLE ACCESS FULL| Z1_OC |   476 |   222K|     3 |
                  -----------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     1 - filter("IMSI" LIKE '4250107%')
                  
                  Note
                  -----
                     - cpu costing is off (consider enabling it)
                     - dynamic sampling used for this statement
                  
                  
                  17:38:29 TEST DS >
                  17:38:29 TEST DS >prompt LITERAL  AND NO DS
                  LITERAL  AND NO DS
                  17:38:29 TEST DS >
                  17:38:29 TEST DS >select  *  from z1_oc
                  17:38:29   2  where IMSI  LIKE     '4250107%';
                  
                  476 rows selected.
                  
                  Elapsed: 00:00:00.16
                  
                  Execution Plan
                  ----------------------------------------------------------
                  Plan hash value: 4181598061
                  
                  -----------------------------------------------------------
                  | Id  | Operation         | Name  | Rows  | Bytes | Cost  |
                  -----------------------------------------------------------
                  |   0 | SELECT STATEMENT  |       |     2 |   956 |     3 |
                  |*  1 |  TABLE ACCESS FULL| Z1_OC |     2 |   956 |     3 |
                  -----------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     1 - filter("IMSI" LIKE '4250107%')
                  
                  Note
                  -----
                     - cpu costing is off (consider enabling it)
                  as you can see
                  with binds the CBO is using 5% estimate.
                  with literals the CBO using dynamic sampling to get the correct cardinality
                  and without DS it getting it by 1/num_distinct.

                  so from what i see here the CBO is not able to use dynamic sampling when using binds .
                  it my conclusion right ?

                  Edited by: Zvika on Jul 12, 2009 5:51 PM
                  • 6. Re: dynamic sampling and bind variables
                    637538
                    can someone elaborate on this issue ?
                    • 7. Re: dynamic sampling and bind variables
                      Hoek
                      Hi,

                      You might be facing bind variable peeking here...

                      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:40230704959128#40286217088705


                      Besides that:

                      "
                      So, where would dynamic sampling be useful? First and foremost, dynamic sampling is useful when you are accessing any table that has been created and loaded but not yet analyzed. Starting in Oracle Database 10g Release 1, the CBO is the only supported optimizer, and it needs accurate statistics to perform its job correctly. If a table exists that hasn’t had statistics gathered yet, the optimizer will be flying blind. Dynamic sampling gives the CBO the information it needs in order to operate correctly.

                      The second use for dynamic sampling is with global temporary tables. Often global temporary tables do not have statistics, and dynamic sampling can provide the optimizer with information about these tables. Your application would load the global temporary table, and the first hard parse of any query that utilized the temporary table would dynamically sample it to ascertain the correct size of the temporary table.
                      "

                      I don't understand why you're using dynamic_sampling here?
                      • 8. Re: dynamic sampling and bind variables
                        William Robertson
                        so from what i see here the CBO is not able to use dynamic sampling when using binds .
                        it my conclusion right ?
                        Dynamic sampling uses runtime sampling of the tables used in the query to produce a better execution plan, at parse time. If you later re-execute that query three times with different bind variable values, then provided that query is still cached, you will still get the same plan (prior to 11g that is). I'm not sure what you're expecting it to differently.

                        Can you elaborate on why you think dynamic sampling should give you any advantage here?
                        • 9. Re: dynamic sampling and bind variables
                          637538
                          the CBO need to peek at the binds when doing hard parse and the use dynamic sampling if i give the hint .
                          from tom kyte article
                          http://www.oracle.com/technology/oramag/oracle/09-jan/o19asktom.html
                          i created the table with flag1 and flag 2 columns
                          and instead of using literals i used binds with dynamic_sampling hint (level 4).
                          the CBO still get the wrong cardinality and not using dynamic sampling :
                          08:17:27 SQL> 
                          08:17:27 SQL> drop table z1_t;
                          
                          Table dropped.
                          
                          Elapsed: 00:01:22.29
                          08:18:50 SQL> 
                          08:18:50 SQL> create table z1_t
                          08:18:50   2         as select decode( mod(rownum,2), 0, 'N', 'Y' ) flag1,
                          08:18:50   3                  decode( mod(rownum,2), 0, 'Y', 'N' ) flag2, a.*
                          08:18:50   4             from all_objects a
                          08:18:50   5             where rownum <= 10000 ;
                          
                          Table created.
                          
                          Elapsed: 00:00:03.01
                          08:18:53 SQL> 
                          08:18:53 SQL> create index z1_t_idx on z1_t(flag1,flag2);
                          
                          Index created.
                          
                          Elapsed: 00:00:00.32
                          08:18:53 SQL> 
                          08:18:53 SQL> 
                          08:18:53 SQL> exec  dbms_stats.gather_table_stats( user, 'z1_T',method_opt=>'for all columns size 254' );
                          
                          PL/SQL procedure successfully completed.
                          
                          Elapsed: 00:00:03.53
                          08:18:57 SQL> 
                          08:18:57 SQL> select num_rows, num_rows/2,
                          08:18:57   2  num_rows/2/2
                          08:18:57   3  from user_tables
                          08:18:57   4  where table_name = 'Z1_T';
                          
                            NUM_ROWS NUM_ROWS/2 NUM_ROWS/2/2                                                                                                                                                            
                          ---------- ---------- ------------                                                                                                                                                            
                               10000       5000         2500                                                                                                                                                            
                          
                          Elapsed: 00:00:00.06
                          08:18:57 SQL> 
                          08:18:57 SQL> 
                          08:18:57 SQL> select  count(*) from z1_t where flag1 = 'Y' ;
                          
                            COUNT(*)                                                                                                                                                                                    
                          ----------                                                                                                                                                                                    
                                5000                                                                                                                                                                                    
                          
                          Elapsed: 00:00:00.01
                          08:18:57 SQL> 
                          08:18:57 SQL> select  count(*) from z1_t where flag2 = 'Y' ;
                          
                            COUNT(*)                                                                                                                                                                                    
                          ----------                                                                                                                                                                                    
                                5000                                                                                                                                                                                    
                          
                          Elapsed: 00:00:00.01
                          08:18:57 SQL> 
                          08:18:57 SQL> select  count(*)     from z1_t where flag1 = 'Y' and  flag2='Y' ;
                          
                            COUNT(*)                                                                                                                                                                                    
                          ----------                                                                                                                                                                                    
                                   0                                                                                                                                                                                    
                          
                          Elapsed: 00:00:00.01
                          08:18:57 SQL> 
                          08:18:57 SQL> 
                          08:18:57 SQL> var x varchar2(1)
                          08:18:57 SQL> var y varchar2(1)
                          08:18:57 SQL> 
                          08:18:57 SQL> 
                          08:18:57 SQL> exec :x:='Y'
                          
                          PL/SQL procedure successfully completed.
                          
                          Elapsed: 00:00:00.00
                          08:18:57 SQL> exec :x:='Y'
                          
                          PL/SQL procedure successfully completed.
                          
                          Elapsed: 00:00:00.00
                          08:18:57 SQL> 
                          08:18:57 SQL> set autot on explain
                          08:18:57 SQL> 
                          08:18:57 SQL> -- no dynamic sampling ==> NOT GOOD
                          08:18:57 SQL> 
                          08:18:57 SQL> select /*+ dynamic_sampling (z1_t 4) */ *  from z1_t where flag1 = 'Y' and  flag2='Y' ;
                          
                          no rows selected
                          
                          Elapsed: 00:00:00.03
                          
                          Execution Plan
                          ----------------------------------------------------------                                                                                                                                    
                          Plan hash value: 3338844109                                                                                                                                                                   
                                                                                                                                                                                                                        
                          ------------------------------------------------------------------------                                                                                                                      
                          | Id  | Operation                   | Name     | Rows  | Bytes | Cost  |                                                                                                                      
                          ------------------------------------------------------------------------                                                                                                                      
                          |   0 | SELECT STATEMENT            |          |     1 |    90 |     1 |                                                                                                                      
                          |   1 |  TABLE ACCESS BY INDEX ROWID| Z1_T     |     1 |    90 |     1 |                                                                                                                      
                          |*  2 |   INDEX RANGE SCAN          | Z1_T_IDX |     1 |       |     1 |                                                                                                                      
                          ------------------------------------------------------------------------                                                                                                                      
                                                                                                                                                                                                                        
                          Predicate Information (identified by operation id):                                                                                                                                           
                          ---------------------------------------------------                                                                                                                                           
                                                                                                                                                                                                                        
                             2 - access("FLAG1"='Y' AND "FLAG2"='Y')                                                                                                                                                    
                                                                                                                                                                                                                        
                          Note                                                                                                                                                                                          
                          -----                                                                                                                                                                                         
                             - cpu costing is off (consider enabling it)                                                                                                                                                
                             - dynamic sampling used for this statement                                                                                                                                                 
                          
                          08:18:57 SQL> 
                          08:18:57 SQL> -- no binds ==> GOOD
                          08:18:57 SQL> 
                          08:18:57 SQL> 
                          08:18:57 SQL> select /*+ dynamic_sampling (z1_t 4) */ *  from z1_t where flag1 = 'Y' and  flag2='Y' ;
                          
                          no rows selected
                          
                          Elapsed: 00:00:00.01
                          
                          Execution Plan
                          ----------------------------------------------------------                                                                                                                                    
                          Plan hash value: 3338844109                                                                                                                                                                   
                                                                                                                                                                                                                        
                          ------------------------------------------------------------------------                                                                                                                      
                          | Id  | Operation                   | Name     | Rows  | Bytes | Cost  |                                                                                                                      
                          ------------------------------------------------------------------------                                                                                                                      
                          |   0 | SELECT STATEMENT            |          |     1 |    90 |     1 |                                                                                                                      
                          |   1 |  TABLE ACCESS BY INDEX ROWID| Z1_T     |     1 |    90 |     1 |                                                                                                                      
                          |*  2 |   INDEX RANGE SCAN          | Z1_T_IDX |     1 |       |     1 |                                                                                                                      
                          ------------------------------------------------------------------------                                                                                                                      
                                                                                                                                                                                                                        
                          Predicate Information (identified by operation id):                                                                                                                                           
                          ---------------------------------------------------                                                                                                                                           
                                                                                                                                                                                                                        
                             2 - access("FLAG1"='Y' AND "FLAG2"='Y')                                                                                                                                                    
                                                                                                                                                                                                                        
                          Note                                                                                                                                                                                          
                          -----                                                                                                                                                                                         
                             - cpu costing is off (consider enabling it)                                                                                                                                                
                             - dynamic sampling used for this statement                                                                                                                                                 
                          
                          08:18:57 SQL> 
                          08:18:57 SQL> -- Binds and DS But NOT GOOD
                          08:18:57 SQL> 
                          08:18:57 SQL> select /*+ this is hard parse dynamic_sampling (z1_t 4) */ *  from z1_t where flag1 = :x and  flag2=:x ;
                          
                          no rows selected
                          
                          Elapsed: 00:00:00.02
                          
                          Execution Plan
                          ----------------------------------------------------------                                                                                                                                    
                          Plan hash value: 2241769311                                                                                                                                                                   
                                                                                                                                                                                                                        
                          ----------------------------------------------------------                                                                                                                                    
                          | Id  | Operation         | Name | Rows  | Bytes | Cost  |                                                                                                                                    
                          ----------------------------------------------------------                                                                                                                                    
                          |   0 | SELECT STATEMENT  |      |  2500 |   219K|     4 |                                                                                                                                    
                          |*  1 |  TABLE ACCESS FULL| Z1_T |  2500 |   219K|     4 |                                                                                                                                    
                          ----------------------------------------------------------                                                                                                                                    
                                                                                                                                                                                                                        
                          Predicate Information (identified by operation id):                                                                                                                                           
                          ---------------------------------------------------                                                                                                                                           
                                                                                                                                                                                                                        
                             1 - filter("FLAG1"=:X AND "FLAG2"=:X)                                                                                                                                                      
                                                                                                                                                                                                                        
                          Note                                                                                                                                                                                          
                          -----                                                                                                                                                                                         
                             - cpu costing is off (consider enabling it)                                                                                                                                                
                          
                          08:18:57 SQL> 
                          08:18:57 SQL> 
                          08:18:57 SQL> 
                          08:18:57 SQL> spool off
                          why in the last select its not using dynamic sampling ?
                          • 10. Re: dynamic sampling and bind variables
                            Hoek
                            Hi Zvika,
                            Why in the last select its not using dynamic sampling ?
                            Because you put extra text in your hint, I guess.

                            Try something like:
                            select /*+ dynamic_sampling(z1_t 4) */ 
                                   *
                            ,      'this is hard parse'            
                            from   z1_t 
                            where  flag1 = :x 
                            and    flag2 = :x;
                            Besides setting autotrace, you might want to trace your tests using:
                            SQL> alter session set events '10053 trace name context forever, level 1';
                            
                            -- your queries etc...
                            
                            SQL> alter session set events '10053 trace name context off';
                            See what you find in the .trc file, like and post results/questions here.

                            Or use this approach, another way to see more:
                            http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:963818400346843003
                            (scroll down to the "explain plan does not bind peek, explain plan optimizes for "?"-part")

                            Also: what is your DB-version?
                            (When I get the time later today I'll try to set up a similar testcase.)
                            • 11. Re: dynamic sampling and bind variables
                              637538
                              great :-)

                              you are right ( from Tom Kyte answer)
                              i didn't see the bind peeking and using the dynamic sampling cause i used autotrace .

                              when used sql_trace or
                              select * from table(dbms_xplan.display_cursor(null,null,'typical +peeked_binds'));


                              i can see it clearly .

                              10x a lot .

                              Zvika
                              • 12. Re: dynamic sampling and bind variables
                                Hoek
                                You're welcome.

                                I'll skip a testcase, since you've already figured it out ;) .
                                (You might want to mark usefull answers as well)
                                • 13. Re: dynamic sampling and bind variables
                                  William Robertson
                                  i didn't see the bind peeking and using the dynamic sampling cause i used autotrace .
                                  Even when the Autotrace execution plan said:
                                  <tt>Note
                                  {noformat}-----{noformat}
                                  - cpu costing is off (consider enabling it)
                                  - dynamic sampling used for this statement</tt>>

                                  I was a bit puzzled by your examples, because that plan followed a query that appeared to be headed <i>"<tt>08:18:57 SQL> -- no dynamic sampling ==> NOT GOOD</tt>"</i>. Perhaps that wasn't a heading. What was it all meant to prove? At first I thought you expected dynamic sampling to evaluate bind variables (which it does not), then it seemed to be about the presence of bind variables preventing dynamic sampling from taking place (which it does not).

                                  btw I'm guessing from the cpu costing reference that this is Oracle 9.2.0.x (which I don't have). The optimizer varies a lot between releases so it's worth mentioning your version.
                                  • 14. Re: dynamic sampling and bind variables
                                    637538
                                    Hi ,

                                    we are working on version 10.2.0.3 .

                                    i just wanted to know how dynamic sampling works with binds
                                    as we are on DWH envirnment.
                                    i was sure that it needs to work at hard parse but didn't see it in the explain plan .

                                    not until i get the link to asktom that showd me how to use the dbms_xplan with binds.


                                    Zvika