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

From: Ukja.dion <>
Date: Mon, 31 Dec 2007 11:03:32 +0900
Message-ID: <>

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: [] On Behalf Of VIVEK_SHARMA
Sent: Monday, December 31, 2007 12:44 AM To:;;;;; krish.hariharan_at_quasardb. com;;;;;; Subject: "cursor: pin S wait on X" during Benchmark Run


Oracle (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,


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



Top 5 Timed Events                                                    Avg
~~~~~~~~~~~~~~~~~~                                                   wait
Event                                            Waits    Time (s)   (ms)
----------------------------------------- ------------ ----------- ------ --
CPU time                                                     7,561
cursor: pin S wait on X                        272,212       4,345     16
^LMutex Sleep DB/Inst: PNB70MB/PNB70MB Snaps: 397-407
-> ordered by Wait Time desc


Mutex Type         Location                                 Sleeps     Time
------------------ -------------------------------- -------------- ---------
Cursor Pin         kkslce [KKSCHLPIN2]                     178,039
Cursor Pin         kksfbc [KKSCHLFSP2]                     148,073
Cursor Pin         kksfbc [KKSCHLPIN1]                         284
Cursor Pin         kksLockDelete [KKSCHLPIN6]               13,883
Cursor Parent      kksfbc [KKSPRTLOC1]                     245,448
  • 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 email address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS******** End of Disclaimer ********INFOSYS*** --
Received on Sun Dec 30 2007 - 20:03:32 CST

Original text of this message