【Oracle Database 12c新特性】Online Statistics Gathering for Bulk-Load 针对批量数据加载的在线统计信息收集

Oracle database 12c中提出了Online Statistics Gathering for Bulk-Load 针对批量数据加载的在线统计信息收集的新特性。

 

通过online statistics gathering,当出现某些批量数据加载操作例如CREATE TABLE AS SELECT CTAS操作 或者 针对一个空表的INSERT INTO … SELECT操作时,统计信息将被自动收集。

 

online statistics gathering省略了当一个批量数据加载后的必要手动统计信息收集操作; 大家还记得我们在讲10/11g 性能调优时 关于数据量大幅变化操作后的手动收集统计信息建议吗? 实际上这个特性一定程度就是为了解决这里还需要手动去收集一次的麻烦。  这个特性表现的很像之前的CREATE INDEX或REBUILD INDEX时自动完成的统计信息收集。  Oracle通过内部维护操作来维护CTAS或物化视图刷新的统计信息更新 。

 

在数据仓库中,用户经常需要加载大量的数据到数据库中; 这里online statistics gathering 就可以起到作用。

Oracle Database 12c中默认启用这种自动统计信息收集特性,主要的收益在于提升批量加载数据后的SQL性能和可管理性,不在需要用户介入来人工收集了。 由于不在需要手动收集统计信息, 所以也就避免了后续的一次可能的全表扫描。

当使用Online Statistics Gathering时,数据库不收集索引统计信息和直方图。如果确实需要索引统计信息和直方图,则Oracle推荐在批量加载数据后再次使用DBMS_STATS.GATHER_TABLE_STATS。   默认情况下 DBMS_STATS.GATHER_TABLE_STATS仅收集缺失的统计信息,因此当你在bulk load批量加载后执行DBMS_STATS.GATHER_TABLE_STATS,数据库将仅仅收集索引统计信息和直方图histograms, 而表和字段的统计信息将不再被收集。

 

补充1点: SYS用户的对象不启用Online Statistics Gathering,不要使用SYS用户去测试该特性。

 

Online Statistics Gathering for Bulk-Load 的其他限制:

 

  1. It is in an Oracle-owned schema such as SYS.
  2. It is a nested table.
  3. It is an index-organized table (IOT).
  4. It is an external table.
  5. It is a global temporary table defined as ON COMMIT DELETE ROWS.
  6. It has virtual columns.
  7. It has a PUBLISH preference set to FALSE.
  8. It is partitioned, INCREMENTAL is set to true, and extended syntax is not used.

 

FROM 孟买-老托拉呱的笔记:

在Oracle Database 12c中,如下两种Bulk-Load方式下,系统将会自动收集表上的统计信息
¤ CTAS – Create Table As Select …
¤ IIS – Insert Into … Select …

说明:(1)必须是使用direct path insert到一个空表/空分区的情况下

(2)如果是空分区表,收集的是global statistics而不是partition-level statistics。

如果是插入到指定的分区/子分区(空),则收集partition-level statistics而不是global statistics。比如Insert Into sales PARTITION(sales_q1_2013) Select …
如果在插入前,分区sales_q1_2013是空的(其他分区不论是否为空),那么就会收集统计信息。如果表上启用了Incremental Statistics Maintenance属性(11gR2开始提供的特性),那么同时也会自动该分区的摘要(synopsis)信息。

(3)如果rollback,统计信息自动删除。

(4)这个特性,不收集index statistics or histograms,所以,如果需要,Oracle推荐通过DBMS_STATS.GATHER_TABLE_STATS(options => ‘GATHER AUTO’…)
来收集index statistics or histograms。

这就有点象从10g版本开始create index/rebuild index自动收集统计信息的意思了。在12c之前的版本,DBA是需要及时(数据插入之后)手工去收集Statistics,否则可能会在后面的使用中导致不正确的执行计划的出现。

 

 

 

隐藏参数_optimizer_gather_stats_on_load(enable/disable online statistics gathering,默认为TRUE)控制该Online Statistics Gathering for Bulk-Load特性是否打开,默认是打开的。

除了设置_optimizer_gather_stats_on_load=false之外还可以通过NO_GATHER_OPTIMIZER_STATISTICS(QKSFM_DBMS_STATS)的HINT来避免使用Online Statistics Gathering特性。 与之相对的是 GATHER_OPTIMIZER_STATISTICS。

 

测试1: Create table AS select 耗时上启用Online Statistics Gathering大约增加15%

 

 

SQL> create table online_gather as select rownum t1, 'maclean' t2 from dual connect by level<=900000; 

表已创建。 

已用时间:  00: 00: 01.09 

SQL> select num_rows,blocks from dba_tables where table_name='ONLINE_GATHER';

  NUM_ROWS     BLOCKS
---------- ----------
    900000       2282

已用时间:  00: 00: 00.17

SQL> alter session set "_optimizer_gather_stats_on_load"=false;

会话已更改。

已用时间:  00: 00: 00.00

SQL> create table online_gather2 as select rownum t1, 'maclean' t2 from dual connect by level<=900000; 

表已创建。 

已用时间:  00: 00: 00.93 

SQL> select num_rows,blocks from dba_tables where table_name='ONLINE_GATHER2';

  NUM_ROWS     BLOCKS
---------- ----------

已用时间:  00: 00: 00.09

 

 

 

 

2、测试 bulk load insert

 

 

conn malcean/maclean

SQL> create table online_load (t1 int, t2 varchar2(200));

表已创建。

SQL> insert into online_load select rownum t1, 'maclean' t2 from dual connect by level<=900000;

已创建 900000 行。

SQL> commit;

提交完成。

SQL> select num_rows,blocks from dba_tables where table_name='ONLINE_LOAD';

NUM_ROWS BLOCKS
---------- ----------

// 注意仅有INSERT APPEND的情况下才会触发Online Statistics Gathering

SQL> create table online_load1 (t1 int, t2 varchar2(200));

表已创建。

SQL> insert /*+ append */ into online_load1 select rownum t1, 'maclean' t2 from dual connect by level<=900000; 已创建 900000 行。 SQL> commit;

提交完成。

SQL> select num_rows,blocks from dba_tables where table_name='ONLINE_LOAD1';

  NUM_ROWS     BLOCKS
---------- ----------
    900000       2282

【Oracle Database 12c新特性】32k varchar2 max_string_size

在Oracle Database 12c中,我们可以为varchar2、nvarchar2和RAW数据类型指定32767 bytes 的最大长度了, 以便用户将更长的字符串存储在数据库中。

 

在12c之前的版本中,varchar2和nvarchar2数据类型的最大长度是4000 bytes,而raw是2000 bytes。

varcha2、nvarchar2和raw字段的定义长度将影响字段的内部存储方式

  • 定义为4000字节或更小的varchar2、nvarchar2以及2000字节或更小的raw字段,将被inline存放
  • 定义为4000字节以上的varchar2、nvarchar2以及2000字节以上的raw字段的话,被称作extended character data type columns,以out of line方式存储。

 

参数MAX_STRING_SIZE控制扩展数据类型extended data type的最大长度:

  • STANDARD 代表12c之前的长度限制,即varchar2、nvarchar2 4000 bytes, raw 是2000  bytes
  • EXTENDED 代表12c 32k strings新特性,varchar2、nvarchar2、raw最大长度32k  bytes

 

Extended character data types 扩展字符类型存在以下的限制:

  • 不支持cluster table 簇表和index-organized tables索引组织表
  • 不支持intrapartition的并行DDL、UPDATE和DELETE DML
  • 不支持在Automatic Segment Space Management (ASSM)表空间上的intrapartition parallel direct-path inserts

[Read more…]

【12c新特性】RAC Cluster Hub Node-Leaf Node

原帖地址:http://www.askmaclean.com/archives/12c-rac-cluster-hub-node-leaf-node.html

 

在12c的cluster中引入了很多新特性和新概念,其中重复最多的几个名词除了flex cluster、flux asm之外 还有Hub Node和Leaf Node,这里来介绍Hub Node和Leaf Node.

 

flex cluster arch

 

  • Hub Node官方解释:
    • A node in and Oracle Flex Cluster that is tightly connected with other servers and has direct access to a shared disk.
  • Leaf Node官方解释:
    • Servers that are loosely coupled with Hub Nodes, which may not have direct access to the shared storage.

可以看到主题区别在于 Leaf Node不能直接访问shared storage ,这意味着leaf node不是share disk的。 这里Hub Node与12c之前的普通cluster node无区别, 而Leaf Node是新技术。

 

Leaf Node的特性:

  • 与 Hub Node相比 更松散地与cluster捆绑
  • 在启动时自动发现Hub Node
  • 通过一个Hub Node连接到集群
  • Hub Node或网络失败都会造成相关的Leaf Node被驱逐
  • 不要求直接访问共享存储
  • 与Hub Node在同一网络

 

使用Leaf Node实现Flex Cluster的好处显而易见:

  • hub-and-spoke技术将cluster分化成可管理的节点组
  • 仅仅需要Hub Node直接访问OCR和Votedisk
  • 通过限制HUB node的数量,从而减少对关键clusterware资源的争用,例如ocr和Votedisk 。
  • 在节点间所需要的网络互动更少
  • 更少的管理用网络流量,例如节点间的心跳

 

 

对比下图可以看到,12节点的Flex cluster包含12个交互通路, 而普通集群则需要 [ n * (n-1)]/2共66个交互通路。

对于上1000节点的集群,上述的差异会更明显。假设有40个Hub Node,每一个Hub Node对应24个Leaf Node,则Flex Cluster将包含1740个交互通路。  与之对比,普通Cluster需要499500个交互通路。

 

flex cluster

 

 

在Flex Cluster中集群中被驱逐的节点无需重启,仅仅cluster software需要重启。

 

如果Hub Node 失败

  • 该节点将被集群驱逐 , 且如果可能则服务将被relocate到其他Hub Node
  • 该Hub Node对应的Leaf Node亦被集群驱逐,如果可能服务也将relocate到其他Leaf Node上

如果Leaf Node失败

  • 该节点将被集群驱逐,如果可能服务将被relocate到另一个Leaf Node上

 

【Oracle Database 12c新特性】ASM Scrubbing Disk Groups

在12.1中Oracle ASM提供了一个改善可用性和可靠度的的新特性 称作Scrubbing Disk Groups, Disk Scrubbing通过检查数据的逻辑讹误,从而能够在Normal 或者High Redundancy的disk group上修复它们。 Scrubbing 进程需要利用镜像盘来修复逻辑讹误。Disk Scrubbing可以与disk group rebalancing组合使用以减少I/O资源消耗。Disk Scrubbing对产品环境的I/O影响不大。

用户可以指定具体要Scrubbing的磁盘组,特定的磁盘,或者磁盘组内的某一个文件,具体要使用ALTER DISKGROUP命令。如下面的例子:

 

 

SQL> ALTER DISKGROUP data SCRUB POWER LOW;

SQL> ALTER DISKGROUP data SCRUB FILE '+DATA/ORCL/ASKMACLEAN/example.266.806582193' 
       REPAIR POWER HIGH FORCE;

SQL> ALTER DISKGROUP data SCRUB DISK DATA_0005 REPAIR POWER HIGH FORCE;

 

 

当执行如上SCRUB 时:

 

  • 选项REPAIR指定自动修复磁盘讹误,如果未指定REPAIR,则SCRUB仅检查和报告指定目标的逻辑讹误。
  • 选项POWER可以设置为AUTO LOW HIGH 或者MAX。 若POWER未指定,则使用AUTO自动调整。
  • 选项WAIT 指定该命令直到scrubbing 命令完成才返回。若WAIT不指定,则scrubbing操作将加入到scrubbing queue 队列,并命令立即返回
  • 若FORCE选项被指定,则即便系统I/O负载很高或者在系统级别已经禁用了scrubbing ,还是执行该命令。

 

【Oracle Database 12c新特性】TTnn TMON新的redo传输后台进程

在Oracle 11g中 Data Guard的redo传输工作主要由以下3组后台进程实现:

  • ARCi (FAL – archived redo shipping, ping, local only archivals)
  • NSAi (async) 12.1 name: TTnn ,
  • NSSi (sync) –– live redo shipping

 

但从版本12c开始 使用TTnn  例如TT00进程来负责async 异步的redo传输。 另一个后台进程TMON来负责做Redo transport monitor。

 

SQL> select banner from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> select program,pid from v$process where program like '%TMON%' or Program like '%TT%';

PROGRAM                               PID
------------------------------ ----------
ORACLE.EXE (TMON)                       7
ORACLE.EXE (TT00)                      24

 

 

 

 

这样做的目的是 在11g 中因为NSAi async redo ship异步传输进程仍需要LGWR进程的通知才能工作,造成短暂的redo 传输延迟; 所以在12c中TTnn进程的redo传输不再依赖于LGWR。

注意是  这里讨论的是async redo ship 异步redo传输!

11g时:

 

11g nsa

 

12c时

12c ttnn tmon

 

 

TTnn TMON Data Guard ASYNC

【12c database 新特性】Adaptive Execution Plans 自适应的执行计划

12c R1 中引入了SQL优化的新特性- Adaptive Execution Plans 自适应的执行计划,该特性让优化器optimizer 可以在运行时(runtime)自动适配一个性能不良的执行计划, 并避免在后续的仍选择该性能糟糕的执行计划。

 

SQL优化器将在运行时 最终确定其使用的执行计划, 这样可以检测到优化器一开始评估的执行计划可能不是最优的。这样执行计划就可以自动适配到实际的运行条件中。一个自适应的执行计划adaptive plan 是在优化器第一次硬解析得到执行计划后在运行时选择了与原计划有区别的子计划,选择子计划subplan的原因是优化器认为一开始的评估并不准确。

 

换大白话来说, 即便统计信息准确 优化器的评估也可能与实际有出入,但没法在执行前知道, 现在的办法是 先让优化器和平时一样给一个认为”最佳的”执行计划, 在执行过长中对某些数据源获得的结果集做buffer 来统计实际行数  然后和优化器评估的做比较,看是否准确,不准确则变化之后的可以改动的执行计划。

 

优化器optimizer 自适应执行计划是基于语句执行时的执行信息统计数据的,这些数据在执行时被收集。所有的自适应技术都可能执行一个不同于优化器最初硬解析获得的plan的计划。 这是12C中对查询处理引擎的重要提升, 优化器的判断将更注重了解过去的执行情况,即优化器有了 前事不忘后事之师的能力。

 

自适应执行计划主要有以下2个技术:

 

  • Dynamic Plans动态计划: 动态计划是指在语句执行期间在多个子计划之间选择;对于动态计划,优化器optimizer需要决定哪一个子计划subplans最终将包含在本次的动态计划中, 哪些执行统计信息需要收集以便选择子计划,以及做出选择需要机遇的阀值。
  • Reoptimization再次优化: 与Dynamic Plans不同的是,Reoptimization是在当前执行之后再次执行时改变执行计划。对于Reoptimization而言,优化器必须判断在原执行计划的哪一步收集哪些统计信息,以及reoptimization是否可行。

 

 

adaptive execution plans

OPTIMIZER_ADAPTIVE_REPORTING_ONLY 参数控制 report-only模式的自适应优化。当该参数设置为TRUE,则自适应的优化器以report-only模式运行,仅收集自适应优化器所需要的信息,但是不采取改变执行计划的行动。

 

Dynamic Plans

动态执行计划仍是一个执行计划,只是它有着多个不同的内置计划选项。在第一次执行时, 在某个特定的子计划激活之前,优化器将作出最终的决定,选择哪一个选项被使用。优化器的选择基于它运行到这一个步骤的整个过程间观察到的数据。 动态计划是优化器最终启用的final plan不同于硬解析时获得的默认计划default plan, 由于final plan比default plan更了解实际情况,所以往往可以改善查询性能。

 

subplan 子计划是整个执行计划的一个部分,优化器在运行时判断是否要切换到这个备选的子计划。

[Read more…]

【Oracle Database 12c新特性】wait event DISPLAY_NAME

在Oracle database 12c 中引入V$EVENT_NAME 视图新增字段DISPLAY_NAME,该字段用以更详细地解释对应的等待事件:

 

DISPLAY_NAME VARCHAR2(64) A clearer and more descriptive name for the wait event that appears in the NAME column. Names that appear in the DISPLAY_NAME column can change across Oracle Database releases, therefore customer scripts should not rely on names that appear in theDISPLAY_NAME column across releases.

 

可惜的是目前并非所有的event都有对应的DISPLAY_NAME,我们列出在12.1.0.1中现有的display name:

 

select name,display_name,wait_class from v$event_name  where name!=display_name order by name

 

NAME DISPLAY_NAME WAIT_CLASS
DFS db file lock quiesce for datafile offline Other
Image redo gen delay redo resource management Other
LGWR real time apply sync standby apply advance notification Idle
concurrent I/O completion online move datafile IO completion Administrative
control file sequential read control file read System I/O
control file single write control file write System I/O
datafile copy range completion online move datafile copy range completion Administrative
datafile move cleanup during resize online move datafile resize cleanup Other
db file parallel read db list of blocks read User I/O
db file parallel write db list of blocks write System I/O
db file scattered read db multiblock read User I/O
db file sequential read db single block read User I/O
db file single write db single block write User I/O
log buffer space log buffer full – LGWR bottleneck Configuration
log file parallel write log file redo write System I/O
log file sequential read log file multiblock read System I/O
log file single write log file header write System I/O
log file sync commit: log file sync Commit
wait for possible quiesce finish quiesce database completion Administrative

【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

 

 

 

ADO Automatic Data Optimization策略语法详解:

ALTER TABLE sales ILM ADD POLICY
ROW STORE COMPRESS ADVANCED
ROW AFTER 3 DAYS OF NO MODIFICATION;  

解释为无修改3天后,则将数据行 高级压缩

 

 

ROW STORE COMPRESS ADVANCED ==>压缩类型

可用的压缩类型包括:

ROW STORE COMPRESS (Basic 压缩)
ROW STORE COMPRESS ADVANCED (Advanced Row 压缩)
COLUMN STORE COMPRESS FOR QUERY LOW/HIGH (HCC Query )
COLUMN STORE COMPRESS FOR ARCHIVE LOW/HIGH (HCC Archive )

 

ROW =>处理对象范畴

处理对象范畴包括:
Tablespace
GROUP ==> 包括表上的索引和LOB
Segment => 表/分区/子分区
ROW => 最小处理单位

 

 
NO MODIFICATION ==> 行为
行为:

NO MODIFICATION =>没有INSERT/UPDATE/DELETE/Merge等修改
NO ACCESS =>没有INSERT/UPDATE/DELETE/Merge/SELECT
CREATION => 创建

 

 

AFTER 3 DAYS ==> 时间
n DAY[s]
n MONTH[s]
n YEAR[s]

 

 

另一种移动Storage Tier的模式:

ALTER TABLE sales ILM ADD POLICY  TIER TO Low_Cost_tbs;

 

TIER 移动到

Low_Cost_tbs为指定的表空间

 

在充分利用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

 

  • 先介绍一下我们演示中要用到的脚本和存储过程
  • ilm_setup_basic 是我们测试ILM的基础环境脚本 负责创建下面的一些过程
  • print_compression_stats 打印出表的压缩状态 主要通过dbms_compression.get_compression_type包
  • list_ilm_policies 列出ILM策略 ,通过查询dba_ilmdatamovementpolicies 、dba_ilmobjects 、dba_ilmpolicies 三个视图
  • set_back_chktime 通过修改ilmobj$等基表 实际将policy的chktime 修改为几天前,这样我们测试ILM就不需要等好几天了!!但是真实的环境中,显然我们不会也不该用到set_back_chktime
  • set_window 设置维护窗口, 用的是dbms_scheduler.open_window ,由于非行级的策略仅在维护窗口中被执行,所以我们通过手动打开窗口来方便演示
  • ilm_demo_cleanup脚本负责清理实验环境

 

 

 

 

实验场景 1 Background Compression and Compression Tiering:

 

 

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

 

 

 

实验场景2 ILM policy with Storage tiering

 

 

 

 

@ilm_setup_basic C:\APP\XIANGBLI\ORADATA\MACLEAN\maclean1.dbf
@ilm_adv_setup C:\APP\XIANGBLI\ORADATA\MACLEAN\ilm_part1.dbf C:\APP\XIANGBLI\ORADATA\MACLEAN\ilm_part2.dbf C:\APP\XIANGBLI\ORADATA\MACLEAN\low_cost_store.dbf C:\APP\XIANGBLI\ORADATA\MACLEAN\source_tbs.dbf
@tktgilm_demo_env_setup

pause
connect scott/tiger
set serveroutput on 

alter table customer_bak ilm 
      add policy tier to low_cost_store
/ 

SQL> execute set_ilm_param('TBS PERCENT USED',10);
ILM parameter settings ...
--------------------------------------
TBS PERCENT USED : 10

PL/SQL 过程已成功完成。

SQL> execute set_ilm_param('TBS PERCENT FREE',95);
ILM parameter settings ...
--------------------------------------
TBS PERCENT FREE : 95

PL/SQL 过程已成功完成。

pause
execute set_back_chktime(get_policy_name('CUSTOMER_BAK',null,'STORAGE','SEGMENT',null, 0,'LOW_COST_STORE',null),'CUSTOMER_BAK',null,6);

SQL> execute set_back_chktime(get_policy_name('CUSTOMER_BAK',null,'STORAGE','SEGMENT',null, 0,'LOW_COST_STORE',null),'CUSTOMER_BAK',null,6);
Object check time reset ...
--------------------------------------
Object Name    : CUSTOMER_BAK
Object Number  : 116367
D.Object Numbr : 116367
Policy Number  : 61
Object chktime : 29-7月 -13 07.52.46.000000 下午
Distnt chktime : 0
--------------------------------------

PL/SQL 过程已成功完成。

pause

SQL> execute list_ilm_policies
--------------------------------------------------
Policies defined for SCOTT
--------------------------------------------------
Object Name------ : CUSTOMER_BAK
Subobject Name--- :
Object Type------ : TABLE
Inherited from--- : POLICY NOT INHERITED
Policy Name------ : P61
Action Type------ : STORAGE
Scope------------ : SEGMENT
Compression level :
Tier Tablespace-- : LOW_COST_STORE
Condition type--- :
Condition days--- : 0
Enabled---------- :   YES
--------------------------------------------------

PL/SQL 过程已成功完成。

pause
SQL> column table_name format a30
SQL> column tablespace_name format a30
SQL> select table_name, tablespace_name
  2  from user_tables
  3  where table_name = 'CUSTOMER_BAK'
  4  /

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
CUSTOMER_BAK                   SRC_TBS

pause

SQL> execute dbms_stats.gather_table_stats('SCOTT','CUSTOMER_BAK');

PL/SQL 过程已成功完成。

SQL> execute estimate_tbs_usage('CUSTOMER_BAK');
Table Name : CUSTOMER_BAK
Num rows : 9999
Avg.Rlen : 37
spc used : 369963
Ttl used : 369963
Net avbl : 10115797
MAX spc. : 10485760

PL/SQL 过程已成功完成。
pause
--
-- Open maintenance window
--
SQL> execute set_window('MONDAY_WINDOW','OPEN');
Set Maint. Window  OPEN
-----------------------------
Window Name   : MONDAY_WINDOW
Enabled?      : TRUE
Active?       : TRUE
-----------------------------

PL/SQL 过程已成功完成。

SQL> insert  into customer_bak select * from customer
  2      where rownum < 8000   3  / 已创建 7999 行。 SQL> commit;

提交完成。
pause
execute dbms_stats.gather_table_stats('SCOTT','CUSTOMER_BAK');
SQL> execute dbms_stats.gather_table_stats('SCOTT','CUSTOMER_BAK');

PL/SQL 过程已成功完成。

SQL> execute estimate_tbs_usage('CUSTOMER_BAK');
Table Name : CUSTOMER_BAK
Num rows : 17998
Avg.Rlen : 37
spc used : 665926
Ttl used : 665926
Net avbl : 41277114
MAX spc. : 41943040

PL/SQL 过程已成功完成。

pause

-- sleep to allow the policy to kick in
execute dbms_lock.sleep(180) 

-- Verify the table is moved to the target tablespace
SQL> select table_name, tablespace_name
  2  from user_tables
  3  where table_name = 'CUSTOMER_BAK'
  4  /

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
CUSTOMER_BAK                   LOW_COST_STORE

pause
SQL> select compression, compress_for
  2  from user_tables
  3  where table_name = 'CUSTOMER_BAK'
  4  /

COMPRESSION      COMPRESS_FOR
---------------- ------------------------------------------------------------
DISABLED
pause
SQL> set serveroutput on
SQL> execute list_ilm_policy_executions
--------------------------------------------------
Policies execution details for SCOTT
--------------------------------------------------
Policy Name------ : P61
Job Name--------- : ILMJOB382
Start time------- : 04-8月 -13 07.53.17.173000 下午
End time--------- : 04-8月 -13 07.53.34.341000 下午
-----------------
Object Name------ : CUSTOMER_BAK
Sub_obj Name----- :
Obj Type--------- : TABLE
-----------------
Exec-state------- : SELECTED FOR EXECUTION
Job state-------- : COMPLETED SUCCESSFULLY
Exec comments---- :
Results comments- :
---
--------------------------------------------------

PL/SQL 过程已成功完成。

pause
SQL> execute report_extended_stat
Extended statistics :
Policy details ....
-------------------
Policy Name           : P61
Obj. number           : 116371
Data Obj. number      : 116371
Last check time       : 04-8月 -13 07.53.17.172000 下午
Last execution time   : 04-8月 -13 07.53.34.341000 下午
Last job status       : 2
Execution results..
-------------------
Policy Name           : P61
Obj. number           : 116371
Execution id          : 3815
Job Name              : ILMJOB382
Job Status            : 2
Completion time       : 04-8月 -13 07.53.34.341000 下午
Execution comments    :
Result comments       :

PL/SQL 过程已成功完成。
pause
set serveroutput off 
-- 
-- Clean up 
-- 
drop table employee purge ; 
drop table customer purge ; 
drop table customer_bak purge ; 

spool off 

spool ilm_usecase_3_cleanup.lst
@ilm_demo_cleanup
spool off