Oracle19c SYSAUX表空间快速增长处理（WRI$_SQLSET_PLAN_LINES）

原创甚至熊熊 2021-10-27

7570

今天碰到一个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 table SYS.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下对象按大小排序情况。

到此处理完毕。

四、小结

SYSAUX中WRI$开头的表可以再研究研究；
WARNING: too many parse errors 参考惜分飞的博客https://www.xifenfei.com/tag/_kks_parse_error_warning
由于大量WARNING: too many parse errors导致alert日志达到2.4G,打开已经很慢了，alert日志的清理
ASTS 功能详细见Doc ID 2686869.1

透明2.png