Forum Stats

  • 3,733,039 Users
  • 2,246,686 Discussions
  • 7,856,490 Comments

Discussions

Fixing Wrong cardinality Estimation in Case Of View

User_OCZ1T
User_OCZ1T Member Posts: 1,921 Gold Badge

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.01Execution 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)
Mark D PowellAndrewSayer

Best Answer

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited February 2018 Accepted Answer

    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)

    User_OCZ1T

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited February 2018 Accepted Answer

    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)

    User_OCZ1T
  • User_OCZ1T
    User_OCZ1T Member Posts: 1,921 Gold Badge
    edited February 2018

    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 ?

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited February 2018

    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 --

  • User_OCZ1T
    User_OCZ1T Member Posts: 1,921 Gold Badge
    edited February 2018

    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.

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited February 2018

    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 --

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited February 2018
    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.

    Mark D Powell
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,602 Gold Crown
    edited February 2018

    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

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,602 Gold Crown
    edited February 2018
    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

    User_OCZ1T
  • User_OCZ1T
    User_OCZ1T Member Posts: 1,921 Gold Badge
    edited February 2018

    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

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,602 Gold Crown
    edited February 2018
    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).

    User_OCZ1T
  • User_OCZ1T
    User_OCZ1T Member Posts: 1,921 Gold Badge
    edited February 2018

    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 T2  WHERE trunc(T.DT_TM) = to_date(:b1,'MM/DD/YYYY');lapsed: 00:00:00.00xecution 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.01Execution 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.05Execution 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.00Execution 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 offSQL> 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(*)----------  34981853Elapsed: 00:00:04.46select min(dt_tm), max(DT_TM) from USER1.TAB1;5/10/2013 3:18:09 PM    2/15/2018 6:34:51 AM
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,602 Gold Crown
    edited February 2018

    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

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited February 2018
    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.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,602 Gold Crown
    edited February 2018
    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 likeset autotrace offIs a clue, those plans were probably produced withset autotrace traceonly explainSo 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

    AndrewSayer
This discussion has been closed.