RE: Statistics Problem on partitioned table

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 1 Oct 2013 11:11:54 -0400
Message-ID: <0c2e01cebeb8$91102f60$b3308e20$_at_rsiz.com>

1. I think you're saying that there is exactly one row where per_ind=0 in your entire table, but that your query has no indication of which partition that row exists.
2. The fragment of your group by count seems to indicate 6 million rows having per_ind=0, on the one row returned because of the group by. Please show us: select count(*) from XXX_PER_YYY where per_ind = 0;
3. Better yet, show us: set linesize 140 pagesize 40 null ~

select
--+ gather_plan_statistics
count(*) from XXX_PER_YYY where per_ind = 0;

select * from table(dbms_xplan.display_cursor(format=>'COST ALLSTATS LAST')); Then we'll see what is actually happening instead of fragments.

Regards,

mwf

-----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


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

Received on Tue Oct 01 2013 - 17:11:54 CEST