Oracle如何监控表的DML次数
在数据库技术大会上，做了《构建高可用数据库监控系统》的分享以后，很多朋友对北斗如何实现表的DML次数监控有兴趣，会上因为时间的原因，我只是说有系统视图可以查到这个信息，因此有了本文，可以稍微详细一点来说明是如何实现的。
我说的系统视图，具体指的是dba_tab_modifications/all_tab_modifications/user_tab_modifications，这几个视图收集了表自从上一次分析之后的DML累积次数。但是要注意，考虑到性能的影响，Oracle并不是实时统计这个数据的，在Oracle9i之前，约3个小时SMON进程会刷新一次数据，而Oracle9i以后这个时间间隔变成了15分钟。
因此以较高的频率来实时监控这个表的话，得到的并不是当前的准确数据。Oracle在dbms_stat包中提供了一个过程来手动刷新统计数据，假如在一天的业务低峰期采集一次数据的话，可以先执行该过程，就能得到较为准确的数据。但是，不建议在业务高峰期执行该过程。
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
在Oracle10g之前，必须手工开启表的monitoring属性，才会将DML统计信息收集到这个视图中。可以通过dba_tables.monitoring列查看表是否已经开启了监控。关于这个变化，可以参考Metalink ID 252597.1
alter table test monitoring;
Oracle10g之后，只要statistics_level是TYPICAL（默认）或者ALL，就能自动收集信息了，即使给表设置为nomonitoring也不能阻止，这个表的属性已经被废弃了。
desc dba_tab_modifications Name Null? Type ----------------------------------------- -------- ---------------------------- TABLE_OWNER VARCHAR2(30) TABLE_NAME VARCHAR2(30) PARTITION_NAME VARCHAR2(30) SUBPARTITION_NAME VARCHAR2(30) INSERTS NUMBER UPDATES NUMBER DELETES NUMBER TIMESTAMP DATE TRUNCATED VARCHAR2(3) DROP_SEGMENTS NUMBER
数据库技术大会PPT：构建高可用数据库监控系统
这两天在北京参加IT168组织的2010数据库技术大会，应该说这次大会非常的成功，场场爆满。见到了很多老朋友，认识了更多的新朋友，不亦乐乎。
今天下午第一场，我分享了一个关于数据库监控系统的topic，开场时话筒出现了些小插曲，加上发现PPT不是我后来提交给大会的最新版本，当时就有点发懵，汗就开始往外冒了。看来以后要多多练习提高演讲的水平和临场的应变控制能力了。希望我结巴的话语不至于让大家感到门票白买了。就数据库监控这个话题本身而言，我相信大部分DBA应该都是有兴趣，并且也是有自己的一些心得的，因此，我带来这个主题，一个重要的目的，是希望能起到抛砖引玉的作用，希望能有更多的DBA能去关注这方面，并且一起交流分享如何把数据库监控做到极致。如果有对数据库监控感兴趣，有想法的朋友，也非常的欢迎来杭州，和我们一起来把这个产品做得更好，或许真有一天能做成产品开源出来也是可能的。
下面是新版本的PPT，和大会上大家看到的可能有一点区别，大家如果对数据库监控有什么建议，欢迎指教。
dstat:一款简单直观的os实时监控工具
在oschina上闲逛，发现一款不错的os实时监控工具dstat，整合了vmstat, iostat, ifstat, netstat等常见os监控工具的优点，输出的结果简单直观，并且结果可以保存到csv文件，这样再写一个简单的perl脚本，就能将os的主要监控信息一次性全部抓取出来，保存到监控数据库中用于分析展示。试用了一下觉得非常不错，因此在这里分享一下这个用python写的工具。
$dstat ----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system-- usr sys idl wai hiq siq| read writ| recv send| in out | int csw 2 0 98 0 0 0| 80k 54k| 0 0 | 335B 381B|1297 1301 22 2 74 0 0 2| 0 416k| 621k 219k| 0 0 |1158 26k 23 3 72 0 0 2| 64k 484k| 11k 11k| 0 0 |1109 30k 21 3 75 0 0 2|4096B 416k| 77k 77k| 0 0 |2104 25k 29 4 66 0 0 2| 0 1240k| 996k 425k| 0 0 |1350 28k
$dstat -ta --output osstat.csv -----time----- ----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system-- date/time |usr sys idl wai hiq siq| read writ| recv send| in out | int csw 05-02 11:37:08| 2 0 98 0 0 0| 80k 54k| 0 0 | 335B 381B|1297 1301 05-02 11:37:09| 16 4 78 0 0 3| 0 1404k|1478k 939k| 0 0 |4316 33k 05-02 11:37:10| 20 2 76 0 0 2| 0 1144k|1109k 828k| 0 0 |5653 28k 05-02 11:37:11| 13 2 83 0 0 2| 0 588k|2590k 1684k| 0 0 |4256 23k
$dstat -h
Usage: dstat [-afv] [options..] [delay [count]]
Versatile tool for generating system resource statistics
Dstat options:
-c, --cpu enable cpu stats
-C 0,3,total include cpu0, cpu3 and total
-d, --disk enable disk stats
-D total,hda include hda and total
-g, --page enable page stats
-i, --int enable interrupt stats
-I 5,eth2 include int5 and interrupt used by eth2
-l, --load enable load stats
-m, --mem enable memory stats
-n, --net enable network stats
-N eth1,total include eth1 and total
-p, --proc enable process stats
-s, --swap enable swap stats
-S swap1,total include swap1 and total
-t, --time enable time/date output
-T, --epoch enable time counter (seconds since epoch)
-y, --sys enable system stats
--ipc enable ipc stats
--lock enable lock stats
--raw enable raw stats
--tcp enable tcp stats
--udp enable udp stats
--unix enable unix stats
-M stat1,stat2 enable external stats
--mods stat1,stat2
-a, --all equals -cdngy (default)
-f, --full expand -C, -D, -I, -N and -S discovery lists
-v, --vmstat equals -pmgdsc -D total
--integer show integer values
--nocolor disable colors (implies --noupdate)
--noheaders disable repetitive headers
--noupdate disable intermediate updates
--output file write CSV output to file
delay is the delay in seconds between each update
count is the number of updates to display before exiting
The default delay is 1 and count is unspecified (unlimited)
tbstat:实时监控数据库统计状态的小工具
用perl写了一个简单的工具，用于实时查看数据库的统计状态信息，展现信息主要来源于Oracle数据字典中v$systat和v$system_event。写这个工具的初衷，是因为目前我们对于数据库的监控，更多的是分钟级别抽样的数据来绘制的图形，粒度相对还比较粗，有一些比较深的问题，需要更加细粒度的数据。而如果把监控的粒度做到秒级，则收集的数据量就会非常大，因此需要一个平衡，平时采用分钟级别的抽样数据已经足够用于预警，而秒级的则用于某个具体问题的分析。
当前tbstat功能还比较简单，类似于iostat/vmstat等os工具，tbstat可以通过指定抽样间隔和抽样次数，来循环抓取Oracle的统计状态信息。tbstat支持三个参数 -i 表示间隔时间 -c 表示循环次数 -n 表示需要查看的统计信息的名字(使用前后%的like来查询)
- tbstat -i 2 -c 10 表示间隔时间2s，循环次数10次，展示经过人工筛选的36项统计信息
- tbstat -i 2 -c 10 -n parse 表示间隔时间2s，循环次数10次，展示所有名字包含parse的统计信息
- tbstat -i 2 -c 10 -n all 表示间隔时间2s，循环次数10次，展示所有不为零的统计信息
也可以使用简化的参数输入方法，第一位表示间隔时间，第二位表示循环次数，第三位表示统计信息名。直接敲入tbstat，则默认的参数为间隔时间10s，次数无限，经过挑选过滤的一些常用的v$sysstat中的统计信息。如果name参数传入的值是event，则展示v$system_event中的等待事件的信息。
$tbstat 1 0
--------------------------------------------------------------------------
-- tbstat v0.3.3 --- a tool for oracle system statistics and event.
-- Powered by NinGoo.net
--------------------------------------------------------------------------
CPU used by this session: 40 CR blocks created: 5
DBWR checkpoint buffers written: 569 DBWR undo block writes: 64
bytes received via SQL*Net from client: 314297 bytes sent via SQL*Net to client: 2761660
cleanouts only - consistent read gets: 4 consistent gets: 48855
db block changes: 2122 db block gets: 3714
enqueue requests: 900 enqueue waits: 7
execute count: 3145 free buffer requested: 1402
index crx upgrade (positioned): 3 index fast full scans (full): 0
leaf node 90-10 splits: 0 leaf node splits: 0
logons cumulative: 1 parse count (failures): 0
parse count (hard): 0 physical reads: 1546
physical reads cache prefetch: 0 physical writes: 603
redo size: 618436 redo synch time: 16
redo synch writes: 181 redo write time: 15
redo writes: 174 rollbacks only - consistent read gets: 0
sorts (disk): 0 sorts (memory): 259
table scans (long tables): 0 table scans (short tables): 9
transaction rollbacks: 0 user commits: 182
$tbstat 1 0 event
-------------------------------------------------------------------------------
-- tbstat v0.3.3 --- a tool for oracle system statistics and event.
-- Powered by NinGoo.net
-------------------------------------------------------------------------------
Event Name: waits time Event Name: waits time
--------------------------------------------------------------------------------------------
LGWR wait for redo copy: 1 0.01 SQL*Net more data from client: 151 19.95
SQL*Net more data to client: 1218 0.01 buffer busy waits: 2 0.01
control file parallel write: 1 0.51 control file sequential read: 1 0.26
cursor: pin S: 0 0.00 cursor: pin S wait on X: 0 0.00
db file parallel read: 0 0.00 db file parallel write: 0 0.00
db file scattered read: 0 0.00 db file sequential read: 2040 3.43
direct path read: 269 0.71 direct path read temp: 0 0.00
direct path write: 23 0.26 direct path write temp: 0 0.00
enq: CF - contention: 0 0.00 enq: HW - contention: 7 9.00
enq: SQ - contention: 0 0.00 enq: TX - allocate ITL entry: 0 0.00
enq: TX - index contention: 0 0.00 enq: TX - row lock contention: 0 0.00
latch free: 0 0.00 latch: cache buffers chains: 0 0.00
latch: library cache: 0 0.00 latch: redo writing: 0 0.00
latch: session allocation: 0 0.00 library cache lock: 0 0.00
log buffer space: 0 0.00 log file parallel write: 145 0.60
log file sequential read: 145 0.53 log file switch completion: 0 0.00
log file sync: 147 0.78 os thread startup: 0 0.00
read by other session: 0 0.00 row cache lock: 0 0.00
undo segment extension: 0 0.00
如果输入的name是精确匹配到只有一条统计信息的，会在后面打印出间隔时间内排名前10的sid的值。利用此功能，可以很方便的抓到造成某些统计信息异常的会话和SQL语句，会话和SQL信息是通过关联v$session来获取的。因此需要注意，如果统计信息对应的事件持续时间很短，从v$session里抓取到的sql可能并不是造成统计信息升高的罪魁祸首，但是sid一般来说还是准确的，因为应用采用的大多是连接池来连接数据库的，因此还是可以更具sid和machine信息来看看造成异常的是哪个具体的应用。
例如，全表扫描一般会导致physical reads cache prefetch等待事件，因此可以通过查看该事件对应的top sid来获得具体的语句，当然，不是所有的physical reads cache prefetch都是全表扫描导致的，因此对于获得的结果，还需要DBA根据具体情况做进一步分析：
$tbstat 1 0 'physical reads cache prefetch'
-------------------------------------------------------------------------------
-- tbstat v0.3.3 --- a tool for oracle system statistics and event.
-- Powered by NinGoo.net
-------------------------------------------------------------------------------
physical reads cache prefetch: 526
sid value % machine sql_id
---------- ----------- ----- ------------------- --------------
2928 302 69.7 test11 79db58a3dg921
4902 67 15.5 test71 79db58a3dg921
4821 64 14.8 test33 3afdq50xt03ch
4544 0 0.0 test54 3afdq50xt03ch
1801 0 0.0 test06 79db58a3dg921
2830 0 0.0 test12 79db58a3dg921
898 0 0.0 test09 4n7675hwwcndc
1031 0 0.0 test16 79db58a3dg921
463 0 0.0 test04 3afdq50xt03ch
1364 0 0.0 test08 cq749u66x06uj
1408 0 0.0 test27 39rbqj3ck76w3
722 0 0.0 test37 26hdkf07336uf
当然，tbstat只是一个用于抽取统计状态的小工具而已，如果要用于故障诊断，则还是要求DBA对于v$systat和v$system_event中各种统计和事件非常的熟悉。tbstat使用了DBD::Oracle以sysdba身份来连接数据库，因此需要为Perl安装DBI和DBD::Oracle模块，并且在数据库服务器本机上执行。如果你对于这个工具有兴趣，可以在这里下载源代码，使用过程中，如果有什么建议和需求，欢迎告诉我。
