Regarding the following “MOS Note Is Table SCHEMA.CMP4$222224 Or Similar Related To Compression Advisor? (Doc ID 1606356.1)”,
we know that since Oracle 11.2.0.4 BP1 or Higher, due to the failure of Compression Advisor some tables with names
that include “CMP”, created “temporary – the time the process is running” by Compression Advisor process (ie CMP4$23590) are not removed from the database as that should be the case.
How theses tables are created ? How to “cleanly” remove them ?
1.Check None CMP tables exist.
SQL> select count(*) from dba_tables where table_name like 'CMP%'; COUNT(*) ---------- 0
2. Check there is no compression enabled for the table we will use to test the Compression Advisor.
SQL> select nvl(COMPRESSION,'NO') as COMPRESSION,nvl(COMPRESS_FOR,'NO') as COMPRESS_FOR from dba_tables where table_name = 'FOO'; COMPRESS COMPRESS_FOR -------- ------------------------------ NO NO
3.Execute the Compression Advisor procedure
The procedure DBMS_COMPRESSION.get_compression_ratio analyzes the compression ratio of a table, and gives information about compressibility of a table.
For information, Oracle Database 12c include a number of enhancements to the DBMS_COMPRESSION package such as In-Memory Compression or Advanced Compression.
Let’s executing the DBMS_COMPRESSION.get_compression_ratio procedure:
SQL> alter session set tracefile_identifier = 'CompTest1110201815h51'; alter session set events '10046 trace name context forever, level 12'; set serveroutput on DECLARE l_blkcnt_cmp PLS_INTEGER; l_blkcnt_uncmp PLS_INTEGER; l_row_cmp PLS_INTEGER; l_row_uncmp PLS_INTEGER; l_cmp_ratio NUMBER; l_comptype_str VARCHAR2(32767); BEGIN DBMS_COMPRESSION.get_compression_ratio ( scratchtbsname => 'USERS', ownname => 'TEST_LAF', objname => 'FOO', subobjname => NULL, comptype => DBMS_COMPRESSION.comp_advanced, blkcnt_cmp => l_blkcnt_cmp, blkcnt_uncmp => l_blkcnt_uncmp, row_cmp => l_row_cmp, row_uncmp => l_row_uncmp, cmp_ratio => l_cmp_ratio, comptype_str => l_comptype_str, subset_numrows => DBMS_COMPRESSION.comp_ratio_allrows, objtype => DBMS_COMPRESSION.objtype_table ); DBMS_OUTPUT.put_line('Number of blocks used (compressed) : ' || l_blkcnt_cmp); DBMS_OUTPUT.put_line('Number of blocks used (uncompressed) : ' || l_blkcnt_uncmp); DBMS_OUTPUT.put_line('Number of rows in a block (compressed) : ' || l_row_cmp); DBMS_OUTPUT.put_line('Number of rows in a block (uncompressed) : ' || l_row_uncmp); DBMS_OUTPUT.put_line('Compression ratio : ' || l_cmp_ratio); DBMS_OUTPUT.put_line('Compression type : ' || l_comptype_str); END; / Number of blocks used (compressed) : 1325 Number of blocks used (uncompressed) : 1753 Number of rows in a block (compressed) : 74 Number of rows in a block (uncompressed) : 55 Compression ratio : 1.3 Compression type : "Compress Advanced" PL/SQL procedure successfully completed.
4.Which “CMP internal” tables are created by DBMS_COMPRESSION.get_compression_ratio ?
To handle the compression advisor process, Oracle creates 4 CMP* tables : CMP1$23590, CMP2$23590, CMP3$23590, CMP4$23590.
Strangely, Oracle Trace 10046 files contains only DDL for the creation of the last 2 ones (we can also use LogMinner to find the DDL) : CMP3$23590, CMP4$23590.
The table CMP3$23590 is a copy of the source table.
The table CMP4$23590 is a copy “compressed” of CMP3$23590 table.
grep "CMP*" DBI_ora_20529_CompTest1110201823h19.trc drop table "TEST_LAF".CMP1$23590 purge drop table "TEST_LAF".CMP2$23590 purge drop table "TEST_LAF".CMP3$23590 purge drop table "TEST_LAF".CMP4$23590 purge create table "TEST_LAF".CMP3$23590 tablespace "USERS" nologging as select /*+ DYNAMIC_SAMPLING(0) FULL("TEST_LAF"."FOO") */ * from "TEST_LAF"."FOO" sample block( 99) mytab create table "TEST_LAF".CMP4$23590 organization heap tablespace "USERS" compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from "TEST_LAF".CMP3$23590 mytab drop table "TEST_LAF".CMP1$23590 purge drop table "TEST_LAF".CMP2$23590 purge drop table "TEST_LAF".CMP3$23590 purge drop table "TEST_LAF".CMP4$23590 purge
As we can see above, the “internal” tables (even the one compressed CMP4$23590) are removed at the end of the process.
To be sure, we check in the database :
SQL> select count(*) from dba_tables where table_name like 'CMP%'; COUNT(*) ---------- 0
So, everything is fine, no ‘CMP’ tables exist and the source table is not compressed :
SQL> select nvl(COMPRESSION,'NO') as COMPRESSION,nvl(COMPRESS_FOR,'NO') as COMPRESS_FOR from dba_tables where table_name = 'FOO'; COMPRESS COMPRESS_FOR -------- ------------------------------ NO NO
5.But what happens if DBMS_COMPRESSION.get_compression_ratio fails ?
Let’s forcing the failure of the DBMS_COMPRESSION.get_compression_ratio procedure…
SQL> alter session set tracefile_identifier = 'CompTest1410201822h03'; alter session set events '10046 trace name context forever, level 12'; set serveroutput on DECLARE l_blkcnt_cmp PLS_INTEGER; l_blkcnt_uncmp PLS_INTEGER; l_row_cmp PLS_INTEGER; l_row_uncmp PLS_INTEGER; l_cmp_ratio NUMBER; l_comptype_str VARCHAR2(32767); BEGIN DBMS_COMPRESSION.get_compression_ratio ( scratchtbsname => 'USERS', ownname => 'TEST_LAF', objname => 'FOO', subobjname => NULL, comptype => DBMS_COMPRESSION.comp_advanced, blkcnt_cmp => l_blkcnt_cmp, blkcnt_uncmp => l_blkcnt_uncmp, row_cmp => l_row_cmp, row_uncmp => l_row_uncmp, cmp_ratio => l_cmp_ratio, comptype_str => l_comptype_str, subset_numrows => DBMS_COMPRESSION.comp_ratio_allrows, objtype => DBMS_COMPRESSION.objtype_table ); 24 DBMS_OUTPUT.put_line('Number of blocks used (compressed) : ' || l_blkcnt_cmp); DBMS_OUTPUT.put_line('Number of blocks used (uncompressed) : ' || l_blkcnt_uncmp); DBMS_OUTPUT.put_line('Number of rows in a block (compressed) : ' || l_row_cmp); DBMS_OUTPUT.put_line('Number of rows in a block (uncompressed) : ' || l_row_uncmp); DBMS_OUTPUT.put_line('Compression ratio : ' || l_cmp_ratio); DBMS_OUTPUT.put_line('Compression type : ' || l_comptype_str); END; 32 / DECLARE * ERROR at line 1: ORA-01013: user requested cancel of current operation
What “CMP*” tables persist after ?
Two “CMP*” tables is always present :
SQL> select count(*) from dba_tables where table_name like 'CMP%'; COUNT(*) ---------- 2 SQL> select owner,table_name from dba_tables where table_name like 'CMP%'; OWNER TABLE_NAME ------- ---------- TEST_LAF CMP3$23687 TEST_LAF CMP4$23687
Since “CMP3*” and “CMP4*” are copy (compressed for the second one) of source table, space disk can increase dramatically if Compressoin Advisor fails frequently and mainly with huge tables, so it’s important to remove these tables.
The source table called FOO, CMP3$23687 and CMP4$23687 internal tables contains same set of data (less for the last 2 ones since we use the sample block option)…
SQL> select count(*) from test_laf.CMP3$23687; COUNT(*) ---------- 22147 SQL> select count(*) from test_laf.CMP4$23687; COUNT(*) ---------- 22147 SQL> select count(*) from test_laf.foo; COUNT(*) ---------- 22387
The worst is that now we are in presence of compressed table while we don’t have the compression license option :
SQL> select nvl(COMPRESSION,'NO') as COMPRESSION,nvl(COMPRESS_FOR,'NO') as COMPRESS_FOR from dba_tables where table_name = 'CMP4$23687'; COMPRESS COMPRESS_FOR -------- ------------------------------ ENABLED ADVANCED
To remove the oracle “CMP*” internal tables tables, let’s analyzing the 10046 trace file to check how oracle remove these tables when the DBMS_COMPRESSION.get_compression_ratio procedure run successfully:
Find below all the steps that oracle does to drop these tables:
drop table "TEST_LAF".CMP1$23687 purge BEGIN BEGIN IF (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name, xdb.DBMS_XDBZ.IS_ENABLED_CONTENTS)) THEN xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name); ELSIF (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name); END IF; EXCEPTION WHEN OTHERS THEN null; END; END; drop table "TEST_LAF".CMP2$23687 purge PARSING IN CURSOR #140606951937256 len=515 dep=2 uid=0 oct=47 lid=0 tim=3421988631 hv=2219505151 ad='69fd11c8' sqlid='ct6c4h224pxgz' BEGIN BEGIN IF (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name, xdb.DBMS_XDBZ.IS_ENABLED_CONTENTS)) THEN xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name); ELSIF (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name); END IF; EXCEPTION WHEN OTHERS THEN null; END; END; drop table "TEST_LAF".CMP3$23687 purge BEGIN BEGIN IF (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name, xdb.DBMS_XDBZ.IS_ENABLED_CONTENTS)) THEN xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name); ELSIF (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name); END IF; EXCEPTION WHEN OTHERS THEN null; END; END; drop table "TEST_LAF".CMP4$23687 purge BEGIN BEGIN IF (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name, xdb.DBMS_XDBZ.IS_ENABLED_CONTENTS)) THEN xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name); ELSIF (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name); END IF; EXCEPTION WHEN OTHERS THEN null; END; END;
To remove “CMP*” tables, Oracle does :
– drop table *** purge
– call internal procedure : xdb.XDB_PITRIG_PKG.pitrig_truncate or xdb.XDB_PITRIG_PKG.pitrig_dropmetadata regarding if Oracle Virtual Private Database is used.
7. Last Test : Check the source table is not compressed, we don’t want to have the compression enabled since we are not licensing…
SQL> select nvl(COMPRESSION,'NO') as COMPRESSION,nvl(COMPRESS_FOR,'NO') as COMPRESS_FOR from dba_tables where table_name = 'FOO'; COMPRESS COMPRESS_FOR -------- ------------------------------ NO NO
6.Conclusion
To drop “CMP*” tables used by the DBMS_COMPRESSION.get_compression_ratio procedure, just execute : drop table CMP* purge.
I have not tested more in details the case where compression is used into Oracle VPD, so I don’t know the impact of executing the system procedure : xdb.XDB_PITRIG_PKG.pitrig_truncate or xdb.XDB_PITRIG_PKG.pitrig_dropmetadata in case we use VPD.