CMP$ table in oracle

Liu Maclean(刘相兵

刘大 and 各位 大神    问个问题  ogg里面 提示错误如下
ERROR   OGG-01028  Oracle GoldenGate Capture for Oracle, ext1.prm:  Object with object number 117751 is compressed.
根据查询返现 对应的对象为

这个已经 exclude掉了  但是想问 这样的表是干啥的  mos里也没有找到信息

CMP3$104374             TABLE                  DROP
CMP4$104374             TABLE                  DROP
CMP4$104374             TABLE                  DROP
CMP4$104374             TABLE                  DROP
CMP4$104374             TABLE                  DROP
CMP4$104374             TABLE                  DROP
CMP3$104374             TABLE                  DROP
CMP3$104374             TABLE                  DROP


answer:

Support confirmed that these Compression Advisor tables CMP<x>$<object_id> will automatically dropped over a period of time and should not be dropped manually.

Liu Maclean(刘相兵

Click to add to Favorites                Is Table SCHEMA.CMP4$222224 Or Similar Related To Compression Advisor?
APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.
< currency check done on 03 Nov16 >
GOAL

The 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?


SOLUTION

Yes, 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', '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);
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 "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 11.2.0.4 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)
mytab ;

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.

Liu Maclean(刘相兵

11gR2 Compression Advisor = Evil

I recently upgraded to 11.2.0.2 from 11.1.0.7. Immediately after the upgrade, I noticed a spike in our redo generation. I also noticed tables with names like DBMS_TABCOMP_TEMP_UNCMP and DBMS_TABCOMP_TEMP_CMP. What are those tables doing in my production schemas and how did they get there? After digging, I was able to find out that these are created by the new 11gR2 Compression Advisor. During the nightly maintenance window, a scheduled job will look at your tables and take a subset of data and load into an uncompressed table (UNCMP). It will then compress your data (CMP) and determine the compression ratio based on your actual data. If the compression ratio is above a certain threshold, the advisor recommends the table for compression. After the Compression Advisor is done, the tables are deleted.
The problem is that the Compression Advisor generates a ton of redo! This has been noted as unpublished bug 8896202. More information can be found on Metalink Note 1284972.1 if you are interested.
I won’t be using the Compression Advisor so I’d like to turn off the nightly job. Unfortunately, the job is part of the Space Advisor and I cannot turn off the Compression Advisor on its own. Well the redo generation rate is simply too high and I guess I can live without the Space Advisor too, so off it goes:
SQL> exec dbms_auto_task_admin.disable(client_name=>'auto space advisor',operation=>null,window_name=>null);

PL/SQL procedure successfully completed.
I see that an enhancement request has been filed to decouple the Compression Advisor from the Space Advisor.

Liu Maclean(刘相兵

cmp$是 压缩建议compression advisor组件生成的表，若你经常看到 说明该组件工作有问题。 可以关闭
exec dbms_auto_task_admin.disable(client_name=>'auto space advisor',operation=>null,window_name=>null);