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 > > -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://mail.opensolaris.org/pipermail/ug-msosug/attachments/20090918/eedd11e8/attachment.html>
