[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操作，才可以出现。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2989212/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [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
