The following is part of a series on the different aspects of disk I/O performance and optimization for Oracle databases. Each tip is excerpted from the not-yet-released Rampant TechPress book, "Oracle disk I/O tuning," by Mike Ault. Check back to the main series page for upcoming installments.
 |
|
Mike Ault
|
|
Mike Ault is one of SearchOracle.com's Oracle Internals experts. Mike is senior Oracle consultant with Burleson Consulting, and one of the leading names in Oracle technology.
To view Mike's expert responses or to ask him a question, click here.
|
|
|
Optimizing ATA performance
Generally speaking, if you are dealing with ATA disks you will be using a Windows 2000 server or a Linux server. You will probably have a maximum of four internal IDE/ATA drives with the operating system and software occupying one of the drives. Hopefully, the swap area will be spread over several drives, as will the database (unless the ATA drives have been RAIDed).
In Windows 2000 and Linux, you should be sure the latest firmware for your controller has been downloaded and flashed onto your controller chips. If you have a drive card, make sure it is the latest technology.
ATA tuning in Windows
Other than getting the best drivers, the best interface cards and the fastest disks, there isn't much else in Windows to be done at the interface level. However, there are a few things to improve EIDE performance:
- Enable DMA for the operating system, for Windows 2000 this is done by:
- Right click on My Computer and choose Manage
- Click on Device Manager
- Click on the (+) sign next to IDE ATA/ATAPI controllers
- Double click on the controller you want to change/check
- Click on Advanced Settings
- Select the drop-down menu for Transfer Mode and make sure DMA if available is selected
- Below the drop-down, you will see Current Transfer Mode. This should display Ultra DMA Mode 2, 4 or 5 depending on your system
If DMA if available is already set, but the transfer mode shows PIO, toggle the setting by selecting PIO Only and click OK, then repeat the above steps.
- Enable IDE Block Mode in the system BIOS. This will allow the system to do multiple read/write operations simultaneously to improve throughput. This will probably involve rebooting and then using the system specific keystrokes to enable your BIOS editor. This can be called Block Mode, IDE Block Mode or Multi-Sector Transfer. The setting for this range is from 0 to 32 sectors. You need to verify that your drive will support this mode. In a single read/write operation, the higher the settings the more blocks transferred, thereby reducing the load on the system.
- Make sure hard drives are not slaves to CD-ROMS or other ATA-EIDE devices. Hard drives should always be the masters on an EIDE chain (usually the last drive in the chain is the master with cable-select).
- Make sure the fastest hard drive is set as the master drive in an EIDE chain.
- Adding additional RAM will reduce the systems dependence on drive space for virtual memory, thus improving throughput.
At the physical disk level in Windows, you should implement a defragmentation routine to make sure your drives are not fragmented. Fragmentation in Windows is endemic to the operating system and will occur, robbing you of up to 70% of your disk performance. In one test, boot time went from over 15 minutes to less than 5 when the server's disks were defragmented. Performance improvements of a similar nature can be expected from your Oracle system if it is located on ATA drives in Windows as well.
