Oracle Database 12.2 -- EXP_HEAD$ Segment is a Top Consumer of SYSAUX

Problem

EXP_HEAD$ segment takes the most space of the SYSAUX tablespace

Additionally, these segments might also be quite large compared to other objects:

• I_EXP_HEAD$
• EXP_STAT$
• EXP_OBJ$

Solution

Those objects belong to the new feature introduced with 12.2 release - Optimizer expression statistics monitoring or "Expression Tracking". For release 12.2 it was only used for Oracle Database In-Memory where it was called Expression Statistics Store (ESS).

To disable the feature:

SQL> alter system set "_column_tracking_level"=17 scope=both;

To purge the data:

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
SQL> truncate table sys.exp_head$ drop storage;
SQL> truncate table sys.exp_obj$ drop storage;
SQL> truncate table sys.exp_stat$ drop storage;
SQL> alter index SYS.I_EXP_HEAD$ rebuild tablespace sysaux online;
SQL> alter index SYS.I_EXP_STAT$ rebuild tablespace sysaux online;
SQL> alter index SYS.I_EXP_OBJ$ rebuild tablespace sysaux online;

To re-enable the feature:

For 12.2:

SQL> alter system set "_column_tracking_level"=21 scope=both;

For 19c:

SQL> alter system set "_column_tracking_level"=53 scope=both;

 

Reference:

• Optimizer Expression Statistics Tracking/Monitoring Generates Growth in Tablespace SYSAUX (Doc ID 2354960.1)
• https://blogs.oracle.com/optimizer/post/expression-tracking