This discussion is archived
1 Reply Latest reply: Jan 20, 2013 11:24 PM by Nikolay Savvinov RSS

Wrong Cardinality estimate for Partition though statistics are up do date

851926 Newbie
Currently Being Moderated
select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
One of the coposite partitioned table shows wrong cardinality estimate for some partitions, not sure how to troubleshoot why this is happening. The table is used in multiple complex queries so the wrong cardinality estimate is causing performance issues. Though the table is composite partitioned we only have 1 subpart in each partition default subpartition.
Simplest case
select * from ADWH.FCT_LOT_DAILY PARTITION(LOTDLY_P150) D;
-------------------------------------
select /*+wrong_rows */ * from ADWH.FCT_LOT_DAILY 
PARTITION(LOTDLY_P1155) D
 
Plan hash value: 1945060244
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |               |       |       |  4737 (100)|          |       |       |
|   1 |  PARTITION LIST SINGLE  |               |  1911K|   421M|  4737  (26)| 00:00:35 |   KEY |   KEY |
|   2 |   PARTITION RANGE SINGLE|               |  1911K|   421M|  4737  (26)| 00:00:35 |     1 |     1 |
|   3 |    Table Access Full    | Fct_Lot_Daily |  1911K|   421M|  4737  (26)| 00:00:35 |   180 |   180 |
---------------------------------------------------------------------------------------------------------
1911K Rows as per optimizer
Following are the statistics information. Also I confirmed the statistics are not stale for the partition in dba_tab_statistics.
---------------------------------------------------------------------------------------------------------
SELECT table_name,
  partition_position,
  num_rows,
  blocks,
  last_analyzed
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME  ='FCT_LOT_DAILY'
AND TABLE_OWNER   ='ADWH'
AND partition_name='LOTDLY_P1155';

TABLE_NAME                     PARTITION_POSITION NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ------------------ -------- ------ -------------
FCT_LOT_DAILY                                 180 111036983 859073 13-JAN-13   

SELECT
  /*+PARALLEL(S,16) */
  COUNT(*)
FROM Adwh.Fct_Lot_Daily Partition(Lotdly_P1155) S;

113927312


Actual rows are more than 110 million where as optimizer reports 1.19 milliion only
The problem is harder considering for some partitions explain plan reflects num_rows in dba_tab_partitiions accurately. I gathered the partition statistics with cascasde true again but did not help either. Any indicators/pointers how I can find the cause, will really be appreciated.

Thanks in advance

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points