At the OS level unbalanced disk I/O and UNDO management can hamper performance. The post below describes how physical files that make up the Oracle database and their configuration across disks can affect the performance of the Oracle Database and the steps that can be taken to improve the performance, like balancing the data files across disks and splitting highly used datafiles so as to balance the reads and writes across disks, minimize I/O contention and improve the database performance.
Disk contention occurs when multiple processes try to access the same physical disk simultaneously. It can be reduced by distributing the disk I/O more evenly over the available disks thereby increasing overall database performance. Disk contention can be reduced by reducing disk I/O to a particular disk.
Use the FILEIO monitor to accomplish the following tasks
- Show how actual database files are being hit by users.
- Move tables and indexes to spread hits equally across all the disks.
If the reads and writes are not distributed evenly between files, the tablespaces may need to be restructured for better performance.
select name, phyrds, phywrts, readtim, writetim
from v$filestat a, v$dbfile b
where a.file# = b.file#
order by readtim desc;
| NAME | PHYRDS | PHYWRTS | READTIM | WRITETIM |
|---|---|---|---|---|
| E:\INSTALL\21CXE\ORADATA\XE\XEPDB1\SYSTEM01.DBF | 3833 | 119 | 6745 | 8 |
| E:\INSTALL\21CXE\ORADATA\XE\XEPDB1\SYSAUX01.DBF | 901 | 358 | 1449 | 106 |
| E:\INSTALL\21CXE\ORADATA\XE\XEPDB1\UNDOTBS01.DBF | 42 | 629 | 243 | 58 |
| E:\INSTALL\21CXE\ORADATA\XE\XEPDB1\HR.DBF | 60 | 337 | 55 | 13 |
| E:\INSTALL\21CXE\ORADATA\XE\XEPDB1\USERS01.DBF | 5 | 2 | 33 | 0 |
The output above is from a windows database server, So it does not give an actual picture wrt disks. But for understanding purpose we can consider each drive to be a separate disk partition.
A large number of physical writes and reads between the disks shows which disk is being overused and over burdened.
The output of the query shows a large number of physical writes on HR tablespace. To get a better balance we can move the HR.dbf tablespace to another drive. say C drive.
ALTER TABLESPACE HR OFFLINE;ALTER TABLESPACE HR RENAME DATAFILE 'E:\INSTALL\21CXE\ORADATA\XE\XEPDB1\HR.DBF' TO 'C:\oracle\21cXE\oradata\XE\XEPDB1\HR.DBF';ALTER TABLESPACE HR ONLINE;Hardware level changes that can be considered for improving database performance
With improvements in hardware and file systems and the reduced cost of disk arrays, RAID has become essential in boosting I/O operations for Oracle databases.
RAID (Redundant array of independent disks) is a storage technology that combines multiple hard disks or solid state drives (SSDs) to store data.
The disk array configuration must be done with a proper planning as to the current load in the system and the expected growth in rate of growth in the future, so that the performance of the database is enhanced and also the system is protected from drive failure. This RAID configuration can be done at the hardware and software level. It should be done is such a way as to ensure best performance for a given configuration.
The primary advantage of using disk arrays for Oracle database performance is that access to files is spread across multiple physical devices and controllers automatically.
A disk array is created by grouping several disks in such a way that the individual disks act as one logical disk. Thus a single logical device gets the benefit of multiple physical devices behind it. If one disk in the array gets destroyed, the group of disks can be structured in such a way that the data exists in more than one place. So the system can never go down because of the failure of a single disk. Users continue to operate as if nothing has happened when there is a disk failure. The system alerts the administrator that a specific disk has failed. The administrator removes the failed disk and inserts a new disk. The operating system automatically writes missing information on the new disk and the system goes on without missing a beat.
RAID is the most commonly uses hardware solution used by hardware companies because of the benefits of performance and reliability. There are different configurations of the RAID disk arrays available and the configuration best suited for your solution can be used.
Below are some of the more common options that you may want to consider
RAID0
This level provides automatic disk striping. This means that the distribution of the Oracle datafiles is automatically spread across multiple disks. The datafiles corresponding to a tablespace can be spread across multiple disks instead of one, thus providing an appreciable improvement in disk I/O.
RAID1
RAID1 uses disk mirroring to to protect data by recording copies of the same data on two or more disks. This is normally used for OS mirroring, but can be used for the Oracle datafiles also for high availability.
RAID5
RAID5 carries the parity on an extra disk which allows for media recovery. Heavy read applications get the maximum advantage from this disk array distribution. It is a low cost solution and is inefficient for write intensive solutions. This is because when a write request is made to a RAID5 array, a stripe of data must first be read from the array (usually 64 t 256KB) , the new data is added to the stripe and a new parity block is calculated and the data is written to disk. This process, regardless of the size of the write request can limit throughput and adversely affect performance.
RAID 1+0
RAID 1+0 provides mirrored disks and striping. It incorporates the advantages of RAID0 and RAID1 by providing the disk I/O benefits of RAID0 and the redundancy benefits of RAID1. For high read write environments where sporadic data access is the norm, this RAID level is the optimum solution.
God's word for the day
Fear of the Lord lengthens one’s life, but the years of the wicked are cut short.
The hopes of the godly result in happiness, but the expectations of the wicked come to nothing.
Proverbs 10:27-28
No comments:
Post a Comment