今天碰到一个Oracle 19.7 SYSAUX表空间暴增的的情况,记录下处理的过程。
事情起因是客户反馈连接数据库报ORA-12537
[oracle@ora19c1 ~]$ oerr ora 12537
12537, 00000, "TNS:connection closed"
// *Cause: "End of file" condition has been reached; partner has disconnected.
// *Action: None needed; this is an information message.
检查alert日志报大量的 ORA-1653:unable to extend tableSYS.WRI$_SQLTEST_PLAN_LINES by 8192 in tablespace SYSAUX 以及 WARNING:too many parse errors…
检查CDB表空间情况,SYSAUX已经达到100G。
SYSAUX是SYSTEM的辅助表空间,存放一些metadata组件,还有awr快照信息,通常都不大(小于1G)。
为不影响客户使用,还是又加了一个数据文件,保障先能使用。
现在需要看看SYSAUX里是啥对象占了这么大的空间
set lines 150 pages 3000
select segment_name,sum(bytes)/1024/1024/1024 as G from dba_segments
where tablespace_name='SYSAUX' group by segment_name order by G;
可见最后一行SYS_LOB开头的对象有63G,占据了2/3的表空间,简直过分。。
其实通过名称已经能大概看出这是LOB对象,还是确认一下
select object_type,object_name from dba_objects
where object_name='SYS_LOBxxxxxxxxxxx$$'
再确认这个LOB对象属于哪张表
col owner for a20
col table_name for a30
col segment_name for a50
set lines 200
select owner,table_name,segment_name,tablespace_name from dba_lobs
where segment_name='SYS_LOBxxxxxxxxxxx$$'
确认LOB属于表:SYS.WRI$_SQLTEST_PLAN_LINES
之前alert日志中提示无法扩展的表也是这张
通过查询,确认该表与19.7版本新的数据库基础结构组件相关,称为自动 SQL 调优集 (ASTS),参考MOS Doc ID 2686869.1
下面是部分机翻介绍:
ASTS 使用自动后台任务进行维护,该任务在 Oracle Database 19c RU 19.7 中默认启用。虽然ASTS 是常见的基础设施功能,不会对数据库的运行时行为产生任何影响(除了占用SYSAUX 中的有限空间)
ASTS 是 SQL 执行计划和 SQL 语句性能指标的历史记录。 ASTS 是 AWR 的补充,被视为 Oracle 数据库的类似核心可管理性基础架构。 ASTS 是一个旨在快速轻松地解决 SQL 语句性能回归问题的组件。它降低了与数据库更改、系统配置更改和升级相关的风险。
检查该功能状态:
--检查确实为true状态
select task_name,enabled from dba_autotask_schedule_control where task_name='Auto STS Capture Task';
ASTS 使用自动后台任务进行维护,该任务在 Oracle Database 19c RU 19.7 中默认启用。但是从19.8起默认是关闭状态,那么我们也将该功能关闭,并清理关联表即可缩减空间。
执行mos给出的关闭脚本
注意:官方提示在 19.7 中,应在所有容器所有 pdbs 中禁用自动任务。经过确认确实在CDB级别关闭后,但是PDB级别查询还是开启的状态
Begin
DBMS_Auto_Task_Admin.Disable(
Client_Name => 'Auto STS Capture Task',
Operation => NULL,
Window_name => NULL);
End;
/
清理相关表,下面清理脚本来自 https://blog.csdn.net/wx370092877/article/details/116662734
exec dbms_auto_task_admin.disable('Auto STS Capture Task', NULL, NULL);
TRUNCATE TABLE WRI$_SQLSET_PLAN_LINES;
truncate table WRI$_SQLSET_STATISTICS;
truncate table WRI$_SQLSET_STATEMENTS;
truncate table WRI$_SQLTEXT_REFCOUNT;
truncate table WRI$_SQLSET_PLANS;
truncate table WRI$_SQLSET_MASK;
清理后的SYSAUX下对象大小,只清理了WRI$_SQLSET_PLAN_LINES一张表,SYSAUX就降到11G大小,下图是SYSAUX下对象按大小排序情况。
到此处理完毕。
1.SYSAUX中WRI$开头的表可以再研究研究; 2. WARNING: too many parse errors 参考惜分飞的博客https://www.xifenfei.com/tag/_kks_parse_error_warning 3. 由于大量WARNING: too many parse errors导致alert日志达到2.4G,打开已经很慢了,alert日志的清理 4. ASTS 功能详细见Doc ID 2686869.1