Is Table SCHEMA.CMP4$222224 Or Similar Related To Compression Advisor

郑全

Is Table SCHEMA.CMP4$222224 Or Similar Related To Compression Advisor? (文档 ID 1606356.1)

Applies to:  Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
GoalThe database is at Oracle 11.2.0.4 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?

SolutionYes, the tables with "CMP" in their name are related to Compression Advisor.

The following test confirms the change in behavior from 11.2.0.3 to 11.2.0.4


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
DECLARE
blkcnt_cmp pls_integer;
blkcnt_uncmp pls_integer;
row_cmp pls_integer;
row_uncmp pls_integer;
cmp_ratio pls_integer;
comptype_str varchar2(100);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO ('USERS', 'XYZ', '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);
END;
/

exit


In 11.2.0.3, 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 "XYZ".DBMS_TABCOMP_TEMP_UNCMP tablespace "USERS" nologging as
  select /*+ DYNAMIC_SAMPLING(0) FULL("STEVE"."FOO") */ * from "XYZ"."FOO"
  partition("K4") sample block( 99) mytab



create table "XYZ".DBMS_TABCOMP_TEMP_CMP organization heap tablespace
  "USERS" compress for all operations nologging as select /*+
  DYNAMIC_SAMPLING(0) */ * from "XYZ".DBMS_TABCOMP_TEMP_UNCMP mytab



In 11.2.0.4 you will see the following (extracted using logminer).  The 10046 did not show the complete table ddl.


create table "XYZ".CMP3$88493 tablespace "USERS" nologging as select /*+ DYNAMIC_SAMPLING(0) FULL("XYZ"."FOO") */ * from " XYZ"."FOO" sample block( 99)
mytab ;

create table "XYZ".CMP4$88493 organization heap tablespace "USERS" compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from "XYZ".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.