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! 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’.<\/p>\n
\nWhy 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.<\/a><\/p>\n
| INST_ID<\/td>\n | SID<\/td>\n | SEQ#<\/td>\n | EVENT<\/td>\n | P1TEXT<\/td>\n | P1<\/td>\n | P2TEXT<\/td>\n | P2<\/td>\n<\/tr>\n |
| 1<\/td>\n | 1341<\/td>\n | 5<\/td>\n | gc buffer busy<\/td>\n | file#<\/td>\n | 185<\/td>\n | block#<\/td>\n | 1108664<\/td>\n<\/tr>\n |
| 1<\/td>\n | 1484<\/td>\n | 5<\/td>\n | read by other session<\/td>\n | file#<\/td>\n | 185<\/td>\n | block#<\/td>\n | 1108664<\/td>\n<\/tr>\n |
| 1<\/td>\n | 1608<\/td>\n | 5<\/td>\n | db file sequential read<\/td>\n | file#<\/td>\n | 185<\/td>\n | block#<\/td>\n | 1108664<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n 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 \ud83d\ude41<\/p>\n","protected":false},"excerpt":{"rendered":" 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…<\/p>\n |