【Oracle Database 12c新特性】Information Lifecycle Management ILM和Storage Enhancements

Oracle Database 12c中引入了Information Lifecycle Management ILM 信息生命周期管理和Storage Enhancements 存储增强的特性。

Lifecycle Management ILM 的一个最重要部分是 Automatic Data Placement 自动数据存放, 简称ADP。

存储增强方面 12c引入了在线移动Datafile的特性 Online Move Datafile, 该特性允许用户在线将一个有数据的datafile在存储之间移动,且数据库保持打开并访问该文件。

目前为止(12.1.0.1)Automatic Data Optimization和heat map仍存在以下的限制:

  1. 在一个多租户数据库 (CDB)中仍不支持Automatic Data Optimization和heat map
  2. Row-level policies for ADO are not supported for Temporal Validity. Partition-level ADO and compression are supported if partitioned on the end-time columns.
  3. Row-level policies for ADO are not supported for in-database archiving. Partition-level ADO and compression are supported if partitioned on the ORA_ARCHIVE_STATE column.
  4. Custom policies (user-defined functions) for ADO are not supported if the policies default at the tablespace level.
  5. ADO does not perform checks for storage space in a target tablespace when using storage tiering.
  6. ADO is not supported on tables with object types or materialized views.
  7. ADO concurrency (the number of simultaneous policy jobs for ADO) depends on the concurrency of the Oracle scheduler. If a policy job for ADO fails more than two times, then the job is marked disabled and the job must be manually enabled later.
  8. Policies for ADO are only run in the Oracle Scheduler maintenance windows. Outside of the maintenance windows all policies are stopped. The only exceptions are those jobs for rebuilding indexes in ADO offline mode.
  9. ADO has restrictions related to moving tables and table partitions.

用户可以在行row,segment数据段和表空间级别指定ADO策略,具体可以在create table或alter table语句中指定。 通过指定ADO策略,用户可以实现数据的自动化移动,这种移动发生在数据库的多个存储层 storage tier , 同时也可以为每一个storage tier指定不同的压缩粒度, 以及何时发生上述的数据移动。ADO策略的作用域可以指定为 segment、row或者group。

在CREATE TABLE和ALERT TABLE中加入ILM的子句,可以实现创建、删除、启用和禁用相关的ADO policy。 一个ILM policy策略子句决定了压缩和存储层策略。 当创建一张表时 可以加入ADO policy, 也可以通过alter table  增加更多的策略,亦或者启用、禁用和删除策略。

CREATE TABLE sales_ado
 (PROD_ID NUMBER NOT NULL,
  CUST_ID NUMBER NOT NULL,
  TIME_ID DATE NOT NULL,
  CHANNEL_ID NUMBER NOT NULL,
  PROMO_ID NUMBER NOT NULL,
  QUANTITY_SOLD NUMBER(10,2) NOT NULL,
  AMOUNT_SOLD NUMBER(10,2) NOT NULL )
  ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT
      AFTER 6 MONTHS OF NO ACCESS;

SQL> SELECT SUBSTR(policy_name,1,24) AS POLICY_NAME, policy_type, enabled
  2         FROM USER_ILMPOLICIES;

POLICY_NAME          POLICY_TYPE                ENABLED
-------------------- -------------------------- --------------
P41                  DATA MOVEMENT              YES

ALTER TABLE sales MODIFY PARTITION sales_1995 
     ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT 
     AFTER 6 MONTHS OF NO ACCESS;

SELECT SUBSTR(policy_name,1,24) AS POLICY_NAME, policy_type, enabled 
   FROM USER_ILMPOLICIES;

POLICY_NAME              POLICY_TYPE   ENABLE
------------------------ ------------- ------
P1                       DATA MOVEMENT YES
P2                       DATA MOVEMENT YES

/* You can disable an ADO policy with the following */
ALTER TABLE sales_ado ILM DISABLE POLICY P1;

/* You can delete an ADO policy with the following */
ALTER TABLE sales_ado ILM DELETE POLICY P1;

/* You can disable all ADO policies with the following */
ALTER TABLE sales_ado ILM DISABLE_ALL;

/* You can delete all ADO policies with the following */
ALTER TABLE sales_ado ILM DELETE_ALL;

/* You can disable an ADO policy in a partition with the following */
ALTER TABLE sales MODIFY PARTITION sales_1995 ILM DISABLE POLICY P2;

/* You can delete an ADO policy in a partition with the following */
ALTER TABLE sales MODIFY PARTITION sales_1995 ILM DELETE POLICY P2;

ILM 的语法主要如下:

ilm_clause

ilm_policy_clause

tiering_clause

table_compression

在充分利用ILM ADP策略之前,需要有几个步骤:

首先需要启动 活跃追踪 activity tracking, 可选的有2个级别的追踪方式,会从不同的维度激活系统自动生成统计信息:

  • SEGMENT-LEVEL段级活跃度是指对一张表或某个分区的读和写
  • ROW-LEVEL行级是指行的生成,最后修改和访问

我们来举几个例子:

1、段级活跃追踪 SEGMENT-LEVEL activity tracking

ALTER TABLE interval_sales ILM  ENABLE ACTIVITY TRACKING SEGMENT ACCESS

上面启用了对于INTERVAL_SALES表的segment level  activity tracking,对该表段的读和写均会被收集为统计信息

2、 行的创建和修改活跃追踪

ALTER TABLE emp ILM ENABLE ACTIVITY TRACKING (CREATE TIME , WRITE TIME);

3、行的访问活跃追踪

ALTER TABLE emp ILM ENABLE ACTIVITY TRACKING  (READ TIME);

在12.1.0.1.0正式发行版中 使用HEAT_MAP特性来追踪数据活跃度, 可以通过在system或者session级别来修改heap_map参数达到启用和关闭的目的。

例如在系统级别启用HEAT MAP特性,则

ALTER SYSTEM SET HEAT_MAP = ON;

当HEAT MAP特性被启用时,所有的访问均会被追踪并存放在内存中的活跃追踪模块中。  注意SYSTEM和SYSAUX表空间上的对象不会被追踪。

在系统级别关闭HEAT MAP特性:

ALTER SYSTEM SET HEAT_MAP = OFF;

默认情况下 HEAT_MAP是关闭的, 当HEAT_MAP关闭时 对数据的访问不会就到内存中的活跃追踪模块中。

该HEAT_MAP同样负责启用和关闭Automatic Data Optimization (ADO)特性。 对于ADO而言,Heat Map 必须在实例级别启用。

可以通过V$HEAT_MAP_SEGMENT 来观察内存中的 HEAT MAP数据

SQL> select * from V$heat_map_segment;

no rows selected

SQL> alter session set heat_map=on;

Session altered.

SQL> select * from scott.emp;

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK          7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN          7698 20-FEB-81       1600        300       30
      7521 WARD       SALESMAN          7698 22-FEB-81       1250        500       30
      7566 JONES      MANAGER          7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN          7698 28-SEP-81       1250       1400       30
      7698 BLAKE      MANAGER          7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER          7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST          7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN          7698 08-SEP-81       1500      0       30
      7876 ADAMS      CLERK          7788 23-MAY-87       1100            20
      7900 JAMES      CLERK          7698 03-DEC-81        950            30
      7902 FORD       ANALYST          7566 03-DEC-81       3000            20
      7934 MILLER     CLERK          7782 23-JAN-82       1300            10

14 rows selected.

SQL> select * from v$heat_map_segment;

OBJECT_NAME          SUBOBJECT_NAME             OBJ#   DATAOBJ# TRACK_TIM SEG SEG FUL LOO     CON_ID
-------------------- -------------------- ---------- ---------- --------- --- --- --- --- ----------
EMP                                            92997      92997 23-JUL-13 NO  NO  YES NO           0

其中v$heat_map_segment的定义,该v$heat_map_segment动态视图的数据来源于内部视图X$HEATMAPSEGMENT

V$HEAT_MAP_SEGMENT displays real-time segment access information.

Column Datatype Description
OBJECT_NAME VARCHAR2(128) Name of the object
SUBOBJECT_NAME VARCHAR2(128) Name of the subobject
OBJ# NUMBER Object number
DATAOBJ# NUMBER Data object number
TRACK_TIME DATE Timestamp of current activity tracking
SEGMENT_WRITE VARCHAR2(3) Indicates whether the segment has write access: (YES or NO)
SEGMENT_READ VARCHAR2(3) Indicates whether the segment has read access: (YES or NO)
FULL_SCAN VARCHAR2(3) Indicates whether the segment has full table scan: (YES or NO)
LOOKUP_SCAN VARCHAR2(3) Indicates whether the segment has lookup scan: (YES or NO)
CON_ID NUMBER The ID of the container to which the data pertains. Possible values include:

 

  • 0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.
  • 1: This value is used for rows containing data that pertain to only the root
  • n: Where n is the applicable container ID for the rows containing data

The Heat Map feature is not supported in CDBs in Oracle Database 12c, so the value in this column can be ignored.

由于HEAP MAP在内存中的数据每一小时才写入到磁盘上,所以查看DBA_HEAT_MAP_SEGMENT一般是有延迟的。 实际数据存放在HEAT_MAP_STAT$字典基表上。

关于Automatic Data Optimization的一个架构图:

ADO

实验场景1:

SQL> alter system set heat_map=on;

系统已更改。

使用下面的页面中的脚本构建 scott用户

http://www.askmaclean.com/archives/scott-schema-script.html

SQL> grant all on dbms_lock to scott;

授权成功。

 SQL> grant dba to scott;

授权成功。

@ilm_setup_basic C:\APP\XIANGBLI\ORADATA\MACLEAN\ilm.dbf
@tktgilm_demo_env_setup 

SQL> connect scott/tiger ;
已连接。

SQL> select count(*) from scott.employee;

  COUNT(*)
----------
      3072

已选择 1 行。

SQL> set serveroutput on
SQL> exec print_compression_stats('SCOTT','EMPLOYEE');
Compression Stats
------------------
Uncmpressed           : 3072
Adv/basic compressed  : 0
Others                : 0

PL/SQL 过程已成功完成。

上面的输出显示3072行数据未压缩

我们执行下面的语句 加入一个policy 对三天未修改的行数据压缩

alter table employee ilm 
      add policy row store compress advanced row 
      after 3 days of no modification 
/ 

SQL> set serveroutput on
SQL> execute list_ilm_policies;
--------------------------------------------------
Policies defined for SCOTT
--------------------------------------------------
Object Name------ : EMPLOYEE
Subobject Name--- :
Object Type------ : TABLE
Inherited from--- : POLICY NOT INHERITED
Policy Name------ : P1
Action Type------ : COMPRESSION
Scope------------ : ROW
Compression level : ADVANCED
Tier Tablespace-- :
Condition type--- : LAST MODIFICATION TIME
Condition days--- : 3
Enabled---------- :   YES
--------------------------------------------------

PL/SQL 过程已成功完成。

SQL> select sysdate from dual;

SYSDATE
--------------
29-7月 -13

SQL> execute set_back_chktime(get_policy_name('EMPLOYEE',null,'COMPRESSION','ROW','ADVANCED',3,null,null),'EMPLOYEE',null,6);
Object check time reset ...
--------------------------------------
Object Name    : EMPLOYEE
Object Number  : 93123
D.Object Numbr : 93123
Policy Number  : 1
Object chktime : 23-7月 -13 08.13.42.000000 上午
Distnt chktime : 0
--------------------------------------

PL/SQL 过程已成功完成。

讲policy的chktime设回到6天前, 注意这里set_back_chktime是通过修改数据字典的方法来实现“时空穿梭”的,不要用在产品环境中,仅仅用来测试的。

打开维护窗口

 alter system flush buffer_cache;
 alter system flush buffer_cache;
 alter system flush shared_pool;
 alter system flush shared_pool;

SQL> execute set_window('MONDAY_WINDOW','OPEN');
Set Maint. Window  OPEN
-----------------------------
Window Name   : MONDAY_WINDOW
Enabled?      : TRUE
Active?       : TRUE
-----------------------------

PL/SQL 过程已成功完成。

SQL> exec dbms_lock.sleep(60) ;

PL/SQL 过程已成功完成。

SQL> exec print_compression_stats('SCOTT', 'EMPLOYEE');
Compression Stats
------------------
Uncmpressed           : 338
Adv/basic compressed  : 2734
Others                : 0

PL/SQL 过程已成功完成。

可以看到进入维护窗口一段时间后 Adv/basic compressed  : 2734 部分行被压缩了

SQL> col object_name for a20
SQL> select object_id,object_name from dba_objects where object_name='EMPLOYEE';

 OBJECT_ID OBJECT_NAME
---------- --------------------
     93123 EMPLOYEE

SQL> execute list_ilm_policy_executions ;
--------------------------------------------------
Policies execution details for SCOTT
--------------------------------------------------
Policy Name------ : P22
Job Name--------- : ILMJOB48
Start time------- : 29-7月 -13 08.37.45.061000 上午
End time--------- : 29-7月 -13 08.37.48.629000 上午
-----------------
Object Name------ : EMPLOYEE
Sub_obj Name----- :
Obj Type--------- : TABLE
-----------------
Exec-state------- : SELECTED FOR EXECUTION
Job state-------- : COMPLETED SUCCESSFULLY
Exec comments---- :
Results comments- :
---
--------------------------------------------------

PL/SQL 过程已成功完成。

ILMJOB48是后台实施policy的JOB,在12.1.0.1中由J00x进程执行

另MMON_SLAVE进程如M00x大约每15分钟实施一些行策略

select sample_time,program,module,action from v$active_session_history where    action  ='KDILM background EXEcution'  order by sample_time;

29-7月 -13 08.16.38.369000000 上午	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.17.38.388000000 上午	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.17.39.390000000 上午	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.23.38.681000000 上午	ORACLE.EXE (M002)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.32.38.968000000 上午	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.33.39.993000000 上午	ORACLE.EXE (M003)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.33.40.993000000 上午	ORACLE.EXE (M003)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.36.40.066000000 上午	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.37.42.258000000 上午	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.37.43.258000000 上午	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.37.44.258000000 上午	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.38.42.386000000 上午	ORACLE.EXE (M001)	MMON_SLAVE	KDILM background EXEcution 

select distinct action  from v$active_session_history where    action like 'KDILM%' 

KDILM background CLeaNup
KDILM background EXEcution

SQL> execute set_window('MONDAY_WINDOW','CLOSE');
Set Maint. Window  CLOSE
-----------------------------
Window Name   : MONDAY_WINDOW
Enabled?      : TRUE
Active?       : FALSE
-----------------------------

PL/SQL 过程已成功完成。

SQL> drop table employee purge ;

表已删除。

关闭窗口 并清理环境

spool ilm_usecase_1_cleanup.lst
@ilm_demo_cleanup ;
spool off
Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

author's avatar The Maclean Liu
Advanced Customer Services
AskMaclean Logo 10g_ocm SHOUG

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today