dbi Blog

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.

Post Views: 1,943

Where come from Oracle CMP$ tables and how to delete them ?

Lazhar Felahi

Leave a Reply:

Related blog articles

Lazhar Felahi

Leave a Reply:

Related blog articles

Another file system for Linux: bcachefs (2) – multi device file systems

Another file system for Linux: bcachefs (1) – basics

PostgreSQL 17: pg_buffercache_evict()

PostgreSQL 17: Split and Merge partitions