Oracle – Does Windows Server File Compression Affect DB Performance?

compressiondisk-structuresoracleperformancewindows

I'm starting a new a new contract and I get to troubleshoot their Oracle instance… I'm more apps and sql, but I'll be trying to figure out their crashes and slow performance. Go figure.

I can guess at reasons for some slowness… they want me to review their queries and tune where I can… DB and queries… Time for me to brush off those old Oracle DBA books.

I don't get access to Oracle for a bit, but I've got access to the Win 2008 R2 server and MS SQL Server. I notice that their MS SQL backups are in pretty poor shape, with 987+ backups across 6 or so databases… they were down to 42M of space left on their 173G drive.

Looking at the system over all, I see that they are keeping their DB and databases on compressed drives. I would think that this would give them a performance hit, but I really don't know.

Would this affect the performance of their Oracle and MS SQL negatively?

Update:
As I look around their system, I see other patterns of Worst Practices:
Multiple applications on one server: Oracle DB, OracleBI, MS SQL 2008, Oracle WebLogic, Sharepoint…
All are on a single spindle — 1 TB set in 5 virtual drives.
Almost all applications are in directories with folder/file compression set.
The sizing for the drives is currently a bit tight. The Oracle "drive" has 4% free space (4.3 G free). The MSSQL drive had what… 0.02% free before I deleted some old backups…

I will recommend additional drives (which probably won't happen) or to at least re-size the "Drives" and share out some of the 400 GB remaining on C:

The 4.3 GB free on the Oracle drive may not be a critical piece, but I can't tell until I can get Oracle up and running long enough to see what their DBs and conditions are. So, "performance"-wise, it's a ZERO… at least MSSQL is running.

Thanks for your input… now off to find and digest the Oracle logs.

Best Answer

Indeed it is a bad idea put oracle datafiles in a compressed folder as it uses async random IO.

eg. on http://support.microsoft.com/kb/156932 we read:

One obstruction to asynchronous operation is NTFS compression. The file system driver will not access compressed files asynchronously; instead all operations are just made synchronous.

If you have enterprise version of Oracle installed you can look into table and index compression provided by Oracle itself.

We can still use NTFS compression for almost everything in the recovery area (archive logs,rman backupsets,...),the dpdump folder and logging files.

Related Question