Oracle Database 12c新特性汇总页面

Maclean Liu关于DB 12c新特性的研究文章如下:

 

 

【Oracle Database 12c新特性】 In-Database Archiving数据库内归档

【Oracle Database 12c新特性】SYS_AUTO_SPM_EVOLVE_TASK 自动作业

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

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

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

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

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

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

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

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

【Oracle Database 12c新特性】ORACLE_MAINTAINED

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

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

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

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

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

【12c新特性】12c中新加入的Enqueue Lock

【12c新特性】12c中新增的V$动态视图

【12c新特性】多LGWR进程SCALABLE LGWR “_use_single_log_writer”

【12c新特性】12cR1 diff 11gR2 Hidden Parameters

【12c新特性】CBO Optimizer优化器新特性列表

【12c新特性】12c中如何自动启动PDB Pluggable Database

Oracle Database 12c架构图

【Maclean Liu技术分享】12c 12.1.0.1 RAC安装教学视频 基于Vbox+Oracle Linux 5.7

oracle database 12c release 1 (12.1.0.1.0) 正式公开发布了

12c Pluggable Database Container Database权限与角色管理

【12c新特性】安装12c Standalone Grid Infrastructure

【12c新特性】EM Database Express

将在版本11.2之后废弃或不再支持的特性

解读Tom介绍的Oracle Database 12c的12个新特性

解读Oracle Database 12.1新特性Pluggable Databases

12c分页查询特性FETCH FIRST ROWS,OFFSET ROWS FETCH NEXT ROW LIMIT Clause子句

【12c新特性】dbms_stats report_gather_auto_stats统计信息报告特性

12c新特性:Recover Table

Oracle Database 12c(12.1) Beta已经开始内部测试

【Database 12c】手动创建CDB Container Database容器数据库

【Oracle Database 12c新特性】 12c DataPump Expdp/Impdp新特性

 

 

12c new feature

 

 

 

DB 12c的课程列表:

•Phase 1 (estimated completion February 2012):
1.Oracle Database 12c: Container Database
•Phase 2 (estimated completion April 2012):
2.Oracle Database 12c: Data Guard New Features
3.Oracle Database 12c: Oracle Availability Machine [was OHARA; may move this to phase 3 or 4]
4.Oracle Database 12c: Recovery Manager New Features
5.Oracle Database 12c: Resource Manager and Scheduler New Features
•Phase 3 (estimated completion May 2012):
6.Oracle Database 12c: Data Pump and SQL*Loader New Features
7.Oracle Database 12c: Information Lifecycle Management New Features [may move this to phase 2]
8.Oracle Database 12c: Performance Enhancements
•Phase 4 (estimated completion June 2012)
9.Oracle Database 12c: Application Continuity [not much functionality exposed to customer?]
10.Oracle Database 12c: Automatic Storage Management
11.Oracle Database 12c: Clusterware and Grid Infrastructure New Features
12.Oracle Database 12c: Installation and Upgrade New Features
13.Oracle Database 12c: Networking New Features
14.Oracle Database 12c: Online Operations New Features
15.Oracle Database 12c: Cloud Computing New Features [was Private Database Cloud]
16.Oracle Database 12c: Real Application Clusters New Features
17.Oracle Database 12c: Security New Features
•Phase 1 (estimated completion February 2012):
•None
•Phase 2 (estimated completion April 2012):
1.Oracle Database 12c: Application Migration New Features
2.Oracle Database 12c: BI and Data Warehousing New Features
3.Oracle Database 12c: Application Development New Features
4.Oracle Database 12c: Miscellaneous Database Enhancements
5.Oracle Database 12c: PL/SQL New Features
•Phase 3 (estimated completion May 2012):
6.Oracle Database 12c: OCI / OCCI New Features [may not create this due to limited interest]
7.Oracle Database 12c: Oracle Data Mining New Features
8.Oracle Database 12c: Oracle Spatial New Features
9.Oracle Database 12c: Real Application Security
10.Oracle Database 12c: SQL Tuning New Features
•Phase 4 (estimated completion June 2012)
11.Oracle Database 12c: Application Express New Features [APEX 4.2]
12.Oracle Database 12c: Java and JDBC New Features
13.Oracle Database 12c: ODP .Net New Features
14.Oracle Database 12c: Oracle XML DB New Features
15.Oracle Database 12c: Oracle Text New Features
16.Oracle Database 12c: Real Application Testing New Features
dba 12capp 12c

【Oracle Database 12c新特性】 In-Database Archiving数据库内归档

Oracle Database 12c中引入了 In-Database Archiving的新特性, 该特性允许用户通过对表上的数据行标记为inactive不活跃的,以归档数据。 这些inactive的数据行可以通过压缩进一部优化,且对应用来说默认不可见。    可以通过一个session级别的参数来控制数据的可见与否。

 

通过In-Database Archiving数据库内归档特性可以在单库中存放更长周期的数据, 而损耗很少的应用性能。 归档数据还可以通过压缩来进一步提升备份效能。

 

要管理一张表的In-Database Archiving,必须为表启用ROW ARCHIVAL  并操作ORA_ARCHIVE_STATE 这个隐藏字段 。 此外用户还可以指定session级别的 ROW ARCHIVAL VISIBILITY为ACTIVE  或者ALL。

 

下面为演示示例:

 

/* Set visibility to ACTIVE to display only active rows of a table.*/
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

CREATE TABLE employees_indbarch 
 (employee_id NUMBER(6) NOT NULL, 
  first_name VARCHAR2(20), last_name VARCHAR2(25) NOT NULL, 
  email VARCHAR2(25) NOT NULL, phone_number VARCHAR2(20), 
  hire_date DATE NOT NULL, job_id VARCHAR2(10) NOT NULL, salary  NUMBER(8,2),
  commission_pct NUMBER(2,2), manager_id NUMBER(6), department_id NUMBER(4)) ROW ARCHIVAL;

INSERT INTO employees_indbarch(employee_id, first_name, last_name, email,
  hire_date, job_id, salary, manager_id, department_id) 
  VALUES (251, 'Scott', 'Tiger', 'scott.tiger@example.com', '21-MAY-2009',
 'IT_PROG', 50000, 103, 60);

INSERT INTO employees_indbarch(employee_id, first_name, last_name, email,
  hire_date, job_id, salary, manager_id, department_id) 
  VALUES (252, 'Jane', 'Lion', 'jane.lion@example.com', '11-JUN-2009', 
  'IT_PROG', 50000, 103, 60);

 commit;

 /* Show all the columns in the table, including hidden columns */
SELECT SUBSTR(COLUMN_NAME,1,22), SUBSTR(DATA_TYPE,1,20), COLUMN_ID AS COL_ID,
  SEGMENT_COLUMN_ID AS SEG_COL_ID, INTERNAL_COLUMN_ID AS INT_COL_ID, HIDDEN_COLUMN, CHAR_LENGTH
  FROM USER_TAB_COLS WHERE TABLE_NAME='EMPLOYEES_INDBARCH';

SELECT SUBSTR(COLUMN_NAME,1,22) NAME, SUBSTR(DATA_TYPE,1,20) DATA_TYPE, COLUMN_ID AS COL_ID,
  SEGMENT_COLUMN_ID AS SEG_COL_ID, INTERNAL_COLUMN_ID AS INT_COL_ID, HIDDEN_COLUMN, CHAR_LENGTH 
  FROM USER_TAB_COLS WHERE TABLE_NAME='EMPLOYEES_INDBARCH';

NAME                   DATA_TYPE                COL_ID SEG_COL_ID INT_COL_ID HID CHAR_LENGTH
---------------------- -------------------- ---------- ---------- ---------- --- -----------
ORA_ARCHIVE_STATE      VARCHAR2                                 1          1 YES        4000
EMPLOYEE_ID            NUMBER                        1          2          2 NO            0
FIRST_NAME             VARCHAR2                      2          3          3 NO           20
LAST_NAME              VARCHAR2                      3          4          4 NO           25
EMAIL                  VARCHAR2                      4          5          5 NO           25
PHONE_NUMBER           VARCHAR2                      5          6          6 NO           20
HIRE_DATE              DATE                          6          7          7 NO            0
JOB_ID                 VARCHAR2                      7          8          8 NO           10
SALARY                 NUMBER                        8          9          9 NO            0
COMMISSION_PCT         NUMBER                        9         10         10 NO            0
MANAGER_ID             NUMBER                       10         11         11 NO            0
DEPARTMENT_ID          NUMBER                       11         12         12 NO            0

/* Decrease the ORA_ARCHIVE_STATE column size to improve formatting in queries */
COLUMN ORA_ARCHIVE_STATE FORMAT a18;
/* The default value for ORA_ARCHIVE_STATE is '0', which means active */
SELECT employee_id, ORA_ARCHIVE_STATE FROM employees_indbarch;

EMPLOYEE_ID ORA_ARCHIVE_STATE
----------- ------------------
        251 0
        252 0

/* Insert a value into ORA_ARCHIVE_STATE to set inactive */
UPDATE employees_indbarch SET ORA_ARCHIVE_STATE = '20' WHERE employee_id = 252;

/* Only active records are in the following query */
SELECT employee_id, ORA_ARCHIVE_STATE FROM employees_indbarch;

EMPLOYEE_ID ORA_ARCHIVE_STATE
----------- ------------------
        251 0

/* Set visibility to ALL to display all records */
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

SELECT employee_id, ORA_ARCHIVE_STATE FROM employees_indbarch;

EMPLOYEE_ID ORA_ARCHIVE_STATE
----------- ------------------
        251 0
        252 20

SQL> desc employees_indbarch
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

  1* select COLUMN_NAME,COLUMN_ID,DATA_TYPE from dba_tab_cols where table_name=upper('employees_indbarch')
SQL> /

COLUMN_NAME          COLUMN_ID DATA_TYPE
-------------------- --------- --------------------
DEPARTMENT_ID               11 NUMBER
MANAGER_ID                  10 NUMBER
COMMISSION_PCT               9 NUMBER
SALARY                       8 NUMBER
JOB_ID                       7 VARCHAR2
HIRE_DATE                    6 DATE
PHONE_NUMBER                 5 VARCHAR2
EMAIL                        4 VARCHAR2
LAST_NAME                    3 VARCHAR2
FIRST_NAME                   2 VARCHAR2
EMPLOYEE_ID                  1 NUMBER
ORA_ARCHIVE_STATE              VARCHAR2

SQL> select dump(ORA_ARCHIVE_STATE,16),dump(EMPLOYEE_ID,16),dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid)  from employees_indbarch;

DUMP(ORA_ARCHIVE_STA DUMP(EMPLOYEE_ID,16) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
-------------------- -------------------- ------------------------------------ ------------------------------------
Typ=1 Len=1: 30      Typ=2 Len=3: c2,3,34                               109465                                    1
Typ=1 Len=2: 32,30   Typ=2 Len=3: c2,3,35                               109465                                    1

	SQL> alter system flush buffer_cache;

System altered.

SQL> alter system dump datafile 1 block 109465;

System altered.

tl: 73 fb: --H-FL-- lb: 0x0  cc: 12
col  0: [ 1]  30
col  1: [ 3]  c2 03 34
col  2: [ 5]  53 63 6f 74 74
col  3: [ 5]  54 69 67 65 72
col  4: [23]
 73 63 6f 74 74 2e 74 69 67 65 72 40 65 78 61 6d 70 6c 65 2e 63 6f 6d
col  5: *NULL*
col  6: [ 7]  78 6d 05 15 01 01 01
col  7: [ 7]  49 54 5f 50 52 4f 47  
col  8: [ 2]  c3 06
col  9: *NULL*
col 10: [ 3]  c2 02 04
col 11: [ 2]  c1 3d
tab 0, row 1, @0x1ecc
tl: 70 fb: --H-FL-- lb: 0x2  cc: 12
col  0: [ 2]  32 30
col  1: [ 3]  c2 03 35
col  2: [ 4]  4a 61 6e 65
col  3: [ 4]  4c 69 6f 6e
col  4: [21]
 6a 61 6e 65 2e 6c 69 6f 6e 40 65 78 61 6d 70 6c 65 2e 63 6f 6d
col  5: *NULL*
col  6: [ 7]  78 6d 06 0b 01 01 01
col  7: [ 7]  49 54 5f 50 52 4f 47
col  8: [ 2]  c3 06
col  9: *NULL*
col 10: [ 3]  c2 02 04  
col 11: [ 2]  c1 3d

 

 

 

可以看到这里 ORA_ARCHIVE_STATE是实际存放在块里的row piece上的第一个字段,类型为varchar2(4000);
In-Database Archiving的限制:

  • ILM is not supported with OLTP table compression for in-database archiving. Segment-level ILM and compression is supported if partitioned on the ORA_ARCHIVE_STATE column.

 

 

ORA_ARCHIVE_STATE相关:

/* constants specifying ROW ARCHIVAL state */
archive_state_active constant varchar2(1) := ‘0’;
archive_state_archived constant varchar2(1) := ‘1’;

/*
* description – Given a value for the ORA_ARCHIVE_STATE column this
* function returns the mapping for the value.
*
* value – “0”, “1” or other values from the ORA_ARCHIVE_STATE column of
* a row archival enabled table
* returns either “archive_state_active” or “archive_state_archived”
*/

【Oracle Database 12c新特性】SYS_AUTO_SPM_EVOLVE_TASK 自动作业

Oracle Database 12c中引入了一个新的自动系统作业,即SYS_AUTO_SPM_EVOLVE_TASK。 该作业将在每天的自动维护作业窗口中自动执行。 SYS_AUTO_SPM_EVOLVE_TASK负责检索和排序SPM中未被接受的执行计划non-accepted plan以便verification。 当此执行计划经过verified,过该计划满足性能阀值则将被自动接受accepted 。因此,当优化器将一个non-accepted的执行计划加入到SQL statement plan history中,在很多情况下若该计划确实是更好的,则会在第二天被接受并可以使用。

 

注意该自动task存在时间上的限制为一个小时(默认TIME_LIMIT=3600s),因此可能造成部分计划未被verified。 在此种场景下,下一个维护窗口该task执行时将处理剩余的执行计划。

 

 

SYS_AUTO_SPM_EVOLVE_TASK

  1  SELECT parameter_name, parameter_value
  2  FROM   dba_advisor_parameters
  3* WHERE  task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
SQL> /

PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ----------------------------------------
DAYS_TO_EXPIRE                 UNLIMITED
END_SNAPSHOT                   UNUSED
END_TIME                       UNUSED
INSTANCE                       UNUSED
JOURNALING                     INFORMATION
MODE                           COMPREHENSIVE
START_SNAPSHOT                 UNUSED
START_TIME                     UNUSED
TARGET_OBJECTS                 1
TIME_LIMIT                     3600
DEFAULT_EXECUTION_TYPE         SPM EVOLVE
CON_DBID_MAPPING               UNUSED
ORA_EM_PARAM1                  UNUSED
ORA_EM_PARAM2                  UNUSED
ORA_EM_PARAM3                  UNUSED
ORA_EM_PARAM4                  UNUSED
ORA_EM_PARAM5                  UNUSED
ORA_EM_PARAM6                  UNUSED
ORA_EM_PARAM7                  UNUSED
ORA_EM_PARAM8                  UNUSED
ORA_EM_PARAM9                  UNUSED
ORA_EM_PARAM10                 UNUSED
EXECUTION_DAYS_TO_EXPIRE       30
SQLSET_NAME                    UNUSED
SQLSET_OWNER                   UNUSED
ACCEPT_PLANS                   TRUE
_SPM_VERIFY                    TRUE
APPLY_CAPTURED_COMPILENV       UNUSED
LOCAL_TIME_LIMIT               UNUSED

已选择 29 行。

 select execution_name,status,execution_start,execution_end from dba_advisor_executions where task_name='SYS_AUTO_SPM_EVOLVE_TASK';


  --     time_limit  (IN) - Time limit in number of minutes.  The time limit
  --                        is global and it is used in the following manner.
  --                        The time limit for first non-accepted plan is equal
  --                        to the input value. The time limit for the second
  --                        non-accepted plan is equal to (input value - time
  --                        spent in first plan verification) and so on. The
  --                        default DBMS_SPM.AUTO_LIMIT means let the system
  --                        choose an appropriate time limit based on the
  --                        number of plan verifications required to be done.
  --                        The value DBMS_SPM.NO_LIMIT means no time limit.
  
  
  
DECLARE
   job                   BINARY_INTEGER := :job;
   next_date             TIMESTAMP WITH TIME ZONE := :mydate;
   broken                BOOLEAN := FALSE;
   job_name              VARCHAR2 (30) := :job_name;
   job_subname           VARCHAR2 (30) := :job_subname;
   job_owner             VARCHAR2 (30) := :job_owner;
   job_start             TIMESTAMP WITH TIME ZONE := :job_start;
   job_scheduled_start   TIMESTAMP WITH TIME ZONE := :job_scheduled_start;
   window_start          TIMESTAMP WITH TIME ZONE := :window_start;
   window_end            TIMESTAMP WITH TIME ZONE := :window_end;
   chain_id              VARCHAR2 (14) := :chainid;
   credential_owner      VARCHAR2 (30) := :credown;
   credential_name       VARCHAR2 (30) := :crednam;
   destination_owner     VARCHAR2 (30) := :destown;
   destination_name      VARCHAR2 (30) := :destnam;
   job_dest_id           VARCHAR2 (14) := :jdestid;
   log_id                NUMBER := :log_id;
BEGIN
   DECLARE
      ename   VARCHAR2 (30);
   BEGIN
      ename := DBMS_SQLTUNE.execute_tuning_task ('SYS_AUTO_SQL_TUNING_TASK');
      ename := DBMS_SPM.execute_evolve_task ('SYS_AUTO_SPM_EVOLVE_TASK');
   END;

   :mydate := next_date;

   IF broken
   THEN
      :b := 1;
   ELSE
      :b := 0;
   END IF;
END;




/* Formatted on 2013/8/4 10:48:19 (QP5 v5.163.1008.3004) */
  SELECT pl.signature,
         pl.category,
         pl.name,
         pl.plan_id,
         DECODE (BITAND (pl.flags, :1), 0, :2, :3) flags,
         pl.sql_handle,
         pl.sql_text,
         pl.comp_data,
         pl.optimizer_env,
         pl.bind_data,
         pl.parsing_schema_name,
         pl.creator,
         (CASE                                  /* plan is already accepted */
             WHEN (BITAND (pl.flags, :4) <> 0)
             THEN
                :5                       /* plan has recently been verified */
             WHEN (pl.is_auto IS NOT NULL
                   AND pl.last_verified >
                          SYSTIMESTAMP
                          - :6)
             THEN
                :7    /* plan's SQL statement hasn't been recently executed */
             WHEN (pl.is_auto
                      IS NOT NULL
                   AND pl.last_verified
                          IS NOT NULL
                   AND pl.sql_last_executed <
                          SYSTIMESTAMP
                          - :8)
             THEN
                :9
             ELSE
                :10
          END)
            pruned
    FROM (SELECT so.signature,
                 so.category,
                 so.name,
                 so.plan_id,
                 so.flags,
                 st.sql_handle,
                 st.sql_text,
                 (DECODE (
                     BITAND (so.flags, 128),
                     128, (SELECT EXTRACT (XMLTYPE (pl.other_xml),
                                           '/*/outline_data').getClobVal ()
                             FROM sys.sqlobj$plan pl
                            WHERE     pl.signature = so.signature
                                  AND pl.category = so.category
                                  AND pl.obj_type = so.obj_type
                                  AND pl.plan_id = so.plan_id
                                  AND pl.other_xml IS NOT NULL),
                     (SELECT sod.comp_data
                        FROM sys.sqlobj$data sod
                       WHERE     sod.signature = so.signature
                             AND sod.category = so.category
                             AND sod.obj_type = so.obj_type
                             AND sod.plan_id = so.plan_id)))
                    comp_data,
                 sox.optimizer_env,
                 sox.bind_data,
                 sox.parsing_schema_name,
                 sox.creator,
                 sox.last_verified,
                 sox.optimizer_cost,
                 sox.created,
                 :11 is_auto,
                 (SELECT MAX (last_executed)
                    FROM sys.sqlobj$ ob
                   WHERE     ob.signature = so.signature
                         AND ob.obj_type = so.obj_type
                         AND ob.category = so.category)
                    sql_last_executed
            FROM sys.sqlobj$ so, sys.sqlobj$auxdata sox, sys.sql$text st
           WHERE (:12 IS NULL
                  OR so.name IN (SELECT EXTRACTVALUE (VALUE (p), '/plan') pname
                                   FROM TABLE (
                                           XMLSEQUENCE (
                                              EXTRACT (XMLTYPE (:13),
                                                       '/plan_list/*'))) p))
                 AND (:14 IS NOT NULL
                      OR (BITAND (so.flags, :15) <> 0
                          AND BITAND (so.flags, :16) = 0))
                 AND so.obj_type = :17
                 AND so.signature = sox.signature
                 AND so.category = sox.category
                 AND so.obj_type = sox.obj_type
                 AND so.plan_id = sox.plan_id
                 AND so.signature = st.signature) pl
ORDER BY DECODE (:18, 0, NULL, pl.name),
         pl.last_verified NULLS FIRST,
         pl.sql_last_executed DESC NULLS LAST,
         pl.optimizer_cost,
         pl.created,
         pl.name

【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