concurrent statistics gathering on Oracle 11g partiitioned table

Question

I am developing a DWH on Oracle 11g. We have some big tables (250+ million rows), partitioned by value. Each partition is a assigned to a different feeding source, and every partition is independent from others, so they can be loaded and processed concurrently.

Data distribution is very uneven, we have partition with millions rows, and partitions with not more than a hundred rows, but I didn't choose the partitioning scheme, and by the way I can't change it.

Considered the data volume, we must assure that every partition has always up-to-date statistics, because if the subsequent elaborations don't have an optimal access to the data, they will last forever.

So for each concurrent ETL thread, we

1. Truncate the partition
2. Load data from staging area with

SELECT /*+ APPEND */ INTO big_table PARTITION(part1) FROM temp_table WHERE partition_colum = PART1

(this way we have direct path and we don't lock the whole table)

3. We gather statistics for the modified partition.

In the first stage of the project, we used the APPROX_GLOBAL_AND_PARTITION strategy and worked like a charm

 dbms_stats.gather_table_stats(ownname=>myschema,
                              tabname=>big_table,
                              partname=>part1,
                              estimate_percent=>1,
                              granularity=>'APPROX_GLOBAL_AND_PARTITION',
                              CASCADE=>dbms_stats.auto_cascade,
                              degree=>dbms_stats.auto_degree) 

But, we had the drawback that, when we loaded a small partition, the APPROX_GLOBAL part was dominant (still a lot faster than GLOBAL) , and for a small partition we had, e.g., 10 seconds of loading, and 20 minutes of statistics.

So we have been suggested to switch to the INCREMENTAL STATS feature of 11g, which means that you don't specify the partition you have modified, you leave all parameters in auto, and Oracle does it's magic, automatically understanding which partition(s) have been touched. And it actually works, we have really speeded up the small partition. After turning on the feature, the call became

 dbms_stats.gather_table_stats(ownname=>myschema,
                              tabname=>big_table,
                              estimate_percent=>dbms_stats.auto_sample_size,
                              granularity=>'AUTO',
                              CASCADE=>dbms_stats.auto_cascade,
                              degree=>dbms_stats.auto_degree) 

notice, that you don't pass the partition anymore, and you don't specify a sample percent.

But, we're having a drawback, maybe even worse that the previous one, and this is correlated with the high level of parallelism we have.

Let's say we have 2 big partition that starts at the same time, they will finish the load phase almost at the same time too.

1. The first thread ends the insert statement, commits, and launches the stats gathering. The stats procedure notices there are 2 partition modified (this is correct, one is full and the second is truncated, with a transaction in progress), updates correctly the stats for both the partitions.

2. Eventually the second partition ends, gather the stats, it see all partition already updated, and does nothing (this is NOT correct, because the second thread committed the data in the meanwhile).

The result is:

PARTITION NAME | LAST ANALYZED        | NUM ROWS | BLOCKS | SAMPLE SIZE
-----------------------------------------------------------------------
PART1          | 04-MAR-2015 15:40:42 | 805731   | 20314  | 805731
PART2          | 04-MAR-2015 15:41:48 | 0        | 16234  | (null)

and the consequence is that I occasionally incur in not optimal plans (which mean killing the session, refresh manually the stats, manually launch the precess again).

I tried even putting an exclusive lock on the gathering, so no more than one thread can gather stats on the same table at once, but nothing changed.

IMHO this is an odd behaviour, because the stats procedure, the second time it is invoked, should check for the last commit on the second partition, and should see it's newer than the last stats gathering time. But seems it's not happening.

Am I doing something wrong? Is it an Oracle bug? How can I guarantee that all stats are always up-to-date with incremental stats feature turned on, and an high level of concurrency?

Answer 1

I managed to reach a decent compromise with this function.

PROCEDURE gather_tb_partiz(
    p_tblname IN VARCHAR2,
    p_partname IN VARCHAR2)
IS
  v_stale all_tab_statistics.stale_stats%TYPE;
BEGIN
  BEGIN
    SELECT stale_stats
    INTO v_stale
    FROM user_tab_statistics
    WHERE table_name = p_tblname
    AND object_type = 'TABLE';
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    v_stale := 'YES';
  END;
  IF v_stale = 'YES' THEN
    dbms_stats.gather_table_stats(ownname=>myschema, 
                                  tabname=> p_tblname,
                                  partname=>p_partname,
                                  degree=>dbms_stats.auto_degree,
                                  granularity=>'APPROX_GLOBAL AND PARTITION') ;
  ELSE
    dbms_stats.gather_table_stats(ownname=>myschema,
                                 tabname=>p_tblname,
                                 partname=>p_partname,
                                 degree=>dbms_stats.auto_degree,
                                 granularity=>'PARTITION') ;
  END IF;
END gather_tb_partiz;

At the end of each ETL, if the number of added/deleted/modified rows is low enough not to mark the table as stale (10% by default, can be tuned with STALE_PERCENT parameter), I collect only partition statistics; otherwise i collect global and partition statistics.

This keeps ETL of small partition fast, because no global partition must be regathered, and big partition safe, because any subsequent query will have fresh statistics and will likely use an optimal plan.

Incremental stats is anyway enabled, so whenever the global has to be recalculated, it is pretty fast because aggregates partition level statistics and does not perform a full scan.

I am not sure if, with incremental enabled, "APPROX_GLOBAL AND PARTITION" and "GLOBAL AND PARTITION" do differ in something, because both incremental and approx do basically the same thing: aggregate stats and histograms without doing a full scan.

Answer 2

Have you tried to have incremental statistics on, but still explicitly name a partition to analyze?

 dbms_stats.gather_table_stats(ownname=>myschema,
                              tabname=>big_table,
                              partname=>part,
                              degree=>dbms_stats.auto_degree);