Multiple Adverts

Just a couple of announcements:

1. I had the message already a couple of days added to the "Upcoming Public Appearances" sidebar on the right hand side of this page, now the official home page is available and registration open for the "CBO Days" at Trivadis 11th and 12th December in Zurich.

Join Mohamed Zait (Manager of the Query Optimizer Group at Oracle), Maria Colgan (Senior Principal Product Manager at Oracle), Jonathan Lewis, Jože Senegačnik, Christian Antognini and myself for two days fully focused on the Cost-Based Optimizer. You will not only learn about the history and present, but also the future of this very important Oracle Database component.

2. Oracle University is going to organize my one day master class "Mastering Parallel Execution" from December on.

This seminar focuses on the building blocks of Parallel Execution: At the end of the day you should be able to really understand how Parallel Execution works, how to read Parallel Execution plans and in particular how to diagnose Parallel Execution issues like skewed data distribution using advanced tools like Real-Time SQL Monitoring and Active Session History.

So after this session you should have the knowledge to fully leverage the power of Parallel Query and Execution.

Ideally as a prerequisite you should already have a good understanding of serial SQL execution and how to read serial execution plans.

If you are interested, get in touch with Oracle University so that they can plan accordingly.

3. Just a short reminder that the DOAG organizes another Webinar about "Cost-Based Optimizer Basics" which is free for DOAG members and takes place at Friday, 12th October 11am

4. The next "DOAG News" will include a accompanying article about "Cost-Based Optimizer Basics", where I provide a summary of the Webinar contents.

5. And last but not least, after OOW rush is over I expect OTN to publish a mini-series on "Parallel Execution" that gives more insights into contents of the master class mentioned above.

DOAG 2012

This year again I'm going to present at the DOAG 2012 conference in November. Since it is a popular topic I decided to talk about "Cost-Based Optimizer Basics" there, too. According to the official schedule the presentation will take place on Thursday, the 22nd of November, "Raum 1" at 13:00.

Like last year, I'll also try to do some Unconference sessions in addition - if it is going to take place, which I don't know yet.

I'll post some updates as soon as I know more details.

Exchange Partition, Virtual Columns And Column Statistics

Here is an odd bug that can lead to some nasty side effects when using the EXCHANGE PARTITION technique. It is probably there for a very long time, simply because it depends on the usage of virtual columns, and the basic technique of virtual columns was introduced way back in the Oracle 8i times with the introduction of Function Based Indexes.

The problem isn't the exchange partition operation itself, but the accompanying swap of object statistics information, in particular the column statistics.

Look the following sequence of DDL and DML commands and pay then special attention to the output for the column statistics before and after the EXCHANGE PARTITION operation:

-- EXCHANGE_PARTITION_VIRTUAL_COLS.SQL -- -- Simple example that demonstrates that differences in Virtual Columns -- between two tables do not prevent an EXCHANGE PARTITION operation -- -- But Column Statistics are screwed up after the EXCHANGE PARTITION operation -- if you have inconsistencies in the INTERNAL_COLUMN_IDs caused by the Virtual Columns -- -- Either EXCHANGE PARTITION should fail or handle this correctly -- -- Reproduced: 10.2.0.4, 10.2.0.5, 11.2.0.1, 11.2.0.2, 11.2.0.3 set echo on linesize 400 column column_name format a30 drop table t_x; purge table t_x; drop table t; purge table t; -- The partitioned table create table t partition by range (id) ( partition p_default values less than (maxvalue) ) as select rownum as id , 'DESC' || rownum as attr000 , rpad('x', 100) as some_char from dual connect by level <= 10000 ; -- Add a virtual column here alter table t add (virt_col as (attr000 || 'BLA')); -- Note that the same problem will show up with other methods that generate a virtual column -- -- So one of the below statements instead of above explicit virtual column definition will show a similar problem in column statistics -- -- exec dbms_stats.gather_table_stats(null, 'T', method_opt => 'for columns (attr000 || ''BLA'') size 1') -- create index t_idx on t (attr000 || 'BLA'); -- Add two non-virtual columns afterwards alter table t add (non_virt_col1 varchar2(100), non_virt_col2 varchar2(100)); -- Some data update t set non_virt_col1 = 'y', non_virt_col2 = 'z'; commit; -- The non-partitioned table create table t_x as select rownum as id , 'DESC' || rownum as attr000 , rpad('x', 100) as some_char , cast('y' as varchar2(100)) as non_virt_col1 -- Data is different from T for this column , cast('a' as varchar2(100)) as non_virt_col2 from dual connect by level <= 10000 ; exec dbms_stats.gather_table_stats(null, 't') exec dbms_stats.gather_table_stats(null, 't_x') -- Correct Column Statistics here for both objects select b.internal_column_id , a.column_name , a.num_distinct , a.low_value , a.high_value , a.density from user_part_col_statistics a , user_tab_cols b where a.table_name = 'T' and b.table_name = 'T' and a.partition_name = 'P_DEFAULT' and a.table_name = b.table_name and a.column_name = b.column_name order by b.internal_column_id; select b.internal_column_id , a.column_name , a.num_distinct , a.low_value , a.high_value , a.density from user_tab_col_statistics a , user_tab_cols b where a.table_name = 'T_X' and b.table_name = 'T_X' and a.table_name = b.table_name and a.column_name = b.column_name order by b.internal_column_id; -- Exchange doesn't complain since on physical level the tables are identical -- But logically the INTERNAL COLUMN_IDs are different -- Hence the Statistics on partition level of T and global level of T_X are now screwed up for all columns that have different INTERNAL COLUMN_IDs in the two tables alter table t exchange partition p_default with table t_x including indexes without validation; -- Bad Column Statistics here, see for example T.VIRT_COL or T_X.NON_VIRT_COL1 select b.internal_column_id , a.column_name , a.num_distinct , a.low_value , a.high_value , a.density from user_part_col_statistics a , user_tab_cols b where a.table_name = 'T' and b.table_name = 'T' and a.partition_name = 'P_DEFAULT' and a.table_name = b.table_name and a.column_name = b.column_name order by b.internal_column_id; select b.internal_column_id , a.column_name , a.num_distinct , a.low_value , a.high_value , a.density from user_tab_col_statistics a , user_tab_cols b where a.table_name = 'T_X' and b.table_name = 'T_X' and a.table_name = b.table_name and a.column_name = b.column_name order by b.internal_column_id; column column_name clear

The relevant Column Statistics for T before and after the exchange partition operation:

INTERNAL_COLUMN_ID COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY ------------------ ------------------------------ ------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- 1 ID 10000 C102 C302 .0001 2 ATTR000 10000 4445534331 4445534339393939 .0001 3 SOME_CHAR 1 7820202020202020202020202020202020202020202020202020202020202020 7820202020202020202020202020202020202020202020202020202020202020 1 4 VIRT_COL 10000 444553433130303030424C41 4445534339424C41 .0001 5 NON_VIRT_COL1 1 79 79 1 6 NON_VIRT_COL2 1 7A 7A 1 INTERNAL_COLUMN_ID COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY ------------------ ------------------------------ ------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- 1 ID 10000 C102 C302 .0001 2 ATTR000 10000 4445534331 4445534339393939 .0001 3 SOME_CHAR 1 7820202020202020202020202020202020202020202020202020202020202020 7820202020202020202020202020202020202020202020202020202020202020 1 4 VIRT_COL 1 79 79 1 5 NON_VIRT_COL1 1 61 61 1 6 NON_VIRT_COL2

and for T_X:

INTERNAL_COLUMN_ID COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY ------------------ ------------------------------ ------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- 1 ID 10000 C102 C302 .0001 2 ATTR000 10000 4445534331 4445534339393939 .0001 3 SOME_CHAR 1 7820202020202020202020202020202020202020202020202020202020202020 7820202020202020202020202020202020202020202020202020202020202020 1 4 NON_VIRT_COL1 1 79 79 1 5 NON_VIRT_COL2 1 61 61 1 INTERNAL_COLUMN_ID COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY ------------------ ------------------------------ ------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- 1 ID 10000 C102 C302 .0001 2 ATTR000 10000 4445534331 4445534339393939 .0001 3 SOME_CHAR 1 7820202020202020202020202020202020202020202020202020202020202020 7820202020202020202020202020202020202020202020202020202020202020 1 4 NON_VIRT_COL1 10000 444553433130303030424C41 4445534339424C41 .0001 5 NON_VIRT_COL2 1 79 79 1

Not only this can lead to situations where columns simply end up with wrong (shifted) or no column statistics at all, but it is even possible to have columns with statistics that are simply illegal because the internal representation of the low / high values for example isn't compatible with the actual column data type.

If you think about how that information is stored in the data dictionary (along the so called INTERNAL column id, there is also a plain COLUMN_ID for display purposes and a SEGMENT column id that defines how the columns are stored in the blocks) and how Oracle very likely performs the swap of object statistic when exchanging partitions the potential problem becomes obvious.

It is however very unlikely to hit that bug for several reasons:

- There are several reasons why you usually want to gather the statistics on the exchanged partition after the exchange took place. If you want to make use of the SIZE AUTO functionality that automatically determines the generation of column histograms based on column usage, or want to ensure that higher level statistics are maintained via aggregation, or if you want to benefit from more recent features like Incremental Partition Statistics, for example.

If you follow this recommended technique you won't have any problems on the partitioned object since the statistics gathered after the exchange operation won't be affected by the bug

However, depending on what you do with the swapped table segment, you might still encounter problems since, without refreshing the statistics on that table you'll potentially end up with screwed up column statistics.

- Until the arrival of more recent features like Virtual Columns and Extended Statistics that under the covers are based on the same technique it is very likely that both objects involved in the exchange partition operation are carefully maintained synchronously with regard to structural changes like column or index additions, since otherwise the EXCHANGE PARTITION operation might fail with corresponding errors

From 11g on however you can far simpler fall into the trap of getting out-of-sync internal column ids by simply gathering Extended Statistics on or by adding a Virtual Column to only one of the two objects. The EXCHANGE PARTITION operation won't fail, but if the Virtual Columns were added in a different order or real columns were added afterwards (to both objects) the column statistics of the affected columns will be screwed up.

Note that the official documentation is not exactly helpful, since it explicitly says here that the Virtual Columns don't need to be in sync between the two objects because only the real columns are relevant for the EXCHANGE PARTITION operation itself.

Furthermore from a pure technical point of view it is a legal approach to gather statistics on the table segment to be exchanged into the partitioned object and rely on EXCHANGE PARTITION to swap properly the statistics between the two segments, which in this case only affects the partition level statistics of the particular partition being exchanged (unless you play with the undocumented parameter "_minimal_stats_aggregation") and the table level statistics of the unpartitioned object.

If you follow this practice and do some of the following:

- Add Function Based Indexes to only one of the objects or both objects in different order, or mixed with additions of other, real columns

- Add Extended Statistics or Virtual Columns only to one of the objects and afterwards add real columns

then the resulting column statistics will be screwed up for all columns that don't have a matching INTERNAL column id.

Oracle obviously simply just swaps the two object_ids in the underlying statistics dictionary tables, which is the most obvious and efficient operation, but unfortunately produces wrong column statistics results if the INTERNAL column ids don't match.

The operation would probably be much more complex if Oracle tried to handle this situation correctly.

The possible workarounds are simple:

- Either prevent such a situation upfront, by ensuring that all operations including those that generate Virtual Columns are always done in sync to the two objects involved

- Gather statistics on both objects after the exchange partition operation took place

The client has opened an SR; it will be interesting to hear what Oracle says.