Leveraging new Oracle 12 c features to reduce storage costs

1. Leveraging new Oracle 12c features to reduce storage costs Wesley Vaske Kai Yu Oracle Solutions Engineering

2. About the authors • Wesley Vaske, Systems Engineer, Dell Oracle Solutions Engineering • Things • Kai Yu,

3. Agenda • Changes in enterprise storage • Oracle ILM • Heat Map • Automatic Data Optimization • Our Case Study • Questions

4. Changes in enterprise storage

5. Decreasing cost and increasing capacity • Price / gigabyte continues to drop dramatically but is outpaced by increasing demand for storage. • This leads to increased spending on storage in spite of decreasing costs. • Source: Historical External Disk Storage Data: IDC Worldwide tracking report

6. Storage tiering – Why? • It allows small capacities of high speed flash to accelerate much larger workloads. • It allows slow, high capacity disks to store cold data for high performance applications. • Use the right tool for the job: • SLC Flash for Write Intensive workloads • MLC Flash for Read Intensive workloads • 15k SAS for sequential workloads • 7.2k SATA/NL-SAS for low performance / archival • Tape for offsite / backup / archival SSD 15k SAS NL SAS / SATA Tape

7. Storage tiering – How? Tiering Layer Description Products Advantages Disadvantages Manual • The storage admin moves or assigns storage to applications/servers based on expected performance/cost requirements. • All storage products • Requires multiple storage products • Application awareness dependent on storage admin • Requires multiple storage products • Complex configurations • Application awareness dependent on storage admin Storage Application • The storage appliance moves blocks or pages of data from one type of disk to another type of disk based on rules or usage. • Application has access to different storages with varying cost and performance and moves data between these storage tiers. • Dell Compellent • Dell EqualLogic • EMC FAST • NetApp • Oracle ADO • VMware vSAN • Microsoft Storage Spaces • Storage snapshots can be utilized • Simplest way to implement storage tiering • Best theoretical performance • Able to define storage tiers based on better data • Vendor Agnostic • Consistent application of tiering rules • Not application aware • Can’t combine different vendors in a full-featured configuration. • Unable to use storage snapshots over entire dataset

8. Oracle Information Lifecycle Management (ILM)

9. Information Lifecycle Management (ILM) Active • ILM is the practice of applying specific policies for effective information management. • With respect to databases, ILM refers to the processes and practices of aligning digital records—table “rows”—with the most appropriate and cost effective IT infrastructure at each phase of its useful life. • Examples: • Moving transaction data to a data warehouse for improved analytics performance • Compressing tables or partitions as data ages to reduce storage capacity • Moving data for completed transactions to read-only storage Less Active Historical Archive

10. New features in Oracle Database 12c • While ILM has been practiced for decades the explosion of new data and the proliferation of storage technologies has lead to innovations in improving management of automated ILM. • Heat Map • Stores system-generated data usage statistics at the block and segment levels • Segment level Heat Map tracks the time of last modification and access of tables and partitions • Row level Heat Map tracks modification times for individual rows (aggregated to the block level) • Heat Map skips internal access done for system tasks—automatically excluding Stats Gathering, DDLs or Table Redefinitions • Automatic Data Optimization • Allows you to create policies for data compression (Smart Compression) and data movement, to implement storage and compression tiering • Smart Compression refers to the ability to utilize Heat Map information to associate compression policies, and compression levels, with actual data usage

11. Heat Map • Views for interacting with Heat Map: • Heat Map can be enabled at the system or session level • To support ADO it must be enabled at the system level • You can use the DMBS_HEAT_MAP PL/SQL package to get more detail information from Heat Map via a set of procedures.

12. Automatic Data Optimization (ADO) • Compression types: • Data can be compressed at the table, partition/segment, or row level (rows are aggregated to the block level) • Data can be tiered at the table or partition level (there is no row-based tiering)

13. Oracle ILM – ADD POLICY • ILM policies are created via “CREATE TABLE” or “ALTER TABLE” commands. • Policies will indicate compression or tiering, row/segment/table level, rule for execution. • Example: Segments in the orders table are compressed as columnar data if 30 days pass with no modifications. • ALTER TABLE orders ILM ADD POLICY • COLUMN STORE COMPRESS FOR QUERY HIGH SEGMENT • AFTER 30 DAYS OF NO MODIFICATION; • Example: Data in the orders table will be tiered to the tablespace “archiveTableSpace” based on the parameters set via the DBMS_ILM_ADMIN.customize_ilm procedure. With the default options, this policy would tier partitions when the source tablespace reaches 85% used. Partitions would be tiered based on the frequency and date of table access. • ALTER TABLE orders ILM ADD POLICY • TIER DATA TO archiveTableSpace;

14. Oracle ILM - Administration • DBMS_ILM_ADMIN Provides an interface to customized Automatic Data Optimization (ADO) policy execution. • customize_ilm Allows you to set parameters for ILM. • disable_ilm Procedure to turn off background ILM • enable_ilm Procedure to turn on background ILM • clear_heat_map_all Procedure to delete all rows except dummy row • set_heat_map_all Procedure to update/insert a row for all tables • set_heat_map_table Procedure to update/insert a row for this table/segment • clear_heat_map_table Procedure to clear all/some statistics for table • set_heat_map_start Procedure to set start date of heat map data

15. Automatic Data Optimization Examples

16. Automatic Data Optimization Examples • Example 1 : Create segment-level ADO policy to automatically compress table after 5 days no modification • Create compression policy . Check the policy Display Heat map tracking data

17. Automatic Data Optimization Examples • Enforce the ADO policy immediately • Check the task status • Verify the tables compression

18. Automatic Data Optimization Examples • Example 2 : Create ADO policy for storage tiering: move objects from T1DATA to T2DATA • Check Tablespaces space usage: • Check ADO parameters for ADO data migration threshold T1DATA: PCT_USED: 91.62% > 80% threshold . Create data movement policy:

19. Automatic Data Optimization Examples • Check the policy • Enforce the ADO policy immediately • Check the task status

20. Automatic Data Optimization Examples • Check the result T1DATA -- CUSTOMER ---> T2DATA • Example 3 : Compress partitions Check partitions and their write time

21. Automatic Data Optimization Examples • Create policy • Check policy • Check execution: • Recreate Policy:

22. Automatic Data Optimization Examples • Check Policy • Check execution: • Check the result

23. Automatic Data Optimization Examples • Example 4: Move partition: • Current tablespace: • Create policy: • Execute the policy: • Why ?? T1DATA’s PCT_USED (84.62%) < 85% threshold

24. Automatic Data Optimization Examples • Force the policy to get executed by change T1DATa PCT_used parameter • Check the task: • Check the result • Before :

25. Links

26. Sources • Historical External Disk Storage Data - http://stevej-lab-notes.blogspot.com/2014/06/historical-external-disk-storage-data.html • Heat Map, Automatic Data Optimization and ILM with Oracle Database - http://www.oracle.com/technetwork/database/enterprise-edition/index-090321.html • DMBS_ILM_ADMIN Documentation - http://docs.oracle.com/database/121/ARPLS/d_ilm_admin.htm

27. Questions?