Often when the size of an Oracle database is increasing, buffer cache hit ratio goes down and database performance decreases. There are multiple ways to improve Oracle database performance but some approaches such as adding server DIMMs or upgrading the server can be expensive.
Using Oracle Database Smart Flash Cache with less expensive server flash is another way to improve database performance. When Oracle Database Smart Flash Cache was introduced in Oracle database release 11, flash-based storage arrays were relatively new to the market. Most Oracle databases were running on HDD based storage arrays. When Oracle databases were deployed to flash based storages (i.e. All Flash Arrays or AFA), there were fewer performance benefits using Oracle Database Smart Flash Cache.
With flash technologies being improved over the years, server flash can now deliver much lower latency and higher read/write throughput today. Oracle database performance can be improved by using the database smart flash cache feature, especially for read intensive workloads.
With 2 Intel SSD DC P4610 drives on each Oracle node, we observed performance improvements in peakmarks OLTP cases with a 2-node Oracle RAC environment using VSP G900 with SSDs. Here are some performance comparison charts.
peakmarks DBX-S1 is light OLTP select performance test. This test performs light SQL transactions on dense tables with access to primary key one row per transaction.
Figure 1 shows peakmarks DBX-S1 performance results with 8, 64 and 128 concurrent jobs in a two-node RAC environment.
peakmarks DBX-S25 is medium OLTP select performance test. This test performs medium SQL transactions on dense tables with access to secondary key on average 25 rows per transaction.
Figure 2 shows peakmarks DBX-S25 performance results with 8, 64 and 128 concurrent jobs in a two-node RAC environment.
peakmarks DBX-M25 is medium OLTP mixed performance test. This test performs medium mixed SQL transactions on dense tables with access to secondary key on average 25 rows per transaction.
Figure 3 shows peakmarks DBX-M25 performance results with update ratio of 40% and 8, 64 and 128 concurrent jobs in a two-node RAC environment.
This doesn’t mean that a high-performance storage array can be replaced by a lower performance storage array when using Oracle Database Smart Flash Cache. Oracle database performance often depends upon write workload performance from the storage array. Storage read performance often reduces the cache warm up time needed for Oracle Database Smart Flash Cache.
Since Oracle server CPU utilization will be higher with Oracle Database Smart Flash Cache a CPU bound environment should avoid this feature.
For the reference architecture, please click here.