Hi Bill,
attached is a document that i reference that you might find useful.
regards
Boris Tsun
On Fri, Sep 18, 2009 at 10:55 AM, Bill Shi <xianbiao.shi at gmail.com> wrote:
> I agree with Nathan & Richard's opinions.
> Physical & Logical I/O related issues were, are and will always be a
> significant factor for performance tuning regarding Oracle database server,
> especially in an OLTP environment(Oracle's multi-versioning & write-ahead
> logging mechanism).
>
> Usually, I will take a look at prstat -Lmc/mpstat and iostat -xncz to find
> out what happen to specific volume or disk, and get into Oracle RDBMS by
> statspack or AWR(since 10g) for Top 5 wait events(log file sync and dbwr
> related entries would commonly be the cases that contribute large amount of
> I/O). Additionally, I/O operations caused by checkpoint actions like log
> file switch which can be reduced greatly if you can properly configure the
> fast_start_mttr_target/log_checkpoint_xxxx parameters, etc. For Oracle
> server processes contention, I suggest to take a look at Latch Free and CPU
> Parses(especially cursor Hard Parses) and so on. You can also find most of
> these information by V$ views mentioned by Richard in previous email, they
> are really helpful. Furthermore, the business model(application) would also
> be one of the factors to find out the root cause of your performance
> problem. Through my point of view, performance tuning should begin at the
> beginning of the system design phase.
>
> As Nathan said, if you can answer the questions below, many problems can be
> exposed:
> - 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
>
> Good luck:)
>
>
> Thanks & Best Regards,
>
> Bill
>
>
> 2009/9/17 Alexander Box <Alexander.Box at sro.vic.gov.au>
>
>>
>> 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
>>
>>
>
> _______________________________________________
> ug-msosug mailing list
> ug-msosug at opensolaris.org
> http://mail.opensolaris.org/mailman/listinfo/ug-msosug
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<http://mail.opensolaris.org/pipermail/ug-msosug/attachments/20090918/0003eb44/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Solaris-Disk-Utilisation-by-Process.pdf
Type: application/pdf
Size: 593746 bytes
Desc: not available
URL:
<http://mail.opensolaris.org/pipermail/ug-msosug/attachments/20090918/0003eb44/attachment.pdf>
