Disabling the Oracle Compression Advisor

Recently, we experienced a brief incident related to tablespace usage. The issue lasted only a few seconds and, by the time we became aware and checked the system, the alert had already cleared and the space pressure in the tablespace had subsided.

The event occurred at 23:20, during the daily scheduled maintenance window. The incident was logged in the alert.log file as follows:

2025-4-02T23:20:27.543020+03:00
ORA-1652: unable to extend temp segment by 1024 in tablespace USERS_XXX

Since we had no prior information about the issue, we started by examining the relevant time window in the AWR report. We generated an AWR snapshot for the specific instance where the alert was recorded and focused on the SQL Statistics section. The three SQL statements were clearly identified and listed under “SQL Ordered by Elapsed Time” and “SQL Ordered by Gets,” which immediately pointed to the root cause of the issue. While SQL IDs can vary across different SQL statements, they remain consistent across all environments for the same statement. Therefore, those experiencing a similar failure may want to check for the following SQL IDs in their AWR reports: ‘6mcpb06rctk0x‘ and ‘1v9v674376aaq‘.

6mcpb06rctk0x : call dbms_space.auto_space_advisor_job_proc (  )
1v9v674376aaq : BEGIN dbms_compression.get_compression_ratio(:scratchtbsname, :ownname, :objname, :subobjname, :comptype, :blkcnt_cmp, :blkcnt_uncmp, :row_cmp, :row_uncmp, :cmp_ratio, :comptype_str); END;

And the third sql statement is actually the failing sql statement with ORA-1652. This statement will be different accross environments.

by4gfs3sd7mbc: create table "xxx".CMP3$2206767 tablespace "USERS_XXX" nologging lob (CONTENT) store as (tablespace "XXX" enable storage in row nocache nologging) as select /*+ DYNAMIC_SAMPLING(0) FULL("xxx"."TABLE_XXX") */ * from "xxx"."TABLE_XXX" sample block (41.118) mytab

After conducting a quick search on My Oracle Support and some blog sites for tables starting with CMP3, it became clear that these CMP3$* and CMP4$* tables (DBMS_TABCOMP_TEMP_UNCMP and DBMS_TABCOMP_TEMP_CMP on versions earlier than 11.2.0.4) are created by the Compression Advisor running under theAutomatic Segment Space Advisor. These tables are generated during the advisor task for analysis purposes. In this case, the table being analyzed contained a LOB column, and the size of the CMP3$ table grew large enough to nearly fill the USERS_XXX tablespace. Once the Compression Advisor process completed, the table was dropped, which immediately freed up space in the USERS_XXX tablespace, leaving plenty of free space.

According to the Database Administrator’s Guide, Oracle Database Release 19 documentation – Part 3 Section 19:  Managing Space for Schema Objects :

The Automatic Segment Advisor does not analyze every database object. Instead, it examines database statistics, samples segment data, and then selects the following objects to analyze:

• Tablespaces that have exceeded a critical or warning space threshold
• Segments that have the most activity
• Segments that have the highest growth rate

In addition, the Automatic Segment Advisor evaluates tables that are at least 10MB and that have at least three indexes to determine the amount of space saved if the tables are compressed with the advanced row compression method.

If an object is selected for analysis but the maintenance window expires before the Segment Advisor can process the object, the object is included in the next Automatic Segment Advisor run.

You cannot change the set of tablespaces and segments that the Automatic Segment Advisor selects for analysis. You can, however, enable or disable the Automatic Segment Advisor task, change the times during which the Automatic Segment Advisor is scheduled to run, or adjust automated maintenance task system resource utilization. 

However, there are numerous old bugs in MOS and user complaints on various blog sites related to this advisor, with some citing excessive redo generation and others about orphaned tables. If the Compression Advisor fails for any reason, these CMP3$* and CMP4$* tables may not be dropped and can remain behind. According to Oracle’s documentation, it is safe to drop these tables.

The general solution that was previously offered to address the issue and prevent tablespace usage errors during the Compression Advisor task was to disable the Segment Space Advisor entirely, as there was no option to disable the Compression Advisor without turning off the Segment Advisor in Oracle prior to version 12.2.

With Oracle 12.2 and later, it is possible to disable the Compression Advisor without turning off the Segment Advisor. I will provide the steps for this process. These solutions are available in How To Disable Compression Advisor Alone Without Disabling Segment Advisor From 12.2 Onwards (Doc ID 2674127.1) and DBMS_SPACE_ADMIN enhancement to avoid running the compression advisor on table(s) with LOB column(s) (Doc ID 2184920.1)

We will use DBMS_SPACE_ADMIN package SET_SEGADV_ATTRIB and GET_SEGADV_ATTRIB Procedure.

The SET_SEGADV_ATTRIB procedure takes two numeric inputs. The first input specifies the disable level: 1 for COMP_ADVISOR (disables the advisor for all tables) and 2 for COMP_LOB (disables the advisor for tables with LOB columns). The second input is the enable/disable flag: 1 to enable and 0 to disable.

Option 1 : Disabling the Compression Advisor for all tables:

SQL> conn sys as sysdba
SQL> exec dbms_space_admin.set_segadv_attrib (1, 0);

SQL> select * from sys.wri$_segadv_attrib;
ATTRIBUTE# ATTRIBVALUE DESCR                              SPARE
---------- ----------- ---------------------------------- -----
1          0           ENABLE/DISABLE compression advisor

You may use DBMS_SPACE_ADMIN.GET_SEGADV_ATTRIB procedure or query the internal sys.wri$_segadv_attrib table directly.

SQL> -- check segment advisor attributes
SQL> --
SQL> set serveroutput on
SQL> DECLARE att_value number;
BEGIN dbms_space_admin.get_segadv_attrib (1, att_value);
dbms_output.enable;
dbms_output.put_line ('Compression Advisor = ' || att_value);
END;
/

Compression Advisor = 1

Option 2 : Disabling the Compression Advisor for tables with lob columns:

SQL> conn sys as sysdba
SQL> exec dbms_space_admin.set_segadv_attrib (2, 0);

SQL> select * from sys.wri$_segadv_attrib;
ATTRIBUTE# ATTRIBVALUE DESCR                                                        SPARE
---------- ----------- ------------------------------------------------------------ -----
2          0           ENABLE/DISABLE compression advisor on table with LOB columns

You may use DBMS_SPACE_ADMIN.GET_SEGADV_ATTRIB procedure or query the internal sys.wri$_segadv_attrib table directly.

SQL> -- check segment advisor attributes
SQL> --
SQL> set serveroutput on
SQL> DECLARE att_value number;
BEGIN dbms_space_admin.get_segadv_attrib (2, att_value);
dbms_output.enable;
dbms_output.put_line ('Compression Advisor for the tables with LOB columns = ' || att_value);
END;
/

Compression Advisor for the tables with LOB columns = 1

Option 3: Disabling the Segment Space Advisor entirely:

You could also choose to disable the Segment Space Advisor entirely, but I prefer not to leave defaults unchanged without a valid reason. Therefore, the first two options seem better to me. I prefer solving problems at a more granular level. This approach is based on general advice from Maria Colgan, which I also apply when tuning SQL statements: Avoiding hidden/underscore parameters at database level unless absolutely necessary, and opting for OPT_PARAM hint SQL patches or query rewrites instead.

SQL EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);                                                                                   
                                                                                                                                                                             
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   

You may also choose to monitor the situation closely and add more datafiles to the tablespace experiencing space pressure. The output column of the query provided below can be used to detect tables with large sizes that encountered an ORA-01652 error while the Compression Advisor was running. These tables can also be evaluated to determine whether they are good candidates for partitioning. Partitioning would split the table into smaller segments, making it easier for sampling during subsequent runs.

SELECT a.log_id,
TO_CHAR(a.output),
b.log_date,
b.name,
b.operation,
b.status 
FROM SYS.SCHEDULER$_JOB_OUTPUT a, SYS.SCHEDULER$_EVENT_LOG b
WHERE a.log_id=b.log_id
and b.name like '%SPC%'
and a.output like '%ORA-01652%';

Final Thoughts :

In summary, addressing issues related to the Compression Advisor and Segment Space Advisor requires careful consideration of available options, along with thorough root cause analysis, as is the case with all system problems. With Oracle 12.2 and later, the ability to disable the Compression Advisor without turning off the Segment Space Advisor offers greater flexibility and control over system performance. By using the DBMS_SPACE_ADMIN package, you can disable the Compression Advisor for all tables or specifically for tables containing LOB columns, allowing for a more granular approach to resolving tablespace pressure.

Hope it helps.