【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

【Oracle Database 12c新特性】ORACLE_MAINTAINED

ORACLE_MAINTAINED是Oracle 12c中一系列视图的新增信息字段,该字段代表对象或用户是Oracle提供的脚本生成的,即Oracle-Supplied objects。

 

ORACLE_MAINTAINED VARCHAR2(1) Denotes whether the object was created, and is maintained, by Oracle-supplied scripts (such as catalog.sql or catproc.sql). An object for which this column has the value Y must not be changed in any way except by running an Oracle-supplied script.

我们来看看那些视图有该字段

oracle@localhost:/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin$ grep -i "ORACLE_MAINTAINED" *|grep comment
cdcore.sql:comment on column USER_OBJECTS.ORACLE_MAINTAINED is
cdcore.sql:comment on column ALL_OBJECTS.ORACLE_MAINTAINED is
cdcore.sql:comment on column DBA_OBJECTS.ORACLE_MAINTAINED is
cdcore.sql:comment on column USER_OBJECTS_AE.ORACLE_MAINTAINED is
cdcore.sql:comment on column ALL_OBJECTS_AE.ORACLE_MAINTAINED is
cdcore.sql:comment on column DBA_OBJECTS_AE.ORACLE_MAINTAINED is
cdenv.sql:comment on column USER_USERS.ORACLE_MAINTAINED is
cdenv.sql:comment on column ALL_USERS.ORACLE_MAINTAINED is
cdenv.sql:comment on column DBA_USERS.ORACLE_MAINTAINED is
cdsec.sql:comment on column DBA_ROLES.ORACLE_MAINTAINED is

DBA_USERS、DBA_OBJECTS、DBA_OBJECTS_AE以及与之相关的ALL_、USER_视图均有ORACLE_MAINTAINED字段。

以下为ORACLE MAINTAINED用户名


  1* select username from dba_users where ORACLE_MAINTAINED='Y'
SQL> /

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
AUDSYS
GSMUSER
SPATIAL_WFS_ADMIN_USR
SPATIAL_CSW_ADMIN_USR
APEX_PUBLIC_USER
SYSDG
DIP
SYSBACKUP
MDDATA
GSMCATUSER
SYSKM
XS$NULL
OJVMSYS
ORACLE_OCM
OLAPSYS
SI_INFORMTN_SCHEMA
DVSYS
ORDPLUGINS
XDB
ANONYMOUS
CTXSYS
ORDDATA
GSMADMIN_INTERNAL
APPQOSSYS
APEX_040200
WMSYS
DBSNMP
ORDSYS
MDSYS
DVF
FLOWS_FILES
SYS
SYSTEM
OUTLN
LBACSYS

【Oracle Database 12cR1新特性】Implementing Temporal Validity

Implementing Temporal Validity

 

Download (PDF, 336KB)

12c Pluggable Database Container Database可插拔数据库特性专题

Oracle Database 12c中带来一种全新的架构,允许用户在一个独立的Oracle数据库中拥有多个pluggable可拔插的数据库。这种Pluggable 可拔插数据库的出现是为了对应 用户目前使用RDBMS数据库的现状,即有一些用户拥有大量的部门级应用构建于Oracle RDBMS数据库之上。

 

以下几个场景适合于使用pluggable database:

  1. 在产品系统中的某些应用实际仅使用十分少量的硬件资源。但是如果存在大量这样的应用,则还是需要构造大量的数据库实例并为这些小规模的数据库分配存储空间
  2. 对于那些并不十分复杂或重要,需要全职DBA花费大量时间管理的数据库
  3. 为了更好地利用硬件和DBA资源,用户有必要将大量的部门级应用整合到少数几个oracle RDBMS数据库中以便部署和管理

 

 

Pluggable Database 可拔插数据库允许DBA整合大量的小的部门级数据库到一个更庞大的数据库中。

 

Pluggable Database 带来的好处

 

在一个集中化的平台上操作多个数据库将有效降低成本:

  1. 更少的实例损耗
  2. 更低的存储成本

 

减少对DBA资源的使用,以及便于维护安全性:

  • 无需应用修改
  • 更快和简便的配置
  • 节省了打patch和升级的时间
  • 分离了以下责任:
    • 不同应用的管理员
    • 应用程序管理员和DBA
    • 应用用户
  • 提供isolation
  • 保证与非CDB 完整的向后兼容性
  • 完整的RAC操作使用
  • 与Oracle Enterprise Manager和Resource Manager整合在一起
  • 可以集中化管理多个数据库
    • 备份和灾难恢复
    • 补丁和升级

 

 

cdb1

 

[Read more…]

从谷歌趋势看谁在研究Oracle 12c

google trends

 

从上图中可以看到在2012年 oow期间12c的搜索趋势出现了一个小高潮,在2013年6月迎来了爆发点一路攀升,目前搜索量已不亚于”Oracle 11g”。

 

从地区上看 不管是12c还是11g,最感兴趣的地区 始终是印度 的卡纳塔克邦和安得拉邦 2个地区,班加罗尔市。

三哥三姐不愧为IT领跑者,对Oracle 12c的研究走到世界最前列!壮哉,我大印度IT产业!

 

12c regional

 

12c regional2

 

 

 

美国本土的话主要集中在 加利福尼亚和 马塞诸塞 2个州。

12c

【12c新特性】12cR1 ROWID IO Batching特性

在介绍12cR1的这个优化器特性之前,我们先来看如下的例子:

 

SQL> create table sample nologging tablespace users as select rownum t1  from dual  connect by level<=900000;  

Table created.  

SQL> alter table sample add t2 number;

Table altered.

update sample set t2=dbms_random.value(1,999999);

900000 rows updated.

SQL> commit;
Commit complete.

SQL> create index ind_t1 on sample(t1) nologging tablespace users;
Index created.

SQL> create index ind_t2 on sample(t2) nologging tablespace users;
Index created.

SQL> exec dbms_stats.gather_table_stats(USER,'SAMPLE',cascade=>TRUE);
PL/SQL procedure successfully completed.

SQL> select blocks,NUM_ROWS from dba_tables where table_name='SAMPLE';

    BLOCKS   NUM_ROWS
---------- ----------
      9107     902319

SQL> select CLUSTERING_FACTOR,LEAF_BLOCKS,DISTINCT_KEYS,index_name from dba_indexes where table_name='SAMPLE';

CLUSTERING_FACTOR LEAF_BLOCKS DISTINCT_KEYS INDEX_NAME
----------------- ----------- ------------- ------------------------------
             1370        2004        900000 IND_T1
           899317        4148        900000 IND_T2

alter session set events '10046 trace name context forever,level 12';

set autotrace traceonly;

alter system flush buffer_cache;

alter session set "_optimizer_batch_table_access_by_rowid"=true;

 select /*+ index(sample ind_t2) */ * from sample where t2 between 1 and 999997;

 select /*+ index(sample ind_t2) */ *
from
 sample where t2 between 1 and 999997

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    60001      4.68       8.56      12754    1810330          0      899999
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    60003      4.68       8.56      12754    1810330          0      899999

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    899999     899999     899999  TABLE ACCESS BY INDEX ROWID BATCHED SAMPLE (cr=1810330 pr=12754 pw=0 time=20413784 us cost=903657 size=24300000 card=900000)
    899999     899999     899999   INDEX RANGE SCAN IND_T2 (cr=63873 pr=4150 pw=0 time=4655140 us cost=4155 size=0 card=900000)(object id 92322)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                   60001        0.00          0.32
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                     11388        0.00          1.70
  SQL*Net message from client                 60001        0.00          8.95
  db file parallel read                         197        0.00          0.00

 alter system flush buffer_cache;

alter session set "_optimizer_batch_table_access_by_rowid"=false;

 select /*+ index(sample ind_t2) */ * from sample where t2 between 1 and 999997;

 select /*+ index(sample ind_t2) */ *
from
 sample where t2 between 1 and 999997

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    60001      4.70       8.82      12754    1810333          0      899999
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    60003      4.70       8.82      12754    1810333          0      899999

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    899999     899999     899999  TABLE ACCESS BY INDEX ROWID SAMPLE (cr=1810333 pr=12754 pw=0 time=25464232 us cost=903657 size=24300000 card=900000)
    899999     899999     899999   INDEX RANGE SCAN IND_T2 (cr=63874 pr=4150 pw=0 time=4404956 us cost=4155 size=0 card=900000)(object id 92322)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                   60001        0.00          0.32
  db file sequential read                     12754        0.00          1.85
  SQL*Net message from client                 60001        0.00          8.95

 

 

我们看到了一个陌生的operation ” TABLE ACCESS BY INDEX ROWID BATCHED” 注意 这个Batched是 之前的版本没有的。

 

须知 TABLE ACCESS BY ROWID 这种常见操作是从 子数据集合(例如INDEX中)获得必要的ROWID, 以便在表上定位到对应的行fetch对应数据。 若该行不在Buffer Cache中,则该 Table Access by ROWID的数据集合需要等待必要的IO完成才能处理下一个ROWID。 在很多场景中IO延迟在这里成为重要的瓶颈, 由于不管是RANGE SCAN、FULL SCAN还是Access By Rowid默认均使用DB FILE SEQUENTIAL READ所以如果访问的数据恰巧不在内存里+ 它要Fetch大量的数据行则 往往其整体相应速度和逻辑读要多于全表扫描。

 

常见在以下三种场景中多需要Table Access by Rowid的数据源访问:

  1. Index Range SCan
  2. Bitmap index plan
  3. Nested Loop Join

 

所以Oracle开发人员想到了要使用prefetch预读取数据源来提升性能,通过遍历ROWID以找出那些需要完成的IO操作并prefetch其数据源,将那些数据块预先读入。这里的实现上应当是通过buffer 驱动数据源哪里获得的ROWID,之后通过遍历这些 ROWID对应的的找到需要做物理读的数据块,并使用向量Io操作(例如上文中的db file parallel read)来prefetch这些数据块到buffer cache中,这样TABLE ACCESS By ROWID的访问就可以保证必要的块(主要是表块)均在buffer cache中。

使用此Batching Io特性可以有效减少IO延迟造成的性能损耗,但并不是任何场景都有效。由于实际能buffer的ROWID是有限的,而且是在不知道哪些ROWID对应需要IO哪些不需要的情况下全部都复制到buffer中,所以如果buffer的所有ROWID对应只需要少量的IO,则该IO Batching特性带来的性能改善将最小化。 亦或者遇到的ROWID对应的数据块全部在内存在 一点Io都不需要,则这种prefetch数据的行为有画蛇添足之嫌,反倒会徒增CPU时间片。

 

目前控制该特性的 优化器参数为_ optimizer_batch_table_access_by_rowid,该参数2个选项 TRUE /FALSE负责控制是否启用Table access by ROWID IO batching。

 

还可以通过 BATCH_TABLE_ACCESS_BY_ROWID和 NO_BATCH_TABLE_ACCESS_BY_ROWID 2个HINT来控制是否启用该特性, HINT的优先级高于参数optimizer_batch_table_access_by_rowid。不过目前在12.1.0.1.0上测试该HINT仍有一些问题。

 

 

 

SQL> select * from V$VERSION where rownum=1;

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

  1* select name from v$SQL_HINT where name like '%BATCH%'

NAME
----------------------------------------------------------------
NLJ_BATCHING
NO_NLJ_BATCHING
BATCH_TABLE_ACCESS_BY_ROWID
NO_BATCH_TABLE_ACCESS_BY_ROWID

SQL> alter session set "_optimizer_batch_table_access_by_rowid"=true;

Session altered.

SQL>   select /*+     index(sample ind_t2)  NO_BATCH_TABLE_ACCESS_BY_ROWID */ * from sample where t2 between 1 and 999997;

899999 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3882332507

----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |   900K|    23M|   903K  (1)| 00:00:36 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SAMPLE |   900K|    23M|   903K  (1)| 00:00:36 |
|*  2 |   INDEX RANGE SCAN                  | IND_T2 |   900K|       |  4155   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------

 

【12c新特性】12cR1中新加入的Statistic

【12c新特性】12cR1中新加入的Statistic

 

select  A.* from v$sysstat A  where A.name not in (select B.name from v$sysstat@db_11gR2 B);

 

STATISTIC# NAME CLASS VALUE STAT_ID CON_ID
52 physical read partial requests 8 0 286702467 0
54 physical write requests optimized 8 0 2483607112 0
55 physical write request redirties 8 0 4146911311 0
56 physical write total bytes optimized 8 0 4085960041 0
57 physical write partial requests 8 0 1535615968 0
70 ka messages sent 32 0 4222258831 0
71 ka grants received 32 0 2310418695 0
81 consistent gets pin 8 248409 1168838199 0
82 consistent gets pin (fastpath) 8 240756 2910712465 0
83 consistent gets examination 8 46775 1966540185 0
84 consistent gets examination (fastpath) 8 45808 1990445227 0
86 fastpath consistent get quota limit 40 0 560973176 0
178 flashback securefile cache read optimizations for block new 8 0 955255216 0
179 flashback securefile direct read optimizations for block new 8 0 963322245 0
180 physical reads cache for securefile flashback block new 8 0 2429466467 0
181 physical reads direct for securefile flashback block new 8 0 3121545084 0
184 data warehousing scanned objects 8 0 247471814 0
185 data warehousing scanned chunks 8 0 3880771368 0
186 data warehousing scanned chunks – memory 8 0 1765983694 0
187 data warehousing scanned chunks – flash 8 0 3811273611 0
188 data warehousing scanned chunks – disk 8 0 1684884558 0
189 data warehousing evicted objects 8 0 1827708704 0
190 data warehousing evicted objects – cooling 8 0 1769197766 0
191 data warehousing evicted objects – replace 8 0 547725926 0
192 data warehousing cooling action 8 0 2905230597 0
200 Streaming Stall Reap 2 0 3489516369 0
201 Streaming No-Stall Reap 2 0 2378677367 0
210 redo writes (group 0) 2 164 2952991530 0
211 redo writes (group 1) 2 16 1083730459 0
212 redo writes (group 2) 2 0 2759403975 0
213 redo writes (group 3) 2 0 3475566097 0
214 redo writes (group 4) 2 0 1807859197 0
215 redo writes (group 5) 2 0 1792560815 0
216 redo writes (group 6) 2 0 1695728381 0
217 redo writes (group 7) 2 0 1074957749 0
218 redo writes adaptive all 2 180 3061077218 0
219 redo writes adaptive worker 2 180 3220418890 0
221 redo blocks written (group 0) 2 995 2520028696 0
222 redo blocks written (group 1) 2 301 3244346714 0
223 redo blocks written (group 2) 2 0 1273391004 0
224 redo blocks written (group 3) 2 0 1050845280 0
225 redo blocks written (group 4) 2 0 2795831152 0
226 redo blocks written (group 5) 2 0 615604096 0
227 redo blocks written (group 6) 2 0 764128333 0
228 redo blocks written (group 7) 2 0 435637049 0
229 redo write size count (   4KB) 2 145 4206847440 0
230 redo write size count (   8KB) 2 15 3604386338 0
231 redo write size count (  16KB) 2 11 1937637258 0
232 redo write size count (  32KB) 2 7 2689404784 0
233 redo write size count (  64KB) 2 0 3887142398 0
234 redo write size count ( 128KB) 2 2 2998280397 0
235 redo write size count ( 256KB) 2 0 2120393820 0
236 redo write size count ( 512KB) 2 0 3912524051 0
237 redo write size count (1024KB) 2 0 395882065 0
238 redo write size count (inf) 2 0 4145578355 0
251 redo synch time overhead (usec) 128 3142053 3961087021 0
252 redo synch time overhead count (  2ms) 128 35 1771370497 0
253 redo synch time overhead count (  8ms) 128 0 2324186582 0
254 redo synch time overhead count ( 32ms) 128 0 2882285036 0
255 redo synch time overhead count (128ms) 128 0 1234629759 0
256 redo synch time overhead count (inf) 128 3 2239006192 0
261 redo write info find 2 38 3584739253 0
262 redo write info find fail 2 0 553778103 0
267 gc cr blocks served with BPS 40 0 1600220233 0
275 gc current blocks served with BPS 40 0 1004484383 0
278 gc cr blocks received with BPS 40 0 3270643842 0
281 gc current blocks received with BPS 40 0 301773697 0
282 gc ka grants received 40 0 912334553 0
283 gc ka grant receive time 40 0 3746639269 0
289 gc cleanout saved 40 0 4119317321 0
290 gc cleanout applied 40 0 1976898865 0
291 gc cleanout no space 40 0 522936568 0
293 gc reader bypass waits 40 0 1120557156 0
298 gc force cr disk read 40 395 1058102273 0
307 AVM files created count 128 0 1887082337 0
308 AVM files deleted count 128 0 4223523824 0
309 AVM file bytes allocated 128 0 3731650962 0
310 AVM au bytes allocated 128 0 3441520794 0
311 AVM file bytes deleted 128 0 1514042146 0
312 AVM non-flash bytes requested 128 0 1829484955 0
313 AVM flash bytes requested 128 0 965137504 0
314 AVM bytes for file maps 128 0 2904743103 0
315 AVM bytes read from flash 128 0 4263147678 0
316 AVM bytes read from disk 128 0 2004986892 0
317 AVM count when 10% of buckets in pb 128 0 652947275 0
318 AVM count when 25% of buckets in pb 128 0 3588709547 0
319 AVM count when 50% of buckets in pb 128 0 2879014823 0
320 AVM count when 75% of buckets in pb 128 0 1964315023 0
321 AVM count when 90% of buckets in pb 128 0 226051874 0
322 AVM count – borrowed from other node 128 0 4037843577 0
323 AVM count – searched in pb 128 0 4000147916 0
324 AVM spare statistic 1 128 0 47653185 0
325 AVM spare statistic 2 128 0 3191674657 0
326 AVM spare statistic 3 128 0 2665872976 0
327 AVM spare statistic 4 128 0 2816010972 0
328 AVM spare statistic 5 128 0 4250363583 0
329 AVM spare statistic 6 128 0 3756487597 0
330 AVM spare statistic 7 128 0 2604881032 0
331 AVM spare statistic 8 128 0 176682480 0
345 storage index soft misses in bytes 8 0 2809906174 0
353 cell num smart IO sessions in rdbms block IO due to open fail 64 0 1611570469 0
363 cell num smartio automem buffer allocation attempts 64 0 145506540 0
364 cell num smartio automem buffer allocation failures 64 0 727055891 0
365 cell num smartio transient cell failures 64 0 2276204331 0
366 cell num smartio permanent cell failures 64 0 299072157 0
367 cell num bytes of IO reissued due to relocation 64 0 3754903472 0
388 recovery marker 2 0 2982845773 0
389 cvmap unavailable 2 0 3849353583 0
390 recieve buffer unavailable 2 0 3480097050 0
462 tracked transactions 128 0 4230695614 0
463 foreground propagated tracked transactions 128 0 2081753160 0
464 slave propagated tracked transactions 128 0 275867045 0
465 large tracked transactions 128 0 1755433832 0
466 very large tracked transactions 128 0 4033000846 0
467 fbda woken up 128 0 138331311 0
468 tracked rows 128 0 943642878 0
469 CLI Flush 128 73 670819718 0
470 CLI BG attempt Flush 128 73 2751550570 0
471 CLI Client Flush 128 0 2418073855 0
472 CLI Imm Wrt 128 0 47996927 0
473 CLI Buf Wrt 128 0 1466815534 0
474 CLI Thru Wrt 128 2 2721289668 0
475 CLI Prvtz Lob 128 0 1688196485 0
476 CLI SGA Alloc 128 32 2076026298 0
477 CLI BG ENQ 128 73 2537508108 0
478 CLI BG Fls done 128 2 1898500432 0
479 CLI Flstask create 128 73 4150293767 0
480 CLI bytes fls to table 128 1376 872375576 0
481 CLI bytes fls to ext 128 0 2251457522 0
482 Heatmap SegLevel – Write 128 0 2305866014 0
483 Heatmap SegLevel – Full Table Scan 128 0 3635715785 0
484 Heatmap SegLevel – IndexLookup 128 0 4088384827 0
485 Heatmap SegLevel – TableLookup 128 0 26595750 0
486 Heatmap SegLevel – Flush 128 0 3466367062 0
487 Heatmap SegLevel – Segments flushed 128 0 2885452372 0
504 KTFB alloc req 128 0 3506976771 0
505 KTFB alloc space (block) 128 0 254882839 0
506 KTFB alloc time (ms) 128 0 573758863 0
507 KTFB free req 128 25 1286187813 0
508 KTFB free space (block) 128 1528 1243401580 0
509 KTFB free time (ms) 128 266 408510199 0
510 KTFB apply req 128 16 2829590811 0
511 KTFB apply time (ms) 128 902 1827629900 0
512 KTFB commit req 128 9 2268695636 0
513 KTFB commit time (ms) 128 16659 3807444826 0
514 KTFB alloc myinst 128 0 637674164 0
515 KTFB alloc steal 128 0 3819194715 0
516 KTFB alloc search FFB 128 0 1572111054 0
522 Heatmap BlkLevel Tracked 128 0 417269865 0
523 Heatmap BlkLevel Not Tracked – Memory 128 0 3244920981 0
524 Heatmap BlkLevel Not Updated – Repeat 128 0 1235344528 0
525 Heatmap BlkLevel Flushed 128 0 3201601810 0
526 Heatmap BlkLevel Flushed to SYSAUX 128 0 153666168 0
527 Heatmap BlkLevel Flushed to BF 128 0 329477246 0
528 Heatmap BlkLevel Ranges Flushed 128 0 3869669302 0
529 Heatmap BlkLevel Ranges Skipped 128 0 120128078 0
530 Heatmap BlkLevel Flush Task Create 128 0 1236100146 0
531 Heatmap Blklevel Flush Task Count 128 0 1887039906 0
568 index compression (ADVANCED LOW) prefix change at block 128 0 1089998764 0
569 index compression (ADVANCED LOW) prefix no change at block 128 0 2879842113 0
570 index compression (ADVANCED LOW) blocks not compressed 128 0 3703793538 0
571 index compression (ADVANCED LOW) reorg avoid split 128 0 2501129012 0
573 index compression (ADVANCED HIGH) leaf block splits avoided 128 0 228768206 0
575 index compression (ADVANCED HIGH) leaf block 90_10 splits faile 128 0 3445701516 0
612 HSC OLTP Compression wide compressed row pieces 128 0 784760009 0
669 EHCC Used on ZFS Tablespace 128 0 2536989047 0
670 EHCC Used on Pillar Tablespace 128 0 3901974308 0
671 EHCC Conventional DMLs 128 0 547882683 0
672 EHCC Block Compressions 128 0 2852097326 0
673 EHCC Attempted Block Compressions 128 0 726324667 0
674 SecureFiles DBFS Link Operations 128 0 408804124 0
675 SecureFiles Move to DBFS Link 128 0 2159528439 0
676 SecureFiles Copy from DBFS Link 128 0 3313150606 0
677 SecureFiles Get DBFS Link Reference 128 0 3776855272 0
678 SecureFiles Put DBFS Link Reference 128 0 1020980477 0
679 SecureFiles Implicit Copy from DBFS Link 128 0 2864160252 0
680 SecureFiles DBFS Link streaming reads 128 0 2291010287 0
681 SecureFiles DBFS Link Overwrites 128 0 3546571658 0
682 index cmph ld, CU under-est 128 0 3487869306 0
683 index cmph ld, CU fit, add rows 128 0 3074245919 0
684 index cmph ld, CU fit 128 0 312995821 0
685 index cmph ld, CU over-est 128 0 3287792462 0
686 index cmph ld, retry in over-est 128 0 2794871331 0
687 index cmph ld, CU negative comp 128 0 747638515 0
688 index cmph ld, lf blks flushed 128 0 3933169485 0
689 index cmph ld, lf blks w/o CU 128 0 2058955770 0
690 index cmph ld, lf blks w/o unc r 128 0 1877031790 0
691 index cmph ld, lf blks w/ und CU 128 0 500852118 0
692 index cmph ld, rows compressed 128 0 2461980696 0
693 index cmph ld, rows uncompressed 128 0 1487477542 0
694 index cmph gencu, uncomp sentinals 128 0 3972713215 0
707 Number of NONE redactions 1 0 2910416594 0
708 Number of FULL redactions 1 0 4021003316 0
709 Number of PARTIAL redactions 1 0 2340397149 0
710 Number of FORMAT_PRESERVING redactions 1 0 2739332778 0
711 Number of RANDOM redactions 1 0 2308447938 0
712 Number of REGEXP redactions 1 0 3081010860 0
795 OLAP Paging Manager Cache Hit 64 0 249788237 0
796 OLAP Paging Manager Cache Miss 64 0 2631123639 0
797 OLAP Paging Manager New Page 64 0 1639856938 0
798 OLAP Paging Manager Cache Write 64 0 2077400790 0
799 OLAP Session Cache Hit 64 0 3766195924 0
800 OLAP Session Cache Miss 64 0 1569481295 0
801 OLAP Aggregate Function Calc 64 0 3109348342 0
802 OLAP Aggregate Function Precompute 64 0 352609299 0
803 OLAP Aggregate Function Logical NA 64 0 2269374713 0
804 OLAP Paging Manager Pool Size 64 0 3621573995 0
805 OLAP Import Rows Pushed 64 0 3846608240 0
806 OLAP Import Rows Loaded 64 0 2782483173 0
807 OLAP Row Source Rows Processed 64 0 1032576542 0
808 OLAP Engine Calls 64 0 4076583183 0
809 OLAP Temp Segments 64 0 3547622716 0
810 OLAP Temp Segment Read 64 0 1927042645 0
811 OLAP Perm LOB Read 64 0 2809117898 0
812 OLAP Paging Manager Cache Changed Page 64 0 2200669834 0
813 OLAP Fast Limit 64 0 283242358 0
814 OLAP GID Limit 64 0 1120107350 0
815 OLAP Unique Key Attribute Limit 64 0 3812252850 0
816 OLAP INHIER Limit 64 0 2844959843 0
817 OLAP Full Limit 64 0 2189109011 0
818 OLAP Custom Member Limit 64 0 3030144806 0
819 OLAP Row Id Limit 64 0 3437716459 0
820 OLAP Limit Time 64 0 2592657924 0
821 OLAP Row Load Time 64 0 953132701 0

【12c新特性】12c中新后台进程

【12c新特性】12c中新后台进程,主要包括但不局限于:

 

OFSD Oracle File Server BG
RMON rolling migration monitor
IPC0 IPC Service 0
BW36 db writer process 36
BW99 db writer process 99
TMON Transport Monitor
RTTD Redo Transport Test Driver
TPZ1 Test Process Z1
TPZ2 Test Process Z2
TPZ3 Test Process Z3
LREG Listener Registration
AQPC AQ Process Coord
FENC IOServer fence monitor
VUBG Volume Driver Umbilical Background
SCRB ASM Scrubbing Master

 

 

可以看到这里LREG进程开始负责对Listener Registration监听器的注册:

Service registration enables the listener to determine whether a database service and its service handlers are available. A service handler is a dedicated server process or dispatcher that acts as a connection point to a database. During registration, the LREG process provides the listener with the instance name, database service names, and the type and addresses of service handlers. This information enables the listener to start a service handler when a client request arrives.

Figure 16-5 shows two databases, each on a separate host. The database environment is serviced by two listeners, each on a separate host. The LREG process running in each database instance communicates with both listeners to register the database.

 

截止目前12c的官方文档中的配图还有问题, 图示还是用PMON注册监听。

12c pmon LREG

 

 

Reference:

E16655_01/E16655_01/server.121/e17633/dist_pro.htm#CHDIBHAD