Optimize Oracle Database Storage with Storage Tiering

    By: Kai Yu on Jun 06, 2017

    Optimize Oracle Database Storage with Storage Tiering

    By Kai Yu ◾ Eric Mader, Editor

    IT organizations face huge challenges with data management performance, capacity and cost due to exponential growth of data. To provide more cost effective storage solution, Solid State Disk (SSD)-based storage can be combined with traditional HDD-based storage to form a tiered solution. In a tiered solution, hot data, or data that is accessed frequently, is stored on SSD storage for performance. The majority of data, however, is not accessed frequently and is therefore stored in cheaper HDD storage for capacity.

    There are several areas that DBAs should consider before implementing a tiered storage solution, including key architecture considerations that may affect the performance of Oracle databases.

    Storage Tiering: Performance vs. Cost

    While SSDs can significantly improve I/O performance, the cost of storing an entire database on this type of storage can be relative high — especially when the volume of data is growing exponentially. However, for many databases, it is quite common that only a small portion of data is actually accessed frequently. Most of this data, such as historical and archive data, doesn't require high-performing I/O and can therefore be moved to lower-cost HDD storage.

    To understand how the I/O performance on hot data impacts the overall database performance, metrics were collected on seven different data distributions ranging from the storage of all database objects in HDDs to storing all database objects in SSDs:

    Table 1

    Config #1 — all database objects stored in HDD storage — was the performance baseline. The six remaining configurations were then compared against this configuration. The performance results were based on a 500 GB database with some TPCC-like transactional application workloads. The SSD storage consisted of 8 PCI e-based SSDs in RAID-10 configuration. The HDD storage consisted of 48 10,000-RPM Hard Disks in RAID-10 configuration. Figure 1 shows the database transaction throughputs of the seven configurations:

    Figure 1

    Figure 2 shows the database average transaction response times of each configuration:

    Figure 2

    Both figures show that hot data consisting of indexes stored in SSD storage — including Config #5, which consists of two hot tables, which account for less than 1/3 of the total database capacity — performs the same as a database configured to use exclusively SSD storage (Config #7). This shows that the tiered storage allows for a database to achieve a high performance by storing a small portion of data in the faster, but more expensive SSD (Tier 1) storage, while storing the remaining data in slower HDD (Tier 2) storage.

    Implementing Storage Tiering for Oracle Database

    The key to storage tiering is selecting performance-sensitive, frequently-accessed hot data for Tier 1 storage, and leaving the remaining, less-frequently accessed data to the Tier 2 or Tier 3 HDD storage. There are a few options to implement this tiered strategy:

    • Use build-in storage tiering in the storage product - Many storage vendors provide a storage tiering feature, which automatically moves data blocks and pages of data between the different tiers within the storage array. This movement is based on predefined rules and metrics how data is accessed.
    • Manually - Database objects are manually moved to the proper storage tier by the DBA. DBAs can move the data objects to the proper tier of the storage based on its performance requirement, data access pattern and capacity of the tiered storage.
    • Information management feature of application or database product - If the tiered storage consists of multiple storage arrays, the storage tiering needs to be independent from a particular physical storage array so the data movement can be managed between the multiple arrays. In this case, it is ideal to have the storage tiering method implemented at the database tier, instead of the physical storage tier. Oracle Database 12c includes new features called Oracle Information Lifecycle Management (ILM) and Automatic Data Optimization (ADO), which can be leveraged for storage tiering.

    Leveraging Oracle ILM for Storage Tiering

    Oracle ILM consists of two parts: 1) a Heat Map, which tracks and stores system-generated data usage statistics about the database segments such as tables, partitions and indexes, and 2) Automatic Data Optimization (ADO) which allows DBAs to create policies and automate actions based on those policies. DBAs need to define the proper polices for ADO to automate the actions that move data between the tiers of storage:

    Moving data between tiers of storage

    For example, the ORDER_HISTORY table can be altered to include a policy to automatically move data from the current tablespace T1Data in Tier 1 storage to T2DATA tablespace in Tier 2 storage:


    The policy also can be defined at the partition level. For example, to move a partition ORD2 to T3DATA tablespace in the Tier3 storage:

    Code Box 1

    When these policies are defined, Oracle ADO will automatically trigger the action once the tablespace utilization meets the certain thresholds set by two ILM ADO parameters: TBS% USED and TBS% FREE, which are both columns in dictionary view DBA_ILMPARAMETER:

    Code Box 2

    TBS% USED indicates the percentage of the tablespace filled when the data begins moving to the new tablespace. TBS% FREE indicates the percentage of tablespace remaining when the data is no longer moved to the new tablespace. In the example above, ORDER_HISTORY will be moved to T2DATA tablespace once its current tablespace T1DATA has reached 85 percent used, and it will stop moving when the T2DATA tablespace space is 25 percent free.

    These threshold numbers can be customized using the CUSTOMIZE_ILMP procedure. For example, we can alter the threshold numbers to 90 percent and 15 percent by calling the CUSTOMIZE_ILM procedure as follows:

    Code Box 3

    The usage statistics, such as the percentage used and percentage free of all tablespaces, are tracked in the database system, and can be queried at any time by the DBA:

    Code Box 4

    Is it possible to automate the table movement based on the table access statistics with Oracle ILM?

    As an alternative to the Oracle ILM/ADO solution which is based on the tablespace usage, DBAs commonly want to use table access statistics to determine which tables should be automatically moved to a lower-tier storage. For example, a DBA may want to move the ORDER_HISTORY table to Tier 2 storage if it has not been modified for 60 days.

    This is a very common use case that is supported in many storage products: the storage tiering feature provided in physical storage product tracks the access pattern of the each data block. Storage administrators can define a tiering rule to move the data blocks based on the data access pattern.

    Oracle ILM/ADO enables DBAs to define a similar data movement policy directly in Oracle Database 12c

    Code Box 5

    In this example, the idea is that if the table has not been updated in 60 days, the user-defined function UPDATE_60DAYS returns TRUE and the ADO action is triggered to move the ORDER_HISTORY table to a new tablespace T2DATA. The function is a PL/SQL function defined by DBAs or developers, which is defined as follows:

    Code Box 6

    To test that this policy actually works, create this policy and confirm that it has been registered by querying USER_ILMDATAMOVEMENTPOLICIES:

    Code Box 7

    Use the PL/SQL procedure dbms_ilm.execute_ILM to execute this policy:

    Code Box 8

    Confirm that the policy was assigned an execution job (in this example, TASK_ID=21):

    Code Box 9

    And that the job completed:

    Code Box 10

    Does the table actually move to a new tablespace T2DATA when the ADO job completes? To validate, check which tablespace ORDER_HISTORY table is stored now:

    Code Box 11

    Unfortunately, the query shows that the ORDER_HISTORY table remains in the original tablespace T1DATA and was not moved the Tier 2 storage tablespace T2DATA.

    This example shows that Oracle ILM/ADO doesn’t support the movement of a table to different tiers of storage based on the table access patterns. For now, DBAs can only utilize Oracle ILM and ADO to automatically move tables between tablespaces in different tiers based on the tablespace capacity utilization thresholds defined by TBS% USE and TBS% FREE parameters.

    Copyright © 2019 Communication Center. All Rights Reserved
    All material, files, logos and trademarks within this site are properties of their respective organizations.
    Terms of Service - Privacy Policy - Contact

    Independent Oracle Users Group
    330 N. Wabash Ave., Suite 2000, Chicago, IL 60611
    phone: 312-245-1579 | email: ioug@ioug.org

    IOUG Logo

    Copyright © 1993-2019 by the Independent Oracle Users Group
    Terms of Use | Privacy Policy