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

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Wed, 2 Jan 2008 21:31:38 +0530
Message-ID: <69E1360E54B50C4A828A136C158E4742036A70760F@BLRKECMBX02.ad.infosys.com>

Thanks Asif Ukja, folks for responding

shared_pool_size , session cached cursors was increased OPatch for Bug 5907779 was applied

Thereafter the resoective wait "cursor: pin S wait on X" disappeared from statcpack report.

Cheers

---

From: Asif Momen [asif_oracle_at_yahoo.com] Sent: Monday, December 31, 2007 2:43 AM
To: VIVEK_SHARMA; oracle-l_at_freelists.org; tanel.poder.003_at_mail.ee; sfaroult_at_roughsea.com; Thomas.Mercadante_at_labor.state.ny.us; mwf_at_rsiz.com; krish.hariharan_at_quasardb.com; kevinc_at_polyserve.com; Prashant.Dabadge_at_e-hps.com; gorbyx_at_gmail.com; greg_at_structureddata.org; rjamya_at_gmail.com; alvaro.fernandez_at_sivsa.com Subject: Re: "cursor: pin S wait on X" during Benchmark Run

Hi Vivek,

I have encountered this wait event once on one of our development databases.

For details on this read:

http://momendba.blogspot.com/2007/06/cursor-pin-s-wait-on-x-wait-event-in.html

Regards

Asif Momen
http://momendba.blogspot.com/

VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com> wrote:

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

  ---

Never miss a thing. Make Yahoo your homepage.<http://us.rd.yahoo.com/evt=51438/*http://www.yahoo.com/r/hs>

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

Received on Wed Jan 02 2008 - 10:01:38 CST