一、查看system表空间大小
检查发现system表空间使用27g。
tablespace_name total free used used_pct
------------------------------ ---------- ---------- ---------- --------
system 29.00 1.66 27.33 94.26
二、检查占用最大对象
select segment_name, sum(bytes/1024/1024/1024) from dba_segments where tablespace_name='system' group by segment_name order by 2
_h_obj#_col# .48828125
c_obj#_intcol# 1.25488281
mvref$_run_stats 3
i_optstat_snapshot$ 6.27929688
stat_tab03 6.84472656
optstat_snapshot$ 7.43164063
看起来像统计信息相关的表和索引,统计信息一般存放在sysaux表空间,但是此处出现在system。
三、分析
检查mos,发现"optstat_snapshot$/i_ optstat_snapshot$ segments growing leading to larger system tbs (doc id 2447653."
from 12r2 when collecting the statistics, below insert statement is executed. table’s dml monitoring information is copied from sys.mon_mods_all$ into optstat_snapshot$,
then optstat_snapshot$ will grow and occupy in system tablespace:
对于sys.mon_mods_all$,对应的视图为dba_tab_modification,用于记录数据库的dml操作记录。
sql_id: 3wh9htctp7199
insert /* ksxm:take_snpshot */ into sys.optstat_snapshot$ (obj#, inserts, updates, deletes, timestamp, flags) (select m.obj#, m.inserts, m.updates, m.deletes, systimestamp, dbms_stats_advisor.compute_volatile_flag( m.obj#, m.flags, :flags, m.inserts, m.updates, m.deletes, s.inserts, s.updates, s.deletes, null, nvl(to_number(p.valchar), :global_stale_pcnt), s.gather) flags from sys.mon_mods_all$ m, (select si.obj#, max(si.inserts) inserts, max(si.updates) updates, max(si.deletes) deletes, decode(bitand(max(si.flags), :gather_flag), 0, 'no_gather', 'gather') gather, max(si.timestamp) timestamp from sys.optstat_snapshot$ si, (select obj#, max(timestamp) ts from sys.optstat_snapshot$ group by obj#) sm where si.obj# = sm.obj# and si.timestamp = sm.ts group by si.obj#) s, sys.optstat_user_prefs$ p where m.obj# = s.obj#( ) and m.obj# = p.obj#( ) and pname( ) = 'stale_percent' and m.obj# = :objn)
本环境为19.9,检查内存中这个sql的情况
dbms_lob.substr(a.sql_fulltext)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert /* ksxm:take_snpshot */ into sys.optstat_snapshot$ (obj#, inserts, updates, deletes, timestamp, flags) (select m.obj#, m.inserts, m.updates, m.deletes, systimestamp, dbms_stats_advisor.compute
_volatile_flag( m.obj#, m.flags, :flags, m.inserts, m.updates, m.deletes, s.inserts, s.updates, s.deletes, null, nvl(to_number(p.valchar), :global_stale_pcnt), s.gather) flags from sys.mon_mo
ds_all$ m, (select si.obj#, max(si.inserts) inserts, max(si.updates) updates, max(si.deletes) deletes, decode(bitand(max(si.flags), :gather_flag), 0, 'no_gather', 'gat
her') gather, max(si.timestamp) timestamp from sys.optstat_snapshot$ si, (select obj#, max(timestamp) ts from sys.optstat_snapshot$ group by obj#) sm where si.obj# = sm.obj# and si.timestamp =
sm.ts group by si.obj#) s, sys.optstat_user_prefs$ p where m.obj# = s.obj#( ) and m.obj# = p.obj#( ) and pname( ) = 'stale_percent' and m.obj# = :objn)
sql> select fetches,executions from gv$sql where sql_id='3wh9htctp7199';
fetches executions
---------- ----------
0 222
0 447
0 8
检查发现该sql执行次数很多。检查对应段大小
对于这条sql,"what does internal sql ‘ksxm:take_snpshot’ with sqlid 3wh9htctp7199 during insert…’ do and does it have performance impact? (doc id 2435833.1)"中说法是这样的
the above insert statement is part of optimizer statistics advisor which analyzes how optimizer statistics are gathered, and then makes recommendations. it is introduced in 12cr2. what this does is insert dml monitoring information from sys.mon_mods_all$ into optstat_snapshot$. this job will help you in getting optimum execution plans for all the queries running in your database. there is no performance impact to the database if it executes.
if you would like to disable optimizer statistics advisor you can do that by referencing the below: (please note that this does not guarantee that the insert won't occur) again we need to state that there is no performance impact to the database it it executes.
是12.2版本引入,将dml 监控信息从 sys.mon_mods_all$ 插入到 optstat_snapshot$ 中。帮助数据库获取最优执行计划。但是"please note that this does not guarantee that the insert won’t occur",关闭该助手不能保证不发生插入。但是不会影响性能。
sql> select segment_name, segment_type, bytes/1024/1024/1024 size_gb from dba_segments where owner = 'sys' and segment_name like '%optstat_snapshot%';
segment_name segment_type size_gb
---------------------------------------- ------------------ ----------
optstat_snapshot$ table 7.43164063
i_optstat_snapshot$ index 6.27929688
检查该表记录的最大最小时间
sql> select min(timestamp),max(timestamp) from optstat_snapshot$;
min(timestamp) max(timestamp)
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
03-nov-21 03.57.32.304717 pm 08:00 31-aug-23 09.13.22.796305 am 08:00
检查自动统计信息任务
sql> select dbms_stats.get_stats_history_retention from dual;
get_stats_history_retention
---------------------------
31
sql> select client_name, status from dba_autotask_client where client_name='auto optimizer stats collection';
client_name status
---------------------------------------------------------------- --------
auto optimizer stats collection disabled
自动统计信息任务处于关闭状态
再次检查mos文档"optstat_snapshot$ was not be purged after disable ‘auto optimizer stats collection’ (doc id 2794465.1)"
文档中说optstat_snapshot$ was not be purged by the statistics purge job, and grows continuously.但是检查同样配置另外一套数据库也是关闭状态,但是最大最小记录却很正常,郁闷。
sql> 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';
task_name parameter_name parameter_value
------------------------- ----------------------------------- --------------------
auto_stats_advisor_task execution_days_to_expire 30
sql>
sql> select min(timestamp),max(timestamp) from optstat_snapshot$;
min(timestamp)
---------------------------------------------------------------------------
max(timestamp)
---------------------------------------------------------------------------
31-jul-23 08.11.46.897391 am 08:00
31-aug-23 09.15.08.683607 am 08:00
检查文档"statistics history of deleted objects may remain if no statistics collection is executed (doc id 2963771.1)"
if you choose not to lock statistics and also do not perform automatic or manual statistics collection, note that following behavior may cause accumulation of statistics history records in optstat_snapshot$.
- data deletion criteria of the statistics history is affected by the date and time of the most recent automatic or manual statistics collection.
- when a table having optimizer statistics is dropped, the statistics collection date and time of that table is not considered in the statistics history deletion criteria.
- the statistics history of deleted objects remains, if there are no manual or automatic statistics collections are executed.
(example of such case includes statistics history of the master table of datapump, or when you manually create objects, collect statistics, then drop objects, etc)
看来这个说法比较靠谱。
四、处置
按照文档建议,可以清理该表记录并回收表空间
- 处置方式1
exec dbms_stats.purge_stats(sysdate-10);
alter table optstat_snapshot$ move tablespace system;
alter index i_optstat_snapshot$ rebuild;
- 处置方式2
exec dbms_stats.purge_stats(dbms_stats.purge_all);
五、多说一点
翻看mos时发现"high pga memory consumption and cpu usage by optimizer statistics advisor task (doc id 2885895.1)".提醒裸奔的19c用户,关了自动统计信息助手吧。
upon reviewing it shows, top memory consuming process is ora_j000_* which is spawned as part of execution of auto task scheduler job .
the process was running “optimizer statistics advisor” task name auto_stats_advisor_task ( job name ora$at_os_opt_sy_<…>)
and it was executing following recursive sql:
sql_id: as2dr3ag24gay
select not_stale.obj# from (select s.obj# obj#, count(*) cnt, max(timestamp) max_time from
optstat_snapshot$ s, wri$_optstat_tab_history t where s.obj# = t.obj# and bitand(s.flags, :gather_stats_flag) > 0 and
t.analyzetime = (select max(analyzetime) from wri$_optstat_tab_history where analyzetime < s.timestamp) and
dbms_stats_internal.is_stale(s.obj#, null, null, s.inserts s.updates s.deletes, t.rowcnt, 0) is null group by s.obj#) not_stale,
(select obj# obj#, count(*) cnt from optstat_snapshot$ where bitand(flags, :gather_stats_flag) > 0 group by obj#) total,
(select obj#, max(timestamp) max_time from optstat_snapshot$ group by obj#) max where not_stale.obj# = total.obj# and
not_stale.cnt > 0.5 * total.cnt and not_stale.obj# = max.obj# and not_stale.max_time = max.max_time and
dbms_stats_internal.check_advisor_obj_filter(:rule_id, not_stale.obj#, :exec_mode) = 't'
the issue is related to:
bug 34201470 - jnnn processes consuming 100% cpu and high memory during autotask execution
to resolve the issue, apply patch for bug 34201470
(please note that this fix above does the same thing the workaround does below, it only turns off auto_stats_advisor_task)
to workaround: exec dbms_stats.set_global_prefs(‘auto_stats_advisor_task’, ‘false’);
(statistics preference auto_stats_advisor_task is available when patch 26749785 is present. the workaround will work when this patch is installed)