Re: Optimizer estimated cardinality very low

From: Daniel Coello <coello.daniel_at_gmail.com>
Date: Thu, 8 Oct 2020 17:48:43 -0400
Message-ID: <CADX5pQUrrFqjBiPG_9Aut1Nic9Qb0bL_NH6ciDxe3kCgmENMmA_at_mail.gmail.com>



Hello,
I did the tests using the partition extended table syntax for a couple of months updating partition names to corresponding months: SELECT

        "ORDER_REQST"."ORDR_RQST_NUMBER"    "ORDR_RQST_NUMBER",
        "ORDER_REQST"."ORDR_RQST_DATE"      "ORDR_RQST_DATE",
        "ORDER_REQST"."ORDR_STATUS"         "ORDR_STATUS"
    FROM "ORDER_REQST" PARTITION(SYS_P*XXXXXX*) "ORDER_REQST"     WHERE EXISTS (
            SELECT 1
            FROM "ORDER_DETL"  PARTITION(SYS_P*YYYYYY*) "ORDER_DETL"
            WHERE "ORDER_DETL"."ORDR_RQST_NUMBER" =
"ORDER_REQST"."ORDR_RQST_NUMBER"
                AND "ORDER_DETL"."ORDR_RQST_DATE" =
"ORDER_REQST"."ORDR_RQST_DATE"
    );

The estimates for this test match the queries using the dates specified for both June and July partitions. For july being the month with the issue:



| Id | Operation | Name | Rows | Bytes
|TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ
Distrib |

| 0 | SELECT STATEMENT | | 1 | 52
| | 15167 (12)| 00:00:01 | | | |
| |
| 1 | PX COORDINATOR | | |
| | | | | | |
| |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 52
| | 15167 (12)| 00:00:01 | | | Q1,00 | P->S | QC
(RAND) |
| 3 | PX PARTITION HASH ALL | | 1 | 52
| | 15167 (12)| 00:00:01 | 1 | 8 | Q1,00 | PCWC
| |
|* 4 | HASH JOIN SEMI | | 1 | 52 |
500M| 15167  (12)| 00:00:01 |       |       |  Q1,00 | PCWP |            |

| 5 | PX PARTITION RANGE SINGLE | | 26M|
692M| | 3322 (42)| 00:00:01 | 248 | 248 | Q1,00 | PCWC
| |
| 6 | TABLE ACCESS STORAGE FULL| ORDER_REQST | 26M|
692M| | 3322 (42)| 00:00:01 | 1977 | 1984 | Q1,00 | PCWP
| |
| 7 | PX PARTITION RANGE SINGLE | | 32M|
770M| | 1835 (7)| 00:00:01 | 248 | 248 | Q1,00 | PCWC
| |
| 8 | INDEX FAST FULL SCAN | ORDER_DETL_PK | 32M|
770M| | 1835 (7)| 00:00:01 | 1977 | 1984 | Q1,00 | PCWP
| |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):

   4 -
access("ORDER_DETL"."ORDR_RQST_NUMBER"="ORDER_REQST"."ORDR_RQST_NUMBER" AND

              "ORDER_DETL"."ORDR_RQST_DATE"="ORDER_REQST"."ORDR_RQST_DATE") Does this mean this is more likely having to do with the optimizer code? I want to make sure I understand your statement about the results and based on that what would be suitable to do next.

Thanks again,

On Thu, Oct 8, 2020 at 4:44 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> You say the plan for the July query didn't change - does that include the
> predicate information section ?
>
> If you've modified the query to use the ">= 1st Jul"  and "< 1st Aug"
> then the date predicate should have disappeared from the predicate
> information since it's an exact match for the partition definition.
>
> The only other thought that crosses my mind at present is that you've got
> a frequency histogram on the date partition stats (in both tables), but a
> hybrid histogram at the global level.  There are various bugs with hybrid
> (and top-N) histograms in 12c with patches available (
> https://jonathanlewis.wordpress.com/2018/01/15/histogram-hassle/) so it's
> possible that something about your hybrid histogram has confused the
> optimizer.
>
> A couple of tests I would do:
> Write the query without the explicit date predicates, but use the
> "partition extended" table syntax to specify the partition.  Do this for
> both June and July.  If the estimates in the plans match the queries with
> dates specified then the anomaly may be something to do with the optimizer
> code; if they do match then the anomaly is more likely to be something to
> do with the available stats (or object definitions).
>
> Regards
> Jonathan Lewis
>
>
>
>
> <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> Virus-free.
> www.avg.com
> <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
> <#m_-1267930624037679084_m_-7058117449535738880_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>


-- 
Daniel Coello Villacis

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 08 2020 - 23:48:43 CEST

Original text of this message