Partition Change Tracking with Surprise

Partition Change Tracking (PCT) is a powerful feature in Oracle for fast refresh of materialized views when one of the base tables is partitioned. But under certain conditions, PCT does not work as expected and returns an unpleasant surprise.

You probably know this situation: The examples in the Oracle documentation, in books, trainings or blogs look simple and easy to understand. But when using the same features in a real project, it may happen that something does not work as expected. This week I had such an experience in a customer project. The fast refresh of a materialized views fails with an ORA-600, and we first could not see an explanation for it. After some research, I found the reason and implemented a simplified test case. Finally, we found a workaround, but nevertheless I hope that the problem will be solved soon by Oracle.

The Data Warehouse Design

The DWH database we are looking at contains several star schemas with dimension and fact tables. All fact tables are monthly partitioned on the time dimension key. To improve query performance in the data marts, some materialized views with monthly aggregations were created. These materialized views are monthly partitioned, too. To refresh the materialized views, Partition Change Tracking is used.

The time dimension uses a numeric date key (not my favorite, but feasible, see my blog post Time Dimension Keys and Partitioning). Additionally to the rows for every day, a date key for every month with DATE_LEVEL = ‘Month’ is stored. These monthly rows caused the ORA-600, as we will see later.

  DATE_ID CALENDAR_DATE CALENDAR_MONTH DATE_LEVEL

201508 01-AUG-15 201508 Month
20150801 01-AUG-15 201508 Day
20150802 02-AUG-15 201508 Day
20150803 03-AUG-15 201508 Day
20150804 04-AUG-15 201508 Day
...
20150828 28-AUG-15 201508 Day
20150829 29-AUG-15 201508 Day
20150830 30-AUG-15 201508 Day
20150831 31-AUG-15 201508 Day

The fact tables are RANGE partitioned by DATE_ID, the dimension key to the time dimension. A partition is created for every month. Foreign keys are created with NOVALIDATE RELY, and a bitmap index is created on each dimension key of the fact tables. Nothing extraordinary so far, everything looks as recommended.

To improve query performance, monthly aggregations are provided in a materialized view for each fact table. The materialized views contain all dimension keys and measures of the fact table, but are aggregated on column CALENDAR_MONTH of the time dimension. The materialized views are RANGE partitioned by month, with column CALENDAR_MONTH as the partition key. To enable Fast Refresh with Partition Change Tracking, additional expressions are included in each materialized view: DBMS_MVIEW.PMARKER, COUNT(measure), COUNT(*), as shown in the following example:

CREATE MATERIALIZED VIEW MV_EXAMPLE_MONTH
PARTITION BY RANGE (CALENDAR_MONTH)
(
PARTITION P_2015_01 VALUES LESS THAN (201502),
...
)
REFRESH FAST ENABLE QUERY REWRITE AS
SELECT DBMS_MVIEW.PMARKER(f.ROWID)
, d.CALENDAR_MONTH
, f.DIMENSION_KEY_1
, f.DIMENSION_KEY_2
, f.DIMENSION_KEY_3
, f.DIMENSION_KEY_4
, SUM(f.MEASURE_1) AS MEASURE_1
, SUM(f.MEASURE_2) AS MEASURE_2
, SUM(f.MEASURE_3) AS MEASURE_3
, COUNT(f.MEASURE_1)
, COUNT(f.MEASURE_2)
, COUNT(f.MEASURE_3)
, COUNT(*)
FROM FCT_EXAMPLE f, DIM_DATE d
WHERE f.DATE_ID = d.DATE_ID
GROUP BY DBMS_MVIEW.PMARKER(f.ROWID)
, d.CALENDAR_MONTH
, f.DIMENSION_KEY_1
, f.DIMENSION_KEY_2
, f.DIMENSION_KEY_3
, f.DIMENSION_KEY_4

The Refresh Surprise

With this design, it is possible to do a PCT fast refresh after loading new data into the underlying fact table. Only the changed partition of the fact table (usually the current month) has to be read by the refresh procedure and applied to the corresponding partition of the materialized view. The refresh is done with the following procedure call. The parameter method => ‘P’ indicated that a PCT refresh is executed, atomic_refresh => FALSE defines that a TRUNCATE instead of a DELETE is performed for the affected partitions.

DBMS_MVIEW.REFRESH(list=>'MV_FACT_MONTH',method=>'P',atomic_refresh=>FALSE);

The result of this procedure call should be a refreshed materialized view with up-to-date data for the current month. In our case, the result was an internal Oracle error:

ORA-12008: error in materialized view refresh path
ORA-00600: internal error code, arguments: [kkpogrfp2], [4294967295], [0], [8], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 85
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2199
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2778
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 2

Interesting detail: The problem happened on an Oracle 11.2.0.3 database, but can be reproduced with Oracle 12.1.0.2, too. However, the ORA-600 does not happen when I run the same script on an Oracle 11.2.0.1 version.

The Workarounds

After playing around with the test case, we found several workarounds to avoid the ORA-600:

  • With atomic_level => TRUE, the PCT refresh works without an error. This workaround is not feasible because of a longer refresh time.
  • Without partitioning on the materialized view, the PCT refresh works, but this has a bad impact on query performance and maintenance of the data marts.
  • An additional WHERE condition DATE_LEVEL = ‘Day’ in the materialized view avoids the ORA-600, but this condition had to be applied to all user queries to allow query rewrite.
  • When the rows with DATE_LEVEL = ‘Month’ are deleted from the time dimension, the PCT refresh works without an ORA-600.
Finally, the customer decided to store only the rows with DATE_LEVEL = ‘Day’ in the time dimension. After this change, the PCT fast refresh runs without an ORA-600. But the price for this solution is a redesign of some fact tables that store fact data on monthly level.
 

The Conclusion

Although my customer found a workaround that helped to solve his problem, I’m still  confused about this bug. I see no obvious explanation for the failure of a Fast Refresh with Partition Change Tracking, only because some additional rows in the time dimension. What is the impact of these monthly rows to a PCT refresh, when they are not referred from the underlying fact table? I hope, Oracle support will find the cause for this ORA-600. To help them, I will open a Service Request and send them the test case below. Hopefully, the error can be fixed in a future release.

The Test Case

If you want to check whether the problem occurs in your database version, here the complete test case:

DROP TABLE DIM_DATE PURGE;
DROP TABLE FCT_FACT PURGE;
DROP MATERIALIZED VIEW MV_FACT_MONTH;

--------------------------------------------------------------------------------
-- Create and fill time dimension DIM_DATE
--------------------------------------------------------------------------------
CREATE TABLE DIM_DATE AS
SELECT TO_NUMBER(TO_CHAR(TRUNC(SYSDATE, 'YEAR') + ROWNUM-1, 'YYYYMMDD')) AS DATE_ID
, (TRUNC(SYSDATE, 'YEAR') + ROWNUM -1) AS CALENDAR_DATE
, TO_NUMBER(TO_CHAR((TRUNC(SYSDATE, 'YEAR') + ROWNUM-1), 'YYYYMM')) AS CALENDAR_MONTH
, CAST('Day' AS VARCHAR2(10)) AS DATE_LEVEL
FROM dual CONNECT BY ROWNUM <= 365;

-- insert additional rows for monthly level (they cause the ORA-600)
INSERT INTO DIM_DATE (DATE_ID, CALENDAR_DATE, CALENDAR_MONTH, DATE_LEVEL)
SELECT TO_NUMBER(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), ROWNUM-1), 'YYYYMM')) AS DATE_ID
, ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), ROWNUM-1) AS CALENDAR_DATE
, TO_NUMBER(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), ROWNUM-1), 'YYYYMM')) AS CALENDAR_MONTH
, CAST('Month' AS VARCHAR2(10)) AS DATE_LEVEL
FROM dual CONNECT BY ROWNUM <= 12;

COMMIT;

CREATE MATERIALIZED VIEW LOG ON DIM_DATE
WITH SEQUENCE, ROWID (DATE_ID, CALENDAR_DATE, CALENDAR_MONTH, DATE_LEVEL)
INCLUDING NEW VALUES;

--------------------------------------------------------------------------------
-- Create fact table FCT_FACT
--------------------------------------------------------------------------------
CREATE TABLE FCT_FACT
( DATE_ID NUMBER,
AMOUNT NUMBER
)
PARTITION BY RANGE (DATE_ID)
(
PARTITION P_2015_01 VALUES LESS THAN (20150201),
PARTITION P_2015_02 VALUES LESS THAN (20150301),
PARTITION P_2015_03 VALUES LESS THAN (20150401),
PARTITION P_2015_04 VALUES LESS THAN (20150501),
PARTITION P_2015_05 VALUES LESS THAN (20150601),
PARTITION P_2015_06 VALUES LESS THAN (20150701),
PARTITION P_2015_07 VALUES LESS THAN (20150801),
PARTITION P_2015_08 VALUES LESS THAN (20150901)
);

--------------------------------------------------------------------------------
-- Create materialized view MV_FACT_MONTH
--------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW MV_FACT_MONTH
PARTITION BY RANGE (CALENDAR_MONTH)
(
PARTITION P_2015_01 VALUES LESS THAN (201502),
PARTITION P_2015_02 VALUES LESS THAN (201503),
PARTITION P_2015_03 VALUES LESS THAN (201504),
PARTITION P_2015_04 VALUES LESS THAN (201505),
PARTITION P_2015_05 VALUES LESS THAN (201506),
PARTITION P_2015_06 VALUES LESS THAN (201507),
PARTITION P_2015_07 VALUES LESS THAN (201508),
PARTITION P_2015_08 VALUES LESS THAN (201509)
)
REFRESH FAST ENABLE QUERY REWRITE AS
SELECT DBMS_MVIEW.PMARKER(f.ROWID)
, d.CALENDAR_MONTH
, SUM(f.AMOUNT) AS AMOUNT
, COUNT(f.AMOUNT)
, COUNT(*)
FROM FCT_FACT f, DIM_DATE d
WHERE f.DATE_ID = d.DATE_ID
GROUP BY DBMS_MVIEW.PMARKER(f.ROWID)
, d.CALENDAR_MONTH
/

BEGIN
DBMS_MVIEW.REFRESH(list=>'MV_FACT_MONTH',method=>'P',atomic_refresh=>FALSE);
END;
/

--------------------------------------------------------------------------------
-- Test Case: Insert some data into fact table, then refresh materialized view
--------------------------------------------------------------------------------

INSERT INTO FCT_FACT
SELECT TO_NUMBER(TO_CHAR(TRUNC(SYSDATE, 'YEAR') + ROWNUM, 'YYYYMMDD')) AS DATE_ID
, 100 AS AMOUNT
FROM dual CONNECT BY ROWNUM <= 20;

COMMIT;

BEGIN
DBMS_MVIEW.REFRESH(list=>'MV_FACT_MONTH',method=>'P',atomic_refresh=>FALSE);
END;
/

--------------------------------------------------------------------------------
-- >>> fails with ORA-00600: internal error code, arguments: [kkpogrfp2]
--------------------------------------------------------------------------------
 
Advertisements

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