It's an interesting question, and without knowing a lot more, (like
grabbing some stack profiles) seeing lots of things in LCK is not
necessarily bad...
It might be a synchronisation lock, or it might be a 'release the
hounds' sort of lock, where the lock is held until there is something
useful to do and the gates are opened. Not having seen this particular
in oracle myself (moreover, not having looked for it or found it) I'd
start looking for more information. (I'll also offer no commentary on
the goodness or badness of any locking strategies... ;)
It might be interesting to do something like:
- pstack the oracle process occasionally and see what all the threads
that are waiting are actually waiting on. It might be a stack that
includes names that look like work, or it might be a stack that
indicates it's waiting... And gives you a view of what the thread
that's actually running is doing...
- dtrace profile of the stacks... Something like a profile-1000 with
an aggregation on processid, ustack etc...
- more funky dtrace that shows at any point of time how many threads
are in what stack state with a profile though that might be a little
more work... have not given it much thought yet...
Of course, this will give you very coarse information...
More interesting would be to get the SQL data out and a statspack to be
able to look at the databases view of what's happening.
Oh - and a thread is considered sleeping if it's waiting on IO's to
happen - eg: A process that is 100% IO bound might look like this in prstat:
PID USERNAME USR SYS TRP TFL DFL LCK SLP LAT VCX ICX SCL SIG
PROCESS/NLWP
9388 root 0.5 6.9 0.0 0.0 0.0 0.0 93 0.0 20 23 23K 0 aio/1
Little user, lots more sys, and sleeping most of the time.
In ZFS land where you might be pushing huge swathes of stuff into memory
before commit, it might look more like this:
PID USERNAME USR SYS TRP TFL DFL LCK SLP LAT VCX ICX SCL SIG
PROCESS/NLWP
9392 root 1.9 48 0.0 0.0 0.0 0.0 50 0.0 7 69 .1M 0 aio/1
where you will see lots more system time (but of course, a much higher
process IO throughput)
So - I'd be looking at:
- What does the statspack say about IO latency?
- what does iostat say about IO latency
- what is the seat of the pants feel in doing an IO on the devices
they are using for that instance? (Logs and data)
- Are the devices logging any errors
Finally - if they box is performing sub expectations, and nothing has
changed (ha!) you could of course log a performance call with Sun and
Oracle and let them go to town. But - Make sure you have *good* data,
metrics, expected case and real case data... Or you will just be wasting
everyone's time.
Oh - and what's the underlying finesystem? :)
Cheers!
Nathan.
Alexander Box wrote:
>
> Following last night's presso from Nathan and Andre (thanks very much
> for another interesting night) -
>
> Yesterday one of our DBAs was complaining about IO performance and
> supplied two PIDs to look at from the OS. (Thanks!)
>
> Although vxstat/iostat on the diskgroup/associated LUNs showed a
> consistently large number of write IOPS, response times were always low.
> The oracle processes had >200 LWPs, and running a prstat -mL -p <PID>
> showed that at all times, every thread bar one spent 98% of its time in LCK.
>
> Any speculation?
>
> Regards,
> Alex
>
>
> Disclaimer: The information transmitted is intended only for the person
> or entity to which it is addressed and may contain confidential and/or
> privileged material. Any review, retransmission, dissemination or other
> use of, or taking of any action in reliance upon, this information by
> persons or entities other than the intended recipient is prohibited. If
> you received this in error, please contact the sender and delete the
> material from your computer.
> Privacy: If you are responding to this email or providing personal
> information to the SRO for the purposes of one of the Acts it
> administers, such information is used only for the purpose for which it
> was collected (administration of SRO legislation ) and is protected by
> the Information Privacy Act 2000 and secrecy provisions contained in
> legislation administered by SRO. It is not disclosed otherwise than in
> accordance with the law. If you would like a copy of the SRO Privacy
> Policy please refer to SRO website (www.sro.vic.gov.au) or contact SRO
> on 13 21 61 and request a copy.
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> ug-msosug mailing list
> ug-msosug at opensolaris.org
> http://mail.opensolaris.org/mailman/listinfo/ug-msosug
