Hi,
We have a created a NEW 2 node RAC test Environment on LInux 6 and with Oracle version 11.2.0.4 .We have the Prod database with EXADATA 11.2.0.4 and test envt as LINUX 6 and with Oracle version 11.2.0.4 .some the Insert statements on Test ENVT are hanging with the GC current request wait event and restarting the query is fixing the issue.when it hangs it hangs for lot of time(17 hours max wait time) and when we restarted it finished in couple of seconds and we never faced same issue on Exadata.
Wait events:
UNDEFINE SE.SID
select se.sid, se.event, se.total_waits, se.time_waited/100 time_waitedseconds,
se.average_wait/100 average_waitseconds,max_wait/100 max_waitsec
from gv$session_event se, gv$session ss
where time_waited > 0
and se.sid=ss.sid
and ss.username is not NULL
and ss.sid='&SID'
and ss.serial#='&serial'
order by 4;
SID EVENT TOTAL_WAITS TIME_WAITEDSECONDS AVERAGE_WAITSECONDS MAX_WAITSEC
----- ------------------------------ ----------- ------------------ ------------------- -----------
533 enq: RO - fast object reuse 6 .01 .0012 0
533 log file sync 5 .01 .0019 .01
533 row cache lock 37 .01 .0003 0
533 db file scattered read 27 .01 .0005 0
533 gc current grant 2-way 26 .01 .0003 0
533 events in waitclass Other 46 .02 .0005 0
533 direct path read 4 .02 .0055 .02
533 db file sequential read 68 .1 .0015 .02
533 SQL*Net message from client 16 .19 .0118 .18
533 gc current request 1 2551.24 2551.2445 0
EXECUTION PLAN:
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 552 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
| 2 | HASH JOIN | | 30225 | 39M| 552 (1)| 00:00:08 |
| 3 | TABLE ACCESS FULL | T$TY_REL_BT | 30225 | 501K| 129 (0)| 00:00:02 |
| 4 | TABLE ACCESS FULL | C_S_BT_CL | 37333 | 47M| 422 (1)| 00:00:06 |
---------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / TBLMAX@SEL$1
4 - SEL$1 / C_S_BT_CL@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
OPT_PARAM('star_transformation_enabled' 'true')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
FULL(@"INS$1" "C_S_PARTY_REL_BT"@"INS$1")
FULL(@"SEL$1" "TBLMAX"@"SEL$1")
FULL(@"SEL$1" "C_S_BT_CL"@"SEL$1")
LEADING(@"SEL$1" "TBLMAX"@"SEL$1" "C_S_BT_CL"@"SEL$1")
USE_HASH(@"SEL$1" "C_S_BT_CL"@"SEL$1")
END_OUTLINE_DATA
*/
Note
-----
- dynamic sampling used for this statement (level=2)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level