1 2 Previous Next 19 Replies Latest reply: Jan 8, 2010 3:56 AM by Randolf Geist RSS

    How does the CBO work with Partition tables and dynamic sampling ?

    705249
      Hi all,

      I would like to know how does the CBO work with Partition tables and dynamic sampling (level 2).
      For example, I have a table T1 which is partitonned on C1 column. If I issue the query below:
      SELECT * FROM T1 where C1=1;

      What I'd want to know is if the CBO will retrieve 64 sample block at the partition level or over all the table ?
      Does it change anything if I use bind variables ?

      thanks for your help
        • 1. Re: How does the CBO work with Partition tables and dynamic sampling ?
          LKBrwn_DBA
          The CBO will retrieve at the partition level depending on examining the values of the bind variables (bind variable look-ahead or "peeking")
          :p
          • 2. Re: How does the CBO work with Partition tables and dynamic sampling ?
            705249
            Thanks fo your reply.

            So if I execute the query select * from T1 where C1=:x;

            If the first time the value of the bind variable is 1 => the CBO will retrieve blocks at partition 1.
            But if the second time the value of the bind variable is 2 => Does it mean the CBO will still retrieve at partition 1 instead of partition 2?
            • 3. Re: How does the CBO work with Partition tables and dynamic sampling ?
              729338
              Hi,

              Everytime you will execute a query , it will do the bind value peek and the perform the appropriate partition pruning to select the records from relevent partition. So next time with value 2, it will go to partition 2 if that is the way partitioning has been done.

              HTH
              • 4. Re: How does the CBO work with Partition tables and dynamic sampling ?
                705249
                but I thought that Bind variable peeking allows the optimizer to take the value of a bind variable into account only when first generating an execution plan for a SQL statement So in my case if I run the second query, as the plan is already in memory the CBO should take into account this plan. Am I wrong?
                • 5. Re: How does the CBO work with Partition tables and dynamic sampling ?
                  Dom Brooks
                  With optiimisation, binds and partitioning, there are a couple of different things at work here.
                  Firstly is bind variable peeking at hard parse time to determine the best plan.
                  Then, with an appropriate plan for partitioned access, what you will often then find is a plan accessing a particular partition according to the value supplied at run time, and in this instance your plan will show access to the partition with pstart/pstop of "key" - hence one plan works for different run-time binds.


                  In terms of what affect partitioning has on dynamic sampling, then it's an interesting question, worthy of further investigation.
                  Dynamic sampling level 2 says apply dynamic sampling to any unanalysed tables.
                  Asif Momen has a post on the combination of sampling and partitioning here
                  Also, Randolf Geist here
                  • 6. Re: How does the CBO work with Partition tables and dynamic sampling ?
                    705249
                    Ok but Dynamic sampling occurs during parsing before the execution of the query. And when the second query is issued, the bind value peek will not be done because only a soft parsed is performed at this time. So how does the CBO know that dynamic sampling must be performed on partition 2 ?
                    • 7. Re: How does the CBO work with Partition tables and dynamic sampling ?
                      Randolf Geist
                      Farenheiit wrote:
                      Ok but Dynamic sampling occurs during parsing before the execution of the query. And when the second query is issued, the bind value peek will not be done because only a soft parsed is performed at this time. So how does the CBO know that dynamic sampling must be performed on partition 2 ?
                      The CBO does not know that dynamic sampling must be performed on partition 2, since you have correctly identified that this will only be a soft parse. So in case of a soft parse no dynamic sampling will be performed at all.

                      You need to distinguish between the optimization phase and what happens at actual execution time.

                      The runtime execution of the query will use the bind variable value passed and if a suitable execution plan was generated at parse time this bind value will be used for partition pruning, but not necessarily (if the plan doesn't use partition pruning).

                      The execution plan will be shared and re-used, based on the estimates which might be based on the results of the dynamic sampling performed at optimization time.

                      At optimization time the optimizer peeks at the bind variable values and potentially performs dynamic sampling based on these values.

                      This means that the execution plan that gets shared potentially is not suitable for executions against different partitions, if these partitions differ significantly in size and data distribution.

                      This is a general issue when working with partitioning and bind variable values on partition keys - execution plans might get shared that potentially should not be shared. Using literals or other purposes of forcing a re-optimization of the query might lead to better results in such cases.

                      I'm not entirely sure if the adaptive cursor sharing of 11g covers this scenario since I haven't tested it yet explicitly, but it's very likely that it does.

                      Regards,
                      Randolf

                      Oracle related stuff blog:
                      http://oracle-randolf.blogspot.com/

                      Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
                      http://www.apress.com/book/view/1430226684
                      http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
                      • 8. Re: How does the CBO work with Partition tables and dynamic sampling ?
                        Randolf Geist
                        DomBrooks wrote:
                        Asif Momen has a post on the combination of sampling and partitioning here
                        Also, Randolf Geist here
                        It's interesting to note that these two blog posts come to obviously contradictory results - I've taken this opportunity to update my blog post to explain why - both test cases are correct, but there is a significant difference which I've outlined in my update.

                        As always there is no simple answer to this question - it depends on a lot of conditions.

                        Regards,
                        Randolf

                        Oracle related stuff blog:
                        http://oracle-randolf.blogspot.com/

                        Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
                        http://www.apress.com/book/view/1430226684
                        http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
                        • 9. Re: How does the CBO work with Partition tables and dynamic sampling ?
                          Dom Brooks
                          Thanks for that Randolf, nice follow-up.

                          Farenheit,

                          If you're asking how, once dynamic sampling needs to be done, it interacts with partitioning then it doesn't do anything specific because of partitioning.

                          In other words, these recursive queries from dynamic sampling don't do something special because there is partitioning but they may themselves may only access a single partition.

                          This is probably best illustrated by the dynamic sampling queries themselves.

                          Taking the range partition test case table from Randolf's post (hope that's ok):
                          SQL> -- Range partitioning testcase
                          SQL> CREATE TABLE wr_test
                          2 ( test_id
                          3 , trade_date
                          4 , CONSTRAINT test_pk PRIMARY KEY (trade_date, test_id) using index local)
                          5 PARTITION BY RANGE (trade_date)
                          6 ( PARTITION p_jan VALUES LESS THAN (DATE '2009-02-01')
                          7 , PARTITION p_feb VALUES LESS THAN (DATE '2009-03-01')
                          8 , PARTITION p_mar VALUES LESS THAN (DATE '2009-04-01') )
                          9 AS
                          10 SELECT ROWNUM AS test_id
                          11 , DATE '2009-02-01' + trunc(dbms_random.value(0, 59)) as trade_date
                          12 FROM dual
                          13 connect by level <= 1000;
                          and then running a 10046 trace before running the same sql in the first example in the same article:
                          SQL> alter session set events '10046 trace name context forever, level 12';
                          
                          Session altered.
                          
                          SQL> explain plan for
                            2  select * from wr_test where trade_date = date '2009-03-01';
                          
                          Explained.
                          Pumping the 10046 trace file through tkprof for ease of reading, you will find the following queries doing the dynamic sampling (this taken from my 10.2.0.4 db):
                          ********************************************************************************
                          
                          SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
                            NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
                            NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0)
                          FROM
                           (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("WR_TEST") FULL("WR_TEST")
                            NO_PARALLEL_INDEX("WR_TEST") */ 1 AS C1, CASE WHEN "WR_TEST"."TRADE_DATE"=
                            TO_DATE(' 2009-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') THEN 1 ELSE 0 END
                            AS C2 FROM "WR_TEST" "WR_TEST" WHERE "WR_TEST"."TRADE_DATE"=TO_DATE('
                            2009-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) SAMPLESUB
                          
                          
                          call     count       cpu    elapsed       disk      query    current        rows
                          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                          Parse        1      0.00       0.00          0          0          0           0
                          Execute      1      0.00       0.00          0          0          0           0
                          Fetch        1      0.00       0.00          0          4          0           1
                          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                          total        3      0.00       0.00          0          4          0           1
                          
                          Misses in library cache during parse: 1
                          Optimizer mode: ALL_ROWS
                          Parsing user id: SYS   (recursive depth: 1)
                          
                          Rows     Row Source Operation
                          -------  ---------------------------------------------------
                                1  SORT AGGREGATE (cr=4 pr=0 pw=0 time=503 us)
                              542   PARTITION RANGE SINGLE PARTITION: 3 3 (cr=4 pr=0 pw=0 time=610 us)
                              542    TABLE ACCESS FULL WR_TEST PARTITION: 3 3 (cr=4 pr=0 pw=0 time=64 us)
                          and
                          ********************************************************************************
                          
                          SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS opt_param('parallel_execution_enabled',
                             'false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE
                            */ NVL(SUM(C1),0), NVL(SUM(C2),0), NVL(SUM(C3),0)
                          FROM
                           (SELECT /*+ NO_PARALLEL("WR_TEST") INDEX("WR_TEST" TEST_PK)
                            NO_PARALLEL_INDEX("WR_TEST") */ 1 AS C1, 1 AS C2, 1 AS C3  FROM "WR_TEST"
                            "WR_TEST" WHERE "WR_TEST"."TRADE_DATE"=TO_DATE(' 2009-03-01 00:00:00',
                            'syyyy-mm-dd hh24:mi:ss') AND ROWNUM <= 2500) SAMPLESUB
                          
                          
                          call     count       cpu    elapsed       disk      query    current        rows
                          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                          Parse        1      0.00       0.00          0          0          0           0
                          Execute      1      0.00       0.00          0          0          0           0
                          Fetch        1      0.00       0.01          1          2          0           1
                          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                          total        3      0.00       0.01          1          2          0           1
                          
                          Misses in library cache during parse: 1
                          Optimizer mode: ALL_ROWS
                          Parsing user id: SYS   (recursive depth: 1)
                          
                          Rows     Row Source Operation
                          -------  ---------------------------------------------------
                                1  SORT AGGREGATE (cr=2 pr=1 pw=0 time=15881 us)
                               24   VIEW  (cr=2 pr=1 pw=0 time=15878 us)
                               24    COUNT STOPKEY (cr=2 pr=1 pw=0 time=15874 us)
                               24     PARTITION RANGE SINGLE PARTITION: 3 3 (cr=2 pr=1 pw=0 time=15849 us)
                               24      INDEX RANGE SCAN TEST_PK PARTITION: 3 3 (cr=2 pr=1 pw=0 time=15821 us)(object id 51602)
                          Follow the same steps for a non-partitioned WR_TEST table and you will see the same queries but obviously each with a different access.
                          • 10. Re: How does the CBO work with Partition tables and dynamic sampling ?
                            705249
                            Many thanks Randolf. That's exactly what I wanted to know.
                            Just one more question: When dynamic sampling is performed, how the sampling blocks are retrieved? i mean,are they retrieved randomly or does the CBO retrieve the first 64 blocks (if level2) ?
                            • 11. Re: How does the CBO work with Partition tables and dynamic sampling ?
                              Hemant K Chitale
                              The runtime execution of the query will use the bind variable value passed and if a suitable execution plan was generated at parse time this bind value will be used for partition pruning, but not necessarily (if the plan doesn't use partition pruning).
                              Just to clarify for my thick skull : Is this correct ?
                              1. At the first parse the Execution Plan will be based on the "peeked" Bind Variable
                              2. However, at the first and every subsequent execution, even when using the same Plan, Partition Pruning will kick in at the Execution Phase -- ie the actual Bind Value (varying at each execution) determines which Partition is scanned
                              (Of course, if the query predicate is the Partition Key !)

                              Thus, an execution plan based on a very small partition may be used against very large partitions (if the data is so skewed). Therefore, as always for data scans with skew, Literals are preferable over Bind Variables.


                              Hemant K Chitale
                              • 12. Re: How does the CBO work with Partition tables and dynamic sampling ?
                                MSK
                                Hi Hemant, Randolf:
                                Please correct my understanding as well (I guess my skull is n-times thicker :) )..

                                From what I understood , because of bind peeking, sometimes a bad plan can be choosen which would not do partition pruning (and scan the entire table) and if I extend this logic, this is not a problem with partitioning + bind variables but in general a side effect of bind peeking.

                                MSK
                                • 13. Re: How does the CBO work with Partition tables and dynamic sampling ?
                                  Randolf Geist
                                  Hemant K Chitale wrote:
                                  The runtime execution of the query will use the bind variable value passed and if a suitable execution plan was generated at parse time this bind value will be used for partition pruning, but not necessarily (if the plan doesn't use partition pruning).
                                  Just to clarify for my thick skull : Is this correct ?
                                  1. At the first parse the Execution Plan will be based on the "peeked" Bind Variable
                                  2. However, at the first and every subsequent execution, even when using the same Plan, Partition Pruning will kick in at the Execution Phase -- ie the actual Bind Value (varying at each execution) determines which Partition is scanned
                                  (Of course, if the query predicate is the Partition Key !)

                                  Thus, an execution plan based on a very small partition may be used against very large partitions (if the data is so skewed). Therefore, as always for data scans with skew, Literals are preferable over Bind Variables.
                                  Hemant,

                                  I'm not sure what you're exactly referring to with your question "Is this correct"?

                                  I think I tried to express exactly what you describe - I don't see the difference between your and mine description.

                                  May be you're unsure about this part and I see that my description leaves room for misinterpretation: "The runtime execution of the query will use the bind variable value passed... this bind value will be used for partition pruning".

                                  What I meant here is that the bind variable value passed at execution time will be used, regardless of the bind variable value used at optimization time. Therefore this corresponds to what you've outlined - including my comments that an execution plan might not be suitable for further executions based on different bind variable values and therefore pointing to different partitions with different size / data distribution etc.

                                  Regards,
                                  Randolf

                                  Oracle related stuff blog:
                                  http://oracle-randolf.blogspot.com/

                                  Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
                                  http://www.apress.com/book/view/1430226684
                                  http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
                                  • 14. Re: How does the CBO work with Partition tables and dynamic sampling ?
                                    Randolf Geist
                                    MSK wrote:
                                    Please correct my understanding as well (I guess my skull is n-times thicker :) )..

                                    From what I understood , because of bind peeking, sometimes a bad plan can be choosen which would not do partition pruning (and scan the entire table) and if I extend this logic, this is not a problem with partitioning + bind variables but in general a side effect of bind peeking.
                                    Bind variable peeking offers advantages and disadvantages: The advantage is that the optimizer can base its calculations on actual values rather than on hard coded defaults, the potential disadvantage is an execution plan that gets shared for further executions but is potentially unsuitable as outlined above.

                                    I think you're correct that this is not necessarily something that only applies to the special case bind variables on partition key + partitioning, but is a general potential issue with bind variable peeking - what we are describing here is a special case of that general issue.

                                    Regards,
                                    Randolf

                                    Oracle related stuff blog:
                                    http://oracle-randolf.blogspot.com/

                                    Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
                                    http://www.apress.com/book/view/1430226684
                                    http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
                                    1 2 Previous Next