Compression Advisory Oracle

Compression advisory is introduced in 11gR2. It is a part of Segment Advisory, will be executed when Segment advisory job is run. We cannot disable it separately. (Doc ID 1284972.1)

Our application team informed that database having new tables CMP3$…. . Then we found it is created by Compression advisory.

How it get created?

When Segment advisory run in its scheduled window then it start the compression advisory to estimate the compression ratio.
It made CMP3$ TEMPORARY Table for estimate.

Syntax:
CMP3$;

We can search these tables by query:

select OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'CMP%$%';

How does it get fixed and prevent in the future?

We can’t not disable it separately. For disable compression advisory, we need to disable the Segment Advisory.
Sometime CMP3$ tables reside in database due to job not completed and some trigger is associated with table.Wait for few days if it not dropped automatic then It’s save to drop the table as normal process by drop command.

Check the status of Segment advisory:

SQL> SELECT client_name, status FROM dba_autotask_client;
CLIENT_NAME STATUS
------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED

Disable the Segment advisory by two ways:

1. Disable the job with help of dbms_scheduler package

execute dbms_scheduler.disable('sys.auto_space_advisor_job');

 
2. Disable the task by dbms_auto_task_admin package

BEGIN
dbms_auto_task_admin.disable(
client_name => 'auto space advisor',
operation => NULL,
window_name => NULL);
END;
/

Query to check the status of the job:

SQL> SELECT client_name, status FROM dba_autotask_client;

Enable the task

BEGIN
dbms_auto_task_admin.enable(
client_name => 'auto space advisor',operation => NULL,window_name => NULL);END;
/

Reference:
http://www.peasland.net/2011/10/12/11gr2-compression-advisor-evil/