Re: RAC wait event
Date: Thu, 13 Apr 2017 07:51:15 +0000
Message-ID: <MMXP123MB1037E93DD1FA146C66E82B3BA5020_at_MMXP123MB1037.GBRP123.PROD.OUTLOOK.COM>
You haven't reported the STATE of the session - it may not be waiting, the "seconds_in_wait" column is badly named. There's a very old note on the topic here: http://jonathanlewis.wordpress.com/2009/05/15/not-waiting/
I think the comment about granularity is out of date, but the principle is still correct:
You session could have been on the CPU throughout the sample you sent us - perhaps because the optimizer has picked a very bad execution plan that is constantly hammering away at buffered blocks of the index you've identified.
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Cee Pee <carlospena999_at_gmail.com> Sent: 13 April 2017 08:11:47
To: oracle-l_at_freelists.org
Subject: RAC wait event
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:51:15 CEST