Compression Advisory Oracle

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/
 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.