|Mandalika's scratchpad||[ Work blog @Oracle | Stock Market Notes | My Music Compositions ]|
(Originally posted on blogs.sun.com at:
The Sun Storage F5100 Flash Array and Sun Flash Accelerator F20 PCIe Card help accelerate I/O bound applications such as databases. The following are some of the guidelines to identify Oracle database objects that can benefit by using the flash storage. Even though the title explicitly states "Oracle", some of these guidelines are applicable to other databases and non-database products. Exercise discretion, evaluate and experiment before implementing these recommendations as they are.
Heavily used database tables and indexes are ideal for flash storage
- The database workloads with severe I/O bottlenecks can fully realize the benefits of flash devices
Top 5 Timed Foreground Events section in any AWR report that was collected on the target database system is useful in finding whether disk I/O is a bottleneck
User I/OWait Class is an indication of I/O contention on the system
Identify the I/O intensive tables and indexes in a database with the help of Oracle Enterprise Manager Database Control, a web-based tool for managing Oracle database(s)
An alternate way to identify the I/O intensive objects in a database is to analyze the AWR reports that are generated over a period of time especially when the database is busy
SQL ordered by ..tables in each AWR report
UPDATEstatements with more elapsed and DB times
The database tables that are updated frequently & repeatedly, along with the indexes created on such tables are good candidates for the flash devices
SQL ordered by Readsis useful in identifying the database tables with large number of physical reads
The database table(s) from which large amounts of data is read/fetched from physical disk(s) are also good candidates for the flash devices
To identify I/O intensive indexes, look through the explain plans of the top SQLs that are sorted by
File IO Stats section in any AWR report that was collected on the target database system
Segments by Physical Reads, Segments by Physical Writes and
Segments by Buffer Busy Waits sections in AWR report
Sun flash storage may not be ideal for storing Oracle redo logs
A redo log write that is not aligned with the beginning of the 4K physical sector results in a significant performance degradation
- In general, Oracle redo log files default to a block size that is equal to the physical sector size of the disk, which is typically 512 bytes
However with a block size of 4K for the redo logs, there will be significant increase in redo wastage that may offset expected performance gains
In addition to the I/O intensive database objects, customers running Oracle 11g Release 2 or later versions have the flexibility of using flash devices to turn on the "Database Smart Flash Cache" feature to reduce physical disk I/O. The Database Smart Flash Cache is a transparent extension of the database buffer cache using flash storage technology. The flash storage acts as a Level 2 cache to the (Level 1) SGA. Database Smart Flash Cache can significantly improve the performance of Oracle databases by reducing the amount of disk I/O at a much lower cost than adding an equivalent amount of RAM.
F20 Flash Accelerator offers an additional benefit - since it is a PCIe card, the I/O operations bypass disk controller overhead.
The database flash cache can be enabled by setting appropriate values to the following Oracle database parameters.
Check Oracle Database Administrator's Guide 11g Release 2 (11.2) : Configuring Database Smart Flash Cache documentation for the step-by-step instructions to configure Database Smart Flash Cache on flash devices.