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.
--
//www.freelists.org/webpage/oracle-l
