Click to add to Favorites Is Table SCHEMA.CMP4$222224 Or Similar Related To Compression Advisor?
Oracle Database - Enterprise Edition - Version 188.8.131.52 and later
Information in this document applies to any platform.
< currency check done on 03 Nov16 >
The database is at Oracle 184.108.40.206 BP1 or higher.
After applying this patch, we are starting to see tables with names that include "CMP", ie CMP4$222224.
Is this related to the compression advisor?
Yes, the tables with "CMP" in their name are related to Compression Advisor.
The following test confirms the change in behavior from 220.127.116.11 to 18.104.22.168
drop table foo;
CREATE TABLE FOO
PARTITION BY RANGE(object_id)
(PARTITION k1 VALUES LESS THAN(10000),
PARTITION k2 VALUES LESS THAN(20000),
PARTITION k3 VALUES LESS THAN(30000),
PARTITION k4 VALUES LESS THAN(40000),
PARTITION k5 VALUES LESS THAN(50000),
PARTITION maxpart VALUES LESS THAN(maxvalue)
) storage (initial 64k next 1k)
as select * from dba_objects;
alter session set tracefile_identifier = 'CompTest';
alter session set events '10046 trace name context forever, level 12';
set serveroutput on
DBMS_COMPRESSION.GET_COMPRESSION_RATIO ('USERS', 'STEVE', 'FOO', NULL, DBMS_COMPRESSION.COMP_FOR_OLTP, blkcnt_cmp, blkcnt_uncmp, row_cmp, row_uncmp, cmp_ratio, comptype_str);
DBMS_OUTPUT.PUT_LINE('Block count compressed = ' || blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Block count uncompressed = ' || blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || row_cmp);
DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || row_uncmp);
DBMS_OUTPUT.PUT_LINE('Compression type = ' || comptype_str);
DBMS_OUTPUT.PUT_LINE('Compression ratio = '||blkcnt_uncmp/blkcnt_cmp||' to 1');
DBMS_OUTPUT.PUT_LINE('Compression ratio org= '||cmp_ratio);
In 22.214.171.124, you will see the following in the file generated by the 10046 tracing. The trace file will be located in user_dump_dest.
create table "STEVE".DBMS_TABCOMP_TEMP_UNCMP tablespace "USERS" nologging as
select /*+ DYNAMIC_SAMPLING(0) FULL("STEVE"."FOO") */ * from "STEVE"."FOO"
partition("K4") sample block( 99) mytab
create table "STEVE".DBMS_TABCOMP_TEMP_CMP organization heap tablespace
"USERS" compress for all operations nologging as select /*+
DYNAMIC_SAMPLING(0) */ * from "STEVE".DBMS_TABCOMP_TEMP_UNCMP mytab
In 126.96.36.199 you will see the following (extracted using logminer). The 10046 did not show the complete table ddl.
create table "STEVE".CMP3$88493 tablespace "USERS" nologging as select /*+ DYNAMIC_SAMPLING(0) FULL("STEVE"."FOO") */ * from " STEVE"."FOO" sample block( 99)
create table "STEVE".CMP4$88493 organization heap tablespace "USERS" compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from "STEVE".CMP3$88493 mytab;
The reason for the change in the naming of the interim tables is to allow concurrent sessions to execute the above procedure (the compression advisor) e.g. for different objects of the same schema (e.g. for different tables of the same schema or for different partitions of the same table). The name of the interim table is now constructed by using the object_id of the object that the above procedure (the compression advisor) is called for.
If you see tables with names like CMP3$xxxxxx or CMP4$xxxxxx (where xxxxxx is a number) left over after running Compression Advisor, it is likely because Compression Advisor failed at some point. These are interim tables created/used by Compression Advisor, which are normally dropped when it completes. You can safely drop those tables.