gc buffer busy on the same instance??

Recently, we have been running a large data load against a three server RAC. This load is running against a single server in the cluster. We found a particular SQL statement used in a PLSQL procedure did a large amount of I/O. While we knew we needed to fix it, we found it odd that the most of the sessions waiting on ‘gc buffer busy’ were waiting on a file and block for which another session had already posted a ‘db file sequential read’ wait event at the same time…in the same instance!
Why would the session waiting on ‘gc buffer busy’ not post ‘buffer busy waits’ or ‘read by other session’? I posted this question on CDOS, and Jonathan Lewis was kind enough to post a response.

Essentially, his thought was that a session on another instance had the block, for whatever reason, the session waiting on ‘db file sequential read’ in the first instance had originally been waiting on ‘gc buffer busy’. For whatever reason, the block was passed by disk (db file sequential read’) rather than interconnect (‘gc buffer busy’). This proved to be true on the surface, as the session waiting on ‘db file sequential read’ did have ‘gc buffer busy’ wait for the same file and block in gv$session_wait_history just prior to the ‘db file sequential read’.

INST_ID SID SEQ# EVENT P1TEXT P1 P2TEXT P2
1 1341 5 gc buffer busy file# 185 block# 1108664
1 1484 5 read by other session file# 185 block# 1108664
1 1608 5 db file sequential read file# 185 block# 1108664

However, after I dug further into that view, I found the session that evidently *originally* had the file and block was waiting on ‘read by other session…also on the the first instance!! I am more lost now than ever 🙁

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.