Re: RAC wait event

  • From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>, "carlospena999@xxxxxxxxx" <carlospena999@xxxxxxxxx>
  • Date: Thu, 13 Apr 2017 07:51:15 +0000


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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf 
of Cee Pee <carlospena999@xxxxxxxxx>
Sent: 13 April 2017 08:11:47
To: oracle-l@xxxxxxxxxxxxx
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


Other related posts: