暂无图片
暂无图片
4
暂无图片
暂无图片
暂无图片
首页 / 总结：Automatic SQL Tuning + Optimizer Statistics Advisor 带来的 SYSAUX 过快增长问题

总结：Automatic SQL Tuning + Optimizer Statistics Advisor 带来的 SYSAUX 过快增长问题

ByteHouse 2024-04-30
2273

SYSAUX 过快增长

11g新特性 - 自动sql调优(Automatic SQL Tuning)

http://bytesystem.online/doc/159/

在Oracle 10g中，引进了自动sql调优特性。此外，ADDM也会监控捕获高负载的sql语句。

在Oracle 11g中，通过运行 sql tuning advisor 加强了自动 sql 调优功能。默认启动了三个任务，分别是自动统计信息收集、自动段指导、STA，默认运行时间是周一到周五是运行4小时（22:00->次日2:00），周六日运行20小时（6:00->次日2:00）。其中，STA以及段指导可以建议关闭，用处不大。而且必要时可以手工运行。

查看任务状态：

col client_name for a35
select client_name,status from dba_autotask_client;

SQL> col client_name for a35
SQL> select client_name,status from dba_autotask_client;

CLIENT_NAME STATUS
------------------------------------------------------------------------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED

禁用STA和段指导：

exec dbms_auto_task_admin.disable(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);
exec dbms_auto_task_admin.disable (client_name => 'auto space advisor', operation => null, window_name => null);

select client_name,status from dba_autotask_client;

SQL> exec dbms_auto_task_admin.disable(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
SQL> exec dbms_auto_task_admin.disable (client_name => 'auto space advisor', operation => null, window_name => null);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> select client_name,status from dba_autotask_client;

CLIENT_NAME STATUS
---------------------------------------------------------------------
auto optimizer stats collection ENABLED
auto space advisor DISABLED
sql tuning advisor DISABLED
Elapsed: 00:00:00.28

查看自动统计任务的3个任务实际执行内容：

set line 500
col PROGRAM_NAME for a35
col PROGRAM_ACTION for a100

select program_name,program_action from dba_scheduler_programs where program_name in (select upper(task_name) from DBA_AUTOTASK_TASK);

SQL> select program_name,program_action from dba_scheduler_programs where program_name in (select upper(task_name) from DBA_AUTOTASK_TASK);

PROGRAM_NAME PROGRAM_ACTION
------------------------------------------------------------------------
AUTO_SQL_TUNING_PROG DECLARE
 ename VARCHAR2(30); BEGIN
 ename := dbms_sqltune.execute_tuning_task( 'SYS_AUTO_SQL_TUNING_TASK'); END;
AUTO_SPACE_ADVISOR_PROG dbms_space.auto_space_advisor_job_proc
GATHER_STATS_PROG dbms_stats.gather_database_stats_job_proc
Elapsed: 00:00:00.01

手工执行系统STA：

exec dbms_sqltune.execute_tuning_task('SYS_AUTO_SQL_TUNING_TASK');

查看STA结果：

select dbms_sqltune.report_tuning_task('SYS_AUTO_SQL_TUNING_TASK') from dual;

手工执行段指导：

exec dbms_space.auto_space_advisor_job_proc;

查询段指导结果：

select tablespace_name, segment_name, segment_type,
 recommendations, c1 from table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'));

相关视图：

  • DBA_ADVISOR_EXECUTIONS

  • DBA_ADVISOR_SQLSTATS

  • DBA_ADVISOR_SQLPLANS

12.2新特性 - 优化器统计顾问（Optimizer Statistics Advisor）

参考MOS文档：

How To Disable Optimizer Statistics Advisor From 12.2 Onwards (Doc ID 2686022.1)

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)
对于Oracle的CBO优化器而言，统计信息是其最重要的组成部分。因此，Oracle在统计信息的准确性方面不断地进行优化和加强。
从12.2版本开始，Oracle推出了优化器统计顾问（Optimizer Statistics Advisor），根据事先定义的规则，定期执行，提供给用户参考的统计信息收集相关的建议。

Optimizer Statistics Advisor是Oracle12.2中新增的功能， 而AUTO_STATS_ADVISOR_TASK任务的自动运行会占用大量sysaux表空间，从而导致sysaux表空间不足，严重情况下导致数据库无法正常使用。

◼ 问题描述

这种情况存在 Oracle 数据库版本12.2.0.1到18.3.0.0.0，由于Optimizer Statistics Advisor新特性的引入，AUTO_STATS_ADVISOR_TASK自动任务的执行，导致SYSAUX表空间使用逐渐增长，主要表象为：

  1. 查看 v$sysaux_occupants，SM/ADVISOR的空间使用排在第一位。
  1. 查看dba_segments，发现SYSAUX表空间中WRI$_ADV_OBJECTS占用最大。
SELECT parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS 
WHERE task_name='AUTO_STATS_ADVISOR_TASK'and PARAMETER_NAME IN 
'EXECUTION_DAYS_TO_EXPIRE';

◼ 问题解决

针对这种情况，建议首先通过以下查询，获得AUTO_STATS_ADVISOR_TASK任务数据的保留时间，缺省的保留时间是没有限制的：

SELECT parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS
WHERE task_name='AUTO_STATS_ADVISOR_TASK'
and PARAMETER_NAME IN 'EXECUTION_DAYS_TO_EXPIRE';

可以通过修改保留时间，限制保留为 31 天：

BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
task_name => 'AUTO_STATS_ADVISOR_TASK'
, parameter => 'EXECUTION_DAYS_TO_EXPIRE'
, value => 31
);
END;
/

WRI$_ADV_OBJECTS 占用很大空间

1.安装完数据库，即关闭或者调整此参数，命令为：

修改保留天数为7天，默认不限制：

BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
task_name => 'AUTO_STATS_ADVISOR_TASK'
, parameter => 'EXECUTION_DAYS_TO_EXPIRE'
, value => 31
);
END;
/

或者之间关闭：

SQL> exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');
PL/SQL procedure successfully completed.
SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;

DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')
---------------------------------------------------------
FALSE

2.已经出现占用大量空间后的清理方法：

配置保留时间自动清理，或者下面方法人工清理：

exec prvt_advisor.delete_expired_tasks;

手动删除（后台DELETE,如果很大会删除慢且大量使用UNDO，建议不用这个方法）

SQL> TRUNCATE TABLE WRI$_ADV_OBJECTS;

升级DB到12.2.0.1版本之后，由于统计信息顾问导致SYSAUX 过快增长 (Doc ID 2440139.1)

适用于:

Oracle Database Exadata Express Cloud Service - 版本 N/A 和更高版本
Oracle Database Cloud Schema Service - 版本 N/A 和更高版本
Oracle Database Cloud Service - 版本 N/A 和更高版本
Oracle Database Backup Service - 版本 N/A 和更高版本
Oracle Database - Enterprise Edition - 版本 12.2.0.1 和更高版本
本文档所含信息适用于所有平台

症状

升级DB到12.2.0.1 版本之后，SYSAUX 表空间增长过快并且消耗了大量的空间。

  • 查询视图 V$SYSAUX_OCCUPANTS 显示 SM/ADVISOR 占用空间排名靠前.

  • 进一步查询 DBA_SEGMENTS 显示 WRI$_ADV_OBJECTS 对象消耗了 SYSAUX 大量空间

例如：

SQL> SET LINES 120
SQL> COL OCCUPANT_NAME FORMAT A30
SQL> SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC;

OCCUPANT_NAME SPACE_USAGE_KBYTES

---

SM/ADVISOR 5901376
SM/OPTSTAT 574080
...

SQL> COL SEGMENT_NAME FORMAT A30
SQL> COL OWNER FORMAT A10
SQL> COL TABLESPACE_NAME FORMAT A10
SQL> COL SEGMENT_TYPE FORMAT A15
SQL> SELECT * FROM (SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,BYTES/1024/1024 "SIZE(MB)",SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SYSAUX' ORDER BY BYTES DESC) WHERE ROWNUM<=10;

SEGMENT_NAME OWNER TABLESPACE SIZE(MB) SEGMENT_TYPE

---

WRI$_ADV_OBJECTS SYS SYSAUX 3600 TABLE
WRI$_ADV_OBJECTS_IDX_01 SYS SYSAUX 1400 INDEX
...

更改

升级数据库到12.2.0.1 或更高 (如18c/19c 等).

原因

这个是由于12.2版本的一个新特性，即优化器统计信息顾问每天在维护窗口期间自动运行，因而引发了该问题。

AUTO_STATS_ADVISOR_TASK 任务运行了很多次导致SYSAUX表空间增长迅速。

例如:

SQL> COL TASK_NAME FORMAT A35
SQL> SELECT TASK_NAME, COUNT(*) CNT FROM DBA_ADVISOR_OBJECTS GROUP BY TASK_NAME ORDER BY CNT DESC;

TASK_NAME CNT

---

AUTO_STATS_ADVISOR_TASK 27082431
SYS_AUTO_SPM_EVOLVE_TASK 19
SYS_AUTO_SQL_TUNING_TASK 39
...

可以看到统计信息顾问任务运行了27082431次，所以消耗了SYSAUX大量的空间。

若是想了解优化器统计信息顾问相关内容，请参考如下文档：Document 2259398.1 Optimizer Statistics Advisor In 12.2

解决方案

  1. 请参考如下文档进行旧数据的清理：

    Document 2660128.1 How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards

  2. 这个自动运行的任务(AUTO_STATS_ADVISOR_TASK) 可以被删除进而释放它占用的大量空间。

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

一旦任务被删除，所有依赖Auto Stats Advisor Task的相关历史输出信息将从WRI$_ADV_OBJECTS表中一并删除。

删除任务AUTO_STATS_ADVISOR_TASK可能引发如下报错：

ORA-20001: Statistics Advisor: Invalid Task Name For the current user

如果上述报错出现，可以用下面的方法重建AUTO_STATS_ADVISOR_TASK来解决：

SQL> connect / as sysdba
SQL> EXEC DBMS_STATS.INIT_PACKAGE();

删除任务之后，重组表和所有索引：

SQL> ALTER TABLE WRI$_ADV_OBJECTS MOVE;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
  1. 如果表WRI$_ADV_OBJECTS中有太多和Auto Stats Advisor Task相关的记录，删除任务时可能会需要大量undo空间.

一旦发生此状况，如下方法可以用来直接清理数据而避免产生大量redo 和undo信息。

检查表 WRI$_ADV_OBJECTS和 Auto Stats Advisor Task相关的有效条数

SQL> SELECT COUNT(*) FROM WRI$_ADV_OBJECTS WHERE TASK_ID=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');

创建新表并保留非 AUTO_STATS_ADVISOR_TASK数据

SQL> CREATE TABLE WRI$_ADV_OBJECTS_NEW AS SELECT * FROM WRI$_ADV_OBJECTS WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');

SQL> SELECT COUNT(*) FROM WRI$_ADV_OBJECTS_NEW;

截断原表

SQL> TRUNCATE TABLE WRI$_ADV_OBJECTS;

把备份表中数据插回原表

SQL> INSERT /*+ APPEND */ INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW;

# For 19c & above, use the below insert statement to avoid ORA-54013 error as there is a new column SQL_ID_VC added to WRI$_ADV_OBJECTS.
SQL> INSERT INTO WRI$_ADV_OBJECTS("ID" ,"TYPE" ,"TASK_ID" ,"EXEC_NAME" ,"ATTR1" ,"ATTR2" ,"ATTR3" ,"ATTR4" ,"ATTR5" ,"ATTR6" ,"ATTR7" ,"ATTR8" ,"ATTR9" ,"ATTR10","ATTR11","ATTR12","ATTR13","ATTR14","ATTR15","ATTR16","ATTR17","ATTR18","ATTR19","ATTR20","OTHER" ,"SPARE_N1" ,"SPARE_N2" ,"SPARE_N3" ,"SPARE_N4" ,"SPARE_C1" ,"SPARE_C2" ,"SPARE_C3" ,"SPARE_C4" ) SELECT "ID" ,"TYPE" ,"TASK_ID" ,"EXEC_NAME" ,"ATTR1" ,"ATTR2" ,"ATTR3" ,"ATTR4" ,"ATTR5" ,"ATTR6" ,"ATTR7" ,"ATTR8" ,"ATTR9" ,
"ATTR10","ATTR11","ATTR12","ATTR13","ATTR14","ATTR15","ATTR16","ATTR17","ATTR18","ATTR19","ATTR20","OTHER" ,"SPARE_N1" , "SPARE_N2" ,"SPARE_N3" ,"SPARE_N4" ,"SPARE_C1" ,"SPARE_C2" ,"SPARE_C3" ,"SPARE_C4" FROM WRI$_ADV_OBJECTS_NEW;

SQL> COMMIT;

重建索引

SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

表 WRI$_ADV_OBJECTS_NEW在上述命令都成功执行后可删除。

从数据字典删除统计信息任务，避免再次造成问题。

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

DBA可以在任何时间重建统计信息顾问任务 (AUTO_STATS_ADVISOR_TASK)，具体参考如下办法：

SQL> EXEC DBMS_STATS.INIT_PACKAGE();

The Optimizer Statistics Advisor Task (AUTO_STATS_ADVISOR_TASK) will NOT be disabled with the below command. This does not stop the execution of AUTO_STATS_ADVISOR_TASK itself. This is only effective in the point of view of reducing the increase of SYSAUX space segments as rule filters are disabled for all operations of Statistics Advisor.

运行如下命令，统计信息顾问任务 (AUTO_STATS_ADVISOR_TASK) 不会被DISABLE. 这不会停止任务AUTO_STATS_ADVISOR_TASK自身，只是把它作为控制SYSAUX 空间增长的过滤规则停掉。

DECLARE
filter1 CLOB;
BEGIN
filter1 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER('AUTO_STATS_ADVISOR_TASK','EXECUTE',NULL,'DISABLE');
END;
/

新的增强补丁已经被申请，将会提供更方便的方法来禁用这个任务。
Unpublished Bug 26749785 NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK.

sysaux表空间使用过高的其他文档：

  • Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER (Doc ID 329984.1)

  • How to Address Issues Where AWR Data Uses Significant Space in the SYSAUX Tablespace (Doc ID 287679.1)

  • roubleshooting Issues with SYSAUX Space Usage (Doc ID 1399365.1)

  • How to Relocate the SYSAUX Tablespace (Doc ID 301186.1)

  • SYSAUX 过快增长 (Doc ID 2440139.1)

墨力计划
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

01.Database（共18篇）
oracle Database

评论