Automatic Data Optimization with Oracle Database 12c

on
Automatic Data Optimization with Oracle Database 12c

http://www.oracle.com/technetwork/database/automatic-data-optimization-wp-12c-1896120.pdf


One of the seriously cool new features in the Oracle database 12c is the automated lifecycle management option. I freely admit you can get the same with other storage vendors (EMC FAST VP is the one I know first hand, but 3PAR and others have similar technology) but this is not really an option for the Oracle Database and Exadata. A quick word of warning: I have not even opened the licensing guide for 12c yet, this may well be a cost option so as always please ensure you are appropriately licensed before using this feature.
Why this post
The official documentation-VLDB and Partitioning Guide, chapter 5-is a bit lacking, as is the SQL Language Reference, hence this post.
Oracle allows you to automate movement of “cold” data from tier 1 storage to lower tiers completely automatic based on so-called ILM policies. These policies can be assigned to a table at creation time or alternatively retrofitted. It is possible to define the policies on the row, segment or a group. Two distinct policies are possible: either to compress the data or to move it to a different tablespace.
Compression
Compression is simple and the documentation is adequate. Consider this example:
1
2
3
4
5
SQL> alter table t1
  2  ilm add policy compress basic segment
  3  after 3 months of no modification;
 
Table altered
Table t1 is of course range partitioned on a date-the only sensible use case for ILM in my opinion anyway. If you are lucky enough to be on Exadata you can use the ILM policy to request Hybrid Columnar Compression too by the way. At the time of writing I could request “compress for archive high” on my laptop as well but that of course is a bug and will lead to problems later. The policy states that a segment which isn’t modified in 3 months is to be compressed with BASIC compression.
Where to find information about ILM policies?
Dictionary views related to the ILM management all start with DBA_ILM:
  • DBA_ILMPOLICIES
  • DBA_ILMDATAMOVEMENTPOLICIES
  • DBA_ILMEVALUATIONDETAILS
  • DBA_ILMPARAMETERS
  • DBA_ILMRESULTS
  • DBA_ILMTASKS
If you get the DDL for a table/partition using DBMS_METADATA.GET_DDL you will also see the ILM policies.
Heat Map Tracking
NOTE: for the ADO option to work you need to set heat_map=ON system wide. Be careful: I haven’t tested if there is an overhead doing so. Once the heat map tracking is enabled you can query v$heat_map_segment and various DBA% views to report reads, writes, index lookups and full scans on the segments under surveillance. In my testing it took a little while for the segments to appear in the views so be patient and keep accessing the data. I used a few scheduler jobs to do so.
Tiering
Tiering is different and here the documentation set is missing detail. Despite the syntax diagram suggesting that you can define a tiering policy based on access/modification/creation as with the compression policies you are mistaken.
By pure chance I found the Oracle By Example for tiering which is so much easier to understand than the official documentation. To enable tiering-which is completely independent of the compression-I used this sample:
1
2
3
4
5
SQL> alter table t1
  2  ilm add policy
  3  tier to ARCH segment;
 
Table altered.
And nothing happened. You cannot specify a condition for tiering yet, i.e. “ilm add policy tier to ARCH segment after …” will cause an error, which is actually expected behaviour.
Enter DBA_ILMPARAMETERS. In this view you see the TBS_PERCENT_USED and TBS_PERCENT_FREE columns which indicate when a segment is to be moved. Again-nothing to do with the access or modification time. As soon as the tablespace containing the segment is full, a job will be started moving the partition in question to the lower tier tablespace.
It is possible to mix and match the compression and tiering with tables, i.e. tables can have multiple ILM policies.

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/ilm_tiering/ilm_tiering.html
https://www.youtube.com/watch?v=k0gLrA9rp5Q


Comments

Post a Comment

Oracle DBA Information