12c, Administration, ILM

Use ILM feature: Automatic Database Optimization (ADO)

Leave a comment

Oracle Information Lifecycle Management (ILM) is the processes by which we can manage data from creation to deletion.    It allows us a method to increase speed of access and to acquire metadata about use.    The two features which support ILM in Oracle 12c are Heat Maps and Automatic Data Optimization.    This procedure will cover the use of Automatic Database Optimization (ADO) within a 12c RDBMS.   ADO allows us to create policies for compression and data movement within database by use of metadata at the row and segment level.

 

  1. Logon to your Oracle server as the Oracle software owner.

 

[root@linux2 ~]# su – oracle

Last login: Tue Jul 13 09:01:45 EST 2016 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

1

[oracle@linux2 ~]$

 

  1. Logon to SQLPLUS with sysdba permissions.

 

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 14 06:55:06 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL>

 

  1. Using the table test.test_hm, add a compression policy for table if data is not modified in 1 day.

 

SQL> ALTER TABLE test.test_hm ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 1 DAYS OF NO MODIFICATION;

Table altered.

SQL>

 

  1. You must delete a policy if you already have a policy on an object. Example

 

SQL> ALTER TABLE test.test_hm ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT AFTER 1 DAYS OF NO ACCESS;

ALTER TABLE test.test_hm ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT AFTER 1 DAYS OF NO ACCESS

*

ERROR at line 1:

ORA-38323: policy conflicts with policy 1

 

SQL> alter table test.test_hm ilm delete policy p1;

Table altered.

 

SQL> ALTER TABLE test.test_hm ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT AFTER 1 DAYS OF NO ACCESS;

Table altered.

 

SQL>

 

  1. Compression options for ADO policies are:

 

  • COMPRESS ADVANCED – on a heap table maps to standard compression for indexes and LOW for LOB segments.
  • COMPRESS FOR QUERY LOW/ QUERY HIGH – on a heap table maps to standard compression for indexes and MEDIUM for LOB segments.
  • COMPRESS FOR ARCHIVE LOW/ ARCHIVE HIGH – on a heap table maps to standard compression for indexes and HIGH for LOB segments.

 

  1. This completes our coverage of ADO in Oracle 12c

 

Larry Catt

OCP

Leave a Reply