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
遭遇Oracle11gR2 ASM文件无法扩展的Bug
在一个11gR2+ASM的环境中，因为产生了大量归档，导致控制文件需要扩展，结果数据库报错：
Errors in file /opt/oracle/diag/rdbms/test/test/trace/test_arc0_11146.trc: ORA-00202: control file: '+DATA/control01.ctl' ORA-17505: ksfdrsz:1 Failed to resize file to size 1920 blocks ORA-15061: ASM operation not supported [41] Control file expansion from 1600 blocks to 1920 blocks denied by OS
这是ASM的一个bug 8898852，可以在Oracle Support上找到对应的小patch，经过验证可以解决该问题。该patch已经包含在前两天刚发布的ASM的PSU中，只需要安装该PSU即可。
从目前我们使用11gR2的一些经验来看，bug还是比较多，尤其是一些影响比较大的bug，还是让人对11gR2无法完全放心，只能在特定的环境，和有足够容错方案的环境中，才能冒着风险来试用。
除了这个ASM文件不能扩展，有几个从10.2.0.4升级到11.2.0.1的库，在switchover中碰到了两次ORA-600 [ktbdchk1: bad dscn]，出现问题后无法执行DML，非常严重的一个问题，并且暂时还没有好的办法解决，只能通过重建有问题的表的方式绕过，因为两次都是在切换后出现，初步推断是Active Data Guard带来的bug，开了SR和Oracle扯了很久，还在继续研究中。
据说Oracle 11.2.0.2将在年中发布，希望这个新的版本能更稳定些吧。
4月份，Oracle11.2.0.1的PSU和ASM(Grid Infrastructure)的第一个PSU都已经发布了，如果需要在产品环境中使用，建议这些PSU都打上吧
| Oracle Database PSU | Unix Patch | Comments |
|---|---|---|
|
11.2.0.1.1 |
||
|
11.2.0.1.1 for GI |
||
|
11.1.0.7.3 |
||
|
11.1.0.7.2 for CRS |
Released in January 2010 |
|
|
10.2.0.4.4 |
||
|
10.2.0.4.4 for CRS |
注：该表格摘自My Oracle Support Note: 854428.1l
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模块，并且在数据库服务器本机上执行。如果你对于这个工具有兴趣，可以在这里下载源代码，使用过程中，如果有什么建议和需求，欢迎告诉我。
使用BBED帮助理解Oracle数据块结构
BBED是Oracle提供的块编辑器，借助BBED，可以帮助我们更好的理解Oracle的Block的结构。当然，反过来说，也只有更加理解块的结构，才能更好的利用BBED完成某些特殊情况下的灾难恢复。
Oracle Data Block的结构简图如下，其中从Data header到Row Data部分合称Data Layer:
--------------------- - Cache Layer - --------------------- - Transaction Layer - --------------------- - Data Header - --------------------- - Table Directory - --------------------- - Row Directory - --------------------- - Free Space - --------------------- - Row Data - --------------------- - Tailchk - ---------------------
通过bbed的map命令，可以看到数据块内部的一些数据结构名：
BBED> map File: /u01/oracle/oradata/dbmon/system.dbf (1) Block: 31729 Dba:0x00407bf1 ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 struct ktbbh, 72 bytes @20 struct kdbh, 14 bytes @92 struct kdbt[1], 4 bytes @106 sb2 kdbr[336] @110 ub1 freespace[821] @782 ub1 rowdata[6585] @1603 ub4 tailchk @8188
Cache Layer：Block的第一部分，长度为20字节，内部数据结构名为kcbh，包括
type_kcbh:块类型(table/index,rollback segment,temporary segment等)
frmt_kcbh:块格式(v6,v7,v8)
rdba_kcbh:块地址DBA
bas_kcbh/wrp_kcbh:SCN
seq_kcbh:块的序列号
flg_kcbh:块的标志
BBED> p kcbh struct kcbh, 20 bytes @0 ub1 type_kcbh @0 0x06 ub1 frmt_kcbh @1 0xa2 ub1 spare1_kcbh @2 0x00 ub1 spare2_kcbh @3 0x00 ub4 rdba_kcbh @4 0x00407bf1 ub4 bas_kcbh @8 0xd6449de8 ub2 wrp_kcbh @12 0x0595 ub1 seq_kcbh @14 0x03 ub1 flg_kcbh @15 0x04 (KCBHFCKV) ub2 chkval_kcbh @16 0x9130 ub2 spare3_kcbh @18 0x0000
Transaction Layer：内部结构名kcbbh。分成两部分，第一部分为固定长度，长度为24字节，包含事务相关的一些基本信息。第二部分为可变长度，包含itl，长度根据itl条目的个数变化，每个itl长度为24字节，内部结构名ktbbhitl。
BBED> p ktbbh
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x000050fc
ub4 ktbbhod1 @24 0x000050fc
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0xd6449de7
ub2 kscnwrp @32 0x0595
b2 ktbbhict @36 2
ub1 ktbbhflg @38 0x02 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x00000000
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x0008
ub2 kxidslt @46 0x000c
ub4 kxidsqn @48 0x0000e991
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x0080222e
ub2 kubaseq @56 0x03a4
ub1 kubarec @58 0x22
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
b2 _ktbitfsc @62 1429
ub2 _ktbitwrp @62 0x0595
ub4 ktbitbas @64 0xd6449de6
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0008
ub2 kxidslt @70 0x0015
ub4 kxidsqn @72 0x0000e992
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x0080222e
ub2 kubaseq @80 0x03a4
ub1 kubarec @82 0x23
ub2 ktbitflg @84 0x0001 (NONE)
union _ktbitun, 2 bytes @86
b2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x00000000
Data Layer：包括Data Header，Table Directory，Row Directory，Free Space和Row Data。其中
Data Header：长度14字节，内部数据结构名kdbh
BBED> p kdbh struct kdbh, 14 bytes @92 ub1 kdbhflag @92 0x00 (NONE) b1 kdbhntab @93 1 b2 kdbhnrow @94 336 sb2 kdbhfrre @96 -1 sb2 kdbhfsbo @98 690 sb2 kdbhfseo @100 1511 b2 kdbhavsp @102 821 b2 kdbhtosp @104 821
Table Directory: 一般table只有一个条目，cluster则有一个或多个条目。每个条目长4字节，内部数据结构名kdbt。
BBED> p kdbt struct kdbt[0], 4 bytes @106 b2 kdbtoffs @106 0 b2 kdbtnrow @108 336
Row Directory：数目由块中数据的行数决定，每个条目长2字节，内部数据结构名kdbr
BBED> p kdbr sb2 kdbr[0] @110 7998 sb2 kdbr[1] @112 8017 ... sb2 kdbr[335] @780 1511
Free Space：表示数据块中可用空间，内部数据结构名freespace
Row Data：表示实际的数据，内部数据结构名rowdata
Tailchk:保存在块结尾用于校验的数据，长度4个字节，内部结构名tailchk。
BBED>p tailchk ub4 tailchk @8188 0x9de80603
注意到tailchk=bas_kcbh低2字节(9de8)+type_kcbh(06)+seq_kcbh(03)
