1 2 Previous Next 26 Replies Latest reply: Jan 27, 2010 7:09 AM by 637538 RSS

    cbo and sysdate wrong cardinality (bug ? )

    637538
      my system is :

      sun solaris 5.10 68 cpu 220 gb ram
      oracle 10.2.0.4

      we have 2 tables
      1. outgoing_calls - 1.7 tb partition daily every day 20M rows , about 4 GB compress and degree 12
      2. period_today - small table 104860 rows 32 mb

      i'm joining the 2 tables likes this:
      SELECT
       *
      FROM
        OUTGOING_CALLS,
        PERIOD_TODAY  PERIOD_OUTGOING
       WHERE
         ( PERIOD_OUTGOING.PERIOD_KEY=OUTGOING_CALLS.CALL_START_DATE
      AND PERIOD_OUTGOING.PERIOD_LEVEL = 10  )
        AND  (
          ( ( PERIOD_OUTGOING.PERIOD_KEY ) >=  trunc(next_day(sysdate,'SUNDAY')-14) 
         AND ( PERIOD_OUTGOING.PERIOD_KEY )<  trunc(next_day(sysdate,'SUNDAY')-7) 
        ))
      the cbo think it will get only 17K rows while in reality it will return about 149M rows from outgoing_calls
      i saw several interesting things :

      1. when using to_date with hard coded dates instead of sysdate the cbo get almost the right cardinality.
      2. when using sysdate on one day so the optimizer will get the partition stats
      ( i'm not sure about this because it says KEY at the explain partition section ) is gets almost the right cardinality.
      3. when looking at 10053 trace i see that the cbo is getting the right cardinality on the " Single Table Cardinality Estimation"
      but on the explain plan in the trace i see different result
      4. when moving the date filter to the big table but still keep the join, the cbo still get way off.
      5. when moving the date filter to the big table and removing the small table (period_today) from the query oracle get almost the right cardinality.


      here is the relevant parts of 10053 trace
        BASE STATISTICAL INFORMATION
      ***********************
      Table Stats::
        Table: PERIOD_TODAY  Alias: PERIOD_OUTGOING
          #Rows: 104860  #Blks:  740  AvgRowLen:  100.00
        Column (#1): PERIOD_KEY(DATE)
          AvgLen: 8.00 NDV: 104260 Nulls: 0 Density: 9.5914e-06 Min: 2450815 Max: 2455221
      ***********************
      Table Stats::
        Table: OUTGOING_CALLS  Alias: OUTGOING_CALLS  (Using composite stats)
        (making adjustments for partition skews)
        ORIGINAL VALUES::    #Rows: 7387968000  #Blks:  108008221  AvgRowLen:  479.00
        PARTITIONS::
        PRUNED: 393
        ANALYZED: 353  UNANALYZED: 40
          #Rows: 7387968000  #Blks:  108054193  AvgRowLen:  479.00
      Index Stats::
        Index: OUTGOING_CALLS$CONTRACTKEY  Col#: 4
          USING COMPOSITE STATS
          LVLS: 2  #LB: 8578638  #DK: 1186960  LB/K: 7.00  DB/K: 5539.00  CLUF: 6575037847.00
        Index: OUTGOING_CALLS$IMEI  Col#: 18
          USING COMPOSITE STATS
          LVLS: 2  #LB: 9739277  #DK: 1400096  LB/K: 6.00  DB/K: 3153.00  CLUF: 4414894870.00
        Index: OUTGOING_CALLS$RTX_ENT_DATE  Col#: 39
          USING COMPOSITE STATS
          LVLS: 2  #LB: 10126156  #DK: 2436637  LB/K: 4.00  DB/K: 2006.00  CLUF: 4888570227.00
      ***************************************
      SINGLE TABLE ACCESS PATH
        -----------------------------------------
        BEGIN Single Table Cardinality Estimation
        -----------------------------------------
      *** 2010-01-21 10:41:56.602
      ** Performing dynamic sampling initial checks. **
        Column (#1): CALL_START_DATE(DATE)
          AvgLen: 8.00 NDV: 8376 Nulls: 0 Density: 1.1939e-04 Min: 2454864 Max: 2455218
      ** Dynamic sampling initial checks returning FALSE.
        Table: OUTGOING_CALLS  Alias: OUTGOING_CALLS
          Card: Original: 7387968000  Rounded: 146971808  Computed: 146971807.60  Non Adjusted: 146971807.60
        -----------------------------------------
        END   Single Table Cardinality Estimation
      and the explain plan from the trace
      ============
      Plan Table
      ============
      ---------------------------------------------------+-----------------------------------+-------------------------+---------------+
      | Id  | Operation                  | Name          | Rows  | Bytes | Cost  | Time      |  TQ  |IN-OUT|PQ Distrib | Pstart| Pstop |
      ---------------------------------------------------+-----------------------------------+-------------------------+---------------+
      | 0   | SELECT STATEMENT           |               |       |       |  307K |           |      |      |           |       |       |
      | 1   |  PX COORDINATOR            |               |       |       |       |           |      |      |           |       |       |
      | 2   |   PX SEND QC (RANDOM)      | :TQ10001      | 2745K | 1552M |  307K |           |:Q1001| P->S |QC (RANDOM)|       |       |
      | 3   |    FILTER                  |               |       |       |       |           |:Q1001| PCWC |           |       |       |
      | 4   |     NESTED LOOPS           |               | 2745K | 1552M |  307K |           |:Q1001| PCWP |           |       |       |
      | 5   |      BUFFER SORT           |               |       |       |       |           |:Q1001| PCWC |           |       |       |
      | 6   |       PX RECEIVE           |               |       |       |       |           |:Q1001| PCWP |           |       |       |
      | 7   |        PX SEND BROADCAST   | :TQ10000      |       |       |       |           |:Q1000| P->P |BROADCAST  |       |       |
      | 8   |         PX BLOCK ITERATOR  |               |   160 |   16K |     6 |           |:Q1000| PCWC |           |       |       |
      | 9   |          TABLE ACCESS FULL | PERIOD_TODAY  |   160 |   16K |     6 |           |:Q1000| PCWP |           |       |       |
      | 10  |      PX BLOCK ITERATOR     |               |   17K | 8208K |   10K |           |:Q1001| PCWC |           | KEY   | KEY   |
      | 11  |       TABLE ACCESS FULL    | OUTGOING_CALLS|   17K | 8208K |   10K |           |:Q1001| PCWP |           | KEY   | KEY   |
      ---------------------------------------------------+-----------------------------------+-------------------------+---------------+
      Predicate Information:
      ----------------------
      1 - filter(TRUNC(NEXT_DAY(SYSDATE@!,1)-14)<TRUNC(NEXT_DAY(SYSDATE@!,1)-7))
      3 - filter(TRUNC(NEXT_DAY(SYSDATE@!,1)-14)<TRUNC(NEXT_DAY(SYSDATE@!,1)-7))
      9 - access(:Z>=:Z AND :Z<=:Z)
      9 - filter(("PERIOD_OUTGOING"."PERIOD_LEVEL"=10 AND "PERIOD_OUTGOING"."PERIOD_KEY">=TRUNC(NEXT_DAY(SYSDATE@!,1)-14) AND "PERIOD_OUTGOING"."PERIOD_KEY"<TRU
      NC(NEXT_DAY(SYSDATE@!,1)-7)))
      11 - access(:Z>=:Z AND :Z<=:Z)
      11 - filter(("PERIOD_OUTGOING"."PERIOD_KEY"="OUTGOING_CALLS"."CALL_START_DATE" AND "OUTGOING_CALLS"."CALL_START_DATE">=TRUNC(NEXT_DAY(SYSDATE@!,1)-14) AND
       "OUTGOING_CALLS"."CALL_START_DATE"<TRUNC(NEXT_DAY(SYSDATE@!,1)-7)))
      what is wrong here ?

      Zvika
        • 1. Re: cbo and sysdate wrong cardinality (bug ? )
          riedelme
          Zvika wrote:
          we have 2 tables
          1. outgoing_calls - 1.7 tb partition daily every day 20M rows , about 4 GB compress and degree 12
          2. period_today - small table 104860 rows 32 mb

          i'm joining the 2 tables likes this:
          SELECT
          *
          FROM
          OUTGOING_CALLS,
          PERIOD_TODAY  PERIOD_OUTGOING
          WHERE
          ( PERIOD_OUTGOING.PERIOD_KEY=OUTGOING_CALLS.CALL_START_DATE
          AND PERIOD_OUTGOING.PERIOD_LEVEL = 10  )
          AND  (
          ( ( PERIOD_OUTGOING.PERIOD_KEY ) >=  trunc(next_day(sysdate,'SUNDAY')-14) 
          AND ( PERIOD_OUTGOING.PERIOD_KEY )<  trunc(next_day(sysdate,'SUNDAY')-7) 
          ))
          the cbo think it will get only 17K rows while in reality it will return about 149M rows from outgoing_calls
          i saw several interesting things :

          1. when using to_date with hard coded dates instead of sysdate the cbo get almost the right cardinality.
          2. when using sysdate on one day so the optimizer will get the partition stats
          ( i'm not sure about this because it says KEY at the explain partition section ) is gets almost the right cardinality.
          3. when looking at 10053 trace i see that the cbo is getting the right cardinality on the " Single Table Cardinality Estimation"
          but on the explain plan in the trace i see different result
          4. when moving the date filter to the big table but still keep the join, the cbo still get way off.
          5. when moving the date filter to the big table and removing the small table (period_today) from the query oracle get almost the right cardinality.

          The more complex a query is the more likely the optimizer is to do something unexpected. Your query isn't very complex without the PQO. However, the PQO is a bit of an enigma all by itself. How is the cardinality reported if you turn the PQO off?

          Could the unnecessary parenthesis in the WHERE be confsuing the optimizer? Probably not, but I remember stories about similar things many years ago.

          Your observations about using SYSDATE are interesting. The precedence rules for predicate evaluation in the on-line documentation suggests that function may or may not have costs associated with them (functions with costs get higher execution precidence than functions without costs). If SYSDATE is one of the functions without cost a default that the optimizer could use a default cost. But, if that is the case, why does removing an unrelated table from the query allow the expected cardinality to be retained?

          What's the cardinality if you include a redundant date range comparison against the dates in both tables (assuming this would return the correct results if run)?
          • 2. Re: cbo and sysdate wrong cardinality (bug ? )
            user503699
            Two things look strange:
            1) EXPLAIN PLAN shows that "sysdate" (as mentioned in original query) is displayed as "sysdate@!". Not sure what is the reason (if any).
            2) EXPLAIN PLAN shows that optimizer is choosing NESTED LOOP to join two tables (but individual tables are accessed using full table scan). Using NESTED LOOP to fetch (estimated) 2745K rows does not look like best choice.
            In reality, how many rows do you expect this query to fetch ?

            Not sure if any of these discrepencies are due to parallel operations. I am not sure this query will benifit from parallel operation.
            What happens if you run the query using NOPARALLEL hint ?
            • 3. Re: cbo and sysdate wrong cardinality (bug ? )
              user503699
              Not sure if this is relevant but do you have RAC ? How many nodes ? Do all nodes have same time ?
              • 4. Re: cbo and sysdate wrong cardinality (bug ? )
                Nicolas.Gasparotto
                user503699 wrote:
                Two things look strange:
                1) EXPLAIN PLAN shows that "sysdate" (as mentioned in original query) is displayed as "sysdate@!". Not sure what is the reason (if any).
                This is how it works :
                SQL> explain plan for select 1 from dual where sysdate=sysdate;
                
                Explained.
                
                SQL> select * from table(dbms_xplan.display);
                Plan hash value: 731024556
                
                -----------------------------------------------------------------
                | Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
                -----------------------------------------------------------------
                |   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
                |*  1 |  FILTER          |      |       |            |          |
                |   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
                -----------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                
                   1 - filter(SYSDATE@!=SYSDATE@!)
                
                14 rows selected.
                SYSDATE is resolved internally like "SYSDATE@!" function.

                Nicolas.
                • 5. Re: cbo and sysdate wrong cardinality (bug ? )
                  user503699
                  Thanks Nicolas.
                  I had though it might be some "conversion" due to parallel operations.
                  • 6. Re: cbo and sysdate wrong cardinality (bug ? )
                    Timur Akhmadeev
                    Hi,

                    can you upload somewhere a complete 10053 trace?
                    • 7. Re: cbo and sysdate wrong cardinality (bug ? )
                      637538
                      the query will return about 149 millions rows.
                      it's a fact table and a time table .
                      so in theory i can just ignore the time table and put the filters on fact table .
                      but we don't want to do that . we built the time table for this kind of situation.
                      from 10053 trace we can see that the cbo is doing the transitive predicate good and get goos estimation of the number of rows it will get from the fact table .
                      but in the plan we don't see it .

                      i tested it without parallel and the same results.
                      i will have to use parallel since it's huge table .

                      when using redundant date range on the fact table and on the time table the explain is the same.



                      i'm not using RAC.

                      we are trying to workaround it by replacing the sysdate with to_date and hard coded dates wgich the cbo is getting good estimation of the cardinality.

                      it is a mystery using sysdate ...


                      thanks for helping
                      • 8. Re: cbo and sysdate wrong cardinality (bug ? )
                        Jonathan Lewis
                        Zvika wrote:
                        my system is :

                        sun solaris 5.10 68 cpu 220 gb ram
                        oracle 10.2.0.4

                        we have 2 tables
                        1. outgoing_calls - 1.7 tb partition daily every day 20M rows , about 4 GB compress and degree 12
                        2. period_today - small table 104860 rows 32 mb
                        BASE STATISTICAL INFORMATION
                        ***********************
                        Table Stats::
                        Table: PERIOD_TODAY  Alias: PERIOD_OUTGOING
                        #Rows: 104860  #Blks:  740  AvgRowLen:  100.00
                        Column (#1): PERIOD_KEY(DATE)
                        AvgLen: 8.00 NDV: 104260 Nulls: 0 Density: 9.5914e-06 Min: 2450815 Max: 2455221
                        ***********************
                        Table Stats::
                        Table: OUTGOING_CALLS  Alias: OUTGOING_CALLS  (Using composite stats)
                        (making adjustments for partition skews)
                        ORIGINAL VALUES::    #Rows: 7387968000  #Blks:  108008221  AvgRowLen:  479.00
                        PARTITIONS::
                        PRUNED: 393
                        ANALYZED: 353  UNANALYZED: 40
                        #Rows: 7387968000  #Blks:  108054193  AvgRowLen:  479.00
                        Index Stats::
                        Index: OUTGOING_CALLS$CONTRACTKEY  Col#: 4
                        USING COMPOSITE STATS
                        LVLS: 2  #LB: 8578638  #DK: 1186960  LB/K: 7.00  DB/K: 5539.00  CLUF: 6575037847.00
                        Index: OUTGOING_CALLS$IMEI  Col#: 18
                        USING COMPOSITE STATS
                        LVLS: 2  #LB: 9739277  #DK: 1400096  LB/K: 6.00  DB/K: 3153.00  CLUF: 4414894870.00
                        Index: OUTGOING_CALLS$RTX_ENT_DATE  Col#: 39
                        USING COMPOSITE STATS
                        LVLS: 2  #LB: 10126156  #DK: 2436637  LB/K: 4.00  DB/K: 2006.00  CLUF: 4888570227.00
                        ***************************************
                        SINGLE TABLE ACCESS PATH
                        -----------------------------------------
                        BEGIN Single Table Cardinality Estimation
                        -----------------------------------------
                        *** 2010-01-21 10:41:56.602
                        ** Performing dynamic sampling initial checks. **
                        Column (#1): CALL_START_DATE(DATE)
                        AvgLen: 8.00 NDV: 8376 Nulls: 0 Density: 1.1939e-04 Min: 2454864 Max: 2455218
                        ** Dynamic sampling initial checks returning FALSE.
                        Table: OUTGOING_CALLS  Alias: OUTGOING_CALLS
                        Card: Original: 7387968000  Rounded: 146971808  Computed: 146971807.60  Non Adjusted: 146971807.60
                        -----------------------------------------
                        END   Single Table Cardinality Estimation
                        
                        
                        ============
                        Plan Table
                        ============
                        ---------------------------------------------------+-----------------------------------+-------------------------+---------------+
                        | Id  | Operation                  | Name          | Rows  | Bytes | Cost  | Time      |  TQ  |IN-OUT|PQ Distrib | Pstart| Pstop |
                        ---------------------------------------------------+-----------------------------------+-------------------------+---------------+
                        | 0   | SELECT STATEMENT           |               |       |       |  307K |           |      |      |           |       |       |
                        | 1   |  PX COORDINATOR            |               |       |       |       |           |      |      |           |       |       |
                        | 2   |   PX SEND QC (RANDOM)      | :TQ10001      | 2745K | 1552M |  307K |           |:Q1001| P->S |QC (RANDOM)|       |       |
                        | 3   |    FILTER                  |               |       |       |       |           |:Q1001| PCWC |           |       |       |
                        | 4   |     NESTED LOOPS           |               | 2745K | 1552M |  307K |           |:Q1001| PCWP |           |       |       |
                        | 5   |      BUFFER SORT           |               |       |       |       |           |:Q1001| PCWC |           |       |       |
                        | 6   |       PX RECEIVE           |               |       |       |       |           |:Q1001| PCWP |           |       |       |
                        | 7   |        PX SEND BROADCAST   | :TQ10000      |       |       |       |           |:Q1000| P->P |BROADCAST  |       |       |
                        | 8   |         PX BLOCK ITERATOR  |               |   160 |   16K |     6 |           |:Q1000| PCWC |           |       |       |
                        | 9   |          TABLE ACCESS FULL | PERIOD_TODAY  |   160 |   16K |     6 |           |:Q1000| PCWP |           |       |       |
                        | 10  |      PX BLOCK ITERATOR     |               |   17K | 8208K |   10K |           |:Q1001| PCWC |           | KEY   | KEY   |
                        | 11  |       TABLE ACCESS FULL    | OUTGOING_CALLS|   17K | 8208K |   10K |           |:Q1001| PCWP |           | KEY   | KEY   |
                        ---------------------------------------------------+-----------------------------------+-------------------------+---------------+
                        Predicate Information:
                        ----------------------
                        1 - filter(TRUNC(NEXT_DAY(SYSDATE@!,1)-14)<TRUNC(NEXT_DAY(SYSDATE@!,1)-7))
                        3 - filter(TRUNC(NEXT_DAY(SYSDATE@!,1)-14)<TRUNC(NEXT_DAY(SYSDATE@!,1)-7))
                        9 - access(:Z>=:Z AND :Z<=:Z)
                        9 - filter(("PERIOD_OUTGOING"."PERIOD_LEVEL"=10 AND "PERIOD_OUTGOING"."PERIOD_KEY">=TRUNC(NEXT_DAY(SYSDATE@!,1)-14) AND "PERIOD_OUTGOING"."PERIOD_KEY"<TRU
                        NC(NEXT_DAY(SYSDATE@!,1)-7)))
                        11 - access(:Z>=:Z AND :Z<=:Z)
                        11 - filter(("PERIOD_OUTGOING"."PERIOD_KEY"="OUTGOING_CALLS"."CALL_START_DATE" AND "OUTGOING_CALLS"."CALL_START_DATE">=TRUNC(NEXT_DAY(SYSDATE@!,1)-14) AND
                        "OUTGOING_CALLS"."CALL_START_DATE"<TRUNC(NEXT_DAY(SYSDATE@!,1)-7)))
                        The single table access path shows 149M rows because in the absence of the join predicate you have a 7 day range out of about 350 days, and allowing for the skew Oracle has your 20M rows per day for about 140M rows.

                        But you're doing a nested loop join, so in the execution plan you also have the join predicate, which is call_start_date = {constant}, and there are 8376 distinct values for this column: 140M / 8376 = 16,700 ... which is your 17K.

                        The big problem is that Oracle can't see any correlation between your grouping column and the date range - so it's going to scan the same 7 partitions many times - for a total of 160 scans, when a correctly filtered hash join would scan the 7 once each.


                        Regards
                        Jonathan Lewis
                        http://jonathanlewis.wordpress.com
                        http://www.jlcomp.demon.co.uk

                        To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                        {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                        fixed format
                        .
                        
                        
                        "Science is more than a body of knowledge; it is a way of thinking" 
                        Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                        • 9. Re: cbo and sysdate wrong cardinality (bug ? )
                          user503699
                          Jonathan Lewis wrote:
                          The single table access path shows 149M rows because in the absence of the join predicate you have a 7 day range out of about 350 days, and allowing for the skew Oracle has your 20M rows per day for about 140M rows.

                          But you're doing a nested loop join, so in the execution plan you also have the join predicate, which is call_start_date = {constant}, and there are 8376 distinct values for this column: 140M / 8376 = 16,700 ... which is your 17K.

                          The big problem is that Oracle can't see any correlation between your grouping column and the date range - so it's going to scan the same 7 partitions many times - for a total of 160 scans, when a correctly filtered hash join would scan the 7 once each.
                          Jonathan,

                          I totally forgot about what gets displayed in "ROWS" column in plan output for NESTED LOOP join. Thanks.
                          But did not understand your last statement. What grouping column are you referring to ? I thought when oracle joins two tables, which are accessed using full table scan, it will always (??) choose hash join (unless explcitly hinted to use Nested Loop). Are there any scenarios (except bugs) when this is not the case ?
                          • 10. Re: cbo and sysdate wrong cardinality (bug ? )
                            Jonathan Lewis
                            user503699 wrote:

                            Jonathan,

                            I totally forgot about what gets displayed in "ROWS" column in plan output for NESTED LOOP join. Thanks.
                            But did not understand your last statement. What grouping column are you referring to ? I thought when oracle joins two tables, which are accessed using full table scan, it will always (??) choose hash join (unless explcitly hinted to use Nested Loop). Are there any scenarios (except bugs) when this is not the case ?
                            The "grouping" column was an error. I couldn't remember what the "PERIOD_LEVEL" column was called had intended to go back to change "grouping" to the correct name - then forgot.

                            One case where Oracle should do a nested loop with full tablescan on the second table is when there is only going to be one row found in the first table. Technically you can therefore argue that this plan is the correct plan. We are not doing a "full TABLE scan", we are doing full scans of some partitions - one partition for each row from the first table. Since this means scanning only 160 partitions out of 393, it is a good idea. The error is that we are only going to visit 7 different partitions: we know that, but the optimizer doesn't - and it's that difference that makes this plan a bad plan in this case.

                            Regards
                            Jonathan Lewis
                            http://jonathanlewis.wordpress.com
                            http://www.jlcomp.demon.co.uk

                            To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                            {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                            fixed format
                            .
                            
                            
                            "Science is more than a body of knowledge; it is a way of thinking" 
                            Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                            • 11. Re: cbo and sysdate wrong cardinality (bug ? )
                              637538
                              thanks a lot Jonathan,

                              1. how can i make oracle know the relation between the range dates and call_start_date ?(is oracle 11g solving this issue with corelate predicates ?)
                              dynamic sampling didn't help here.
                              and i have 10g instance.
                              2. why when i use to_date('some date','dd/mm/yyyy') instead of the trunc(sysdate) oracle is using hash join and get the good cardinality
                              • 12. Re: cbo and sysdate wrong cardinality (bug ? )
                                601262
                                Zvika wrote:
                                thanks a lot Jonathan,

                                1. how can i make oracle know the relation between the range dates and call_start_date ?(is oracle 11g solving this issue with corelate predicates ?)
                                dynamic sampling didn't help here.
                                and i have 10g instance.
                                2. why when i use to_date('some date','dd/mm/yyyy') instead of the trunc(sysdate) oracle is using hash join and get the good cardinality
                                The reason for #2 is that SYSDATE is not [constant foldable|http://en.wikipedia.org/wiki/Constant_folding] because it is evaluated at execution time, not parse time so you will never be able to get a non KEY pstart/pstop plan using SYSDATE. In order for the CBO to know what partitions it needs (or doesnt) it needs a literal or a constant foldable expression so it can be determined at parse time.

                                --
                                Regards,
                                Greg Rahn
                                http://structureddata.org
                                • 13. Re: cbo and sysdate wrong cardinality (bug ? )
                                  637538
                                  Thanks for helping.


                                  from Jonathan i understand that

                                  the cbo is changing the cardinality on OUTGOING_CALLS table when it chooses nested loops join.
                                  it uses the predicate CALL_START_DATE = PERIOD_KEY to get the cardianlity on OUTGONG_CALLS.
                                  so it knows that it will use about 7 partition and that is about 140M and add to that the selectivity of CALL_START_DATE = period_key and get to 17K.
                                  if it will use hash join it will not be able to use the predicate CALL_START_DATE = PERIOD_KEY .


                                  did i understand correctly ?

                                  >
                                  one partition for each row from the first table. Since this means scanning only 160 partitions out of 393, it is a good idea.
                                  The error is that we are only going to visit 7 different partitions: we know that, but the optimizer doesn't - and it's that difference that makes this plan a bad plan in this case
                                  >

                                  Jonathan, can you elaborate on this ?


                                  i still don't understand why it chooses the nested loopes over hash join?
                                  i still not sure how can i help the CBO choose to use hash and not using hints .

                                  Zvika
                                  • 14. Re: cbo and sysdate wrong cardinality (bug ? )
                                    user503699
                                    Jonathan Lewis wrote:
                                    The single table access path shows 149M rows because in the absence of the join predicate you have a 7 day range out of about 350 days, and allowing for the skew Oracle has your 20M rows per day for about 140M rows.

                                    The big problem is that Oracle can't see any correlation between your grouping column and the date range - so it's going to scan the same 7 partitions many times - for a total of 160 scans, when a correctly filtered hash join would scan the 7 once each.
                                    If what Greg has said above is correct, then oracle does not know (at parse time) that it has 7 day range and so it can not arrive at the computed cardinality
                                    of about 140M (= 20M rows per partition * 7 partitions).
                                    In that case another possible explaination for the computed cardinality looks like
                                    7387968000 (Original card) / 8376 (NDV) * 160 (computed cardinality of PERIOD_TODAY table) ~ 140M
                                    On the other hand, oracle knows (at parse time) that it has 7 days of range, then (in theory) it can also know that it needs to access a maximum of 7 partitions for OUTGOING_CALLS table. The transitive closure ensures that filter predicate is applied to OUTGOING_CALLS table as well (Execution Plan proves this).

                                    p.s. BTW, I am assuming that OUTGOING_CALLS table is range partitioned on CALL_START_DATE column.

                                    Edited by: user503699 on Jan 24, 2010 10:46 PM
                                    1 2 Previous Next