Oracle9i Performance Issue

15 pts.
Tags:
Average Disk Queue Length
Oracle 9i
Oracle 9i performance
Oracle performance
I got Oracle9i R2 database running on windows 2003. On a very busy day I got very bad performance with Average Disk Queue Length very high on the performance monitor. There is heaps of free RAM and I am running Raid 10. I am still new to Oracle, so I need some help. Thanks and regards.
ASKED: October 31, 2008  11:15 PM
UPDATED: November 20, 2008  7:51 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Lots of I/O for multiple concurrent queries or transactions is probable when the disk queue length climbs. Another possibility is that logging activity due to a heavy update load requires a different storage system configuration to perform well at peak times.

To reduce read I/O:

1) Size the database shared memory (SGA) correctly. There is lots of information about this topic in the Oracle doc and online. Quick summary: if Oracle isn’t configured to use the RAM then it will keep reading from disk even if caching data in RAM would improve performance (unlike SQL Server). This get more automagic in version 10g and above, but Oracle will never take over RAM without being given permission to do so.

2) If the SGA is sized correctly and you still have the high I/O rates, then you either need better execution plans or additional indexes (or both).

a) To improve execution plans, the first thing to do as a DBA is to make sure that the optimizer statistics have been gathered properly and are reasonable up to date. With 9i you should never use the ANALYZE command, always use one of the DBMS_STATS.GATHER_xxx_STATS methods. The Oracle performance tuning doc and online resources will get you going.

b) With the SGA sized right and good stats, next you must actually look at the workload. If you already know the problem queries, use EXPLAIN PLAN on them, see if there is an index or some indexes that can be created to reduce the I/O’s. If you need to identify the long-running queries, you need a tool or tools.

If you don’t have any tools for this, “STATSPACK” is built-in so start with it. Since 9i is not a current version of Oracle, you can get a copy of Don Burleson’s book for tuning 9i with STATSPACK for not much money. This will help you identify the SQL causing all the I/O, so you can either add indexes or suggest some changes to the application developer.

Heavy updates and logging

With RAID 1+0, the storage configuration and logging shouldn’t be the problem, but incorrectly sized redo log files could be a contributor. Check the log switches; if switching to new redo in less than 5 minutes during peak times, increase the redo file sizes.

Oracle’s SAME (stripe and mirror everything) recommended storage configuration is predicated on having a very high bandwidth connection (or connections). If you have the database, redo logs, and archive logs all in one RAID array attached to one SCSI channel, you may need to move the redos to another storage system. Measure your redo log write rate at peak time (just divide file size by minutes between log switches). If you’re writing more than about 50MB per minute via the same SCSI channel as your tablespaces, I would recommend separate storage for redo’s (and for arcive logs if you can get it).

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following