1

We have a large table range partitioned by month. Incremental statistics turned on. After scheduled statistics gathering cardinality estimation become weird, like

select count(*) from my_table where date >= trunc(sysdate) - 30 and date < trunc(sysdate)

gives 1.3M rows, but estimation is 20K. Only after manually regathering statistics estimation becomes accurate. Code samples:

-- Scheduled
dbms_stats.gather_table_stats
(
    ownname=> 'ownname', 
    tabname=> 'tabname' , 
    estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,  
    cascade=> DBMS_STATS.AUTO_CASCADE, 
    degree=> 4,  
    no_invalidate=> DBMS_STATS.AUTO_INVALIDATE, 
    granularity=> 'AUTO', 
    method_opt=> 'FOR ALL COLUMNS SIZE AUTO'
);

-- Manual
DBMS_STATS.GATHER_TABLE_STATS 
(
    ownname => '"ownname"',
    tabname => '"tabname"',
    partname => '"partname"',
    method_opt => 'FOR COLUMNS DATE SIZE 254',
    estimate_percent => 1
);

Other partitioned tables are ok.

The differences between this table and others are (as we know):

  1. There were wrong inserts in this table. Most dates are between 2014 and 2023, but there are some rows with 1970 and 2024 (we can't change it). Also there is empty partition with 2045. We tried recreating this but didn't get same behaviour.
  2. We messed with histograms, removed some automatically created and manually created some useful function-based. But in USER_TAB_COL_STATISTICS and USER_TAB_HISTOGRAMS histograms for DATE column were present.

What can cause such behaviour? How can we fix it?

1 Answer 1

1

This is a common issue with ascending dates in partitioned tables. The first thing is to verify that the incremental stats are working.

SELECT notes
  FROM dba_tab_col_statistics
 WHERE table_name = 'MYTABLE'

You should see "INCREMENTAL" for each of the columns. If you don't, then you need to get incremental stats working properly. To get incremental stats to work, you must set these options:

GRANULARITY -> AUTO
INCREMENTAL -> TRUE
PUBLISH -> TRUE
ESTIMATE_PERCENT -> DBMS_STATS.AUTO_SAMPLE_SIZE
APPROXIMATE_NDV_ALGORITHM -> LIKE '%HYPERLOGLOG%'
INCREMENTAL_LEVEL -> PARTITION

You must then gather stats with these settings once, which will initially do a global gather. It might be a good idea to delete stats on the table first to start with a clean slate. Then, subsequent gathers should gather only stale partitions and use that to estimate global column min/max/distinct values.

Also, you don't want histograms on a date column unless you have a magic value (like 12/31/9999) that is over-represented or a single row with a bogus date that goes back to 1970 or 1900 or some silly date so far back a simple min/max average will be really off. Histograms are for skew, not for evenly spread-out values as most date columns are. I think I recall reading that histograms override incremental stats, so that may be another reason for deleting them from always-ascending date columns. In our big data warehouse we don't have any histograms on date columns, especially those that are partitioning keys. We rely on incremental only and it generally works pretty well.

Assuming your incremental stats are working, the next thing is to see whether the stats gathering is happening too late (the bad query may be executed very soon after major partition modification/loading before stats have a chance to be gathered on the new data). To check this, look at LAST_ANALYZED and cross-check it with the start time of the bad query and the time of the last major data load.

You can also double check to see that the max value for your date column is roughly correct. You'll have to decode it from its raw value. Here is a set of functions that you can use to display the min/max value for your columns:

CREATE OR REPLACE FUNCTION CAST_RAW_TO_CHAR (bdr IN raw) 
  RETURN varchar2
  DETERMINISTIC 
AS  
BEGIN
  RETURN utl_raw.cast_to_varchar2(bdr);
EXCEPTION 
  WHEN OTHERS THEN 
    RETURN NULL;
END CAST_RAW_TO_CHAR;
/
CREATE OR REPLACE FUNCTION CAST_RAW_TO_NUMBER (bdr IN raw) 
  RETURN number
  DETERMINISTIC 
AS  
BEGIN
  RETURN utl_raw.cast_to_number(bdr);
EXCEPTION 
  WHEN OTHERS THEN 
    RETURN NULL;
END CAST_RAW_TO_NUMBER;
/
CREATE OR REPLACE FUNCTION CAST_RAW_TO_DATE (bdr IN raw) 
  RETURN date
  DETERMINISTIC 
AS  
BEGIN
  RETURN
     date'1-1-1'
     + NUMTOYMINTERVAL(
         100 * (to_number(substr(bdr,1,2), 'xx') - 100) + 
         to_number(substr(bdr,3,2), 'xx') - 101, 
       'year')
     + NUMTOYMINTERVAL(to_number(substr(bdr,5,2), 'xx')-1, 'month')
     + NUMTODSINTERVAL(to_number(substr(bdr,7,2), 'xx')-1, 'day')
     + NUMTODSINTERVAL(to_number(substr(bdr,9,2), 'xx') - 1, 'hour')   
     + NUMTODSINTERVAL(to_number(substr(bdr,11,2), 'xx') - 1, 'minute')   
     + NUMTODSINTERVAL(to_number(substr(bdr,13,2), 'xx') - 1, 'second');
EXCEPTION 
  WHEN OTHERS THEN 
    RETURN NULL;
END CAST_RAW_TO_DATE;
/

CREATE OR REPLACE FUNCTION CAST_RAW_TO_DISPLAY_STRING (bdr IN raw, data_type IN varchar2)
  RETURN varchar2
  DETERMINISTIC 
AS
BEGIN
  RETURN CASE WHEN (bdr IS NULL) THEN NULL
              WHEN (data_type LIKE '%CHAR%') THEN cast_raw_to_char(bdr) 
              WHEN (data_type = 'NUMBER') THEN TO_CHAR(cast_raw_to_number(bdr))
              WHEN (data_type = 'DATE' OR data_type LIKE '%TIMESTAMP%') THEN TO_CHAR(cast_raw_to_date(bdr))
         END;
END;
/

Now query the stats:

SELECT /*+ NO_MERGE(cs) NO_MERGE(tc) */
       cs.owner,
       cs.table_name,
       cs.column_name,
       cs.num_distinct,
       tc.data_type,
       cast_raw_to_display_string(cs.low_value,tc.data_type) low_value_display,
       cast_raw_to_display_string(cs.high_value,tc.data_type) high_value_display,
       cs.density,
       cs.num_nulls,
       cs.num_buckets,
       cs.last_analyzed,
       cs.sample_size,
       cs.global_stats,
       cs.user_stats,
       cs.notes,
       cs.avg_col_len,
       cs.histogram,
       cs.scope
  FROM dba_tab_col_statistics cs,
       dba_tab_cols tc
 WHERE cs.table_name = 'MYTABLE'
   AND cs.owner = tc.owner
   AND cs.table_name = tc.table_name
   AND cs.column_name = tc.column_name

Run this when your query is bad (before gathering stats) to see if the max value is significantly lower than it should be (and on dates that might be a couple weeks behind or more). This is another indication that stats aren't getting gathered on time. You may have to adjust your stats gathering window or add a manual stats call (without overriding any parameters) to the code that loads the data, just after the load is complete.

If all else fails and you can't get the stats timing to jive with your application patterns, you can always throw in the stats-towel and just hint the query with a CARDINALITY or OPT_ESTIMATE hint to tell Oracle to expect a million rows from that table:

SELECT /*+ CARDINALITY(x,1000000) */ *
  FROM mytable x,
       someothertable y
 WHERE x.joinid = y.id

SELECT /*+ OPT_ESTIMATE(TABLE x ROWS=1000000) */ *
  FROM mytable x,
       someothertable y
 WHERE x.joinid = y.id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.