Thursday, April 23, 2009

I hate mutex!

AppPeople called that Big-App is not working fine... Shit!

Short check...

SQL> select event, count(*) from v$session_wait group by event order by 2;

EVENT COUNT(*)
---------------------------------------------------------------- ----------
(...)
jobq slave wait 4
log file sync 20
db file sequential read 21
rdbms ipc message 24
latch: cache buffers chains 72
cursor: pin S 155
SQL*Net message from client 772


... More than 100 sessions waiting on cursor: pin S event! WTF!

... Metalink examination leads to bug 6968152...

(...)
If default "_kks_use_mutex_pin=TRUE"(MUTEX) setting compared to "_kks_use_mutex_pin=FALSE"(NON-MUTEX) setting in the same processing situation,
the high value of the wait event of "cursor:pin S" is shown, at the same time, the CPU usage rate become high load, and executable transactions decreases.
So performance degraded more by MUTEX("_kks_use_mutex_pin=TRUE" setting) than by NON-MUTEX("_kks_use_mutex_pin=FALSE" setting).
(...)


... So I have checked my CPU's...

System: zulu01
Load averages: 15.46, 15.35, 15.08
1301 processes: 1005 sleeping, 295 running, 1 zombie

I hate mutex! Why new amazing stuff always doesn't work! They have said mutex will solve latches problems. Maybe even improve! ;-)

That is theory. Practice is quite different - I need to disable it by _kss_use_mutex_pin=FALSE :-) Shit!

2 comments:

Kyle Hailey said...

nice
I think there was a bug in 10gR2 on this - will have to check.
Linked in here:
http://sites.google.com/site/embtdbo/wait-event-documentation/oracle-library-cache#TOC-cursor:-pin-S
will be adding more in the coming weeks

frenchy51 said...

Looks like I'm having the same problem, did you ever meet this again cause it looks like it's a rare case?
I guess that even with the last patchset applied in 10.2.0.5 I will still have to contact Oracle support before any change to that hidden paramater...