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…]

ORA-01578: 对一个新的数据文件

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638    QQ号:47079569    邮箱:service@parnassusdata.com

问题描述:
====================

你刚刚创建了一个新的数据文件和一个表空间,当你尝试访问一个新的表空间时,会收到下列错误信息:

ORA-01578: ORACLE 数据块损坏 (file # %s, block # %s)
原因: 表明数据块损坏,大都由于软件错误。
措施: 尝试恢复含有指定块的段,这可能涉及删除该段并重建,如果存在一个跟踪文件,向你的 ORACLE代表报告其中的错误。
注释: 当创建指定的 REUSE 语句时。

问题解释:
====================

指定 REUSE语句时,用户引起损坏,因为数据文件正被其他数据库使用。

搜索词:
=============

ora-1578, corruption

DBVERIFY返回带有 16 或32K 块大小的数据文件的损坏的块

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638    QQ号:47079569    邮箱:service@parnassusdata.com

 

DBVERIFY返回带有 16 或32K 块大小的数据文件的损坏的块 (文档 ID 1038857.6) 转到底部

________________________________________

问题描述
===================

使用 dverify,指定一个块大小大于8k
会发生一件事:

dbv-600 [7] 后是一个核心转储。

或者

它返回很多损坏块:

Page 8 is marked software corrupt
Page 16 is marked software corrupt
Page 20 is marked software corrupt
Page 21 is marked software corrupt
Page 22 is marked software corrupt
Page 23 is marked media corrupt
搜索词:
=============

dbv, block, blocksize, size, 16, 32 db_block_size

方法描述
====================

Dbverify不与16k 和32k大小的块工作。

在 V7.3.x 中打开了BUG 441525,在7.3.4中修复

使用SALVAGE 脚本/ 程序从损坏的Oracle表提取数据

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638    QQ号:47079569    邮箱:service@parnassusdata.com

 

引言
~~~~~~~~~~~~
从含有损坏块的表拯救数据可能是耗时且容易出错的,如果从表中提取好的块的常规方法都失败了,那么这个脚本 / 程序的集合在其他方法失败的地方会有帮助。

实用程序
~~~~~~~~~~~~~
有三个实用程序可能会帮助从损坏的表中恢复所有未损坏的块,这里描述的方法应该作为最后的方法使用因为它们要求用手握着,并且比其它方法更耗时,参考 Note:28814.1 ,获取更多拯救方法的细节。

o 如果存在唯一索引,那么通过使用WHERE条件可能从损坏块的周围选择,如果存在很多损坏的块,那么使用Pro*C or PL/SQL 程序拯救损坏的表也是有用的。

这些程序背后的主要观点是通过在一个循环中生成单个 ROWIDs来选择所有的行而不是损坏的那些,同一个程序有三个版本,两个是Pro*C,一个是 PL/SQL 程序,因为不可能在PL/SQL里捕捉到ORA-1578或ORA-600 ,要求用户进入到 PL/SQL版本的损坏块号,这个表将会作为例外表示用,该行中的ROWID’s将会跳过。
免责声明: 这些实用程序不是 Oracle产品,所以不受支持,使用该程序, Oracle Support不对出现的问题负责。

连接到使用程序
~~~~~~~~~~~~~~~~~~~~~~
SALVAGE8i.PC for Oracle8.1 – Oracle9.2 Note:97357.1
SALVAGE.PC for Oracle7 Note:2077307.6
SALVAGE.SQL for Oracle7/8 Note:2064553.4

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569