A problem with dbms_stats.copy_table_stats

Last week, when I was tuning a SQL statement at a 10.2.0.4 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;

commit;

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

https://support.oracle.com/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=h09a4q76()),(page=KBNavigator&id=h09a4q7i(enterprise=true&defaultSearch=true&clickstreamSource=Bookmark&cmd=getAllNodes&docsRequested=20&back=true&startIndex=1&requestedFacets=5,587&facetIdClicked=&userQuery=7381308&powerview_search=&filterSource=KB)),(page=PatchDetailPage&id=h09a5a9u(platformId=23&releaseId=80102040&patchName=7381308&patchId=7381308))

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 10.2.0.4 through 11.1.0.7 (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