RE: "cursor: pin S wait on X" during Benchmark Run
Date: Mon, 31 Dec 2007 11:03:32 +0900
Message-ID: <47784e08.0d528c0a.502e.ffffbb21@mx.google.com>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of VIVEK_SHARMA
Sent: Monday, December 31, 2007 12:44 AM
To: 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: "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 1629.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,4481.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 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*** -- http://www.freelists.org/webpage/oracle-l
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Dec 30 2007 - 20:03:32 CST