RE: "cursor: pin S wait on X" during Benchmark Run

This wait is related with cursor mutex and generally with hard parse.
Oracle 10g introduced the concept of mutex which is lighter than
traditional library cache pin.

Mutex is expected to be lightweight and beneficial for cursor manipulation
performance.
But, unfortunately, it sometimes causes problems. Many bugs are reported
high wait time for mutex contention.

To turn off the mutex, you can set "_kks_use_mutex_pin" hidden parameter to
false. 
Search metalink for similar phenomenons and you will find many valuable
informations.

Anyway, how frequent hard parses do you have?


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of VIVEK_SHARMA
Sent: Monday, December 31, 2007 12:44 AM
To: oracle-l@xxxxxxxxxxxxx; tanel.poder.003@xxxxxxx; sfaroult@xxxxxxxxxxxx;
Thomas.Mercadante@xxxxxxxxxxxxxxxxx; mwf@xxxxxxxx; krish.hariharan@quasardb.
com; kevinc@xxxxxxxxxxxxx; Prashant.Dabadge@xxxxxxxxx; gorbyx@xxxxxxxxx;
greg@xxxxxxxxxxxxxxxxxx; rjamya@xxxxxxxxx; alvaro.fernandez@xxxxxxxxx
Subject: "cursor: pin S wait on X" during Benchmark Run


Folks

Oracle 10.2.0.3 (NON-RAC), Solaris 10
In a Benchmark Run, on Firing 2000 Application processes connecting VIA 24
dispatchers & 100 Shared Servers, getting the following WAITs
NOTE - This wait is NOT occuring when firing 700 (Lesser Number of)
Application processes connecting to the Database via the same MTS Setup.
NOTE - This wait is also NOT occuring in production where 20,000
Application processes connect via a similar MTS Setup.
DB CPU Usage is 25 % , APP CPU usage is 8 %

Is this a BUG? How is this to be approached?

Should the BLOCKING & Waiting SQLs/sessions be identified? Will the
following SQL Script work:-
SELECT s.sid, s.username,
e.wait_time,
b.sql_text,
m.p1,m.p2,m.p3
from v$session s, v$session_wait e, v$sqlarea b, v$MUTEX_SLEEP_HISTORY m
where m.p1= e.p1
and s.username is not null
and s.sid = e.sid
and s.sql_address=b.address
order by e.wait_time;
Cheers & Thanks

Vivek

P.S.
Top 5 Timed Events                                                    Avg
%Total
~~~~~~~~~~~~~~~~~~                                                   wait
Call
Event                                            Waits    Time (s)   (ms)
Time
----------------------------------------- ------------ ----------- ------ --
----
CPU time                                                     7,561
52.1
cursor: pin S wait on X                        272,212       4,345     16
29.9
^LMutex Sleep  DB/Inst: PNB70MB/PNB70MB  Snaps: 397-407
-> ordered by Wait Time desc
 
Wait
Mutex Type         Location                                 Sleeps     Time
(s)
------------------ -------------------------------- -------------- ---------
---
Cursor Pin         kkslce [KKSCHLPIN2]                     178,039
2,583.1
Cursor Pin         kksfbc [KKSCHLFSP2]                     148,073
1,675.8
Cursor Pin         kksfbc [KKSCHLPIN1]                         284
3.2
Cursor Pin         kksLockDelete [KKSCHLPIN6]               13,883
2.6
Cursor Parent      kksfbc [KKSPRTLOC1]                     245,448
1.2
...

**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended
solely for the use of the addressee(s). If you are not the intended
recipient, please notify the sender by e-mail and delete the original
message. Further, you are not to copy, disclose, or distribute this e-mail
or its contents to any other person and any such actions are unlawful. This
e-mail may contain viruses. Infosys has taken every reasonable precaution
to minimize this risk, but is not liable for any damage you may sustain as
a result of any virus in this e-mail. You should carry out your own virus
checks before opening the e-mail or attachment. Infosys reserves the right
to monitor and review the content of all messages sent to or from this e-
mail address. Messages sent to or from this e-mail address may be stored on
the Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Other related posts: