gc buffer busy acquire等待事件

coolingverse

awr报告中的gc buffer busy acquire时间很多

1. Event        Waits        Time(s)        Avg wait (ms)        % DB time        Wait Class
   
2. DB CPU                 26,647                 41.49         
   
3. gc buffer busy acquire        1,440,775        13,202        9        20.56        Cluster
   
4. read by other session        3,022,000        8,559        3        13.33        User I/O
   
5. db file sequential read        2,155,668        8,061        4        12.55        User I/O
   
6. db file scattered read        814,223        5,351        7        8.33        User I/O

复制代码

gc buffer busy acquire和gc buffer busy有什么区别吗?完整的awr在附件中.
谢谢, 刘大.

[ 本帖最后由 coolingverse 于 2012-4-19 15:14 编辑 ]

Maclean Liu(刘相兵

FROM ADDM:


   ADDITIONAL INFORMATION:
      Waits on event "log file sync" were the cause of significant database
      wait on "gc buffer busy" when releasing a data block.  Waits on event
      "log file sync" in this instance can cause global cache contention on
      remote instances.

Maclean Liu(刘相兵

Definitions

Let’s define these events first. Event ‘gc buffer busy’ event means that a session is trying to access a buffer,but there is an open request for Global cache lock for that block already, and so, the session must wait for the GC lock request to complete before proceeding. This wait is instrumented as ‘gc buffer busy’ event.

From 11g onwards, this wait event is split in to ‘gc buffer busy acquire’ and ‘gc buffer busy release’. An attendee asked me to show the differentiation between these two wait events. Fortunately, we had a problem with LGWR writes and we were able to inspect the waits with much clarity during the class.

Remember that Global cache enqueues are considered to be owned by an instance. From 11g onwards, gc buffer busy event differentiated between two cases:

    If existing GC open request originated from the local instance, then current session will wait for ‘gc buffer busy acquire’. Essentially, current process is waiting for another process in the local instance to acquire GC lock, on behalf of the local instance. Once GC lock is acquired, current process can access that buffer without additional GC processing (if the lock is acquired in a compatible mode).
    If existing GC open request originated from a remote instance, then current session will wait for ‘gc buffer busy release’ event. In this case session is waiting for another remote session (hence another instance) to release the GC lock, so that local instance can acquire buffer.

Example

Following output should show the differentiation with much clarity.

Notice that SID 53, instance is has open GC request for the block File #10, block #56051(line #1 in the output) and the session is waiting for ‘gc current request’ (which is a placeholder event, btw). All processes requesting an access to this block in instance 1 waits for ‘gc buffer busy acquire’. Similarly, all processes waiting for the block access in instance #2 is waiting for ‘gc buffer busy release’. Essentially, instance 1 sessions are waiting for local instance to acquire the GC lock, and instance 2 sessions are waiting for instance 1 to release the GC lock. Of course, LGWR is completely stuck in this case and so, Global cache layer is also nearly frozen.

INST_ID    SID EVENT                   USERNAME   STATE    WIS P1_P2_P3_TEXT
------- ------ ----------------------- ---------- -------- -------------------------------
      1     53 gc current request      SYS        WAITING  26 file# 10-block# 560651-id# 16777217
      1     40 gc buffer busy acquire  SYS        WAITING  file# 10-block# 560651-class# 1
      1     60 gc buffer busy acquire  SYS        WAITING  file# 10-block# 560651-class# 1
      1     59 gc buffer busy acquire  SYS        WAITING  file# 10-block# 560651-class# 1
      1     58 gc buffer busy acquire  SYS        WAITING  file# 10-block# 560651-class# 1
      1     56 gc buffer busy acquire  SYS        WAITING  file# 10-block# 560651-class# 1
      1     55 gc buffer busy acquire  SYS        WAITING  file# 10-block# 560651-class# 1
      1     54 gc buffer busy acquire  SYS        WAITING  file# 10-block# 560651-class# 1
      1     53 gc buffer busy acquire  SYS        WAITING  file# 10-block# 560651-class# 1
      1     48 gc buffer busy acquire  SYS        WAITING  file# 10-block# 560651-class# 1
      2      1 gc buffer busy release  SYS        WAITING  file# 10-block# 560651-class# 1
      2     68 gc buffer busy release  SYS        WAITING  file# 10-block# 560651-class# 1
      2     65 gc buffer busy release  SYS        WAITING  file# 10-block# 560651-class# 1
      2     64 gc buffer busy release  SYS        WAITING  file# 10-block# 560651-class# 1
      2     69 gc buffer busy release  SYS        WAITING  file# 10-block# 560651-class# 1
      2     57 gc buffer busy release  SYS        WAITING  file# 10-block# 560651-class# 1
      2     43 gc buffer busy release  SYS        WAITING  file# 10-block# 560651-class# 1
      2     36 gc buffer busy release  SYS        WAITING  file# 10-block# 560651-class# 1
      2     47 log file sync           SYS        WAITING  22 buffer# 4450-sync scn 30839721- 0

In summary, this differentiation is useful. In most cases, ‘gc buffer busy’ is a symptom and so, in this example, I would review instance 1 closely since the waits are ‘gc buffer busy acquire’ in that instance and most probably, I would quickly start to diagnose session with sid=53 @inst=1

Maclean Liu(刘相兵

"如果是这样的话, 同一条语句会在两个节点间共同并行的执行吗?"

那取决于 你是 如何 分配应用程序的 连接的 ， 如果没有做过应用隔离之类的限制的话， 那么显然 可能同一个APP 在多个实例均有 功能相似的 session， 进而执行 SQL statement 完全一致的语句。

coolingverse

如果是这样的话, 同一条语句会在两个节点间共同并行的执行吗?

cnaning

再于这个sql操作：
insert into T_DZ_COMPLAINWFDATA select MONTHDAY, WFSERIALNO, SERVICECLASSID, REQUESTSERIALNO, ACCEPTTIME, ACCEPTPHONE, CITYID, USERTYPE, ACCEPTTYPE, COMPLAINCONTENT, SATISFYID, DUTYREASON, ARCHIVETIME, DYNACHAR1, DYNACHAR2, DYNACHAR3, DYNACHAR4, FILENAME, PROVINCE, COMPONYNUMNAME, TERMINELNUM from icdrpt.T_DZ_COMPLAINWFDATA sample(:"SYS_B_0") t where to_char(t.accepttime, :"SYS_B_1") = :"SYS_B_2" and t.province = :"SYS_B_3"

导致的集群等待严重

Maclean Liu(刘相兵

gc buffer busy 等待事件在 11g中 开始被 细分为  gc buffer busy acquire /release 2种gc 全局等待事件。

Maclean Liu(刘相兵

参考 http://t.askmaclean.com/thread-438-1-1.html


As Maclean Said:

gc current block busy 等待是RAC中global cache全局缓存当前块的争用等待事件， 该等待事件时长由三个部分组成：

Time to process current block request in the cache= (pin time + flush time + send time)

gc current block flush time
The current block flush time is part of the service (or processing) time for a current block. The pending redo needs to be flushed to the log file by LGWR before LMS sends it. The operation is asynchronous in that LMS queues the request, posts LGWR, and continues processing. The LMS would check its log flush queue for completions and then send the block, or go to sleep and be posted by LGWR. The redo log write time and redo log sync time can influence the overall service time significantly.


flush time 是Oracle为了保证Instance Recovery实例恢复机制，而要求每一个current block在本地节点local instance被修改后(modify/update) 必须要将该current block相关的redo 写入到logfile 后（要求LGWR必须完成写入后才能返回)，才能由LMS进程传输给其他节点使用。


                                                                                                                                                                                                                                       

而gc buffer busy acquire/release 往往是 gc current block busy的衍生产品， 当同一实例内的 多个进程并发地访问同一个数据块时 ， 首先发起的进程 将进入 gc current block busy的等待 ，而在 buffer waiter list 上的后续进程 会陷入gc buffer busy acquire/release 等待(A user on the same instance has started a remote operation on the same resource and the request has not completed yet or the block was requested by another node and the block has not been released by the local instance when the new local access was made)， 这里存在一个排队效应， 即 gc current block busy是缓慢的，那么在 排队的gc buffer busy acquire/release就会更慢：

Pin time = (time to read the block into cache) + (time to modify/process the buffer)
Busy time = (average pin time) * (number of interested users waiting ahead of me)

不局限于current block （reference AWR Avg global cache current block flush time (ms)),  cr block(Avg global cache cr block flush time (ms)) 也存在flush time。

可以通过 设置_cr_server_log_flush to false(LMS are/is waiting for LGWR to flush the pending redo during CR fabrication. Without going too much in to details, you can turn off the behaviour by setting   _cr_server_log_flush to false.) 来禁止cr server flush redo log，但是该参数对于current block的flush time无效， 也强烈不推荐使用。

以上告诉我们 IO 在RAC中是十分重要的，特别是log file的write性能， 其重要性不亚于CPU 和 Interconnect network。