oracle创建cmp表,CMP$ table in oracle - Oracle数据库管理 - Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusData诗檀...

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.