A problem with dbms_stats.copy_table_stats

Last week, when I was tuning a SQL statement at a database, I came across a rather interesting issue.

The SQL was performing poorly because of an inefficient plan (wrong join order), and it was relatively straighforward to determine the reason for that: a table partition appeared empty on the plan, while in fact it contained a few hundred thousand records. The table was range-partitioned on a timestamp column (1 partition = 1 day).

As the development team explained to me, they were relying on a rather sophisticated maintenance procedure that was looking over a previous period to chose a suitable partition as the source, and then copied its stats into a newly created partition before it was filled with data to prevent it from being analyzed as empty.

However, copying stats wasn’t helping! EXPLAIN PLAN was showing cardinality = 1 for this partition, despite num_rows = 677700 in DBA_TAB_PARTITIONS. It took me a while to reproduce the problem on my local database, but once I succeeded in that, the rest was simple because I could use 10053 event tracing.

Here is a “toy model” that I used to look into the issue (I used list partitioning because it illustrates the main problem more clearly):

create table mytable (flag char(1), x number) partition by LIST(flag)
partition p_a values ('A'),
partition p_b values ('B')

create unique index uq$mytable on mytable(flag, x) local;

insert into mytable select 'A', level from dual connect by level <= 1e5;


exec dbms_stats.gather_table_stats('FDR', 'MYTABLE',
     method_opt=> 'for all columns size 1');

exec dbms_stats.copy_table_stats('FDR', 'MYTABLE', 'P_A', 'P_B');

explain plan for SELECT * FROM MYTABLE WHERE flag = 'B';

select * from dbms_xplan.display();


Plan hash value: 867565752

| Id  | Operation             | Name       | Rows  | Time     |
|   0 | SELECT STATEMENT      |            |     1 | 00:00:01 |
|   1 |  PARTITION LIST SINGLE|            |     1 | 00:00:01 |
|*  2 |   INDEX RANGE SCAN    | UQ$MYTABLE |     1 | 00:00:01 |

Predicate Information (identified by operation id):

   2 - access("FLAG"='B')

The 10053 trace file showed that the optimizer estimated index selectivity to be 1e-5. This convinced me that the problem was not that the optimizer thought that the partition was empty despited DBA_TAB_PARTITIONS.num_rows, it was something else… so I checked the range of column values:

select partition_name, low_value, high_value
from dba_part_col_statistics
where table_name = 'MYTABLE'
and column_name = 'FLAG';


P_A 41 41
P_B 41 41

Apparently, the optimizer “forgot” to update low and high values for the partitioning key when copying stats from one partition to another… A search in MOS bug database showed that this bug was known and there was a patch for it:

7381308 Adjusting MIN/MAX of partition column (#5643297) does not work


The page that contains the patch mentions several bug numbers for this behavior (oddly enough, one of them won’t even open), but I think the most closely matching is 785108.1, according to which this behavior is observed on versions through (and indeed, the test case above didn’t work on my 11.2 database).

2 thoughts on “A problem with dbms_stats.copy_table_stats”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s