Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » Oracle 12c+

一句话描述12.1新特性(一)

12.1从推出到现在有2年了,也推出了很多新特性,下面通过简短的一句话来概括这些特性。


A.DMU取代了CSSCAN and CSALTER

在12c之前dmu默认是没有安装的,12c已经把她囊括到自己麾下

[oracle@db1 121]$ cd /u01/oracle/product/121/dmu/
[oracle@db1 dmu]$ ls -l
total 132
drwxr-xr-x 9 oracle oinstall  4096 Mar  9 23:38 dmu
-rwxr-xr-x 1 oracle oinstall 32768 Dec 23  2012 dmu32.exe
-rwxr-xr-x 1 oracle oinstall 18432 Dec 23  2012 dmu64.exe
-rwxr-xr-x 1 oracle oinstall    53 Jul 19  2012 dmu.sh
-rwxr-xr-x 1 oracle oinstall 32768 Dec 23  2012 dmuW32.exe
-rwxr-xr-x 1 oracle oinstall 18432 Dec 23  2012 dmuW64.exe
drwxr-xr-x 7 oracle oinstall  4096 Mar  9 23:38 ide
drwxr-xr-x 2 oracle oinstall  4096 Mar  9 23:39 jlib
drwxr-xr-x 8 oracle oinstall  4096 Mar  9 23:39 modules
drwxr-xr-x 3 oracle oinstall  4096 Mar  9 23:38 sleepycat
drwxr-xr-x 2 oracle oinstall  4096 Mar  9 23:39 timingframework
[oracle@db1 dmu]$
B.函数的定义支持With
SQL> with
function get_domain(url varchar2) return varchar2 is
 2    3     pos binary_integer;
 4     len binary_integer;
 5   begin
 6     pos := instr(url, 'www.');
 7     len := instr(substr(url, pos + 4), '.') - 1;
 8     return substr(url, pos + 4, len);
 9   end;
10  select get_domain('www.ohsdba.cn') from dual
11  /

GET_DOMAIN('WWW.OHSDBA.CN')
--------------------------------------------------------------------------------
ohsdba

SQL>
C.IDENTITY Columns 自动增长列,比sequence更方便
[oracle@db1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 15 11:43:34 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
SQL> create table t1 (c1 number generated by default on null as identity, c2 varchar2(10));

Table created.

SQL>
SQL> desc t1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
C1                                        NOT NULL NUMBER
C2                                                 VARCHAR2(10)

SQL> insert into t1(c2) values('robin');

1 row created.

SQL> c/robin/han
 1* insert into t1(c2) values('han')
SQL> /

1 row created.

SQL> c/han/ohsdba
 1* insert into t1(c2) values('ohsdba')
SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

       C1 C2
---------- ----------
        1 robin
        2 han
        3 ohsdba

SQL>

D.分区表的增强

Global Index Maintenance for DROP and TRUNCATE Partition
当Drop或Truncate分区时,全局索引的维护可以避开高峰期延迟删除

Cascade Functionality for TRUNCATE and EXCHANGE Partition
当Truncate或Exchange分区时,对Reference-partitioned表,在父表上的的操作,字表会继承,简化了DBA的操作

ONLINE Move Partition
在线移动分区在操作期间,DML的操作正常进行,不用担心被中断,全局索引会自动维护,无须手动重建

Interval Reference Partitioning
一种新的满足业务需求而增加的新分区类型,维护更方便

Partial Indexes for Partitioned Tables
本地索引和全局索引可以在表的部分分区上创建
http://docs.oracle.com/database/121/VLDBG/GUID-256BA7EE-BF49-42DE-9B38-CD2480A73129.htm#VLDBG14102


E.Performance方面

Adaptive Query Optimization

优化器可能会因为误算而生成一个不好的plan,Adaptive query optimization通过下面两个方面可以帮助纠正这些错误
   1.如果有adaptive plans,当前正在执行的计划会终止,采用好的执行计划
   2.收集并监视在执行期间的信息,如果真实的最初的信息差别比较大,在下次执行时会重新评估看是否会采用新的执行计划

Adaptive SQL Plan Management
有了这个,哪些没有被evolve的plan会在nightly maintenance window期间自动校验,如果性能比现在的好,系统会自动接受

Automatic Column Group Detection
这个是multiple columns Extended statistics的改进,通过捕获负载信息分析,给用户提供建议,当你不知道该如何组合时,这个很不错,主要依赖的包是DBMS_STATS.SEED_COL_USAGE and REPORT_COL_USAGE

http://docs.oracle.com/database/121/TGSQL/tgsql_astat.htm#GUID-E1F39134-24F1-4EF7-B614-82F9428CA762


Concurrent Execution of UNION and UNION ALL Branches
之前的union和union all的执行是要一个执行完之后,再执行下一个。现在可以并行执行

Concurrent Statistics Gathering
并行收集统计信息在multiple tables in a schema (or database) and multiple partitions (or subpartitions) 上时,当参数CONCURRENT设置为true时,会根据系统资源信息自动在后台创建scheduler job并行去收集统计信息
SET LINES 200 PAGES 0
SET LONG 100000
COLUMN REPORT FORMAT A200
SQL> SET LINES 200 PAGES 0
SQL> SET LONG 100000
SQL> COLUMN REPORT FORMAT A200
SQL>
SQL> VARIABLE my_report CLOB;
SQL> BEGIN
 2    :my_report :=DBMS_STATS.REPORT_GATHER_SCHEMA_STATS(
 3      ownname      => 'SYS'       ,
 4      detail_level => 'TYPICAL'  ,
 5      format       => 'HTML'     );
 6  END;
 7  /

PL/SQL procedure successfully completed.

SQL>


Database Smart Flash Cache Support for Multiple Flash Devices

数据库Smart Flash Cache支持更多的闪存设备,也支持ASM,相关的初始化参数为
DB_FLASH_CACHE_FILE = /dev/sda, /dev/sdb, /dev/sdc
DB_FLASH_CACHE_SIZE = 32G, 32G, 64G

Dynamic Statistics
动态统计信息的使用,这个有了很大改进,会根据实际去判断动态信息是否有用,自动采样的级别。现在包含JOIN and GROUP BY clauses

Enhanced Parallel Statement Queuing
Critical statement可以跳过并行语句的queue。Critical statements are enabled to bypass the parallel statement queue to reflect business criticality and to provide more flexibility. Parallel statement queuing provides more comprehensive monitoring information, including historical information.

Enhancements to Incremental Statistics
分区表信息的收集包含table level和partition level,Incremental Statistics允许Oracle只收集partition level的信息,进而更准确的计算global-level statistics。在之前的版本中,如果分区上有DML操作,那么其上面incremental statistics被算作为stale,现在通过设置incremental staleness threshold可避免即使有DML操作。 

Enhancements to System Statistics
随着智能存储的广泛使用,比如Exadata Storage,优化器需要更多的系统信息来评估存储的能力。DBMS_STATS.GATHER_SYSTEM_STATS多了EXADATA
SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('EXADATA');
PL/SQL procedure successfully completed.
EXADATA
The system statistics consider the unique capabilities provided by using Exadata, such as large I/O size and high I/O throughput. The optimizer sets the multiblock read count and I/O throughput statistics along with CPU speed.

New Types of Optimizer Statistics
Oracle引入了2种针对某些列不重复值超过254的histogram,可以更好的来评估表的基数。她们是TOP-FREQUENCY和HYBRID(Frequency和Height-Balanced的组合)
Oracle creates histograms on columns that have a data skew to improve cardinality estimates. Two additional types of histograms have been introduced for columns which have more than 254 distinct values to improve the cardinality estimates generated using histograms.
SQL> select distinct histogram from dba_tab_col_statistics;
HISTOGRAM
---------------
FREQUENCY
HYBRID
TOP-FREQUENCY
NONE
SQL>


Online Statistics Gathering for Bulk Loads

这个是指类似CREATE TABLE AS SELECT,INSERT INTO ... SELECT的操作时,Oracle会自动收集统计信息

Out-of-Place Materialized View Refresh
她和in-place是相对的,都是MV Refresh的方式,这中方式会更好。她会创建一个或多个表来完成refresh,来保证在refresh时MV的高可用性,尤其在refresh要花费很长时间才能完成的时候。The out-of-place refresh creates one or more outside tables and executes the refresh statements on the outside tables and then switches the materialized view or affected materialized view partitions with the outside tables。The out-of-place refresh enables high materialized view availability during refresh, especially when refresh statements take a long time to finish.

Session-Private Statistics for Global Temporary Tables
传统上来讲,在所有的会话中,全局临时表只有一套统计信息。在12.1有了改进,每个会话可以有自己的统计信息。只有可以避免因统计信息的错误而造成错误的执行计划

SQL Plan Directives
除了DBMS_STATS收集的统计信息外,优化器还能收集在compilation(可以理解为在执行之前的阶段)和execution(执行阶段)阶段的统计信息。这些信息存在disk上,存在表空间SYSAUX上,之前这些信息存在在cursor cache中,并没有永久保存下来。她可以更好的提升执行计划的准确性。
Besides the statistics gathered using the PL/SQL DBMS_STATS package, the optimizer can also gather statistics during compilation using dynamic sampling and during execution time using adaptive execution plans.

Synchronous Materialized View Refresh
就是说表和MV是一起更新的,这个主要用在数据仓库方面,在OLTP上就很糟糕了
Materialized views can be refreshed simultaneously with its base tables by leveraging partitioning and the logical dependencies between tables and the corresponding materialized views.



关键词:12c 

相关文章

How to generate AWR on PDB and ADG(12.2 afterwards)
在12c上使用wm_concat
Exadata with database 12.2
如何在oracle 12c中正确的应用补丁?
在OEL6.8上安装12.2 RAC
Oracle Database 12.2 Hands-On Lab
How to create single physical standby for RAC
12c 安装、配置和使用GDS/GSM
12c 如何通过DG Broker配置far_sync
Upgrade 11gR2 RAC database to 12.1.0.2 with DBUA
Upgrade 11gR2 RAC Grid Infrastructure to 12.1.0.2
12c RAC和RAC One Node之间相互转换
Top