# RE: Statistics Problem on partitioned table

Date: Tue, 1 Oct 2013 09:58:04 -0500

Message-ID: <C970F08BBE1E164AA8063E01502A71CF021C0D09_at_WIN02.hotsos.com>

The optimizer is a pretty simple machine when you get down to it. Here is exactly why you are getting the estimated number of rows you are getting:

(1/721,701) = 0.000001 this is the density for the PER_IND column 1 over number of distinct keys.

49,754,928 is the rows in the table.

With a simple equality predicate like this the optimizer does a simple rows X density calculation:

49,754,928 X 0.000001 = 68.941193 rounded to 69.

Since the optimizer can't do anything about partition pruning at this level since there is no reference to the partition key, that is what you get.

From: Mohamed Houri [mailto:mohamed.houri_at_gmail.com]
Sent: Tuesday, October 01, 2013 10:47 AM
To: Ric Van Dyke

Cc: ORACLE-L

Subject: Re: Statistics Problem on partitioned table

Ric,

If you divide A-Rows/Starts you will find 550K/111 = 4954 rows. That's close to what you have pointed out. How did you figure out this?

select leaf_blocks, distinct_keys, clustering_factor, num_rows,partitioned

from all_indexes where index_name = 'XXX_PER_IND';

leaf_blocks distinct_keys clustering_factor num_rows partitioned

119369 721701 204870 49754928 YES

Mohamed Houri

2013/10/1 Ric Van Dyke <ric.van.dyke_at_hotsos.com>

I suspect that the real issue for the optimizer is the fact that the index is a non-prefixed local index. I can only presume that the estimated rows you see are a "pre partition" based number, it's still wrong should be in the near 4000 not less than 100. (given the total number for rows returned is 6 million) Since it's not prefixed the optimizer has no idea which partitions it will go once it retrieves a row from the index. What at the global level stats for the per_ind column? Especially the number for distinct values.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org

[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mohamed Houri
Sent: Tuesday, October 01, 2013 10:07 AM
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 -- http://www.freelists.org/webpage/oracle-l -- Bien Respectueusement Mohamed Houri -- http://www.freelists.org/webpage/oracle-lReceived on Tue Oct 01 2013 - 16:58:04 CEST