Understanding "cursor: pin S wait on X"
- From: Thomas Kellerer <thomas.kellerer@xxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 09 Apr 2013 10:59:28 +0200
Hello all,
we had a situation on a 2 node RAC system where for some reason the system more
or less stopped working due to excessive "cursor: pin S wait on X" waits. (89%
of the DB time on one node, 36% on the other node).
This seems to be the result of the application servers opening an excessive
high number of connections to the RAC (the reason for that is yet unknown).
We are not done invstigating everything that happened on the application server
yet, but it seems to be that all those sessions were running the same
statements and for some reason they were all routed to the same RAC node.
The top 5 Wait events from the overloaded node show up like this:
Event Waits Time(s) Avg wait (ms) % DB time
Wait Class
cursor: pin S wait on X 4,411 429,949 97472 88.68
Concurrency
library cache lock 246 30,608 124424 6.31
Concurrency
db file sequential read 1,237,878 8,275 7 1.71
User I/O
DB CPU 4,728 0.98
db file scattered read 1,014,046 2,738 3 0.56
User I/O
(1 hour AWR report)
In order to understand better the backround on when a "cursor: pin S wait on X"
is requested, I tried to create a test environment to reproduce this. But up to
now without luck. The "best" I can do simulating the high concurrency (on a
test environment) will result in a high number of "latch: shared pool" and
"latch: row cache objects".
So my question is:
Could somebody explain in more detail at which step(s) in the query processing
a "cursor: pin S wait on X" is requested?
By understanding the sequence that leads to such a wait event I hope to
understand better what was going on on the Oracle side of the problem.
Btw: is 0.6 logons per second considered a "logon storm"?
Thanks in advance
Kind regards
Thomas
--
//www.freelists.org/webpage/oracle-l
Other related posts:
