[20260407]Real-Time Statistics带来的问题.txt
--//19c开始引入Real-Time Statistics以及High-Frequency Statistics，不知道这两个是否表示一个意思。在exadata缺省应该是打开
--//的。去年就遇到这个问题，导致相关sql语句出现大量子光标，但是不知道如何关闭这个特性，以为是某个隐含参数，实际上通过
--//使用EXEC DBMS_STATS.SET_GLOBAL_PREFS修改AUTO_TASK_STATUS属性等于on,普通的服务器打开会报ORA-40216 feature not
--//supported错误。尝试解决，并做一个记录。
1.问题提出：
--//实际上这个测试主要原因是2024年底以及2025年初遇到的问题，生产系统exadata服务器(19c)，许多sql语句出现大量子光标。
SYS@127.0.0.1:9105/xtdb/xtdb1> @ unshare 1avrbsmrhcggw
SQL_ID NONSHARED_REASON COUNT(*)
------------- ----------------------------- ----------
1avrbsmrhcggw ROLL_INVALID_MISMATCH 1068
1avrbsmrhcggw HASH_MATCH_FAILED 250
1avrbsmrhcggw BIND_MISMATCH 2
1avrbsmrhcggw PURGED_CURSOR 8
--//主要原因ROLL_INVALID_MISMATCH。
--//不能共享的主要原因是ROLL_INVALID_MISMATCH，是由于表分析太多造成的，当时有点不理解，就算每天分析，也就是每天变化1次，
--//不大可能产生这么多子光标。当时不知道如何关闭这个特性，直接采用lock统计信息的方法。
SYS@127.0.0.1:9105/xtdb/xtdb1> column PARTITION_NAME noprint
SYS@127.0.0.1:9105/xtdb/xtdb1> column SUBPARTITION_NAME noprint
SYS@127.0.0.1:9105/xtdb/xtdb1> @ tabh PPPPPP_HHH.MS_YSKS
OWNER TABLE_NAME STATS_UPDATE_TIME STATS_UPDATE_TIME1
---------- ---------- --------------------------------- -------------------
PPPPPP_HHH MS_YSKS 2024-11-29 22:00:25.680175 +08:00 2024-11-29 22:00:25
PPPPPP_HHH MS_YSKS 2024-11-30 10:06:17.955187 +08:00 2024-11-30 10:06:17
PPPPPP_HHH MS_YSKS 2024-11-30 14:06:52.521970 +08:00 2024-11-30 14:06:52
PPPPPP_HHH MS_YSKS 2024-11-30 18:07:26.761619 +08:00 2024-11-30 18:07:26
PPPPPP_HHH MS_YSKS 2024-12-01 10:09:50.017601 +08:00 2024-12-01 10:09:50
PPPPPP_HHH MS_YSKS 2024-12-01 14:09:42.921804 +08:00 2024-12-01 14:09:42
PPPPPP_HHH MS_YSKS 2024-12-01 18:10:08.231917 +08:00 2024-12-01 18:10:08
PPPPPP_HHH MS_YSKS 2024-12-02 22:00:37.148594 +08:00 2024-12-02 22:00:37
PPPPPP_HHH MS_YSKS 2024-12-03 22:00:21.881256 +08:00 2024-12-03 22:00:21
PPPPPP_HHH MS_YSKS 2024-12-04 22:00:32.121706 +08:00 2024-12-04 22:00:32
PPPPPP_HHH MS_YSKS 2024-12-05 22:00:35.442210 +08:00 2024-12-05 22:00:35
PPPPPP_HHH MS_YSKS 2024-12-06 22:00:21.377552 +08:00 2024-12-06 22:00:21
PPPPPP_HHH MS_YSKS 2024-12-07 10:01:17.145881 +08:00 2024-12-07 10:01:17
PPPPPP_HHH MS_YSKS 2024-12-07 14:01:51.817844 +08:00 2024-12-07 14:01:51
PPPPPP_HHH MS_YSKS 2024-12-07 18:02:29.512253 +08:00 2024-12-07 18:02:29
PPPPPP_HHH MS_YSKS 2024-12-08 10:04:49.914048 +08:00 2024-12-08 10:04:49
PPPPPP_HHH MS_YSKS 2024-12-08 14:05:23.217829 +08:00 2024-12-08 14:05:23
PPPPPP_HHH MS_YSKS 2024-12-09 22:00:27.507467 +08:00 2024-12-09 22:00:27
PPPPPP_HHH MS_YSKS 2024-12-10 22:00:27.678509 +08:00 2024-12-10 22:00:27
PPPPPP_HHH MS_YSKS 2024-12-11 22:00:22.051842 +08:00 2024-12-11 22:00:22
PPPPPP_HHH MS_YSKS 2024-12-12 22:00:21.935809 +08:00 2024-12-12 22:00:21
PPPPPP_HHH MS_YSKS 2024-12-13 22:00:23.004944 +08:00 2024-12-13 22:00:23
PPPPPP_HHH MS_YSKS 2024-12-14 10:04:13.585980 +08:00 2024-12-14 10:04:13
PPPPPP_HHH MS_YSKS 2024-12-14 14:04:04.649380 +08:00 2024-12-14 14:04:04
PPPPPP_HHH MS_YSKS 2024-12-15 10:05:16.206171 +08:00 2024-12-15 10:05:16
PPPPPP_HHH MS_YSKS 2024-12-15 14:05:08.616017 +08:00 2024-12-15 14:05:08
PPPPPP_HHH MS_YSKS 2024-12-16 22:00:25.254034 +08:00 2024-12-16 22:00:25
PPPPPP_HHH MS_YSKS 2024-12-17 22:00:27.346972 +08:00 2024-12-17 22:00:27
PPPPPP_HHH MS_YSKS 2024-12-18 22:00:20.859693 +08:00 2024-12-18 22:00:20
PPPPPP_HHH MS_YSKS 2024-12-19 22:00:20.249083 +08:00 2024-12-19 22:00:20
PPPPPP_HHH MS_YSKS 2024-12-20 22:00:21.718190 +08:00 2024-12-20 22:00:21
PPPPPP_HHH MS_YSKS 2024-12-21 10:00:16.869548 +08:00 2024-12-21 10:00:16
PPPPPP_HHH MS_YSKS 2024-12-21 14:00:39.604991 +08:00 2024-12-21 14:00:39
PPPPPP_HHH MS_YSKS 2024-12-22 10:01:34.106071 +08:00 2024-12-22 10:01:34
PPPPPP_HHH MS_YSKS 2024-12-22 14:01:32.167540 +08:00 2024-12-22 14:01:32
PPPPPP_HHH MS_YSKS 2024-12-23 22:00:23.801285 +08:00 2024-12-23 22:00:23
PPPPPP_HHH MS_YSKS 2024-12-24 22:00:29.574435 +08:00 2024-12-24 22:00:29
PPPPPP_HHH MS_YSKS 2024-12-25 22:00:25.518361 +08:00 2024-12-25 22:00:25
PPPPPP_HHH MS_YSKS 2024-12-26 22:00:25.620270 +08:00 2024-12-26 22:00:25
PPPPPP_HHH MS_YSKS 2024-12-27 22:00:24.333870 +08:00 2024-12-27 22:00:24
PPPPPP_HHH MS_YSKS 2024-12-28 10:06:34.885662 +08:00 2024-12-28 10:06:34
PPPPPP_HHH MS_YSKS 2024-12-28 14:07:04.139375 +08:00 2024-12-28 14:07:04
PPPPPP_HHH MS_YSKS 2024-12-28 18:07:43.906366 +08:00 2024-12-28 18:07:43
PPPPPP_HHH MS_YSKS 2024-12-29 10:08:32.957176 +08:00 2024-12-29 10:08:32
PPPPPP_HHH MS_YSKS 2024-12-29 14:08:40.863353 +08:00 2024-12-29 14:08:40
45 rows selected.
-- exec dbms_stats.restore_table_stats('PPPPPP_HHH','MS_YSKS','2024-12-29 14:08:40',No_Invalidate => false)
--//lock后再也没有分析.
--//仔细查看表MS_YSKS的分析历史记录，可以发现经常分析.
--//如果仔细看STATS_UPDATE_TIME时间,发现更新统计的时间并不是每次出现在晚上22点.出现10,14,18点存在分析的情况.
--//视乎是19c相关设置的特点,并不是在特定时间分析,而是根据业务的变化分析表存在大量记录修改或者插入时,主动触发分析表的情况.
--//当时不知道如何控制这个特性。
--//使用如下脚本可以查询经常分析的表：
SELECT object_id,data_object_id,owner,object_name
FROM dba_objects
WHERE object_id IN (select /*+ unnest*/ obj# FROM sys.optstat_snapshot$ WHERE flags = 32 GROUP BY obj# HAVING count(*)>=20);
--//现在知道如何取消这个特性，个人建议还是关闭它，当时生产系统遇到的表MS_YSKS存在一些更新特点，看dml语句如下：
--//扫描共享池可以发现这个表的特点,我估计早上重置(7点左右),然后修改字段YGRS，JZXH每次增加1次(PDHM类似).字段YYRS减少1次.
1c7cdh8bd454h update MS_YSKS set YGRS =YGRS + 1 , JZXH =JZXH + 1 where MZKS =:1 and YSDM =:2 and JZXH =:3
4p726vfq5wqsk update MS_YSKS set YGRS =YGRS + 1 , JZXH =JZXH + 1 , YYRS =YYRS - 1 where MZKS =:1 and YSDM =:2 and JZXH =:3
c1ma61n3b4umm update MS_YSKS Set YGRS =:1 , YYRS =:2 , JZXH =1 , PDHM =:3 Where MZKS =:4 and YSDM =:5 --//重置
f7vdux1p99zr0 update MS_YSKS set YGRS = YGRS + 1,JZXH = JZXH + 1,YYRS = YYRS - 1 where MZKS = :ksdm and YSDM = :ysdm
8tc5tds0nyth5 update MS_YSKS SET PDHM =PDHM + 1 WHERE MZKS =:1 and YSDM =:2
--//因为开始业务存在大量这些update语句，导致经常分析表。
--//而且导致与该表查询相关的sql语句存在大量的子光标。
--//在测试环境做一个测试：
2.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
SYS@book> EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON');
BEGIN DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON'); END;
*
ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 27084
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 12340
ORA-06512: at "SYS.DBMS_STATS", line 52409
ORA-06512: at "SYS.DBMS_STATS", line 52658
ORA-06512: at line 1
--//通过MOS查询需要开启一个隐患参数并重启数据库服务,实际上这个特性仅仅在exadata机器有效。
SYS@book> alter system set "_exadata_feature_on"=true scope=spfile;
System altered.
--//重新启动数据库，再次尝试：
SYS@book01p> EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON');
PL/SQL procedure successfully completed.
--//注：仅仅在pdb级别打开它。
SCOTT@book01p> @ dbms_stats ^auto
SNAME SVAL1 SPARE1 SPARE4
------------------------------ ---------- ------ ------------------------------
AUTOSTATS_TARGET 1 AUTO
AUTO_STATS_ADVISOR_TASK 1 TRUE
AUTO_STAT_EXTENSIONS 1 OFF
AUTO_TASK_INTERVAL 1 900
AUTO_TASK_MAX_RUN_TIME 1 3600
AUTO_TASK_STATUS 1 ON
GRANULARITY 1 AUTO
7 rows selected.
--//仅仅查询AUTO开头的统计属性。
3.简单测试其相关功能：
SCOTT@book01p> create table t as select * from all_objects;
Table created.
SCOTT@book01p> @ sl all
alter session set statistics_level = all;
Session altered.
SCOTT@book01p> Select count(*) from t;
COUNT(*)
----------
69912
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2xf6hp7cvffsn, child number 0
-------------------------------------
Select count(*) from t
Plan hash value: 2966233522
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 411 (100)| | 1 |00:00:00.03 | 1477 | 1475 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.03 | 1477 | 1475 |
| 2 | TABLE ACCESS FULL| T | 1 | 69912 | 411 (1)| 00:00:01 | 69912 |00:00:00.03 | 1477 | 1475 |
----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / "T"@"SEL$1"
20 rows selected.
--//可以发现E-Rows，A-Rows都有记录，并且正确，这个是因为12c以后这类加载模式自动生成统计信息。通过参数
--//_optimizer_gather_stats_on_load控制。
SCOTT@book01p> insert into t select * from t ;
69912 rows created.
SCOTT@book01p> commit ;
Commit complete.
--//相当于产生大量的插入。理论给等AUTO_TASK_INTERVAL=900秒(15分钟)，测试需要直接刷新看看。
SCOTT@book01p> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SCOTT@book01p> alter system flush shared_pool;
System altered.
SCOTT@book01p> Select count(*) from t;
COUNT(*)
----------
139824
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2xf6hp7cvffsn, child number 0
-------------------------------------
Select count(*) from t
Plan hash value: 2966233522
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 829 (100)| | 1 |00:00:00.01 | 2964 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 2964 |
| 2 | TABLE ACCESS FULL| T | 1 | 139K| 829 (1)| 00:00:01 | 139K|00:00:00.01 | 2964 |
-------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / "T"@"SEL$1"
20 rows selected.
--//注意看刷新共享池后，重新执行的sql语句，E-Rows估计正确，可以推测T重新做了分析。
SCOTT@book01p> @ tab2z scott.^t$
Show tables matching condition "scott.^t$" (if schema is not specified then current user's tables only are shown)...
OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED DEGREE COMPRESS
----- ---------- ---- ------------ ------------- --------- ------ ------ ------------------- -------------------- --------
SCOTT T TAB 139824 3048 0 0 145 2026-04-06 16:14:23 1 DISABLED
--//NUM_ROWS=139824，可以发现表已经做了新的统计分析。
SCOTT@book01p> @ tab_mod scott.t
no rows selected
OBJ# INSERTS UPDATES DELETES FLAGS TIMESTAMP
---------- ---------- ---------- ---------- ---------- ------------------------------------
207440 69912 0 0 32 2026-04-06 16:14:23.899202 +08:00
--//在表T的监测里面记录插入69912条记录，FLAGS=32是触发表分析的标志，INSERTS，UPDATES，DELETES字段记录的信息是累积的。
4.小结：
--//当时不知道如何取消这个特性，个人建议还是关闭它，主要问题是导致相关表的sql语句出现大量子光标。
--//测试参考如下链接：
https://gavinsoorma.com.au/knowledge-base/oracle-19c-new-feature-real-time-statistics/
https://gavinsoorma.com.au/knowledge-base/oracle-19c-new-feature-high-frequency-statistics/
5.收尾：
SCOTT@book01p> EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','OFF');
PL/SQL procedure successfully completed.
SYS@book> alter system reset "_exadata_feature_on";
System altered.
6.附上测试使用脚本：
$ cat dbms_stats.sql
column SPARE4 format a30
column spare1 format 99999
set term off
col 1 new_value 1
select null "1" from dual where 1=2;
select decode('&&1',null,'*',replace('&&1','%','*')) "1" from dual ;
set term on
SELECT sname
,sval1
,spare1
,spare4
from sys.optstat_hist_control$
WHERE REGEXP_LIKE (sname, '&1', 'i') OR REGEXP_LIKE (spare4, '&1', 'i')
ORDER BY sname;
$ cat tab_mod.sql
column TRUNCATED format a12
column table_owner format a12
column table_name format a20
column PARTITION_NAME format a20
column SUBPARTITION_NAME format a20
set verify off
set term off
column v_owner new_value v_owner
column v_table new_value v_table
select
upper(CASE
WHEN INSTR('&1','.') > 0 THEN
SUBSTR('&1',INSTR('&1','.')+1)
ELSE
'&1'
END
) v_table,
CASE WHEN INSTR('&1','.') > 0 THEN
UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
ELSE
user
END v_owner
from dual;
define noprint='noprint'
col tpt_version_old &noprint new_value _tpt_version_old
col tpt_version_new &noprint new_value _tpt_version_new
col tpt_noprint &noprint new_value _tpt_noprint
WITH version AS (SELECT TO_NUMBER (SUBSTR (version, 1, 2)) v FROM v$instance)
SELECT CASE WHEN v <= 11 THEN '' ELSE '--' END tpt_version_old
,CASE WHEN v > 11 THEN '' ELSE '--' END tpt_version_new
FROM version;
set term on
SELECT * FROM SYS.DBA_TAB_MODIFICATIONS WHERE table_owner = '&v_owner' and table_name = '&v_table';
column DUMMY noprint
select * from
&&_tpt_version_new (select * from sys.optstat_snapshot$ where obj# in (select object_id from dba_objects where owner = '&v_owner' and object_name = '&v_table') order by TIMESTAMP desc) where rownum<=40
&&_tpt_version_old dual
;
column DUMMY clear
$ cat tabh.sql
set term off
column v_owner new_value v_owner
column v_table new_value v_table
column STATS_UPDATE_TIME1 new_value V_STATS_UPDATE_TIME1
select
upper(CASE
WHEN INSTR('&1','.') > 0 THEN
SUBSTR('&1',INSTR('&1','.')+1)
ELSE
'&1'
END
) v_table,
CASE WHEN INSTR('&1','.') > 0 THEN
UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
ELSE
user
END v_owner
from dual;
set term on
select DBA_TAB_STATS_HISTORY.*,
to_char(STATS_UPDATE_TIME,'yyyy-mm-dd hh24:mi:ss') STATS_UPDATE_TIME1
from DBA_TAB_STATS_HISTORY
where
upper(table_name) LIKE
upper(CASE
WHEN INSTR('&1','.') > 0 THEN
SUBSTR('&1',INSTR('&1','.')+1)
ELSE
'&1'
END
) ESCAPE '\'
AND owner LIKE
CASE WHEN INSTR('&1','.') > 0 THEN
UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
ELSE
user
END ESCAPE '\'
order by STATS_UPDATE_TIME
/
prompt
prompt -- exec dbms_stats.restore_table_stats('&v_owner','&v_table','&V_STATS_UPDATE_TIME1',No_Invalidate => false);
prompt
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3115491/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
