Martins Blog

Trying to explain complex things in simple terms

HCC error on Exadata after partitioning maintenance

Posted by Martin Bach on January 7, 2014

Recently I have been asked to investigate the following error on an Exadata system.

ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type

Well, that’s simple I thought! Must be (d)NFS mounted storage, right? Everyone knows that you can have HCC on Exadata (and a few other storage products). So I looked at the problem and soon found out that the data files in question all resided on the cells. Here is the sequence of events:

  1. HCC is introduced to a range-list partitioned table, residing on TBS1 (all segments, no exception). The range partitions are in fact interval partitions
  2. Subpartitions are moved to TBS2, this way they are compressed
  3. TBS1 is no longer needed and dropped

Now when we tried to insert a row into the table we get the above error. As with so many Oracle errors, this one is not very helpful in the situation. The error only occurs when a new partition is automatically created… so here is the test case to demonstrate the problem.

create table martin.T1 (id, c, name)
tablespace tbs1
partition by range (id)
interval (1000) store in (tbs1)
subpartition by list (c) subpartition template
(
  subpartition sp1 values ('0'),
  subpartition sp2 values ('1'),
  subpartition sp3 values ('2'),
  subpartition sp4 values ('3'),
  subpartition sp5 values ('4')
)
(
  partition p_man values less than (1000) tablespace tbs1
)
as select object_id, to_char(mod(rownum,4)) c, object_name from dba_objects;

You can see for yourself that everything is neatly stored on TBS1:

MARTIN:mbach1> select count(*),tablespace_name
  2 from dba_segments where segment_name = 'T1'
  3 group by tablespace_name;

  COUNT(*) TABLESPACE_NAME
---------- ------------------------------
       399 TBS1

Personal thoughts

Two comments about the above configuration.

There are 400 entries in user_tab_subpartitions, but only 399 in user_segments. It turned out that P_MAN_SP5 wasn’t created due to deferred segment creation which has been left at its default value of TRUE. Interestingly all subpartitions were created for the automatically created interval partition although I had a bug in my data generation query: mod(rownum,4) will NEVER generate a value of 4.

This leads to point 2-now you are wasting space! Due to _partition_large_extents default value in my database (actually from 11.2.0.2 onwards) any partition will be 8 MB in size, even if there are only 250 (or 0 as in this case!) records in it…

Enter HCC Compression

Now I enable compression (note this is propagated to partitions and subpartitions):

MARTIN:mbach1> alter table T1 compress for query high;

Table altered.

This doesn’t compress anything, it merely enables compression if rows are inserted using direct path operations. Therefore I need to move the subpartitions which causes them to be compressed. In this scenario I have a dedicated tablespace for the compressed data, TBS2.

MARTIN:mbach1> select count(*),tablespace_name from dba_segments where segment_name = 'T1' group by tablespace_name;

   COUNT(*) TABLESPACE_NAME
----------- ------------------------------
        399 TBS2

Since all segments for T1 are on TBS2, I can drop TBS1 which I did.

Now what has changed? The best answer you can get is from DBMS_METADATA:

MARTIN:mbach1> select dbms_metadata.get_ddl('TABLE','T1') from dual;

  CREATE TABLE "MARTIN"."T1"
   (	"ID" NUMBER,
	"C" VARCHAR2(40),
	"NAME" VARCHAR2(128)
   ) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
 COMPRESS FOR QUERY HIGH
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS1"
  PARTITION BY RANGE ("ID") INTERVAL (1000) STORE IN ("TBS1")
  SUBPARTITION BY LIST ("C")
  SUBPARTITION TEMPLATE (
    SUBPARTITION "SP1" VALUES ( '0' ),
    SUBPARTITION "SP2" VALUES ( '1' ),
    SUBPARTITION "SP3" VALUES ( '2' ),
    SUBPARTITION "SP4" VALUES ( '3' ),
    SUBPARTITION "SP5" VALUES ( '4' ) )
 (PARTITION "P_MAN"  VALUES LESS THAN (1000)
PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS1"
 COMPRESS FOR QUERY HIGH
 ( SUBPARTITION "P_MAN_SP1"  VALUES ('0')
  TABLESPACE "TBS2"
 COMPRESS FOR QUERY HIGH ,
  SUBPARTITION "P_MAN_SP2"  VALUES ('1')
  TABLESPACE "TBS2"
 COMPRESS FOR QUERY HIGH ,
  SUBPARTITION "P_MAN_SP3"  VALUES ('2')
  TABLESPACE "TBS2"
 COMPRESS FOR QUERY HIGH ,
  SUBPARTITION "P_MAN_SP4"  VALUES ('3')
  TABLESPACE "TBS2"
 COMPRESS FOR QUERY HIGH ,
  SUBPARTITION "P_MAN_SP5"  VALUES ('4')
  TABLESPACE "TBS2"
 COMPRESS FOR QUERY HIGH ) )

As you can see only the subpartition definition changed and correctly reflect the move to TBS2. What’s going to hurt me is the reference to TBS1 in the STORE IN clause for the interval partition. And indeed:

MARTIN:mbach1> insert into T1 values(150000,'1', 'ICH BINS');
insert into T1 values(150000,'1', 'ICH BINS')
            *
ERROR at line 1:
ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type

Well I wonder if you could compress data on a non-existing tablespace ….

I tried various ways to change the store-in clause of the interval partition but for some reason didn’t find the correct syntax in the documentation Thanks to Andy Klock I finally managed to get around the issue, he found the correct syntax in a MOS note. And it does even exist in the documentation as alter_interval_partitioning clause.

Now I can force the new interval partitions to be created on TBS2:

MARTIN@MBACH1:1> alter table martin.t1 set store in (tbs2);

Table altered.

The resulting metadata shows the change:

  CREATE TABLE "MARTIN"."T1"
   (	"ID" NUMBER,
	"C" VARCHAR2(40),
	"NAME" VARCHAR2(128)
   ) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
 COMPRESS FOR QUERY HIGH
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS1"
  PARTITION BY RANGE ("ID") INTERVAL (1000) STORE IN ("TBS2")
  SUBPARTITION BY LIST ("C")
  SUBPARTITION TEMPLATE (
    SUBPARTITION "SP1" VALUES ( '0' ),
    SUBPARTITION "SP2" VALUES ( '1' ),
    SUBPARTITION "SP3" VALUES ( '2' ),
    SUBPARTITION "SP4" VALUES ( '3' ),
    SUBPARTITION "SP5" VALUES ( '4' ) )
 (PARTITION "P_MAN"  VALUES LESS THAN (1000)
PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS1"
 COMPRESS FOR QUERY HIGH
 ( SUBPARTITION "P_MAN_SP1"  VALUES ('0')
  TABLESPACE "TBS2"
 COMPRESS FOR QUERY HIGH ,
  SUBPARTITION "P_MAN_SP2"  VALUES ('1')
  TABLESPACE "TBS2"
 COMPRESS FOR QUERY HIGH ,
  SUBPARTITION "P_MAN_SP3"  VALUES ('2')
  TABLESPACE "TBS2"
 COMPRESS FOR QUERY HIGH ,
  SUBPARTITION "P_MAN_SP4"  VALUES ('3')
  TABLESPACE "TBS2"
 COMPRESS FOR QUERY HIGH ,
  SUBPARTITION "P_MAN_SP5"  VALUES ('4')
  TABLESPACE "TBS2"
 COMPRESS FOR QUERY HIGH ) )

It now is possible to insert data again:

MARTIN@MBACH1:1> insert into T1 values(150000,'1', 'ICH BINS');

1 row created.

MARTIN@MBACH1:1> insert into T1 values(50,'2', 'ICH BINS');

1 row created.

MARTIN@MBACH1:1> insert into T1 values(2050,'2', 'ICH BINS');

1 row created.

MARTIN@MBACH1:1> commit;

Commit complete.

All is well again. You could now change the default attributes of the table and all partitions to TBS2 using commands similar to these:

MARTIN@MBACH1:1> alter table t1 modify default attributes tablespace tbs2;

Table altered.

MARTIN@MBACH1:1> alter table t1 modify default attributes for partition p_man tablespace tbs2;

Table altered.

The last command obviously has to be repeated for the partitions that have already been created. The DDL for the table now looks more healthy:

MARTIN@MBACH1:1> select dbms_metadata.get_ddl('TABLE','T1') from dual;

DBMS_METADATA.GET_DDL('TABLE','T1')
--------------------------------------------------------------------------------

  CREATE TABLE "MARTIN"."T1"
   (	"ID" NUMBER,
	"C" VARCHAR2(40),
	"NAME" VARCHAR2(128)
   ) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
 COMPRESS FOR QUERY HIGH
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS2"
  PARTITION BY RANGE ("ID") INTERVAL (1000) STORE IN ("TBS2")
  SUBPARTITION BY LIST ("C")
  SUBPARTITION TEMPLATE (
    SUBPARTITION "SP1" VALUES ( '0' ),
    SUBPARTITION "SP2" VALUES ( '1' ),
    SUBPARTITION "SP3" VALUES ( '2' ),
    SUBPARTITION "SP4" VALUES ( '3' ),
    SUBPARTITION "SP5" VALUES ( '4' ) )
 (PARTITION "P_MAN"  VALUES LESS THAN (1000)
PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS2"
 COMPRESS FOR QUERY HIGH )

Problem solved! Although it shouldn’t really matter what’s in user_tab_partitions as these segments do not exist (only the subpartitions exist!)

Summary

The partition maintenance operation followed by dropping the default tablespace for new (interval) partitions caused Oracle to raise a misleading error message. Instead of the ORA-64307 error (stating the HCC is not supported on a void tablespace) a better error message would have been to let the user know that the tablespace did not exist.

This was on 11.2.0.3, I will try and test on 11.2.0.4 and 12.1.0.1 later and update the post.

About these ads

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

 
Follow

Get every new post delivered to your Inbox.

Join 2,313 other followers

%d bloggers like this: