Compression Advisor killed my database!

Over the weekend one of the databases hung due to the flash recovery area was 100% full. I noticed one J001 process consuming significant CPU and I/O resources. Turns out this process was the automatic segment advisor job that runs on the weekend maintenance window.

The SQL executed was something like:

CREATE TABLE <owner>.dbms_tabcomp_temp_uncmp

TABLESPACE <tablespace> NOLOGGING

AS

SELECT /*+ FULL(<owner>.<table>) */

*

FROM <owner>.<table> ...

After reading Oracle note Id 13463481.8 and confirming this with an SR, this is related to a bug for 11.2.0.3 version and fixed in 11.2.0.4. This bug generates excessive amount of redo when running the compression advisor on a table with a LOB column in a database running in ARCHIVELOG mode.

As we can’t just apply the required patch to the ORACLE_HOME right away, we decided to perform the workaround of disabling the automatic segment advisor task. The compression advisor is part of the segment advisor and is not possible just to disable one or the other.

To disable the segment advisor:

SQL> BEGIN

dbms_auto_task_admin.disable(

client_name => 'auto space advisor',

operation => NULL,

window_name => NULL);

END;

/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

After executing the procedure, verify that the “auto space advisor” is disabled.

SQL> SELECT client_name, status FROM dba_autotask_client;

CLIENT_NAME                                                      STATUS

---------------------------------------------------------------- --------

auto optimizer stats collection                                  ENABLED

auto space advisor                                               DISABLED

sql tuning advisor                                               ENABLED

Although the advisor will not automatically run, you can always run it manually on the segments or indexes you want to be analyzed.

Thanks,

Alfredo