oracle lmd_oracle connect by level「建议收藏」

一、 信息生命周期管理简介

1. 概念

ILM（Information lifecycle management）这个概念诞生于上世纪60-70年代，由StorageTek（2010年被Oracle收购） 提出，EMC推广，是指从一个信息系统数据及其相关元数据产生和初始存储阶段到最后过时被删除时的一套综合管理方法。

可以根据各项数据标准（常用性、重要性等）自动把数据归到各层，并且自动完成数据在各层之间的移动。

2. 产生背景

• 信息爆炸增长
• 成本限制
• 信息价值
• 法律法规要求…

二、 Oracle中的ILM

1. 12c前的实现方法

① 实现步骤

• 定义数据层级，确定分区策略 — 按常用性、隐私性、重要性等分层
• 为各数据等级确定存储层策略
• 创建数据访问和移动策略
• 定义和实行复杂策略

Data Retention（数据保留，保证某些数据未被删除 — 用户权限限制）

Immutability（不变性，保证某些数据未被修改过 — 证书与数字签名）

Privacy（数据隐私 — VPD、数据加密）

Auditing（标准审计、细粒度审计）

Expiration（数据过期 — 移动到归档数据库、数据删除）

② 缺点

• 按常用性分级时，须由DBA判断哪些是冷数据
• 分区移动策略实现较复杂
• 分区移动不支持online操作
• 分区维护（move,drop,truncate分区等）会导致索引失效

③ 实现测试

创建表空间
CREATE TABLESPACE ILM_HIGH_COST
DATAFILE '/data/ilm_high_cost.dbf' SIZE 1G
AUTOEXTEND ON NEXT 1G MAXSIZE 30G;

CREATE  TABLESPACE ILM_LOW_COST
DATAFILE '/data/ilm_low_cost.dbf' SIZE 1G
AUTOEXTEND ON NEXT 10G MAXSIZE 30G;

创建测试用户并授权
create user ilmtest identified by ilmtest default TABLESPACE ILM_HIGH_COST;
grant connect,resource to ilmtest;

ALTER USER ilmtest QUOTA UNLIMITED ON ILM_HIGH_COST;
ALTER USER ilmtest QUOTA UNLIMITED ON ILM_LOW_COST;


建分区表
CREATE TABLE ilmtest.ilm_table
 ( ID VARCHAR2(120) primary key, 
 time NUMBER(18,0)
 ) partition by range(time)
interval (86400000) --一天
(
   partition p_day_1 values less than (1535731200000) --2018-09-01 00:00:00
);

建索引
CREATE INDEX ilmtest.ilm_table_N2 ON ilmtest.ilm_table(time);

导入测试数据


查看分区情况
select table_name,partition_name from dba_tab_partitions where table_name='ILM_TABLE';

手动移动分区，注意11g会锁表，move后索引会失效
ALTER TABLE ilmtest.ilm_table MOVE PARTITION SYS_P929 TABLESPACE ilm_low_cost COMPRESS;

创建存储过程及job定期移动分区
略

2. 12c实现方法

① 12c的改进

• 热图 HEAT MAP
• 自动数据优化ADO
• 在线move分区（支持online move操作且move后索引不失效）

② 热图

用于追踪Oracle DB数据访问（段级）及修改情况（行级和段级）

特点：

• 启用简单，不需重启

ALTER SYSTEM SET HEAT_MAP = ON;

启用后数据访问情况将被追踪（system及sysaux中对象除外），启/禁用热图同时会启/禁用ADO

• “热图”跟踪

跟踪数据段数据查询及修改时间

跟踪数据块修改时间

• 全面性

区分索引lookup访问及全表扫描访问

自动排除维护任务访问：统计信息收集、DDL、备份、表重定义等

• 高性能

对象级无成本

块级成本<< 5%

相关视图：

V$HEAT_MAP_SEGMENT – 展示实时段访问信息

ALL_,DBA_, USER_HEAT_MAP_SEGMENT – 展示最近段访问信息（V$视图每小时写入一次）

ALL_, DBA_,USER_HEAT_MAP_SEG_HISTOGRAM – 展示段访问信息

DBA_HEATMAP_TOP_OBJECTS – 展示最活跃对象

DBA_HEATMAP_TOP_TABLESPACES – 展示最活跃表空间

DBMS_HEAT_MAP包提供更为灵活的信息展示，支持查看块、区、段、对象、表空间级热图信息，ADO用其过滤“热”块

SELECT * FROM TABLE(DBMS_HEAT_MAP.BLOCK_HEAT_MAP('SH','SALES')) WHERE ROWNUM < 10;

SELECT SUBSTR(tablespace_name,1,16) Tblspace, min_writetime, min_ftstime
FROM TABLE(DBMS_HEAT_MAP.TABLESPACE_HEAT_MAP('EXAMPLE'));

SELECT relative_fno, block_id, blocks, TO_CHAR(min_writetime, 'mm-dd-yy hh-mi-ss') Mintime,
TO_CHAR(max_writetime, 'mm-dd-yy hh-mi-ss') Maxtime,
TO_CHAR(avg_writetime, 'mm-dd-yy hh-mi-ss') Avgtime
FROM TABLE(DBMS_HEAT_MAP.EXTENT_HEAT_MAP('SH','SALES')) WHERE ROWNUM < 10;

SELECT SUBSTR(owner,1,10) Owner, SUBSTR(segment_name,1,10) Segment,
SUBSTR(partition_name,1,16) Partition, SUBSTR(tablespace_name,1,16) Tblspace,
segment_type, segment_size FROM TABLE(DBMS_HEAT_MAP.OBJECT_HEAT_MAP('SH','SALES'));

③ 自动数据优化 ADO

ADO根据热图收集的信息，按照用户定义的策略自动压缩和在不同层级存储间移动数据

ADO 策略

压缩策略

create table t1 (C1 number, C2 varchar2(9)) partition by list(C2)
(partition p1 values(‘clerk’, ‘salesman’) ilm add policy column
store compress for archive high segment after 3 months of creation);

ALTER TABLE sales MODIFY PARTITION sales_q1_2002
ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW
AFTER 30 DAYS OF NO MODIFICATION;

存储层策略

默认按表空间使用率(85%,25%)移动

ALTER TABLE sales MODIFY PARTITION sales_q1_1999
ILM ADD POLICY TIER TO my_low_cost_sales_tablespace;

图中设置TBS_PERCENT_FREE=55

自定义表空间使用率达到多少时移动

BEGIN 
DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_USED, 85):
DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_FREE, 25): 
END;

       在上例中，当表空间达到用户定义的阈值 (85%) 时，数据库会自动将表空间中的极冷表/分区移至目标表空间（注意不会检查目标表空间剩余存储空间），直到表空间配额至少有25%的可用百分比。

可定义按函数策略移动（表空间级不支持）

CREATE OR REPLACE FUNCTION NOT_MODIFIED_CUSTOM (objn IN NUMBER) RETURN BOOLEAN
IS
days number:=0;
begin
select max(sysdate - SEGMENT_WRITE_TIME) into days from user_heat_map_segment where subobject_name = (select subobject_name from user_objects where object_id=objn);
-- For DEBUG purposes only
-- insert into ado_table select object_id,object_name,subobject_name,days, sysdate from user_objects where object_id=objn;
-- commit;
if (days>1) then return true; else return false; end if;
end;
/


ALTER TABLE t MODIFY PARTITION P1 ILM ADD POLICY TIER TO ILM_LOW_COST ON NOT_MODIFIED_CUSTOM;

执行原理图

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

3. 12.2新特性

① ADO支持In-Memory存储

ALTER TABLE sales_2015 ILM ADD POLICY NO INMEMORY AFTER 7 DAYS OF NO ACCESS;

② ADO和heat map支持多租户架构，可在PDB中单独设置

ORACLE-BASE – Heat Map, Information Lifecycle Management (ILM) and Automatic Data Optimization (ADO) in Oracle Database 12c Release 2 (12.2)

③ ADO支持按时间条件指定tier策略（但表空间必须为只读）

ALTER TABLE invoices MODIFY PARTITION invoices_2016_q4 ILM ADD POLICY TIER TO slow_storage_ts READ ONLY SEGMENT AFTER 6 MONTHS OF NO ACCESS;

4. 禁用和删除ILM ADO 策略

/* You can disable or delete an ADO policy in a table with the following */
ALTER TABLE sales_ado ILM DISABLE POLICY P1;
ALTER TABLE sales_ado ILM DELETE POLICY P1;

/* You can disable or delete all ADO policies in a table with the following */
ALTER TABLE sales_ado ILM DISABLE_ALL;
ALTER TABLE sales_ado ILM DELETE_ALL;

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

/* You can disable or delete all ADO policies in a partition with the following */
ALTER TABLE sales MODIFY PARTITION sales_q1_2000 ILM DISABLE_all;
ALTER TABLE sales MODIFY PARTITION sales_q1_2000 ILM DELETE_ALL;

5. ADO的增强

立即执行ADO策略（默认会在维护窗口执行）

declare
v_executionid number;
begin
dbms_ilm.execute_ILM (ILM_SCOPE => dbms_ilm.SCOPE_SCHEMA,
execution_mode => dbms_ilm.ilm_execution_online,
task_id => v_executionid);
end;
/

6. ADO相关视图

• (USER/DBA)_ILMPOLICIES – 查看ADO 策略及状态
• (USER/DBA)_ILMDATAMOVEMENTPOLICIES – ADO 策略细节
• (USER/DBA)_ILMOBJECTS – 策略、对象、继承信息和状态 

• (USER/DBA)_ILMEVALUATION_DETAILS — 策略评估结果记录

• (USER/DBA)_ILMTASKS –  任务及状态，每个task ID跟踪一个ADO评估/执行实例
• (USER/DBA)_ILMEVALUATIONDETAILS – 每个task的评估细节
• (USER/DBA)_ILMRESULTS –  ADO job状态及结果

• (USER/DBA)_ILMPARAMETERS — ILM相关参数

7. 使用限制

• Partition-level ADO and compression are supported for Temporal Validity except for row-level ADO policies that would compress rows that are past their valid time(access or modification).
• Partition-level ADO and compression are supported for in-database archiving if partitioned on the ORA_ARCHIVE_STATE column.
• Custom policies (user-defined functions) for ADO are not supported if the policies default at the tablespace level.
• ADO does not perform checks for storage space in a target tablespace when using storage tiering.
• ADO is not supported on tables with object types or materialized views.
• ADO is not supported with index-organized tables or clusters.
• 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.
• ADO has restrictions related to moving tables and table partitions

三、 Oracle分区部分新特性及注意事项

12.1 – 19c Oracle分区表的新特性_Hehuyi_In的博客-CSDN博客_dbms_part

参考：

19c及11g官方文档  vldb-and-partitioning-guide

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/ilm_tiering/ilm_tiering.html

Example for ILM ADO Storage Tiering Policy on Table Partition (文档 ID 1966394.1)

Example for ILM ADO Storage Tiering Policy Using Custom PL/SQL Policy Function on Table Partition (文档 ID 1967038.1)

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

《partitioning-wp-12c-1896137》

《NoCOUG_201505_Kanagaraj_ILM》

《NoCOUG_201805_Lakshmanan_ADO》

《ilm-on-oracle11g-1-129053》

今天的文章oracle lmd_oracle connect by level「建议收藏」分享到此就结束了，感谢您的阅读。