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

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Sun, 30 Dec 2007 21:13:45 +0530
Message-ID: <69E1360E54B50C4A828A136C158E4742036A707607@BLRKECMBX02.ad.infosys.com>


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 %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
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
Received on Sun Dec 30 2007 - 09:43:45 CST

Original text of this message