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.
|
|
|
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 oracleora9ioem_webstagesysman
eportingwebhelpdba
213 1,595 KB Documents and SettingsAULTMLocal SettingsTEMPGoing out for the LOB2.doc
155 143 KB Documents and SettingsAULTMLocal SettingsTEMPVBEMSForms.exd
428 5,125 KB Documents and SettingsAll UsersApplication DataSymantecNorton AntiVirus Corporate Edition.5 d173c10.vdb
131 2,000 KB Documents and SettingsAULTMLocal SettingsTEMPAcr18F.tmp
109 468 KB oracleora9ioem_webstageoraclesysman
esources
188 816 KB oracleora9ioem_webstageoraclesysmanhelpmasterdba
119 9,511 KB oracleora9ioem_webstagejava-pluginjinit11818.exe
201 162 KB Documents and SettingsAULTMLocal SettingsTEMPWord8.0MSForms.exd
431 5,100 KB Documents and SettingsAll UsersApplication DataSymantecNorton AntiVirus Corporate Edition.5þr_tmpFE8FACA.xfr
129 5,042 KB Program FilesOracleInventorylogsinstallActions2001-10-12_02-00-45-PM.log
342 16,739 KB oracleora9ildapoidadminosdadminhelp.jar
203 11,036 KB oracleora9ijlibcvd.zip
495 44,437 KB oracleora9ijliboembase-9_2_0.jar
125 4,828 KB oracleora9ijliboemlt-9_2_0.jar
139 1,013 KB WINNTSECURITYLOGSwinlogon.log
293 5,100 KB Documents and SettingsAll UsersApplication DataSymantecNorton AntiVirus Corporate Edition.5VD172E09.VDB
415 4,955 KB Documents and SettingsAll UsersApplication DataSymantecNorton AntiVirus Corporate Edition.5þr_tmpF8DD5E8.xfr
169 85 KB WINNTDirectX.log
204 820 KB oracleora9idocEMWebhelpdba
237 2,980 KB oracleora9idmdocodmjdoc.tar
1,118 54,880 KB oracleora9idemoschemasales_historysh_sales.dat
138 22,881 KB oracleora9ictxdata rlxdroldF.dat
255 36,881 KB oracleora9ictxdataenlxdroldUS.dat
155 28,784 KB oracleora9iBINoracle.exe
2,700 156 MB oracleora9issistantsdbca emplatesData_Warehouse.dfj
955 143 MB oracleora9issistantsdbca emplatesTransaction_Processing.dfj
192 203 KB oracleora9idminOEMREPdumplert_oemrep.log
223 238 KB oracleora9idminultdb2dumplert_aultdb2.log
1,777 53,148 KB Documents and SettingsAULTMLocal SettingsTemporary Internet FilesContent.IE5NCGWIBWIi_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 WINNTSYSTEM32CONFIGSOFTWARE.LOG
2 32 KB WINNTSYSTEM32CONFIGSECURITY
2 1 KB WINNTSYSTEM32CONFIGSECURITY.LOG
4 3,148 KB WINNTSYSTEM32CONFIGSYSTEM
2 257 KB WINNTSECURITYLOGSscepol.log
2 65 KB oracleora9ioramts raceOracleMTSRecoveryService(664).trc
2 17 KB Documents and SettingsAULTMLocal SettingsTemporary Internet FilesContent.IE5NCGWIBWImount.8[1].html
3 121 MB Documents and SettingsAULTMLocal SettingsApplication DataMicrosoftOutlookoutlook.pst
2 10 KB Documents and SettingsAULTMLocal SettingsTEMPSOF2.tmp
8 1 KB Documents and SettingsAULTMNTUSER.DAT.LOG
2 10 KB Documents and SettingsAULTMLocal SettingsTEMPSOFF.tmp
2 3 KB Documents and SettingsAULTMLocal SettingsTEMPSOF10.tmp
2 3 KB Documents and SettingsAULTMLocal SettingsTEMPSOF11.tmp
5 330 MB Documents and SettingsAULTMLocal SettingsApplication DataMicrosoftOutlookrchive.pst
2 145 MB oracleora9ioradataOEMREPEXAMPLE01.DBF
4 330 MB oracleora9ioradataOEMREPSYSTEM01.DBF
2 200 MB oracleora9ioradataOEMREPUNDOTBS01.DBF
2 148 MB oracleora9ioradataultdb2EXAMPLE01.DBF
4 400 MB oracleora9ioradataultdb2SYSTEM01.DBF
2 25 KB oracleora9i
etworkloggntsrvc.log
2 64 KB oracleora9i
etworklogdbsnmp.log
2 1,187 KB oracleora9i
etworkloglistener.log
2 156 MB oracleora9issistantsdbca emplatesData_Warehouse.dfj
2 540 KB WINNTShellIconCache
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!
