"cursor: pin S wait on X" during Benchmark Run
- From: VIVEK_SHARMA <VIVEK_SHARMA@xxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>, "tanel.poder.003@xxxxxxx" <tanel.poder.003@xxxxxxx>, "sfaroult@xxxxxxxxxxxx" <sfaroult@xxxxxxxxxxxx>, "Thomas.Mercadante@xxxxxxxxxxxxxxxxx" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>, "mwf@xxxxxxxx" <mwf@xxxxxxxx>, "krish.hariharan@xxxxxxxxxxxx" <krish.hariharan@xxxxxxxxxxxx>, "kevinc@xxxxxxxxxxxxx" <kevinc@xxxxxxxxxxxxx>, "Prashant.Dabadge@xxxxxxxxx" <Prashant.Dabadge@xxxxxxxxx>, "gorbyx@xxxxxxxxx" <gorbyx@xxxxxxxxx>, "greg@xxxxxxxxxxxxxxxxxx" <greg@xxxxxxxxxxxxxxxxxx>, "rjamya@xxxxxxxxx" <rjamya@xxxxxxxxx>, "alvaro.fernandez@xxxxxxxxx" <alvaro.fernandez@xxxxxxxxx>
- Date: Sun, 30 Dec 2007 21:13:45 +0530
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***
--
//www.freelists.org/webpage/oracle-l
Other related posts:
