[20231011]查询sys.optstat_snapshot$了解表的DML情况.txt

来源：这里教程网时间：2026-03-03 19:00:29 作者：

[20231011]查询sys.optstat_snapshot$了解表的DML情况.txt --//参考连接:https://blog.dbi-services.com/12cr2-dml-monitoring-and-statistics-advisor/ --//查询sys.optstat_snapshot$了解表的DML情况.oracle从12c引入了volatile tables概念,所谓volatile tables可以理解为频繁DML的 --//表.要检测这些特殊情况表的,需要使用更细的粒度跟踪表DML频率.oracle称之为Statistics Advisor. --//我仅仅想通过sys.optstat_snapshot$了解生产系统验证一些dml比较多的表的操作情况。 1.环境: SYS@192.168.100.235:1521/orcl> @ver1 SYS@192.168.100.235:1521/orcl> @ pr ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 19.0.0.0.0 BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. 2.测试: SYS@192.168.100.235:1521/orcl> select * from V$STATS_ADVISOR_RULES where rule_id=14 2 @ pr ============================== RULE_ID : 14 NAME : LockVolatileTable RULE_TYPE : OBJECT DESCRIPTION : Statistics for objects with volatile data should be locked CON_ID : 0 PL/SQL procedure successfully completed. --//exec dbms_stats.set_global_prefs('TRACE',0+524288) --//查看MMON从属跟踪的m001跟踪文件，可以看到相关信息，生产系统，我没有做这样的测试。 SYS@192.168.100.235:1521/orcl> @ o2 lis.lis_test SYS@192.168.100.235:1521/orcl> @ pr ============================== O_OWNER : LIS O_OBJECT_NAME : LIS_TEST O_OBJECT_TYPE : TABLE SEG_PART_NAME : O_STATUS : VALID OID : 73755 D_OID : 76032 CREATED : 2020-11-27 16:43:13 LAST_DDL_TIME : 2023-10-11 08:49:24 PL/SQL procedure successfully completed. SYS@192.168.100.235:1521/orcl> @ zzdate C30 C30 C31 -------------------------------------- -------------------------------------- -------------------------------------- 2023-10-11 10:08:13 trunc(sysdate)+10/24+08/1440+13/86400 "timestamp'2023-10-11 10:08:13'" SYS@192.168.100.235:1521/orcl> select * from ( select * from sys.optstat_snapshot$ where obj#=73755 order by timestamp) where rownum<=4; OBJ# INSERTS UPDATES DELETES FLAGS TIMESTAMP ---------- ---------- ---------- ---------- ---------- --------------------------------- 73755 1940851 16860346 30090 0 2023-09-10 07:30:53.944887 +08:00 73755 1941405 16863899 30092 0 2023-09-10 08:30:57.076361 +08:00 73755 1944240 16877068 30099 0 2023-09-10 09:30:59.991730 +08:00 73755 1946088 16896472 30107 0 2023-09-10 10:31:02.518259 +08:00 --//最早可以记录到2023-09-10. SYS@192.168.100.235:1521/orcl> select * from sys.optstat_snapshot$ where obj#=73755 and timestamp>=trunc(sysdate-1)+12/24 order by timestamp; OBJ# INSERTS UPDATES DELETES FLAGS TIMESTAMP ---------- ---------- ---------- ---------- ---------- --------------------------------- 73755 2412434 21172076 36796 0 2023-10-10 12:32:55.065224 +08:00 73755 2412797 21191786 36810 0 2023-10-10 13:32:57.623819 +08:00 73755 2413140 21204190 36821 0 2023-10-10 14:33:00.052246 +08:00 73755 2413629 21213268 36823 0 2023-10-10 15:33:02.536754 +08:00 --//没有16点的数据,why？ 73755 2414584 21222361 36842 0 2023-10-10 17:18:06.894644 +08:00 73755 2414993 21226609 36854 0 2023-10-10 18:18:09.558982 +08:00 73755 2415192 21228862 36857 0 2023-10-10 19:18:12.169457 +08:00 73755 2415429 21230807 36861 0 2023-10-10 20:18:14.722482 +08:00 73755 2415635 21232509 36862 0 2023-10-10 21:18:17.524019 +08:00 73755 2415760 21233949 36864 0 2023-10-10 22:18:20.062678 +08:00 73755 2415913 21235457 36869 0 2023-10-10 23:18:22.893430 +08:00 73755 2415988 21236141 36873 0 2023-10-11 00:18:25.829606 +08:00 73755 2416079 21236778 36874 0 2023-10-11 01:18:28.532687 +08:00 73755 2416112 21237191 36874 0 2023-10-11 02:18:31.218668 +08:00 73755 2416166 21237584 36875 0 2023-10-11 03:18:33.868493 +08:00 73755 2416198 21238002 36877 0 2023-10-11 04:18:36.608717 +08:00 73755 2416213 21238279 36877 0 2023-10-11 05:18:39.187901 +08:00 73755 2416344 21239009 36881 0 2023-10-11 06:18:41.786330 +08:00 73755 2416499 21240235 36882 0 2023-10-11 07:18:44.397096 +08:00 73755 2417558 21244292 36884 0 2023-10-11 08:18:47.249510 +08:00 73755 2421808 21262610 36907 0 2023-10-11 09:18:49.855098 +08:00 21 rows selected. --//查询昨天12点开始到现在的情况,可以发现每一个小时记录1次.有点特殊的地方就是16点没有出现数据，出现了延后的情况。我不知 --//道为什么? --//字段flag有特殊含义,按照作者的介绍: 32表示dbms_stats分析过. 1表示truncate过. 64标识是volatile tables. --//参考连接:https://blog.dbi-services.com/12cr2-dml-monitoring-and-statistics-advisor/ --//你可以发现这些值是累积的,存在小量删除操作. --//拿最后两条记录分析,不考虑delete的情况。 --//(21244292-21262610)/(2417558-2421808) = 4.31011764705882352941 --//但是可以看出几乎每插入1条记录,就修改4次，这样导致产生大量归档日志。 --//可以参考以前连接:https://www.cnblogs.com/lfree/p/17169667.html --//再看看lis.lis_result的情况. SYS@192.168.100.235:1521/orcl> @ o2 lis.lis_result SYS@192.168.100.235:1521/orcl> @ pr ============================== O_OWNER : LIS O_OBJECT_NAME : LIS_RESULT O_OBJECT_TYPE : TABLE SEG_PART_NAME : O_STATUS : VALID OID : 73760 D_OID : 73760 CREATED : 2020-11-27 16:43:13 LAST_DDL_TIME : 2023-09-06 18:37:55 PL/SQL procedure successfully completed. SYS@192.168.100.235:1521/orcl> select * from sys.optstat_snapshot$ where obj#=73760 and timestamp>=trunc(sysdate-1)+12/24 order by timestamp; OBJ# INSERTS UPDATES DELETES FLAGS TIMESTAMP ---------- ---------- ---------- ---------- ---------- --------------------------------- 73760 33094250 98397255 791373 0 2023-10-10 12:32:55.065224 +08:00 73760 33102806 98432037 792435 0 2023-10-10 13:32:57.623819 +08:00 73760 33106957 98452189 792518 0 2023-10-10 14:33:00.052246 +08:00 73760 33112949 98520401 793780 0 2023-10-10 15:33:02.536754 +08:00 73760 33126784 98600771 793945 0 2023-10-10 17:18:06.894644 +08:00 --//没有16点的数据,why？ 73760 33134222 98620683 794083 0 2023-10-10 18:18:09.558982 +08:00 73760 33137433 98629399 794129 0 2023-10-10 19:18:12.169457 +08:00 73760 33141108 98637243 794212 0 2023-10-10 20:18:14.722482 +08:00 73760 33144303 98643063 794254 0 2023-10-10 21:18:17.524019 +08:00 73760 33147129 98651195 794300 0 2023-10-10 22:18:20.062678 +08:00 73760 33149732 98657970 794415 0 2023-10-10 23:18:22.893430 +08:00 73760 33150973 98661366 794430 0 2023-10-11 00:18:25.829606 +08:00 73760 33152625 98664914 794463 0 2023-10-11 01:18:28.532687 +08:00 73760 33153471 98667063 794469 0 2023-10-11 02:18:31.218668 +08:00 73760 33154348 98669287 794510 0 2023-10-11 03:18:33.868493 +08:00 73760 33155095 98671125 794527 0 2023-10-11 04:18:36.608717 +08:00 73760 33155618 98672886 794531 0 2023-10-11 05:18:39.187901 +08:00 73760 33158322 98679519 794642 0 2023-10-11 06:18:41.786330 +08:00 73760 33162471 98690372 794677 0 2023-10-11 07:18:44.397096 +08:00 73760 33179853 98715629 794732 0 2023-10-11 08:18:47.249510 +08:00 73760 33240142 98842645 795068 0 2023-10-11 09:18:49.855098 +08:00 73760 33284732 99005374 795442 0 2023-10-11 10:18:52.663604 +08:00 22 rows selected. --//(99005374-98842645)/(33284732-33240142) = 3.64945054945054945054 --//(98842645-98715629)/(33240142-33179853) = 2.1067856491233890096 --//情况类似,每插入1条,修改也是接近4次,当然这样看修改语句如何写.这也是我们当前这套系统为什么产生大量日志的原因. --//你可以查询底层视图x$ksxmme了解情况: SYS@192.168.100.235:1521/orcl> select * from x$ksxmme where objn=73760; ADDR INDX INST_ID CON_ID CHUNKN SLOTN OBJN INS UPD DEL DROPSEG CURROWS PAROBJN LASTUSED FLAGS ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 00007F46E2F91148 0 1 0 64 256 73760 7754 31694 45 0 172577663 0 1684208137 640 SYS@192.168.100.235:1521/orcl> host sleep 10 SYS@192.168.100.235:1521/orcl> select * from x$ksxmme where objn=73760; ADDR INDX INST_ID CON_ID CHUNKN SLOTN OBJN INS UPD DEL DROPSEG CURROWS PAROBJN LASTUSED FLAGS ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 00007F46E2FBD3E8 0 1 0 64 256 73760 7791 32192 45 0 172577700 0 1684208137 640 --//7791-7754 = 37 --//172577700-172577663 = 37 --//(32192-31694)/(7791-7754) = 13.45945945945945945945,怎么变化这么大。 --//按照作者的测试sys.optstat_snapshot$的flag位与64出现超过13次(24小时内),才被认为是volatile tables. $ echo "obase=2;640;64" | bc 1010000000 1000000 --//我感觉至少在我的工作中很难遇到这类表，除非大量频繁的DML操作，才可以出现。

编辑推荐：

返回列表

[20231011]查询sys.optstat_snapshot$了解表的DML情况.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推