Statistics Problem on partitioned table

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Tue, 1 Oct 2013 16:07:02 +0200
Message-ID: <CAJu8R6gHRQ2BqpCF2dOis0gq=bgYsP3u9J92up280RuTNJy6yw_at_mail.gmail.com>

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

Received on Tue Oct 01 2013 - 16:07:02 CEST