19c sysaux表空间爆满
## 第一步：检查表空间情况
巡检发现 sysaux 表空间不足
TABLESPACE_NAME TP_REAL_SIZE TP_REAL_FREE_SI TP_FREE_RATING
############################## ############### ############### ##############
SYSAUX 31.99GB 3.94GB 12.31%
SYSTEM 31.99GB 25.15GB 78.61%
TSP_TPACS 54.95GB 5.83GB 10.61%
UNDOTBS1 31.99GB 21.98GB 68.70%
USERS 31.99GB 31.99GB 99.99%
检查发现是 WRI$_ADV_OBJECTS 对象占用过多。
## 第二步：查看参数配置
此问题可以通过如下文档分析解决：
How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1) To BottomTo Bottom
默认保留30天数据，查询如下
col parameter_name format a35
col parameter_value format a20
set lines 120
select TASK_NAME,parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS WHERE task_name='AUTO_STATS_ADVISOR_TASK' and PARAMETER_NAME='EXECUTION_DAYS_TO_EXPIRE';
SQL> SQL> SQL> SQL>
TASK_NAME PARAMETER_NAME PARAMETER_VALUE
######################### ################################### ####################
AUTO_STATS_ADVISOR_TASK EXECUTION_DAYS_TO_EXPIRE 30
## 第三步： 查看作业和数据情况
数据信息 如下：
SQL> col task_name format a25
col EXECUTION_NAME format a15
select TASK_ID,TASK_NAME,EXECUTION_NAME ,to_date(execution_start,'yyyy/mm/dd hh24:mi:ss') from dba_advisor_executions where TASK_NAME='AUTO_STATS_ADVISOR_TASK';
SQL> SQL>
TASK_ID TASK_NAME EXECUTION_NAME TO_DATE(E
########## ######################### ############### #########
6 AUTO_STATS_ADVISOR_TASK EXEC_3921 24#DEC#15
6 AUTO_STATS_ADVISOR_TASK EXEC_4351 24#DEC#16
6 AUTO_STATS_ADVISOR_TASK EXEC_4611 24#DEC#17
6 AUTO_STATS_ADVISOR_TASK EXEC_4881 24#DEC#18
6 AUTO_STATS_ADVISOR_TASK EXEC_5141 24#DEC#19
6 AUTO_STATS_ADVISOR_TASK EXEC_5301 24#DEC#20
6 AUTO_STATS_ADVISOR_TASK EXEC_5551 24#DEC#21
6 AUTO_STATS_ADVISOR_TASK EXEC_5621 24#DEC#22
6 AUTO_STATS_ADVISOR_TASK EXEC_5891 24#DEC#23
6 AUTO_STATS_ADVISOR_TASK EXEC_6121 24#DEC#24
6 AUTO_STATS_ADVISOR_TASK EXEC_6351 24#DEC#25
TASK_ID TASK_NAME EXECUTION_NAME TO_DATE(E
########## ######################### ############### #########
6 AUTO_STATS_ADVISOR_TASK EXEC_6601 24#DEC#26
6 AUTO_STATS_ADVISOR_TASK EXEC_6851 24#DEC#27
6 AUTO_STATS_ADVISOR_TASK EXEC_7021 24#DEC#28
6 AUTO_STATS_ADVISOR_TASK EXEC_7041 24#DEC#29
6 AUTO_STATS_ADVISOR_TASK EXEC_7171 24#DEC#30
6 AUTO_STATS_ADVISOR_TASK EXEC_7341 24#DEC#31
6 AUTO_STATS_ADVISOR_TASK EXEC_7511 25#JAN#01
6 AUTO_STATS_ADVISOR_TASK EXEC_7671 25#JAN#02
6 AUTO_STATS_ADVISOR_TASK EXEC_7761 25#JAN#03
6 AUTO_STATS_ADVISOR_TASK EXEC_7901 25#JAN#04
6 AUTO_STATS_ADVISOR_TASK EXEC_7931 25#JAN#05
TASK_ID TASK_NAME EXECUTION_NAME TO_DATE(E
########## ######################### ############### #########
6 AUTO_STATS_ADVISOR_TASK EXEC_8091 25#JAN#06
6 AUTO_STATS_ADVISOR_TASK EXEC_8251 25#JAN#07
6 AUTO_STATS_ADVISOR_TASK EXEC_8361 25#JAN#08
6 AUTO_STATS_ADVISOR_TASK EXEC_8512 25#JAN#09
6 AUTO_STATS_ADVISOR_TASK EXEC_8592 25#JAN#10
6 AUTO_STATS_ADVISOR_TASK EXEC_8682 25#JAN#11
6 AUTO_STATS_ADVISOR_TASK EXEC_8723 25#JAN#12
6 AUTO_STATS_ADVISOR_TASK EXEC_8912 25#JAN#13
## 第四步：进行修改和清理
我们修改为只保留3天数据
EXEC DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (task_name => 'AUTO_STATS_ADVISOR_TASK', parameter => 'EXECUTION_DAYS_TO_EXPIRE', value => 3);
运行作业清理数据：
SQL> conn / as sysdba
SQL> exec prvt_advisor.delete_expired_tasks;
清理高水位：
alter table WRI$_ADV_OBJECTS move;
alter index WRI$_ADV_OBJECTS_PK rebuild;
alter index WRI$_ADV_OBJECTS_IDX_01 rebuild;
alter index WRI$_ADV_OBJECTS_IDX_02 rebuild;
## 第五步：再次检查作业
SQL> col task_name format a25
col EXECUTION_NAME format a15
select TASK_ID,TASK_NAME,EXECUTION_NAME ,to_date(execution_start,'yyyy/mm/dd hh24:mi:ss') from dba_advisor_executions where TASK_NAME='AUTO_STATS_ADVISOR_TASK';SQL> SQL>
TASK_ID TASK_NAME EXECUTION_NAME TO_DATE(E
########## ######################### ############### #########
6 AUTO_STATS_ADVISOR_TASK EXEC_18745 25#JAN#11
6 AUTO_STATS_ADVISOR_TASK EXEC_18756 25#JAN#12
6 AUTO_STATS_ADVISOR_TASK EXEC_18804 25#JAN#13
SQL> col parameter_name format a35
col parameter_value format a20
set lines 120
select TASK_NAME,parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS WHERE task_name='AUTO_STATS_ADVISOR_TASK' and PARAMETER_NAME='EXECUTION_DAYS_TO_EXPIRE';
SQL> SQL> SQL>
TASK_NAME PARAMETER_NAME PARAMETER_VALUE
######################### ################################### ####################
AUTO_STATS_ADVISOR_TASK EXECUTION_DAYS_TO_EXPIRE 3
表空间信息：
SQL> select /*+rule */ all_tp.TP_NAME as "TABLESPACE_NAME",
2 to_char(trunc(all_tp.TP_ALL_SIZE_KB / 1024 / 1024, 2),
'FM9999990.0099') || 'GB' as "TP_REAL_SIZE",
to_char(trunc(free_tp.TP_FREE_SIZE_KB / 1024 / 1024, 2),
'FM9999990.0099') || 'GB' as "TP_REAL_FREE_SIZE 3 ",
to_char(trunc(free_tp.TP_FREE_SIZE_KB * 100 / all_tp.TP_ALL_SIZE_KB,
2),
'FM9999990.0099') || '%' as "TP_FREE_RATING"
from (
select 4 /*+rule */ sum(decode(sign(ddf.maxbytes # ddf.bytes),
1,
ddf.maxbytes,
ddf.bytes)) / 1024 as "TP_ALL_S 5 IZE_KB",
ddf.tablespace_name as "TP_NAME"
from DBA_DATA_FILES ddf
group by ddf.tablespace_name) all_tp,
6 (
select /*+rule */ sum(ifree_tp.TPF_SIZE_KB) as "TP_FREE_SIZE_KB",
ifree_tp.TP_NAME
from (
select /*+rule */ dfs.bytes / 1024 as "TPF_SIZE_KB",
7 8 9 10 11 12 13 14 15 16 dfs.tablespace_name as "TP_NAME"
17 from DBA_FREE_SPACE dfs
union all
select /*+rule */ decode(sign(ddf.maxbytes # ddf.bytes),
1,
18 ddf.maxbytes # ddf.bytes,
0) / 1024 as "TPF_SIZE_KB",
19 20 21 22 23 24 25 26 27 28 ddf.tablespace_name as "TP_NAME"
from DBA_DATA_FILES ddf
where ddf.autoextensible = 'YES') ifree_tp
group by ifree_tp.TP_NAME) free_tp
where all_tp.TP_NAME = free_t 29 p.TP_NAME(+) ; 30 31 32 33
TABLESPACE_NAME TP_REAL_SIZE TP_REAL_FREE_SI TP_FREE_RATING
############################## ############### ############### ##############
SYSAUX 31.99GB 23.86GB 74.57%
SYSTEM 31.99GB 25.15GB 78.61%
TSP_TPACS 54.95GB 5.83GB 10.61%
UNDOTBS1 31.99GB 21.98GB 68.70%
USERS 31.99GB 31.99GB 99.99%
参考文档：
How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69924215/viewspace-3065602/ ，如需转载，请注明出处，否则将追究法律责任。
