Identifying Ideal Oracle Database Objects for Flash Storage and Accelerators

(Originally posted on blogs.sun.com at:
http://blogs.sun.com/mandalika/entry/identifying_ideal_oracle_database_objects)

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 no I/O bottlenecks may not show significant performance gains

  • - 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

      • Large number of Waits and the large amount of time in DB spent waiting for some blocked resource under User I/O Wait 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)

  • - The "Performance" page in OEM Database Control helps you quickly identify and analyze performance problems
  • - Historical and the real-time database activity can be viewed from the "performance" page.
    • The same page also provides information about the top resource consuming database objects

• 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

  • - Scan through the SQL ordered by .. tables in each AWR report
  • - Look for the top INSERT & UPDATE statements 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 Reads is 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 Physical Reads

• Examine the File IO Stats section in any AWR report that was collected on the target database system

  • - Consider moving the database files with heavy reads, writes and relatively high average buffer wait time to flash volumes

• Examine Segments by Physical Reads, Segments by Physical Writes and Segments by Buffer Busy Waits sections in AWR report

  • - The database tables and indexes with large number of physical reads, physical writes and buffer busy waits may benefit from the flash acceleration

• Sun flash storage may not be ideal for storing Oracle redo logs

  • - Sun Flash Modules (FMOD) in F5100 array and F20 Flash Accelerator Card are optimized for 4K sector size

    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

    • Majority of the recent Oracle Database platforms detect the 4K sector size on Sun flash devices
    • Oracle database automatically creates redo log files with a 4K block size on file systems created on Sun flash devices

      • 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

F5100 Flash Storage and F20 PCIe Flash Accelerator Card as Oracle Database Smart Flash Cache

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.

 db_flash_cache_file
 db_flash_cache_size

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.

Read More