Plan of Action for Reducing Disk Contention
Tuning file I/O should be part of an overall performance tuning strategy. Getting a better and more accurate picture of the file I/O is easier after that database has been created and is in use. You can and should, however, do some initial planning to minimize disk contention based on known operational characteristics common to all Oracle databases. Here are some general tips and guidelines for helping you plan the initial physical layout of file.
The design of the database files influences disk contention and performance on I/O bound systems. Having several heavily accessed files on the same disk increases disk contention and decrease performance.
Try to share out I/O across as several disks as possible. In general, using a bigger number of small disks is better than using a few large disks. Even if you are using disk striping, having larger number of disks is beneficial.
Separate the archived logs destination and the online redo log files onto different disks. Place the undo segments or undo tablespace onto a separate, dedicated disk, if possible.Also put SYSTEM tablespace and SYSAUX tablespace in separate disk to avoid high disk read and write. Temp tablespace should be with disk caches on disk. Distribute control files on different and various disk for deploying mirroring and reduce disk contention. These are basic principles of Oracle disk I/O tuning. Lots of books are showing these concepts. But how many Oracle DBA are strictly following?
Place all database related files on dedicated disks so the same disk is not shared with other applications. This makes studying and analyzing disk I/O much easier and more predictable.
Use the appropriate RAID type if you are using redundant disks, different types of RAID disks have different performance implications. Avoid RAID 5, especially for highly write-intensive files, such as redo log files and undo segments on transaction oriented databases. Generally RAID0+1 provide better read performance along with protection from disk failure.
Check with the application's vendor for any special file configuration instructions of recommendations because the design of the database applications influences I/O rates against different tables and their containing data files.
Predicting data file I/O fully before the database is created is difficult, if not impossible. After the database is created and in use, you can query data dictionary views for getting a clearer picture on I/O rates. If I/O bottlenecks are noticed, you can then move data files to different disks as needed to reduce contention.
Above all points are mentioned in every technical articles and books. But unfortunately, nobody follows these principles. Therefore, database server is affected by heavy disk I/O. After configuration of 24*7 running OLTP database, we don't get downtime for relocating data files from one disk to another disk.
Periodically archive data from transaction logs and audit logs. This is very important because if you are not purging data periodically then it may be resulting in high disk I/O during pick hours of OLTP databases. Contact your application team, try to convince them about scheduling purging activity and if needed then take backup of those targeted tables before start purging activity.
Expert Remote DBA team of Dbametrix provides excellent performance tuning of database server with guaranteed performance optimization without adding new hardware in server. Out expert service of Remote Database Monitoring offers proactive and future forecasting of performance related problems with full database health checkup of your all critical databases. Dbametrix assures unbreakable and high availability of expert Oracle Support Services for every critical database servers. A strong and expert Oracle DBA is our guarantee and assurance.
Indeed, remote dba experts are able to manage Oracle database in critical condition and troubleshoot issues very quickly. Maintaining high security is most essential part of remote dba work. Proactive database administration tasks are equally important to manage smooth database operations during remote dba services. Dbametrix is worldwide leader in remote dba support and database services. For competitive quote, kindly contact our sales department with your technical specification and requirements.