Deprecated: Array and string offset access syntax with curly braces is deprecated in /home/u158179386/domains/dba-scripts.com/public_html/wp-content/plugins/easy-table/inc/Encoding.php on line 156
Deprecated: Array and string offset access syntax with curly braces is deprecated in /home/u158179386/domains/dba-scripts.com/public_html/wp-content/plugins/easy-table/inc/Encoding.php on line 158
Deprecated: Array and string offset access syntax with curly braces is deprecated in /home/u158179386/domains/dba-scripts.com/public_html/wp-content/plugins/easy-table/inc/Encoding.php on line 159
Deprecated: Array and string offset access syntax with curly braces is deprecated in /home/u158179386/domains/dba-scripts.com/public_html/wp-content/plugins/easy-table/inc/Encoding.php on line 160
- Renaming a RAC cluster - 27/09/2018
- Stop/Start all RAC databases at once - 26/09/2018
- RMAN Backup script - 08/11/2017
Presentation of Information Lifecycle Management (ILM)
With data volume growing, finding adapted storage solutions to storage costs and performance objectives is a real challenge for IT department in large companies.
Information Lifecycle management is about managing the data all along its useful life while offering the best performance and storage cost.
The concept is simple, data is spread into multiple storage tiers, each one with specific properties. According to this, you can keep active data on fast storage devices and inactive or less active data compressed or into cheap storage devices or offline storage (tape for example).
As you can see in previous diagram, storage tiers are classed by usage, these data classes are definied in the database using partitions. The only thing missing here is the way to manage where and when to move the data.
Oracle 12c offers a new tool to achieve this objective.
Automatic Data Optimization
Automatic data optimization is an automated way to compress and move data across different storage tiers.
To decide which data needs to be moved or compressed, Oracle 12c uses policy management.
These policies could be defined at row, segment or tablespace level.
The policy defines where to move the data and which level of compression should by applied, but we need to know when to do it. This is why ADO can use a new type of statistics called HEAT MAP.
Heat Map is a monitoring tool, it gets statistics about access and mofifications made to segments or rows.
thanks to these statistics, ADO is able to know when data can be compressed or moved. Here is an example of policy that can be applied to a segment.
ALTER TABLE orders ILM ADD POLICY ROW STORE COMPRESS ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION;
This policy, automatically compress partitions using Advanced Row Compression after there have been no modifications for 30 days. The storage will be reduced for older data and queries performance for accessing a large amount of rows in the partition containing the old data will be improved.
By using statistics from Heat Map and the Advanced Row Compression, ADO is capable to compress only the less accessed or modified rows, it compress only data blocks used by the row leaving the most accessed rows uncompressed. Here is an example of policy at a row level:
ALTER TABLE orders ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 3 DAYS OF NO MODIFICATION;
For data movement to a different tier, Oracle default criteria is the tablespace usage percentage, if you want to move data based on other criteria, you can use a policy based on a PL/SQL function.
If the function return true then the data is moved, if it returns false, nothing is done.
Here is an example of tablespace level policy:
ALTER TABLE orders ILM ADD POLICY tier to low_cost_store;
By default Oracle will automatically try to move the data to the new tier when the tablespace will start to become full.
Policies summary
Policies can be applied on :
LEVEL | EXAMPLE |
---|---|
ROW LEVEL | ALTER TABLE sales MODIFY PARTITION sales_q1_2002 ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 30 DAYS OF NO MODIFICATION; |
SEGMENT LEVEL | ALTER TABLE sales MODIFY PARTITION sales_q1_2001 ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT AFTER 6 MONTHS OF NO MODIFICATION; |
TABLESPACE LEVEL | ALTER TABLE sales MODIFY PARTITION sales_q1_1999 ILM ADD POLICY TIER TO my_low_cost_sales_tablespace; |
They can use different types of compression depending on storage tier properties and the objectives.
COMPRESSION TYPE | DESCRIPTION |
---|---|
COMPRESS ADVANCED | standard compression for indexes and LOW for LOB segments. |
COMPRESS FOR QUERY LOW/QUERY HIGH | standard compression for indexes and MEDIUM for LOB segments. |
COMPRESS FOR ARCHIVE LOW/ARCHIVE HIGH | standard compression for indexes and HIGH for LOB segments |
They can apply on different criterias:
- NO MODIFICATION
- NO ACCESS
- CREATION
Conclusion
Information Lifecycle Management is for sure a good solution for companies with large amount of data.
With those tools you can achieve both performance improvement and storage cost reduction with practically no human action once all the policies are created and every tier is correctly set up.
This feature is part of Enterprise Edition and requires the Advanced Compression option.