1 Reply Latest reply: Jan 21, 2013 1:24 AM by Nikolay Savvinov RSS

    Wrong Cardinality estimate for Partition though statistics are up do date

    851926
      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