Google it ....

Friday, October 16, 2020

SYS.EXP_HEAD$ Growth in Tablespace SYSAUX

 Optimizer Expression Tracking allocates too much space in SYSAUX with following objects:


SYS.EXP_HEAD$
SYS.EXP_OBJ$
SYS.EXP_STAT$

Optimizer expression statistics monitoring is introduced in 12.2 database version. Expression statistics monitoring is enabled by default in 12.2. For specific applications, this might end up in growing repository segments. Monitoring expression statistics is introduced for a new optimizer feature in 12.2 to collect expression usage statistics in SQL queries. Monitoring feature is controlled by parameter "_column_tracking_level". High growth have been noticed when monitoring expression statistics is active, enabled by default. Note: There is no effects to execution plan of SQL if in-memory is disable. What is this feature used for? You can imagine that it is useful for optimization in general, but right now it has a single "client": Oracle Database In-Memory, where it’s referred to as the Expression Statistics Store (ESS).
Solution:
Disable future monitoring:
alter system set "_column_tracking_level"=17 scope=both;
--wait 10 minutes

To purge data from tables:
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
truncate table sys.exp_head$ drop storage;
truncate table sys.exp_obj$ drop storage;
truncate table sys.exp_stat$ drop storage;
alter index SYS.I_EXP_HEAD$ rebuild tablespace sysaux online;
alter index SYS.I_EXP_STAT$ rebuild tablespace sysaux online;
alter index SYS.I_EXP_OBJ$ rebuild tablespace sysaux online;

To re-enable expression statistics monitoring later :
alter system set "_column_tracking_level"=21 scope=both; 

No comments:

Post a Comment