【12c】新特性：一文带你详细了解Oracle 12c 数据库的自动数据优化（ADO）

【12c】新特性：一文带你详细了解Oracle 12c 数据库的自动数据优化（ADO）

写在前面

Oracle 12c 可以通过热图和自动数据优化（ADO）实现信息生命周期的管理（ILM），上篇介绍了热图，本篇将对自动数据优化进行展开，通过热图和自动数据优化，最终实现信息生命周期管理。

1 自动数据优化工作流程

使用自动数据优化，必须先在系统级别启用热图，通过修改初始化参数heat_map即可，启用热图后，数据库会自动在段上进行热图统计信息的收集，可通过数据字典视图查看热图相关的信息，具体可参考：Oracle 12c Heat Map 热图 。

2 创建策略

策略可以在执行create table或alter table时在行、段、表空间级别进行创建，在满足指定的策略时可以进行数据的自动压缩和移动，从而完成数据的归档。

2.1 策略创建语法

通过下面的语法结构我们可以知道以下内容。

1）ADO主要在两个层面实现数据的优化，分别为

• 压缩
• 存储分层

2）压缩的类型

• ROW STORE COMPRESS BASIC（Bisic压缩）
• ROW STORE COMPRESS ADVCANCED（Advanced压缩）
• COLUMN STORE COMPRESS FOR QUERY LOW/HIGH（HCC Query压缩）
• COLUMN STORE COMPRESS FOR ArCHIVE LOW/HIGH（HCC Archive压缩）

3）压缩作用域

• Tablespace 表空间：默认设置存储对象
• Group 组：目标对象的索引和LOB
• Segment 段：表、分区和子分区
• Row 行：数据行

4）访问模式

• NO ACESS：无INSERT、DELETE、UPDATE、SELECT
• NO MODIFICATION：无INSERT、DELETE、UPDATE
• CREATION：创建段

5）启动时机

• DAY
• MONTH
• YEAR

ilm_clause::=

ilm_policy_clause::=

ilm_compression_policy::=

table_compression::=

ilm_tiering_policy::=

ilm_time_period::=

2.2 示例：演示压缩

0）查看heat_map初始化参数配置

ALEN@PROD2> show parameter heat_mapNAME TYPE VALUE------------------------------------ ----------- ------------------------------heat_map string ON

1）创建测试表，可以看出，数据行为259418，表没有被压缩，表占用空间为11534336 BYTES

ALEN@PROD2> create table sales_ado as select * from sales;Table created.ALEN@PROD2> select count(1) from sales_ado;COUNT(1)----------259418ALEN@PROD2> select t.compression,t.compress_for from user_tables t where t.table_name='SALES_ADO';COMPRESS COMPRESS_FOR-------- ------------------------------DISABLEDALEN@PROD2> select t.bytes from user_segments t where t.segment_name='SALES_ADO';BYTES----------11534336

2）创建策略

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

3）查看创建的策略

ALEN@PROD2> col policy_name for a10ALEN@PROD2> col policy_type for a30ALEN@PROD2> select policy_name,policy_type,enabled,deleted from user_ilmpolicies;POLICY_NAM POLICY_TYPE ENABLED DELETED---------- ------------------------------ ------- -------P102 DATA MOVEMENT YES NO

4）查看策略对象

ALEN@PROD2> col object_owner for a10ALEN@PROD2> col object_name for a20ALEN@PROD2> set linesize 200ALEN@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）查看热图信息

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 NOALEN@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）因为策略是30天内没有修改的话，就启用高级压缩，下面进行模拟，将时间向前推33天

SYS@PROD2> exec dbms_ilm_admin.set_heat_map_table('ALEN','SALES_ADO','',sysdate-33,2);PL/SQL procedure successfully completed.

7）再次查看热图信息，可以看出 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 NOSALES_ADO 2020-04-23 19:06:03 NO NO NOALEN@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）通过第7步可知，30天内对表没有进行修改操作，那么按照策略，会启用压缩，但策略的执行，需要对应的窗口，或时间间隔，下面进行手工触发策略

ALEN@PROD2> set serveroutput onALEN@PROD2> declarev_taskid number;begindbms_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 63PL/SQL procedure successfully completed.

9）查看任务的执行情况，可以看出任务执行完成

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:46ALEN@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）检查策略执行后，表是否压缩，以及压缩比率

ALEN@PROD2> select t.compression,t.compress_for from user_tables t where t.table_name='SALES_ADO';COMPRESS COMPRESS_FOR-------- ------------------------------ENABLED ADVANCEDALEN@PROD2> select t.bytes from user_segments t where t.segment_name='SALES_ADO';BYTES----------4194304

可以看出，执行策略后，表进行了高级压缩，4194304/11534336 = 0.36 差不多压缩到原来的三分之一。

2.3 示例：演示存储分层

1）创建表空间，分别用于存放数据以及归档数据

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）创建测试表，表段占用空间为11M，占用ts_data表空间的85%

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_DATAALEN@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）策略默认参数是表空间使用百分比TBS PERCENT USED达到85时，会进行数据移动，这里为了实验的目的，修改该值为80

SYS@PROD2> exec dbms_ilm_admin.customize_ilm(8,80);PL/SQL procedure successfully completed.

4）创建存储分层策略，当表空间使用达到80%时，即会进行数据的移动

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

5）查看策略

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 NOP142 ALEN SALES_TS_ADO TABLE POLICY NOT INHERITED YES NO

6）手工触发策略

ALEN@PROD2> declarev_taskid number;begindbms_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 84PL/SQL procedure successfully completed.

7）查看任务执行情况，验证策略执行结果

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:21ALEN@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

可以看到，执行策略后，表段对应的表空间由TS_DATA移到了TS_LOW_STORE，从而自动完成了数据的存储分层移动。

3 管理策略

3.1 禁用/启用策略

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 删除策略

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

3.3 管理ADO参数

1）查看ADO参数

ALEN@PROD2> select * from dba_ilmparameters;NAME VALUE------------------------------ ----------ENABLED 1RETENTION TIME 30JOB LIMIT 2EXECUTION MODE 2EXECUTION INTERVAL 15TBS PERCENT USED 80TBS PERCENT FREE 25POLICY TIME 18 rows selected.

2）修改参数，使用dbms_ilm_admin.customize_ilm

ALEN@PROD2> conn / as sysdbaConnected.SYS@PROD2> exec dbms_ilm_admin.customize_ilm(parameter =>11 , value => 0);PL/SQL procedure successfully completed.

4 监控策略

策略的监控主要通过数据字典视图，分别如下：

1）dba / user_ilmdatamovementpolicies

展示和ILM策略相关的数据移动信息。

2）dba / user_ilmtasks

展示ILM执行的任务信息。

3）dba / user_ilmevaluationdetails

展示ILM任务的评估信息。

4）dba / user_ilmobjects

展示策略和对象信息。

5）dba / user_ilmpolicies

展示策略信息。

6）dba / user_ilmresults

展示数据移动相关的作业信息。

7）dba_ilmparameters

展示ADO相关的参数信息。

以上就是自动数据优化相关的介绍和演示。