List,
We have a two node 12.1 RAC and while monitoring a 'slow SQL', I noticed
the following:
SID CLIENT_INFO LAST_CALL_ET SQL_ID ROW_WAIT_OBJ#
EVENT SECONDS_IN_WAIT
---------- --------------- ------------ ------------- -------------
-------------------------------- ---------------
110 60 5fchucqp9tjxx 15506411 gc cr
block 2-way 60
SID CLIENT_INFO LAST_CALL_ET SQL_ID ROW_WAIT_OBJ#
EVENT SECONDS_IN_WAIT
---------- --------------- ------------ ------------- -------------
-------------------------------- ---------------
110 154 5fchucqp9tjxx 15506411 gc cr
block 2-way 154
SID CLIENT_INFO LAST_CALL_ET SQL_ID ROW_WAIT_OBJ#
EVENT SECONDS_IN_WAIT
---------- --------------- ------------ ------------- -------------
-------------------------------- ---------------
110 322 5fchucqp9tjxx 15506411 gc cr
block 2-way 322
SQL> /
SID CLIENT_INFO LAST_CALL_ET SQL_ID ROW_WAIT_OBJ#
EVENT SECONDS_IN_WAIT
---------- --------------- ------------ ------------- -------------
-------------------------------- ---------------
110 330 5fchucqp9tjxx 15506411 gc cr
block 2-way 331
OBJECT_ID OBJECT_NAME
OBJECT_TYPE STATUS
---------- ----------------------------------------
----------------------- -------
15506411 PS_PSTREESELECT10
INDEX VALID
The statement completed after 380 seconds or so. It seems all the execution
time was for 'gc cr block 2-way'. The table underneath has only 100K rows
and dba_tables/indexes reflect that. Does this mean that the index was
modified on the other node and the updated version of the index was needed
by this instance? It looked like all the waits for the execution of this
statement was over interconnect. Does this mean we need to look at our
interconnect speed?
TIA, CP.
