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模块,并且在数据库服务器本机上执行。如果你对于这个工具有兴趣,可以在这里下载源代码,使用过程中,如果有什么建议和需求,欢迎告诉我。

无觅相关文章插件,快速提升流量