Hi Friends,
I am seeing one strange issue, i am seeing gc buffer busy acquire event even though all the queries are running on node 1.
My question(For my academic knowledge): Why am i seeing this wait event gc buffer busy acquire on node 1 even though nothing is running from node 2 so there is not possibility of any table/index getting accessed from the other node.
DB version:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
The query is a simple one.
SELECT OBJID
FROM TABLE_C C
WHERE EXISTS (
SELECT (OBJID)
FROM TABLE_CO CON
WHERE CON.OBJID = C.CASE_STATE2CONDITION
AND CON.S_TITLE = 'CLOSED'
)
AND MODIFY_STMP < :B1
AND IS_SUPERCASE = 1
AND C.CASE_TYPE_LVL1 IN (
'LSAR'
,'CA'
,'OOP'
,'EER'
,'UC'
,'BRM'
)
;
Execution plan is simple:
Plan hash value: 2507583235
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3317 (100)| |
| 1 | NESTED LOOPS SEMI | | 65053 | 3811K| 3317 (1)| 00:00:40 |
|* 2 | TABLE ACCESS BY INDEX ROWID| TABLE_C | 70128 | 2876K| 3316 (1)| 00:00:40 |
|* 3 | INDEX RANGE SCAN | TABLE_C_1IX | 328K| | 101 (0)| 00:00:02 |
|* 4 | INDEX UNIQUE SCAN | TABLE_CO_1UQ | 3744K| 64M| 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("IS_SUPERCASE"=1 AND INTERNAL_FUNCTION("CASE_TYPE_LVL1")))
3 - access("MODIFY_STMP"<:B1)
4 - access("CON"."OBJID"="CASE_STATE2CONDITION" AND "CON"."S_TITLE"='CLOSED')
Few other details:
1. TABLE_C has 3 LOB columns.
2. TABLE_C has 23 indexes .
p.s. Due to company's security policy i wont be able to give the DDL of the objects involved.
Regds,
Sachin