[20231011]查詢sys.optstat_snapshot$瞭解表的DML情況.txt
[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部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2989212/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 查詢鎖等待情況
- [20170406]查詢佔用記憶體情況.txt記憶體
- Oracle 查詢DB的負載情況Oracle負載
- [20130125]DML操作出現交集的情況.txt
- ORACLE SQL的查詢表的鎖的情況OracleSQL
- 初步瞭解表單操作
- 查詢當前正在執行的job的情況
- Mongodb記憶體管理和使用情況情況查詢MongoDB記憶體
- [20130125]DML操作出現交集的情況[補充].txt
- Sqlserver查詢alwayson同步情況指令碼（2）SQLServer指令碼
- Sqlserver查詢alwayson同步情況指令碼（1）SQLServer指令碼
- 月結各模組關閉情況查詢SQLSQL
- 動態sql查詢結果多行的處理情況SQL
- 透過閃回事務檢視資料dml的情況
- 通過閃回事務檢視資料dml的情況
- java查詢資料庫，int型欄位為null的情況Java資料庫Null
- SQLServer DML操作阻塞SELECT查詢SQLServer
- 如何查詢DML操作的詳細記錄
- 批次查詢並設定極兔快遞延誤情況的方法
- 批次查詢並設定中通快遞延誤情況的方法
- 什麼情況下應該分解複雜的查詢來提升效能
- Oracle 11g RAC查詢物理備庫延遲情況Oracle
- 查詢RAC環境中例項間資源分配情況
- 檢查備份情況的指令碼指令碼
- 查詢鎖的狀況的物件物件
- 檢查oracle的patch的升級情況Oracle
- 總結Oracle根據時間查詢的一些常見情況Oracle
- 資料庫一些預設的屬性及情況設定查詢資料庫
- 輿情繫統查詢
- [20200219]strace跟蹤設定ENABLE=BROKEN的情況(網路的情況).txt
- 詳解表連線順序和連線方式是否影響查詢結果
- Oracle 查詢各個 “表空間/資料檔案” 的空間使用比情況Oracle
- 表中出現碎片，造成查詢、DML操作變慢
- ELK查詢---輿情繫統
- 多箇中通快運的物流情況是怎麼批量查詢並儲存到電腦的
- 瞭解通用查詢日誌
- SQL SERVER中什麼情況會導致索引查詢變成索引掃描SQLServer索引
- Java 中如何使用 SQL 查詢 TXTJavaSQL