Using Oracle ZFS Storage Appliance for OLAP Workload

OLAP database workloads are mostly SQL queries going after a large chunk of tables or batch process loading bulk of data overnight. You might be loading Terabytes of data to your data warehouse system but those will not be considered random DML statements, hence critical workload will be users only query supporting DSS. This very true nature of DDS can be a good fit for databases compression including HCC. When it comes to using database compression one must understand that HCC will require some maintenance work to keep database HCC compress and it does not support OLTP compression. You should only use compression if you are cloning or duplicating a databases environment which already uses database compression so it can be a true representation of source environment. Additionally, you have an option to both tablespace format traditional big file tablespace but it’s recommended to user big file tablespace to achieve better performance, flexibility, and capacity. Finally, remember to use Oracle Direct NFS as it’s not recommended to use Oracle Intelligent Storage Protocol (OISP) for OLAP workload.


Recommended OLTP Database Layout on ZFS Storage
Oracle Files Record Size Sync Write Bias Read Cache Compression Example Share Name Storage Profile
Datafiles 32K latency all data and metadata LZ4 data Mirrored (NSPF)
Temp 128K latency do not use cache devices LZ4 temp Mirrored (NSPF)
Archive logs 1M throughput do not use cache devices LZ4 archive Mirrored (NSPF)
RMAN Backup 1M throughput do not use cache devices LZ4 backup Mirrored (NSPF)

Source: Oracle

Now let’s look into rest of the recommendation mapping different databases files to their recommended location and record sizes. Data files, temp files, and archive logs can be placed ton ZFS share with respective record sizes 128K, 128K, and 1M.  Even though you have the option to place redo logs and control on ZFS share, it’s best practices to place online redo logs and control files in the default Fast Recovery Area (FRA) on the preconfigured Exadata ASM diskgroup. It is hard to justify placing these two type of sensitive database files on ZFS share as they will not take much space and require high latency.