Oracle disk I/O tuning: ATA tuning in Windows

The following is part of a series on the different aspects of disk I/O performance and optimization for Oracle databases.

This Content Component encountered an error

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.


ATA tuning in Windows

Figure 2-6 shows a screen capture from a seldom used Windows 2000 system drive (all data and file storage is done to a shared file system). Notice even a lightly used drive is showing over 20% fragmentation. On a laptop using NT4.0 SP6a, after two years of work, the drive was 100% fragmented! No wonder performance was terrible.


Figure 2-6: Disk Fragmentation on Windows

Here is the report that goes along with the display in figure 2-6.

 Volume (C:): Volume size = 9,522 MB Cluster size = 512 bytes Used space = 7,862 MB Free space = 1,659 MB Percent free space = 17 % Volume fragmentation Total fragmentation = 27 % File fragmentation = 54 % Free space fragmentation = 0 % File fragmentation Total files = 86,076 Average file size = 113 KB Total fragmented files = 2,252 Total excess fragments = 51,322 Average fragments per file = 1.59 Pagefile fragmentation Pagefile size = 384 MB Total fragments = 4 Directory fragmentation Total directories = 9,736 Fragmented directories = 645 Excess directory fragments = 4,222 Master File Table (MFT) fragmentation Total MFT size = 130 MB MFT record count = 96,071 Percent MFT in use = 71 % Total MFT fragments = 3 ------------------------------------------------------------------- Fragments File Size Most fragmented files 199 820 KB \oracle\ora9i\oem_webstage\sysman\reporting\webhelp\dba 213 1,595 KB \Documents and Settings\AULTM\Local Settings\TEMP\Going out for the LOB2.doc 155 143 KB \Documents and Settings\AULTM\Local Settings\TEMP\VBE\MSForms.exd 428 5,125 KB \Documents and Settings\All Users\Application Data\Symantec\Norton AntiVirus Corporate Edition\7.5\vd173c10.vdb 131 2,000 KB \Documents and Settings\AULTM\Local Settings\TEMP\Acr18F.tmp 109 468 KB \oracle\ora9i\oem_webstage\oracle\sysman\resources 188 816 KB \oracle\ora9i\oem_webstage\oracle\sysman\help\master\dba 119 9,511 KB \oracle\ora9i\oem_webstage\java-plugin\jinit11818.exe 201 162 KB \Documents and Settings\AULTM\Local Settings\TEMP\Word8.0\MSForms.exd 431 5,100 KB \Documents and Settings\All Users\Application Data\Symantec\Norton AntiVirus Corporate Edition\7.5\xfer_tmp\3FE8FACA.xfr 129 5,042 KB \Program Files\Oracle\Inventory\logs\installActions2001-10-12_02-00-45-PM.log 342 16,739 KB \oracle\ora9i\ldap\oidadmin\osdadminhelp.jar 203 11,036 KB \oracle\ora9i\jlib\cvd.zip 495 44,437 KB \oracle\ora9i\jlib\oembase-9_2_0.jar 125 4,828 KB \oracle\ora9i\jlib\oemlt-9_2_0.jar 139 1,013 KB \WINNT\SECURITY\LOGS\winlogon.log 293 5,100 KB \Documents and Settings\All Users\Application Data\Symantec\Norton AntiVirus Corporate Edition\7.5\VD172E09.VDB 415 4,955 KB \Documents and Settings\All Users\Application Data\Symantec\Norton AntiVirus Corporate Edition\7.5\xfer_tmp\3F8DD5E8.xfr 169 85 KB \WINNT\DirectX.log 204 820 KB \oracle\ora9i\doc\EM\Webhelp\dba 237 2,980 KB \oracle\ora9i\dm\doc\odmjdoc.tar 1,118 54,880 KB \oracle\ora9i\demo\schema\sales_history\sh_sales.dat 138 22,881 KB \oracle\ora9i\ctx\data\frlx\droldF.dat 255 36,881 KB \oracle\ora9i\ctx\data\enlx\droldUS.dat 155 28,784 KB \oracle\ora9i\BIN\oracle.exe 2,700 156 MB \oracle\ora9i\assistants\dbca\templates\Data_Warehouse.dfj 955 143 MB \oracle\ora9i\assistants\dbca\templates\Transaction_Processing.dfj 192 203 KB \oracle\ora9i\admin\OEMREP\bdump\alert_oemrep.log 223 238 KB \oracle\ora9i\admin\aultdb2\bdump\alert_aultdb2.log 1,777 53,148 KB \Documents and Settings\AULTM\Local Settings\Temporary Internet Files\Content.IE5\NCGWIBWI\10i_beta1_lnx_Disk1.cpio[1].gz

In order to fix the above fragmentation, it took multiple passes through the defragmentation routine provided by Windows 2000. The first pass resulted in extreme freespace fragmentation, which would have caused the first file that was written after defragmentation to be fragmented! After 5 passes, the fragmentation was down to 12%, after 9 passes, 10%, which seemed to be the best it could do, leaving 21% of the files fragmented. Figure 2-8 shows the final screen shot after optimization.


Figure 2-8: Final Defragmentation Screen

The final report from the defragmentation utility is shown below. Compare the initial report with the final report and you can see significant reduction in the number of fragments, especially in the directories and system files.

 Volume (C:): Volume size = 9,522 MB Cluster size = 512 bytes Used space = 7,890 MB Free space = 1,632 MB Percent free space = 17 % Volume fragmentation Total fragmentation = 10 % File fragmentation = 21 % Free space fragmentation = 0 % File fragmentation Total files = 86,252 Average file size = 113 KB Total fragmented files = 24 Total excess fragments = 58 Average fragments per file = 1.00 Pagefile fragmentation Pagefile size = 384 MB Total fragments = 4 Directory fragmentation Total directories = 9,745 Fragmented directories = 1 Excess directory fragments = 1 Master File Table (MFT) fragmentation Total MFT size = 130 MB MFT record count = 96,332 Percent MFT in use = 71 % Total MFT fragments = 3 ------------------------------------------------------------------- Fragments File Size Files that did not defragment 9 1 KB \WINNT\SYSTEM32\CONFIG\SOFTWARE.LOG 2 32 KB \WINNT\SYSTEM32\CONFIG\SECURITY 2 1 KB \WINNT\SYSTEM32\CONFIG\SECURITY.LOG 4 3,148 KB \WINNT\SYSTEM32\CONFIG\SYSTEM 2 257 KB \WINNT\SECURITY\LOGS\scepol.log 2 65 KB \oracle\ora9i\oramts\trace\OracleMTSRecoveryService(664).trc 2 17 KB \Documents and Settings\AULTM\Local Settings\Temporary Internet Files\Content.IE5\NCGWIBWI\mount.8[1].html 3 121 MB \Documents and Settings\AULTM\Local Settings\Application Data\Microsoft\Outlook\outlook.pst 2 10 KB \Documents and Settings\AULTM\Local Settings\TEMP\SOF2.tmp 8 1 KB \Documents and Settings\AULTM\NTUSER.DAT.LOG 2 10 KB \Documents and Settings\AULTM\Local Settings\TEMP\SOFF.tmp 2 3 KB \Documents and Settings\AULTM\Local Settings\TEMP\SOF10.tmp 2 3 KB \Documents and Settings\AULTM\Local Settings\TEMP\SOF11.tmp 5 330 MB \Documents and Settings\AULTM\Local Settings\Application Data\Microsoft\Outlook\archive.pst 2 145 MB \oracle\ora9i\oradata\OEMREP\EXAMPLE01.DBF 4 330 MB \oracle\ora9i\oradata\OEMREP\SYSTEM01.DBF 2 200 MB \oracle\ora9i\oradata\OEMREP\UNDOTBS01.DBF 2 148 MB \oracle\ora9i\oradata\aultdb2\EXAMPLE01.DBF 4 400 MB \oracle\ora9i\oradata\aultdb2\SYSTEM01.DBF 2 25 KB \oracle\ora9i\network\log\agntsrvc.log 2 64 KB \oracle\ora9i\network\log\dbsnmp.log 2 1,187 KB \oracle\ora9i\network\log\listener.log 2 156 MB \oracle\ora9i\assistants\dbca\templates\Data_Warehouse.dfj 2 540 KB \WINNT\ShellIconCache

To reduce the chance of fragmentation in Windows with Oracle, consider using raw partitions (partitions where Oracle handles the IO to the disk instead of the operating system). Windows uses an optimistic write algorithm that writes blocks back to the first available location, not their original location, causing Windows to fragment from day one of operation.

Some claim that since the file system in Windows (or AIX or VMS) is designed to fragment, it performs better when fragmented. This statement seems odd since every time you defragment in Windows (or AIX or VMS) your performance improves. If this old SA tale where true, wouldn't performance get worse when you defragment?

Click to buy the book, "Oracle disk I/O tuning," by Mike Ault.


About the author

Mike Ault is a SearchOracle.com expert and a senior Oracle consultant with Burleson Consulting, and one of the leading names in Oracle technology. The author of more than 20 Oracle books and hundreds of articles in national publications, Mike Ault has five Oracle Masters Certificates and was the first popular Oracle author with his landmark book "Oracle7 administration and management." Mike also wrote several of the "Exam Cram" books, and enjoys a reputation as a leading author and Oracle consultant. Ask Mike a question today!


This was first published in August 2004

Dig deeper on Oracle database performance problems and tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

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

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close