RAC wait event

From: Cee Pee <carlospena999_at_gmail.com>
Date: Thu, 13 Apr 2017 02:11:47 -0500
Message-ID: <CAPTPB12TTGTdCD+sr_N70NRGLwBLjXh-omUBSvEqU2b9_1pvtw_at_mail.gmail.com>

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.
--

http://www.freelists.org/webpage/oracle-l Received on Thu Apr 13 2017 - 09:11:47 CEST