Re: partitions cardinality

From: Mauro Pagano <mauro.pagano_at_gmail.com>
Date: Tue, 21 Apr 2015 10:17:31 -0400
Message-ID: <CAAnDMS2QkyoVKH4B7OdSreuOoKK2KjUy5VNyMH2a2BSDVarYeg_at_mail.gmail.com>

I think the partition adjustments isn't the main factor here, if you recreate the table as non partitioned you get the same estimation (more or less).

The main reason for the drop I think is because part of the requested interval is out-of-range
- Filter is i_date >= to_date('20150402', 'yyyymmdd') and i_date < to_date('20150515', 'yyyymmdd')
- Depending when you create the TC you have a low value around 2015/04/18 or 2015/04/21 (not so great to work with a TC that changes overnight though :-()

So the overlapping interval would roughly be 20150421->20150515 (upper bound excluded so it should be +1*1/NDV) that translates to a selectivity of aprox 0.405 that leads to around 81k
There are probably some more small adjustments here and there to change the numbers a little more.

Corrections are super-welcome, as usual :-)

On Tue, Apr 21, 2015 at 4:25 AM, Stefan Koehler <contact_at_soocs.de> wrote:

> Hi Cheng,
> just a few points to mention about your provided test case. Here is a CBO
> trace from my environment.
>
> ***************************************
> BASE STATISTICAL INFORMATION
> ***********************
> Table Stats::
> Table: F_ORDER Alias: F_ORDER (Using composite stats)
> (making adjustments for partition skews)
> ORIGINAL VALUES:: #Rows: 200000 #Blks: 650 AvgRowLen: 16.00
> ChainCnt: 0.00
> PARTITIONS::
> PRUNED: 2
> ANALYZED: 2 UNANALYZED: 0
> #Rows: 200000 #Blks: 412 AvgRowLen: 16.00 ChainCnt: 0.00
> Access path analysis for F_ORDER
> ***************************************
> SINGLE TABLE ACCESS PATH
> Single Table Cardinality Estimation for F_ORDER[F_ORDER]
>
> Column (#1): I_DATE(
> AvgLen: 8 NDV: 60 Nulls: 0 Density: 0.016667 Min: 2457134 Max: 2457193
> Table: F_ORDER Alias: F_ORDER
> Card: Original: 200000.000000 Rounded: 79936 Computed: 79935.97 Non
> Adjusted: 79935.97
> Access Path: TableScan
> Cost: 86.67 Resp: 86.67 Degree: 0
> Cost_io: 86.00 Cost_cpu: 30821350
> Resp_io: 86.00 Resp_cpu: 30821350
> Best:: AccessPath: TableScan
> Cost: 86.67 Degree: 1 Resp: 86.67 Card: 79935.97 Bytes: 0
> ***************************************
>
> 1) The global statistics are used (trace text "Using composite stats"),
> because of the query predicates span over multiple partitions. Local
> statistics would be used, if the query would prune to one partition only.
>
> 2) The global statistic #Blks is adjusted from 650 to 412. The 412 results
> from the two local partition #Blks statistics. In my case partition
> P_201504 (110 #Blks) + partition P_201505 (302 #Blks).
>
> 3) However there also seems to be some adjustment to the selectivity that
> i am not aware of as it seems like the common arithmetic for between
> predicates does not work out here: (high_limit - low_limit) / (high_value
> – low_value) + 2 x 1/NDV = 43 / 59 + 2 x 0.016667 = selectivity of
> 0.76214755932203. Cardinality = 200000 x 0.76214755932203 = 152429.511
> rows, but Oracle got 79936 rows, which is much closer to the reality (80012
> rows).
>
> Maybe Jonathan Lewis or Randolf Geist know how this is scaled down in case
> of pruning to multiple partitions.
>
> Best Regards
> Stefan Koehler
>
> Freelance Oracle performance consultant and researcher
> Homepage: http://www.soocs.de
> Twitter: _at_OracleSK
>
>
> > Ls Cheng <exriscer_at_gmail.com> hat am 18. April 2015 um 18:39
> geschrieben:
> >
> > select count(*) from f_order
> > where i_date >= to_date('20150402', 'yyyymmdd')
> > and i_date < to_date('20150515', 'yyyymmdd')
> >
> > ***************************************
> > BASE STATISTICAL INFORMATION
> > ***********************
> > Table Stats::
> > Table: F_ORDER Alias: F_ORDER (Using composite stats)
> > (making adjustments for partition skews)
> > ORIGINAL VALUES:: #Rows: 200000 #Blks: 674 AvgRowLen: 16.00
> ChainCnt: 0.00
> > PARTITIONS::
> > PRUNED: 2
> > ANALYZED: 2 UNANALYZED: 0
> > #Rows: 200000 #Blks: 492 AvgRowLen: 16.00 ChainCnt: 0.00
> > Access path analysis for F_ORDER
> > ***************************************
> > SINGLE TABLE ACCESS PATH
> > Single Table Cardinality Estimation for F_ORDER[F_ORDER]
> >
> > Column (#1): I_DATE(
> > AvgLen: 8 NDV: 60 Nulls: 0 Density: 0.016667 Min: 2457131 Max:
> 2457190
> > Table: F_ORDER Alias: F_ORDER
> > Card: Original: 200000.000000 Rounded: 91382 Computed: 91382.02
> Non Adjusted: 91382.02
> > Access Path: TableScan
> > Cost: 137.33 Resp: 137.33 Degree: 0
> > Cost_io: 135.00 Cost_cpu: 36038010
> > Resp_io: 135.00 Resp_cpu: 36038010
> > Best:: AccessPath: TableScan
> > Cost: 137.33 Degree: 1 Resp: 137.33 Card: 91382.02 Bytes: 0
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 21 2015 - 16:17:31 CEST