14 Replies Latest reply on Feb 21, 2018 12:37 PM by Jonathan Lewis

    Fixing Wrong cardinality Estimation in Case Of View

    933257

      We are using Version 11.2.0.4.0 of oracle. We have a query whose was running fine since 2-months with a fix execution path but suddenly two days back changed the execution path and consumed lot of tempspace and failed. and after analysis i see this is getting varied because of large variation in cardinality estimation in below part of query. Then i found the predicate section is actually doing something complex. Which then appears to be due to a view on which the sql query is being written. Below is the view definition.

       

      Sometimes the estimation went over 5-billion causing the change in overall execution path and consuming high tempspace failing the query. The query works fine when the below estimation stays below ~500million. Anyway as i see the current estimation 479-million is also way off than the actual one which is ~40 million record for bind :b1 := 02/03/2018 00:00:00. So i wanted to understand how to fix this issue. I do see the query is being opting dynamic sampling -3 , is it because of the complex predicate comes into play because of the view? And as i know the profile may give us fix paln, but is not good to go for it in first attempt. SO my question was is there anyway i should fix the cardinailty estimation to have it better?

       

      CREATE OR REPLACE FORCE VIEW TAB2
      (c1,c2,c3.....,
         DT_TM,
         DFG,
         DT,
         TIME,
         ACTUAL_DT,
         ACTUAL_TM
        )
      AS
         SELECT c1,c2,c3...,TB1."DT_TM",
                 TB1."DFG",
                TRUNC (TB1.DT_TM) AS DT,
                ROUND (86400 * (TB1.DT_TM - TRUNC (TB1.DT_TM)))
                   TIME,
                TRUNC (TB1.ACTUAL_DT_TM) ACTUAL_DT,
                ROUND (
                     86400
                   * (  TB1.ACTUAL_DT_TM
                      - TRUNC (TB1.ACTUAL_DT_TM)))
                   ACTUAL_TM
           FROM TAB1 TB1
          WHERE COALESCE (TB1.DFG, 'N') = 'N';
      

       

       

      Though this table is partitioned but we are not having direct filter in the partition key here so i hope it fetching details from global stats only. Below is the global stats for the column of table TAB1

       

      table_name, column_name,data_type,num_distinct, low_value, high_value, density, num_nulls, histogram

      TAB1    DT    DATE    6179571    78740B1E0A383C    7876020B01262B    1.61823531115671E-7    0    NONE

      TAB1    DFG    VARCHAR2    1    4E    4E    1    0    NONE

       

      Table stats TAB1-

      num_rows,blocks

      80120000000    80087344

       

       

      SQL> select * from TAB2 T
        2  WHERE T.DT = to_date(:b1,'MM/DD/YYYY HH24:MI:SS');
      Elapsed: 00:00:00.01
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2580948721
      
      ------------------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                    | Name                     | Rows  | Bytes | Cost (%CPU)| Time  | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
      ------------------------------------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT             |                          |   479M|    76G|  1756K (14)| 05:51:14 |       |       |        |      |            |
      |   1 |  PX COORDINATOR              |                          |       |       |            |       |  |       |        |      |            |
      |   2 |   PX SEND QC (RANDOM)        | :TQ10000                 |   479M|    76G|  1756K (14)| 05:51:14 |       |       |  Q1,00 | P->S | QC (RAND)  |
      |   3 |    PX PARTITION HASH ALL     |                          |   479M|    76G|  1756K (14)| 05:51:14 |     1 |  1024 |  Q1,00 | PCWC |            |
      |*  4 |     TABLE ACCESS STORAGE FULL| TAB1                     |   479M|    76G|  1756K (14)| 05:51:14 |     1 |  2048 |  Q1,00 | PCWP |            |
      ------------------------------------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         4 - storage(COALESCE("TB1"."DFG",'N')='N' AND TRUNC(INTERNAL_FUNCTION("TB1"."DT_TM"))=TO_DATE(:B1,'MM/DD/YYYY
                    HH24:MI:SS'))
             filter(COALESCE("TB1"."DFG",'N')='N' AND TRUNC(INTERNAL_FUNCTION("TB1"."DT_TM"))=TO_DATE(:B1,'MM/DD/YYYY
                    HH24:MI:SS'))
      
      Note
      -----
         - dynamic sampling used for this statement (level=3)
      
      
        • 1. Re: Fixing Wrong cardinality Estimation in Case Of View
          AndrewSayer

          Ignore that it's a view, the optimizer is trying to work out the cardinality of

           

          1. SELECT c1,c2,c3...,TB1."DT_TM"
          2.            TB1."DFG"
          3.          TRUNC (TB1.DT_TM) AS DT, 
          4.           ROUND (86400 * (TB1.DT_TM - TRUNC (TB1.DT_TM))) 
          5.              TIME
          6.           TRUNC (TB1.ACTUAL_DT_TM) ACTUAL_DT, 
          7.           ROUND ( 
          8.                86400 
          9.              * (  TB1.ACTUAL_DT_TM 
          10.                 - TRUNC (TB1.ACTUAL_DT_TM))) 
          11.              ACTUAL_TM 
          12.      FROM TAB1 TB1 
          13.     WHERE COALESCE (TB1.DFG, 'N') = 'N';
          14. and  TRUNC (TB1.DT_TM)  = to_date(:b1,'MM/DD/YYYY HH24:MI:SS'); 

           

          That trunc(tb1.dt_tm) is an expression and that is going to mean you either need a stat on it or Oracle is going to guess (wrongly). I suggest you filter instead on the raw dt_tm column with a range filter like

           

          and dt_tm >= trunc(to_date(:b1,'MM/DD/YYYY HH24:MI:SS'))

          and dt_tm < trunc(to_date(:b1,'MM/DD/YYYY HH24:MI:SS'))+1

           

          You'll probably find the same difficulty with the coalesce function, you could transform that to where (dfg='N' or dfg is null)

          1 person found this helpful
          • 2. Re: Fixing Wrong cardinality Estimation in Case Of View
            933257

            Just for curiosity, when you said "That trunc(tb1.dt_tm) is an expression and that is going to mean you either need a stat on it or Oracle is going to guess (wrongly)."  are you pointing towards a function based index on trunc(dt_tm) that will provide the required statistics on the same ?

            • 3. Re: Fixing Wrong cardinality Estimation in Case Of View
              Mark D Powell

              user933257, why is dynamic sampling in use?  It is appear that the object is hash partitioned and that this is an EXADATA system.  All of which is important information that should have been explicitly stated up front.

              - -

              Using the good plan I suggest you consider creating an SQL Baseline for the plan.

              - -

              HTH -- Mark D Powell --

              • 4. Re: Fixing Wrong cardinality Estimation in Case Of View
                933257

                My apology . Yes its a List-Hash composite partition table and Exadata system. but as my query doesn't have any filter on partition key so it must be using global stats which i had shared in the original post. Still not able to understand if the dynamic sampling is anyway related with the HASH partition? i do see if i remove the TRUNC function and coalesce function and use the inline table rather than view the dynamic sampling disappears from the query predicate section. So hopefully it means its using dynamic sampling to evaluate the complex predicate section which is created due to presence of TRUNC and COALESCE in the view definition.

                • 5. Re: Fixing Wrong cardinality Estimation in Case Of View
                  Mark D Powell

                  user933257, with Andrew's changes to TRUNC and COALESCE is the resulting plan the same as your good plan or are you getting a third plan?  Have you tested the actual run time of the changed query?

                  - -

                  HTH -- Mark D Powell --

                  • 6. Re: Fixing Wrong cardinality Estimation in Case Of View
                    AndrewSayer

                    Andrew Sayer wrote:

                     

                    ...

                    You'll probably find the same difficulty with the coalesce function, you could transform that to where (dfg='N' or dfg is null)

                    Jonathan Lewis shared this earlier today, I suspect he intended to link to it here https://jonathanlewis.wordpress.com/2018/02/13/coalesce-v-nvl/

                    Essentially if you used nvl instead of coalesce, the CBO will know what it really means.

                    • 7. Re: Fixing Wrong cardinality Estimation in Case Of View
                      Jonathan Lewis

                      This was the thread that prompted me to finish that nvl/coalesce post.

                       

                      Another point about this thread is that the trunc(dt_tm) = constant will also have a selectivity of 1%, and the combination of predicates will have a selectivity of 1/100 * 1/100 = 1/10,000. Since there are 80,000,000,000 rows in the table this means the derived cardinality "ought" to be 8,000,000 rows.  The fact that the estimated cardinality is 479M is a side effect of the dynamic sampling, which was probably a small sample on a large table where most of the rows are 'N' (or null), the date column has skewed data, and the interesting data is fairly well clustered.

                       

                      To investigate the impact of the dynamic sampling (how many rows sampled, how many rows match) the OP could run with event 10053 enabled and look for the section about "dynamic sampl"

                       

                      Regards

                      Jonathan Lewis

                      • 8. Re: Fixing Wrong cardinality Estimation in Case Of View
                        Jonathan Lewis

                        933257 wrote:

                         

                        Yes its a List-Hash composite partition table

                        Can you confirm that. I would have expected to see a "PARTITION LIST {something}" operation before the "PARTITION HASH ALL" operation in a plan for a list/hash composite partitioned table - but that omission may be an oddity of 11g, and it may be that the differences in the PSTOP for lines 3 and 4 of the plan are a pointer to what's happening.

                         

                        Which column is the partitioning column, and how many partitions, and are the partitions the same size (to a factor of 2)

                        Which column is the hash subpartitioning column, and how many subpartitions (per partition)

                        How many distinct trunc(dt_tm) values are there in the table and how does the number for the date you're interested in compare to the average number of days per trunc(dt_tm). How many of the dates have a relatively small number of rows, how many have a relatively large number of rows compared to average (use a factor of 5 difference as a guide line nothing stands out as dramatically different)

                         

                        Regards

                        Jonathan Lewis

                        1 person found this helpful
                        • 9. Re: Fixing Wrong cardinality Estimation in Case Of View
                          933257

                          Yes Jonathan. Its a list-Hash composite partitioned table. List partitioned by one column c1_cd with '4' distinct values('1','2','3','4') in it. And c1_cd with value '1' is in one partition(P1) and others with values '2','3','4' is aligned to another list partition(p2). So P1 holds ~62 billion records and p2 holds ~15billion records.

                           

                          There are 1024 subpartitions alligned to each partition P1 and P2. Column/key which is hash partitioned is a different column other than DT_TM, i mean that is not being used as filter either in view definition. And subpartition key is "c2_fk" and as i i see from column stats its is having 22790 distinct values in it.

                           

                          I have fetched TRUNC(DT_TM) records for last few days. its as below. As you said initially that the skewness of data in this column might be a factor of the change in cardinailty on specific day(mainly 5th feb), but the below data pattern seems consistent.

                           

                          And i have verified, Column "DFG" is having all values as 'N' no NULL values exists in this table for this column.

                           

                          TRUNC(DT_TM)   COUNT(*)

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

                          01-FEB-18   44254425

                          02-FEB-18   46585349

                          03-FEB-18   43383099

                          04-FEB-18   32748364

                          05-FEB-18   37993126

                          06-FEB-18   39708994

                          07-FEB-18   38696777

                          08-FEB-18   41871780

                          09-FEB-18   46702852

                          10-FEB-18   42744870

                          11-FEB-18   34971845

                          12-FEB-18   37165983

                          • 10. Re: Fixing Wrong cardinality Estimation in Case Of View
                            Jonathan Lewis

                            Yes Jonathan. Its a list-Hash composite partitioned table. List partitioned by one column c1_cd with '4' distinct values('1','2','3','4') in it. And c1_cd with value '1' is in one partition(P1) and others with values '2','3','4' is aligned to another list partition(p2). So P1 holds ~62 billion records and p2 holds ~15billion records.

                             

                            There are 1024 subpartitions alligned to each partition P1 and P2.

                             

                            Thanks for that - I'll have to run up a model to check whether 12.1.0.2 and 12.2.0.1 also "lose" the LIST PARTITION.  At least there was a clue in the way the PSTOP was 1024 in one line of the plan and 2048 in the next, reflecting your 2 partitions and 1024 subpartitions.

                             

                            So the recent past shows about 40M rows per date-only value.  But you have 80Billion, and the low_value/high_value for the column show a spread from 30-Nov-2016 to 11-Feb-2018 which is 438 days, so an average of about 182M rows per day. So this means you've got some days with FAR more rows than other days. An unlucky sample (and the sample size was probably very small) could easily push the estimate of rows per day over your critical limit.

                             

                            One option is to do two things:

                            (a) change the coalesce(dfg) to be an nvl(dfg) so that Oracle uses 50% instead of 1% for its calculation (or you could create a histogram on the column so that Oracle used 100%, but I don't think you need to do that.

                            (b) create a virtual column or (in case the application uses "select *" or "insert" without "values()") extended stats on trunc(dt_tm) and gather stats on the resulting column. If you create a histogram you may find that you get an estimate of about 40M for your recent dates, if you don't create a histogram it would probably be about 182M - either is within your critical limit.

                             

                            Another option - as indicated by Andrew Sayer would be to change the code so that the predicate is a range-based predicate against the dt_tim column rather than equality on trunc(dt_tm).  You might still want to change from coalesce() to nvl()

                             

                            Regards

                            Jonathan Lewis

                             

                            UPDATE - I forgot to say that the point of the exercise is to stop the optimizer from thinking it needs to do a dynamic sample. Level 3 is the "I'm guessing" level for dynamic sampling, using virtual columns and/or nvl() eliminates the guesses (i.e. the two assumptions of 1%).  You may find, unfortunately, that Oracle then decides to sample at level 4 (multiple predicates on single table).

                            1 person found this helpful
                            • 11. Re: Fixing Wrong cardinality Estimation in Case Of View
                              933257

                              Thank You Jonathan I did few queries on base table to test the estimation Coalesce VS NVL. Below are the results. I see the COALESCE is bit closer to the actual. Not sure if its due to some other stats impacting the figure.

                               

                               

                              var b1 VARCHAR2(32)

                                  

                              exec :b1 := '02/11/2018'

                               

                              QL> select * from USER1.TAB1 T
                              2  WHERE trunc(T.DT_TM) = to_date(:b1,'MM/DD/YYYY');
                              lapsed: 00:00:00.00
                              
                              xecution Plan
                              ---------------------------------------------------------
                              lan hash value: 2580948721
                              
                              -----------------------------------------------------------------------------------------------------------------------------------------------------
                              Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time  | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
                              -----------------------------------------------------------------------------------------------------------------------------------------------------
                                0 | SELECT STATEMENT            |                          |  801M|  127G|  1986K (25)| 06:37:16 |      |      |        |      |            |
                                1 |  PX COORDINATOR              |                          |      |      |            |      |  |      |        |      |            |
                                2 |  PX SEND QC (RANDOM)        | :TQ10000                |  801M|  127G|  1986K (25)| 06:37:16 |      |      |  Q1,00 | P->S | QC (RAND)  |
                                3 |    PX PARTITION HASH ALL    |                          |  801M|  127G|  1986K (25)| 06:37:16 |    1 |  1024 |  Q1,00 | PCWC |            |
                              *  4 |    TABLE ACCESS STORAGE FULL| TAB1                    |  801M|  127G|  1986K (25)| 06:37:16 |    1 |  2048 |  Q1,00 | PCWP |            |
                              -----------------------------------------------------------------------------------------------------------------------------------------------------
                              
                              redicate Information (identified by operation id):
                              --------------------------------------------------
                              
                                4 - storage(TRUNC(INTERNAL_FUNCTION("T"."DT_TM"))=TO_DATE(:B1,'MM/DD/YYYY'))
                                    filter(TRUNC(INTERNAL_FUNCTION("T"."DT_TM"))=TO_DATE(:B1,'MM/DD/YYYY'))
                              
                              
                              SQL> select * from USER1.TAB1 T
                                2  where DT_TM >= trunc(to_date(:b1,'MM/DD/YYYY HH24:MI:SS'))
                                3  and DT_TM < trunc(to_date(:b1,'MM/DD/YYYY HH24:MI:SS'))+1;
                              Elapsed: 00:00:00.01
                              
                              Execution Plan
                              ----------------------------------------------------------
                              Plan hash value: 2025865596
                              
                              -------------------------------------------------------------------------------------------------------------------------------------------------------
                              | Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time  | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
                              -------------------------------------------------------------------------------------------------------------------------------------------------------
                              |  0 | SELECT STATEMENT              |                          |  200M|    31G|  1811K (17)| 06:02:17 |      |      |        |      |            |
                              |*  1 |  PX COORDINATOR              |                          |      |      |            |      |  |      |        |      |            |
                              |  2 |  PX SEND QC (RANDOM)        | :TQ10000                |  200M|    31G|  1811K (17)| 06:02:17 |      |      |  Q1,00 | P->S | QC (RAND)  |
                              |*  3 |    FILTER                    |                          |      |      |            |      |  |      |  Q1,00 | PCWC |            |
                              |  4 |    PX PARTITION HASH ALL    |                          |  200M|    31G|  1811K (17)| 06:02:17 |    1 |  1024 |  Q1,00 | PCWC |            |
                              |*  5 |      TABLE ACCESS STORAGE FULL| TAB1                    |  200M|    31G|  1811K (17)| 06:02:17 |    1 |  2048 |  Q1,00 | PCWP |            |
                              -------------------------------------------------------------------------------------------------------------------------------------------------------
                              
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                              
                                1 - filter(TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS'))+1>TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS')))
                                3 - filter(TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS'))+1>TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS')))
                                5 - storage("DT_TM"<TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS'))+1 AND "DT_TM">=TRUNC(TO_DATE(:B1,'MM/DD/YYYY
                                            HH24:MI:SS')))
                                    filter("DT_TM"<TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS'))+1 AND "DT_TM">=TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS')))
                              
                              SQL>
                              SQL> select * from USER1.TAB1 T
                                2  where DT_TM >= trunc(to_date(:b1,'MM/DD/YYYY HH24:MI:SS'))
                                3  and DT_TM < trunc(to_date(:b1,'MM/DD/YYYY HH24:MI:SS'))+1
                                4  AND  COALESCE (DFG,'N') = 'N';
                              Elapsed: 00:00:00.05
                              
                              Execution Plan
                              ----------------------------------------------------------
                              Plan hash value: 2025865596
                              
                              -------------------------------------------------------------------------------------------------------------------------------------------------------
                              | Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time  | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
                              -------------------------------------------------------------------------------------------------------------------------------------------------------
                              |  0 | SELECT STATEMENT              |                          |    38M|  6207M|  1752K (15)| 05:50:34 |      |      |        |      |            |
                              |*  1 |  PX COORDINATOR              |                          |      |      |            |      |  |      |        |      |            |
                              |  2 |  PX SEND QC (RANDOM)        | :TQ10000                |    38M|  6207M|  1752K (15)| 05:50:34 |      |      |  Q1,00 | P->S | QC (RAND)  |
                              |*  3 |    FILTER                    |                          |      |      |            |      |  |      |  Q1,00 | PCWC |            |
                              |  4 |    PX PARTITION HASH ALL    |                          |    38M|  6207M|  1752K (15)| 05:50:34 |    1 |  1024 |  Q1,00 | PCWC |            |
                              |*  5 |      TABLE ACCESS STORAGE FULL| TAB1                    |    38M|  6207M|  1752K (15)| 05:50:34 |    1 |  2048 |  Q1,00 | PCWP |            |
                              -------------------------------------------------------------------------------------------------------------------------------------------------------
                              
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                              
                                1 - filter(TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS'))+1>TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS')))
                                3 - filter(TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS'))+1>TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS')))
                                5 - storage(COALESCE("DFG",'N')='N' AND "DT_TM"<TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS'))+1 AND
                                            "DT_TM">=TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS')))
                                    filter(COALESCE("DFG",'N')='N' AND "DT_TM"<TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS'))+1 AND
                                            "DT_TM">=TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS')))
                              
                              Note
                              -----
                                - dynamic sampling used for this statement (level=3)
                              
                              SQL> select * from USER1.TAB1 T
                                2  where DT_TM >= trunc(to_date(:b1,'MM/DD/YYYY HH24:MI:SS'))
                                3  and DT_TM < trunc(to_date(:b1,'MM/DD/YYYY HH24:MI:SS'))+1
                                4  AND  nvl (DFG,'N') = 'N';
                              Elapsed: 00:00:00.00
                              
                              Execution Plan
                              ----------------------------------------------------------
                              Plan hash value: 2025865596
                              
                              -------------------------------------------------------------------------------------------------------------------------------------------------------
                              | Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time  | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
                              -------------------------------------------------------------------------------------------------------------------------------------------------------
                              |  0 | SELECT STATEMENT              |                          |  200M|    31G|  1827K (18)| 06:05:28 |      |      |        |      |            |
                              |*  1 |  PX COORDINATOR              |                          |      |      |            |      |  |      |        |      |            |
                              |  2 |  PX SEND QC (RANDOM)        | :TQ10000                |  200M|    31G|  1827K (18)| 06:05:28 |      |      |  Q1,00 | P->S | QC (RAND)  |
                              |*  3 |    FILTER                    |                          |      |      |            |      |  |      |  Q1,00 | PCWC |            |
                              |  4 |    PX PARTITION HASH ALL    |                          |  200M|    31G|  1827K (18)| 06:05:28 |    1 |  1024 |  Q1,00 | PCWC |            |
                              |*  5 |      TABLE ACCESS STORAGE FULL| TAB1                    |  200M|    31G|  1827K (18)| 06:05:28 |    1 |  2048 |  Q1,00 | PCWP |            |
                              -------------------------------------------------------------------------------------------------------------------------------------------------------
                              
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                              
                                1 - filter(TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS'))+1>TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS')))
                                3 - filter(TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS'))+1>TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS')))
                                5 - storage("DT_TM"<TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS'))+1 AND "DT_TM">=TRUNC(TO_DATE(:B1,'MM/DD/YYYY
                                            HH24:MI:SS')) AND NVL("DFG",'N')='N')
                                    filter("DT_TM"<TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS'))+1 AND "DT_TM">=TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS'))
                                            AND NVL("DFG",'N')='N')
                              
                              SQL> set autotrace off
                              SQL> select count(*) from USER1.TAB1 T
                                2  where DT_TM >= trunc(to_date(:b1,'MM/DD/YYYY HH24:MI:SS'))
                                3  and DT_TM < trunc(to_date(:b1,'MM/DD/YYYY HH24:MI:SS'))+1
                                4  AND  nvl (DFG,'N') = 'N';
                              
                                COUNT(*)
                              ----------
                                34981853
                              
                              Elapsed: 00:00:04.46
                              
                              select min(dt_tm), max(DT_TM) from USER1.TAB1;
                              5/10/2013 3:18:09 PM    2/15/2018 6:34:51 AM
                              
                              • 12. Re: Fixing Wrong cardinality Estimation in Case Of View
                                Jonathan Lewis

                                I have to assume you didn't actually execute these queries and pull the resulting plans from memory as the time to "execute" is far too low.

                                 

                                Working from the top:

                                1) 801M - standard cardinality 1% for function(column) = constant

                                2) 200M - standard cardinality (5% of 5%) = 1/400, for two range-based comparisons with unknown

                                3) 38M - has used dynamic sampling at level 3 so if it's got it about right that's what it's supposed to do

                                4) 200M - hasn't used dynamic sampling, but has 1/400 for the unknown range-based again, x (special case nvl() for dfg one distinct value and no nulls => 100%)

                                 

                                 

                                Your actual min(date) shows that there's something wrong with your stats (which may be due to unlucky sampling) because the LOW_VALUE reported a few posts back is 30-Nov-2016, not 10th May 2013.

                                 

                                Regards

                                Jonathan Lewis

                                • 13. Re: Fixing Wrong cardinality Estimation in Case Of View
                                  AndrewSayer

                                  Jonathan Lewis wrote:

                                   

                                  I have to assume you didn't actually execute these queries and pull the resulting plans from memory as the time to "execute" is far too low.

                                  ...

                                  It looks to me like

                                  set autotrace off

                                  Is a clue, those plans were probably produced with

                                  set autotrace traceonly explain

                                  So we're seeing just explain plans (SQL*Plus autotrace doesn't ever get the real execution plans from memory). This means none of the bind variables would have been peeked.

                                  • 14. Re: Fixing Wrong cardinality Estimation in Case Of View
                                    Jonathan Lewis

                                    Andrew Sayer wrote:

                                     

                                    Jonathan Lewis wrote:

                                     

                                    I have to assume you didn't actually execute these queries and pull the resulting plans from memory as the time to "execute" is far too low.

                                    ...

                                    It looks to me like

                                    set autotrace off

                                    Is a clue, those plans were probably produced with

                                    set autotrace traceonly explain

                                    So we're seeing just explain plans (SQL*Plus autotrace doesn't ever get the real execution plans from memory). This means none of the bind variables would have been peeked.

                                     

                                     

                                    That was the premise behind quoting the standard figures for "unknown values" for most of the examples; but there is the one oddity of the plan with a note about dynamic sampling - which gets the right value when the "unknown bind" cardinality should probably have been 1% of 0.25%  (i.e. 2M rows, not 38M).

                                     

                                    Regards

                                    Jonathan Lewis