11.2.0.2单机出现大量关于审计索引的ROW CACHE LOCK

huagan0523

DB版本:
ACTION_TIME                    ID         ACTION               VERSION    COMMENTS
------------------------------ ---------- -------------------- ---------- ----------------------------------------
12-OCT-11 06.46.52.478544 PM   0          APPLY                11.2.0.2   Patchset 11.2.0.2.0

OS版本:
Red Hat Enterprise Linux Server release 5.7 (Tikanga)

单实例

系统整体情况描述:
2013-7-10日15：00-16：00由于应用在跑批，出现大量buffer busy waits和row cache lock等待事件，导致批量比较慢。后来经应用证实，这次跑批出现大量等待事件是由于调度程序运行顺序错乱导致。

但是在AWR中发现以下2个问题，一时没找到原因:
1.
从AWR中还发现SEG$表存在比较严重的ITL争用，该表是由SMON自动维护的，存在这么大量的ITL争用，是不正常的。暂时无法得知出现ITL争用的原因。

2.
oracle内部使用的审计表以及索引体积过大，审计表AUD$体积为152.24GB，对应索引I_AUD1体积为60GB，在负载高时引发大量ROW CACHE LOCK争用，另外USER$表上也存在大量buffer busy wait，怀疑还是和用户审计有关，我给的建议是
清理数据，后续在观察。
针对该问题，主要想了解，第一，对审计表AUD$进行手动清理数据，有何风险，该表是oracle内部自带的审计表，oracle内部是否有机制定期清理该表。第二，关于该表引发ROW CACHE LOCK争用是否存在某种bug。

huagan0523

压缩包内文件说明:

addmrpt_1_14840_14841.txt是2013-7-10出现问题时的ADDM报告
ashrpt_1_0710_1600.html是2013-7-10出现问题时的ASH报告
awrrpt_1_14840_14841.html是2013-7-10出现问题时的AWR报告
awrrpt_1_14864_14865.html是2013-7-11出现问题时的AWR报告
SQL详情.xlsx是引发buffer busy waits和row cache lock等待事件的相关SQL

因为程序调度的问题已经搞清，针对我帖子的问题只需要关注awrrpt_1_14840_14841.html即可。

Maclean Liu(刘相兵

是否有当时的OSW 、NMON或   vmstat  信息？

huagan0523

2013-7-10 15:00-16:00的OSW已上传，ML麻烦看下！

Maclean Liu(刘相兵

简单谈下看法：

Linux    OSW v2.1.1    cnsz031446
zzz ***Wed Jul 10 15:00:07 CST 2013
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
17  8 965192 19805616 1588912 385712672    0    0  5561  1271    0    0  7  2 83  9  0
8  6 965192 19834444 1588932 385681408    0    0 113208 68479 3972 26796  9  3 83  4  0
6  8 965192 19867316 1588936 385646176    0    0 156816 84924 4658 24955  7  1 88  5  0
zzz ***Wed Jul 10 15:00:37 CST 2013
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
13  8 965192 20080156 1588944 385646176    0    0  5561  1271    0    0  7  2 83  9  0
4  9 965192 20077044 1588948 385646208    0    0 242216 185074 5137 27891  6  1 88  5  0
6 10 965192 20076924 1588948 385646528    0    0 236816 217988 4893 26648  5  1 89  5  0
zzz ***Wed Jul 10 15:01:07 CST 2013
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
10 10 965192 19328808 1588976 385647904    0    0  5561  1271    0    0  7  2 83  9  0
6  8 965192 19371652 1588980 385647904    0    0 154256 91385 4136 25386  6  1 88  4  0
6 11 965192 19355940 1588988 385647936    0    0 103784 97403 3813 27098  7  1 88  5  0
zzz ***Wed Jul 10 15:01:37 CST 2013
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
14  6 965192 19165140 1588988 385648896    0    0  5561  1271    0    0  7  2 83  9  0
8  7 965192 19155420 1588992 385657152    0    0 118120 140924 4210 26352  7  1 87  5  0
8 10 965192 19153716 1588992 385656960    0    0 336568 146919 5745 28780  7  1 87  5  0
zzz ***Wed Jul 10 15:02:07 CST 2013


有大量的 阻塞进程 b这一列一直没小过




zz ***Wed Jul 10 15:09:07 CST 2013
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           7.90    0.00    1.42    8.69    0.00   81.98

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00    18.33  0.00  2.67     0.00   168.00    63.00     0.00    0.12   0.13   0.03
sda1              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00    18.33  0.00  2.67     0.00   168.00    63.00     0.00    0.12   0.13   0.03
sdb               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdc              15.00     0.67 23.33  8.00  8832.00  1498.67   329.70     0.33   10.60   5.05  15.83
sdc1             15.00     0.67 23.33  8.00  8832.00  1498.67   329.70 6148914691236516.00   10.60  30.78  96.43
sdd               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sde               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sde1              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdf               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdg               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdh               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdh1              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdi               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdj               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdj1              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdk               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdk1              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdl               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdm               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdn               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdn1              0.00     0.00  0.00  0.00     0.00     0.00     0.00 6148914691236517.00    0.00   0.00 100.07
sdo              35.67     2.67 70.67  9.67 22085.33  2282.67   303.34     1.05   13.02   4.78  38.37
sdo1             35.67     2.67 70.67  9.67 22085.33  2282.67   303.34     1.05   13.02   4.78  38.37
sdp               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdq               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdr               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdr1              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sds               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdt               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdt1              0.00     0.00  0.00  0.00     0.00     0.00     0.00 6148914691236515.00    0.00   0.00 100.07
sdu               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdu1              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdv               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdw               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdx              13.67     1.00 19.33  2.67  7648.00   682.67   378.67     0.33   14.85   5.82  12.80
sdx1             13.67     1.00 19.33  2.67  7648.00   682.67   378.67     0.33   14.85   5.82  12.80




zzz ***Wed Jul 10 15:09:37 CST 2013
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           6.00    0.00    1.16    5.95    0.00   86.89

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00    70.33  0.00 18.67     0.00   712.00    38.14     0.01    0.45   0.07   0.13
sda1              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00    70.33  0.00 18.67     0.00   712.00    38.14     0.01    0.45   0.07   0.13
sdb               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdc               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdc1              0.00     0.00  0.00  0.00     0.00     0.00     0.00 6148914691236515.00    0.00   0.00 100.07
sdd               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sde               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sde1              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdf               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdg               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdh               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdh1              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdi               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdj               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdj1              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdk               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdk1              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdl               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdm               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdn               0.00    12.00 44.67 22.33  1984.00  3144.00    76.54     0.13    1.98   1.80  12.03
sdn1              0.00    12.00 44.67 22.33  1984.00  3144.00    76.54 6148914691236517.00    1.98  13.23  88.63
sdo               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdo1              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdp               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdq               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdr               0.00     1.33 28.33  6.67  1088.00  1506.67    74.13     0.10    3.03   2.60   9.10
sdr1              0.00     1.33 28.33  6.67  1088.00  1506.67    74.13     0.10    3.03   2.60   9.10
sds               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdt               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdt1              0.00     0.00  0.00  0.00     0.00     0.00     0.00 6148914691236515.00    0.00   0.00 100.03
sdu               0.00     0.00  3.67  0.00   576.00     0.00   157.09     0.01    3.91   3.91   1.43
sdu1              0.00     0.00  3.67  0.00   576.00     0.00   157.09     0.01    3.91   3.91   1.43
sdv               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdw               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdx               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdx1              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdy               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdz               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdaa              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdaa1             0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdab              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdab1             0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdac              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdac1             0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdad              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdae              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdaf              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdag              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdag1             0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdah              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdah1             0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdai              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdaj              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdaj1             0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdak              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdal              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdam              0.00     0.00 35.00  0.00  1194.67     0.00    34.13     0.04    1.13   1.13   3.97
sdam1             0.00     0.00 35.00  0.00  1194.67     0.00    34.13     0.04    1.13   1.13   3.97
sdan              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdan1             0.00     0.00  0.00  0.00     0.00     0.00     0.00 6148914691236515.00    0.00   0.00 100.03
sdao              0.00     5.00 20.00 14.67  1354.67  3768.00   147.77     0.13    3.62   2.25   7.80
sdao1             0.00     5.00 20.00 14.67  1354.67  3768.00   147.77 6148914691236517.00    3.62  27.01  93.63


可以看一下iostat 输出有几块盘的avgqu-sz 始终不太正常

huagan0523

1.
从AWR中还发现SEG$表存在比较严重的ITL争用，该表是由SMON自动维护的，存在这么大量的ITL争用，是不正常的。暂时无法得知出现ITL争用的原因。

2.
oracle内部使用的审计表以及索引体积过大，审计表AUD$体积为152.24GB，对应索引I_AUD1体积为60GB，在负载高时引发大量ROW CACHE LOCK争用，另外USER$表上也存在大量buffer busy wait，怀疑还是和用户审计有关，我给的建议是
清理数据，后续在观察。
针对该问题，主要想了解，第一，对审计表AUD$进行手动清理数据，有何风险，该表是oracle内部自带的审计表，oracle内部是否有机制定期清理该表。第二，关于该表引发ROW CACHE LOCK争用是否存在某种bug。

对这2点，有何看法？

kevinlin.ora

捕获到的在SEG$上的ITL等待次数为12次，应该谈不上比较严重与大量。看到还是有不少建表操作的。

USER$上的buffer busy wait为331，也谈不上大量。

一般row cache lock只出现在数据字典信息变动时，即有DDL操作时。

但是buffer busy waits与row cache lock的平均等待时间非常长，分别为29.579秒与57.675秒，而且是发生在总的waits不高的情况下。
在Wait Event Histogram信息里，可以看到超过4分钟的等待就上百次。
如果AWR数据没有溢出等问题的话，我猜测可能出现过短暂的hang，不过这没什么用，11.2.0.2上的已知bug还是比较多的，比如：Bug 11930616 - sporadic buffer busy waits [ID 11930616.8](A missing post might from the DBWR cause different kinds of buffer busy waits.)。每个release及patchset上都有bug，不过较成熟的版本上至少可以减少诊断范围，有时候太多的可能性会让人无所适从。

总觉得oracle的自动诊断系统有待完善，毕竟很多东西在事后都很难找回和重现了。

AUD$的管理在metalink上有几篇文章，可以去参考。

Maclean Liu(刘相兵

huagan0523 发表于 2013-7-12 12:45
1.
从AWR中还发现SEG$表存在比较严重的ITL争用，该表是由SMON自动维护的，存在这么大量的ITL争用，是不正常 ...

我的观点是 kenvilin.ora基本一致 ，

2种可能性：
1、 bug或not real bug引起的HANG，
2、 当时的IOstat反应IO确实不太正常

huagan0523

OK，我知道了，感谢各位！