RE: Statistics Problem on partitioned table
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "mohamed.houri@xxxxxxxxx" <mohamed.houri@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 1 Oct 2013 14:49:24 +0000
Which version of Oracle ?
Your "Starts" is only 111, and the A-rows only 500K rather than 6M so can we
assume you stopped the query before it had completed ?
Your row count must be well in excess of the 6M with the flag set, so
presumably a lot of rows are null on per_ind - according to the stats, how many
partitions have no rows with a non-null value ? (I'm contemplating ideas of
how the optimizer handles known skews in partition sizes and content.)
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Mohamed Houri [mohamed.houri@xxxxxxxxx]
Sent: 01 October 2013 15:07
To: ORACLE-L
Subject: Statistics Problem on partitioned table
Dear list
I have a performance problem that I have narrowed to a statistics problem
where the CBO is not doing good estimations on a partitioned table
The culprit select looks like
select
a
,b
,c
,per_ind
from XXX_PER_YYY
where per_ind = 0;
XXX_PER_YYY is a table range partitioned by a date. There are 1493
partitions.
AND there is an index XXX_PER_IND on (per_ind) local (note that it is local
non prefixed)
The execution plan with Estimations and Actuals looks like
----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |
A-Rows |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
|550K |
| 1 | PARTITION RANGE ALL | | 1 | 69
|550K |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| XXX_PER_YYY | 111 | 69
|550K |
|* 3 | INDEX RANGE SCAN | XXX_PER_IND | 111 | 69
|550K |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PER_IND"=0)
And the execution plan showing the number of partition looks like
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 69 | 1173
| | |
| 1 | PARTITION RANGE ALL | | 69 | 1173
| 1 | 1493 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| XXX_PER_YYY | 69 | 1173
| 1 | 1493 |
|* 3 | INDEX RANGE SCAN | XXX_PER_IND | 69 |
| 1 | 1493 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PER_IND"=0)
There is only one distinct value of per_ind (per_ind =0)
select per_ind, count(1) from XXX_PER_YYY group by per_ind;
*per_ind cout(1)*
0 6,118,184
And I have the following partitions num_rows distribution
758 partitions with num_rows = 0;
60 partitions with num_rows <= 5000
295 partitions with num_rows between 5000 and 10000
315 partitions with num_rows > 10,000 and num_rows <50,000
65 partitions with num_rows > 100,000;
Statistics are calculated at a global level
How to make the CBO having correct estimations and hence an optimal
execution plan?
Thanks in advance
--
Bien Respectueusement
Mohamed Houri
www.hourim.wordpress.com
--
//www.freelists.org/webpage/oracle-l--
//www.freelists.org/webpage/oracle-l
Other related posts:
