Re: partitions cardinality

  • From: Ls Cheng <exriscer@xxxxxxxxx>
  • To: Mauro Pagano <mauro.pagano@xxxxxxxxx>
  • Date: Tue, 21 Apr 2015 18:12:25 +0200

Hi Mauro

Thanks for the explanation. The out of range was the trick!

Thanks again



On Tue, Apr 21, 2015 at 4:17 PM, Mauro Pagano <mauro.pagano@xxxxxxxxx>
wrote:

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@xxxxxxxx> 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: @OracleSK


Ls Cheng <exriscer@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l




Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 04-2015