[12c] new features: This article will give you a detailed understanding of the automatic data optimization (ADO) of Oracle 12c database

Write in front

Oracle 12c can realize information lifecycle management (ILM) through heat map and automatic data optimization (ADO). The first chapter introduces heat map. This chapter will expand automatic data optimization, and finally realize information lifecycle management through heat map and automatic data optimization.

1 automatic data optimization workflow

To use automatic data optimization, you must first enable the heat map at the system level by modifying the initialization parameter heat_map can be used. After the heat map is enabled, the database will automatically collect the heat map statistical information on the segment. You can view the heat map related information through the data dictionary view. For details, please refer to: Oracle 12c Heat Map .

2 create strategy

The policy can be created at the level of row, segment and table space when the create table or alter table is executed. When the specified policy is satisfied, the data can be automatically compressed and moved to complete the data archiving.

2.1 policy creation syntax

We can know the following through the following syntax structure.

1) ADO mainly optimizes data in two levels, namely

  • compress
  • Storage Tiering

2) Type of compression

  • ROW STORE COMPRESS BASIC
  • Row store compress Advanced
  • COLUMN STORE COMPRESS FOR QUERY LOW/HIGH
  • COLUMN STORE COMPRESS FOR ArCHIVE LOW/HIGH

3) Compression scope

  • Tablespace tablespace: default storage objects
  • Group group: index and LOB of target object
  • Segment segments: tables, partitions, and subpartitions
  • Row: data row

4) Access mode

  • NO ACESS: no INSERT, DELETE, UPDATE, SELECT
  • NO MODIFICATION: no INSERT, DELETE, UPDATE
  • CREATION: create segment

5) Start time

  • DAY
  • MONTH
  • YEAR

ilm_clause::=

ilm_policy_clause::=

ilm_compression_policy::=

table_compression::=

ilm_tiering_policy::=

ilm_time_period::=

2.2 example: demonstrate compression

0) view the heat_map initialization parameter configuration

ALEN@PROD2> show parameter heat_map



NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

heat_map string ON

1) Create a test table. You can see that the data behavior is 259418, the table is not compressed, and the table takes up 11534336 BYTES

ALEN@PROD2> create table sales_ado as select * from sales;



Table created.



ALEN@PROD2> select count(1) from sales_ado;



COUNT(1)

----------

259418

ALEN@PROD2> select t.compression,t.compress_for from user_tables t where t.table_name='SALES_ADO';



COMPRESS COMPRESS_FOR

-------- ------------------------------

DISABLED

ALEN@PROD2> select t.bytes from user_segments t where t.segment_name='SALES_ADO';



BYTES

----------

11534336

2) Create policy

ALEN@PROD2> alter table sales_ado ilm add policy row store compress advanced segment after 30 days of no modification;



Table altered.

3) View created policies

ALEN@PROD2> col policy_name for a10

ALEN@PROD2> col policy_type for a30

ALEN@PROD2> select policy_name,policy_type,enabled,deleted from user_ilmpolicies;



POLICY_NAM POLICY_TYPE ENABLED DELETED

---------- ------------------------------ ------- -------

P102 DATA MOVEMENT YES NO

4) View policy objects

ALEN@PROD2> col object_owner for a10

ALEN@PROD2> col object_name for a20

ALEN@PROD2> set linesize 200

ALEN@PROD2> select policy_name,object_owner,object_name,object_type,inherited_from,enabled,deleted from user_ilmobjects;



POLICY_NAM OBJECT_OWN OBJECT_NAME OBJECT_TYPE INHERITED_FROM ENA DEL

---------- ---------- -------------------- ------------------ -------------------- --- ---

P102 ALEN SALES_ADO TABLE POLICY NOT INHERITED YES NO

5) View heat map information

ALEN@PROD2> select object_name,track_time,segment_write,full_scan,lookup_scan from user_heat_map_seg_histogram where object_name='SALES_ADO';



OBJECT_NAME TRACK_TIME SEG FUL LOO

-------------------- ------------------- --- --- ---

SALES_ADO 2020-05-26 18:59:53 NO YES NO

ALEN@PROD2> select object_name,segment_write_time,segment_read_time,full_scan,lookup_scan from user_heat_map_segment where object_name='SALES_ADO';



OBJECT_NAME SEGMENT_WRITE_TIME SEGMENT_READ_TIME FULL_SCAN LOOKUP_SCAN

-------------------- ------------------- ------------------- ------------------- -------------------

SALES_ADO 2020-05-26 19:01:14

6) Since the policy is not modified within 30 days, advanced compression is enabled, and the following simulation will push the time forward by 33 days

SYS@PROD2> exec dbms_ilm_admin.set_heat_map_table('ALEN','SALES_ADO','',sysdate-33,2);



PL/SQL procedure successfully completed.

7) Looking at the heat map information again, we can see that there is no modification to the table after 2020-04-23

ALEN@PROD2> select object_name,track_time,segment_write,full_scan,lookup_scan from user_heat_map_seg_histogram where object_name='SALES_ADO';



OBJECT_NAME TRACK_TIME SEG FUL LOO

-------------------- ------------------- --- --- ---

SALES_ADO 2020-05-26 19:06:38 NO YES NO

SALES_ADO 2020-04-23 19:06:03 NO NO NO



ALEN@PROD2> select object_name,segment_write_time,segment_read_time,full_scan,lookup_scan from user_heat_map_segment where object_name='SALES_ADO';



OBJECT_NAME SEGMENT_WRITE_TIME SEGMENT_READ_TIME FULL_SCAN LOOKUP_SCAN

-------------------- ------------------- ------------------- ------------------- -------------------

SALES_ADO 2020-04-23 19:06:03 2020-05-26 19:06:55

8) It can be seen from step 7 that if the table has not been modified within 30 days, compression will be enabled according to the policy, but the implementation of the policy requires the corresponding window or time interval. The following manual trigger policy

ALEN@PROD2> set serveroutput on

ALEN@PROD2> declare

v_taskid number;

begin

dbms_ilm.execute_ilm(v_taskid,dbms_ilm.scope_schema,dbms_ilm.ilm_execution_offline);

dbms_output.put_line('Task ID is '||v_taskid);

end;

/

Task ID is 63



PL/SQL procedure successfully completed.

9) Check the execution of the task, and you can see that the task is completed

ALEN@PROD2> select task_id,state,creation_time +0 ct,start_time+0 st,completion_time+0 dt from user_ilmtasks where task_id=63;



TASK_ID STATE CT ST DT

---------- --------- ------------------- ------------------- -------------------

63 COMPLETED 2020-05-26 19:12:44 2020-05-26 19:12:44 2020-05-26 19:12:46

ALEN@PROD2> select task_id,policy_name,object_name,object_type,SELECTED_FOR_EXECUTION,job_name from user_ilmevaluationdetails;



TASK_ID POLICY_NAM OBJECT_NAME OBJECT_TYPE SELECTED_FOR_EXECUTION JOB_NAME

---------- ---------- -------------------- ------------------ ---------------------------------------- ----------

63 P102 SALES_ADO TABLE SELECTED FOR EXECUTION ILMJOB608

10) Check whether the table is compressed and the compression ratio after the implementation of the policy

ALEN@PROD2> select t.compression,t.compress_for from user_tables t where t.table_name='SALES_ADO';



COMPRESS COMPRESS_FOR

-------- ------------------------------

ENABLED ADVANCED



ALEN@PROD2> select t.bytes from user_segments t where t.segment_name='SALES_ADO';



BYTES

----------

4194304

As you can see, after the implementation of the policy, the table has been advanced compressed, 4194304 / 11534336 = 0.36, which is almost one third of the original compression.

2.3 example: demonstrate storage Tiering

1) Create tablespaces for storing and archiving data

SYS@PROD2> create tablespace ts_data datafile '/u01/app/oracle/oradata/PROD2/ts_data01.dbf' size 13M;



Tablespace created.



SYS@PROD2> create tablespace ts_low_store datafile '/u01/app/oracle/oradata/PROD2/ts_low01.dbf' size 50M;



Tablespace created.

2) Create a test table with 11M segment space and TS_ 85% of data table space

ALEN@PROD2> create table sales_ts_ado tablespace ts_data as select * from sales;



Table created.

ALEN@PROD2> select t.compression,t.compress_for,t.tablespace_name from user_tables t where t.table_name='SALES_TS_ADO';



COMPRESS COMPRESS_FOR TABLESPACE_NAME

-------- ------------------------------ ------------------------------

DISABLED TS_DATA

ALEN@PROD2> select sum(t.bytes)/1024/1024 from user_segments t where t.segment_name='SALES_TS_ADO';



SUM(T.BYTES)/1024/1024

----------------------

11

3) The default parameter of the policy is that when the percentage of tablespace used reaches 85, the data will be moved. For the purpose of experiment, change the value to 80

SYS@PROD2> exec dbms_ilm_admin.customize_ilm(8,80);



PL/SQL procedure successfully completed.

4) Create a storage tiering policy, when the table space usage reaches 80%, the data will be moved

ALEN@PROD2> alter table sales_ts_ado ilm add policy tier to ts_low_store;



Table altered.

5) View policy

ALEN@PROD2> select policy_name,object_owner,object_name,object_type,inherited_from,enabled,deleted from user_ilmobjects;



POLICY_NAM OBJECT_OWN OBJECT_NAME OBJECT_TYPE INHERITED_FROM ENA DEL

---------- ---------- -------------------- ------------------ -------------------- --- ---

P102 ALEN SALES_ADO TABLE POLICY NOT INHERITED NO NO

P142 ALEN SALES_TS_ADO TABLE POLICY NOT INHERITED YES NO

6) Manual trigger strategy

ALEN@PROD2> declare

v_taskid number;

begin

dbms_ilm.execute_ilm(v_taskid,dbms_ilm.scope_schema,dbms_ilm.ilm_execution_offline);

dbms_output.put_line('Task ID is '||v_taskid);

end;

/

Task ID is 84



PL/SQL procedure successfully completed.

7) View task execution and verify policy execution results

ALEN@PROD2> select task_id,state,creation_time +0 ct,start_time+0 st,completion_time+0 dt from user_ilmtasks where task_id=84;



TASK_ID STATE CT ST DT

---------- --------- ------------------- ------------------- -------------------

84 COMPLETED 2020-05-26 20:25:19 2020-05-26 20:25:19 2020-05-26 20:25:21

ALEN@PROD2> select t.compression,t.compress_for,t.tablespace_name from user_tables t where t.table_name='SALES_TS_ADO';



COMPRESS COMPRESS_FOR TABLESPACE_NAME

-------- ------------------------------ ------------------------------

DISABLED TS_LOW_STORE

As you can see, after the policy is executed, the table space corresponding to the table segment is determined by TS_DATA moved to TS_LOW_STORE, which automatically completes the storage hierarchical movement of data.

3 management strategy

3.1 disable / enable policy

ALEN@PROD2> alter table sales_ado ilm disable policy P102;



Table altered.



ALEN@PROD2> alter table sales_ado ilm enable policy P102;



Table altered.

3.2 deletion strategy

ALEN@PROD2> alter table sales_ado ilm delete policy P102;



Table altered.

3.3 manage ADO parameters

1) View ADO parameters

ALEN@PROD2> select * from dba_ilmparameters;



NAME VALUE

------------------------------ ----------

ENABLED 1

RETENTION TIME 30

JOB LIMIT 2

EXECUTION MODE 2

EXECUTION INTERVAL 15

TBS PERCENT USED 80

TBS PERCENT FREE 25

POLICY TIME 1



8 rows selected.

2) Modify parameters, use dbms_ilm_admin.customize_ilm

ALEN@PROD2> conn / as sysdba

Connected.

SYS@PROD2> exec dbms_ilm_admin.customize_ilm(parameter =>11 , value => 0);



PL/SQL procedure successfully completed.

4 monitoring strategy

The strategy is monitored mainly through the data dictionary view, as follows:

1)dba / user_ilmdatamovementpolicies

Present data mobility information related to ILM policies.

2)dba / user_ilmtasks

Displays task information that ILM performs.

3)dba / user_ilmevaluationdetails

Present assessment information for ILM tasks.

4)dba / user_ilmobjects

Show policy and object information.

5)dba / user_ilmpolicies

Display strategy information.

6)dba / user_ilmresults

Display job information related to data movement.

7)dba_ilmparameters

Display ADO related parameter information.

The above is the introduction and demonstration of automatic data optimization.

Tags: Oracle SQL Database

Posted on Wed, 27 May 2020 04:47:19 -0700 by Karamja