Oracle 19c和20c新特性解密

1、Oracle 19c 和 20c新特性解密PART 01Oracle 18c 新特性PART 02Oracle 19c 新特性PART 03Oracle 20c 新特性C O N T E N T S01O r a c l e1 8 c新特性高效性能：极致性能来自持续革新高效性能：右向增长索引分裂竞争优化右向增长索引经常在索引分裂时引起严重竞争尤其是以序列单调递增的方式优化了叶块分裂算法缩减集群消息传递使用更佳的索引特性18c的Scalable Sequences模拟 Scalable 方式创建散列索引0102030405060Executions / sec (thousands)081624

2、3240485664# Clients / Instance (4 RAC instances)11.2.0.4 Baseline12.2 with all optimizations1 2In3de4x 5Blo6ck7 81 2In3de4x Block5 6In7de8x B9lo1c0k115 6In7de8x Block9 101121.1.0.2 Baseline高效性能：可扩展序列 Scalable Sequence序列新的参数选项SCALE Extend |NoExtend | NoScale 实例号和会话号参与到到序列算法中需要修改现有索引DBA/User/ALL_seque

3、nces 视图增加额 外信息显示SCALE属性指定SCALE时，在传统的序列前增加了6位 数字前3位是由RAC里的实例号产生随后3位由Session的SID产生目的是分散序列、避免索引竞争Scalable SequencesSQL create sequence seq_scale start with 1increment by 1 scale;SQL select seq_scale.nextval from dual; NEXTVAL- 1.0140E+27SQL select seq_scale.nextval from dual;NEXTVAL- 101395000000000000

4、0000000001SQL select instance_number from v$instance; 1SQL select sid from v$mystat where rownum=1; 395SQL select sid from v$mystat where rownum=1;264SQL select seq_scale.nextvalfrom dual;NEXTVAL- 1012640000000000000000000005外部表定义在运行时提供与inline视图类似对于单次使用的外部表不需要提前创建Before:Now:Inline 外部表CREATE TABLE sa

5、les_xt (prod_id number, ) TYPE ORACLE_LOADERLOCATION new_sales_kw13) REJECT LIMIT UNLIMITED );INSERT INTO sales SELECT * FROMsales_xt;DROP TABLE sales_xt;INSERT INTO sales SELECT sales_xt.* FROM EXTERNAL(prod_id number, )TYPE ORACLE_LOADERLOCATION new_sales_kw13)REJECT LIMIT UNLIMITED );In-memory Ex

6、ternal TablesRDBMSIn-memory Database TablesExternal DataDB TABLESIn-Memory支持外部表Object StorageFilesHadoop外部表允许透明访问数据库外部的 数据外部表的In-Memory功能为数据库外 部的数据建立in-memory列缓存以便 实现外部数据的超快速分析支持所有In-Memory优化功能向量处理, JSON表达式透明地扩展到 外部数据百倍速度提升计划内switchoverServer1CDB1CDB2Server21. alter pluggable database Grey refresh m

7、ode every 2 minutes from Greydblink switchover;可刷新PDB的Switchover计划外的switchoverServer1CDB1CDB2Server2alter pluggable database Greyrefresh;alter pluggable database Grey refresh mode none;alter pluggable database Greyopen read write;可刷新PDB的SwitchoverDBMS_PRIVILEGE_CAPTUREOracle 12.1引入18c中属于企业版基本功能，不需要

8、额外licenseExample: Capture processBEGIN DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE( name = COLLECT_USED_DEMOAPPS_PRIVS , description = Find used privileges , type = DBMS_PRIVILEGE_CAPTURE.G_CONTEXT , condition = SYS_CONTEXT (USERENV,CURRENT_SCHEMA) = DEMOAPPS );END;/BEGIN DBMS_PRIVILEGE_CAPTURE.ENABLE_CAP

9、TURE (COLLECT_USED_DEMOAPPS_PRIVS);END;BEGIN DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE(COLLECT_USED_DEMOAPPS_PRIVS);END;BEGIN DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT(COLLECT_USED_DEMOAPPS_PRIVS); END;Then check: DBA_USED_PRIVS and DBA_UNUSED_PRIVS权限捕获02O r a c l e1 9 c新特性自动化索引创建和实施 Automatic IndexingMon

10、itorOnlineValidationCaptureIdentifyVerifyDecide自动索引是借鉴于人工工作的专家系统自动化索引是基于手动SQL调优的常用方法它标识候选索引并在实现之前验证它们的有效性整个过程是全自动的透明度与复杂的自动化同样重要-所有的调整活动都可通过报告进行审核自治索引 任务组件15根据列使用情况确定索 引选择列（SQL工作负 载）在不可用和不可见模 式下创建索引编译在当前循环/迭代 中硬解析的语句重建优化器选择的索引（状态为不可见）如果语句的性能得到改 善，则将索引标记为可 见每15分钟重复一次整个工作负载的验证 仍在继续，索引状态 更改为完成或已撤消自治索引的

11、实现过程通过DBA_AUTO_INDEX_CONFIG 查看系统配置CDB$ROOTSYSselect banner_full from v$version;BANNER_FULL-Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.2.0.0.0CDB$ROOTSYSselect * from DBA_AUTO_INDEX_CONFIG; PARAMETER_NAMEPARAMETER_VALUELAST_MODIFIEDMODIFIED_BY- - - - AUTO_INDEX_

12、DEFAULT_TABLESPACEOFF31373AUTO_INDEX_MODE AUTO_INDEX_REPORT_RETENTION AUTO_INDEX_RETENTION_FOR_AUTO AUTO_INDEX_RETENTION_FOR_MANUAL AUTO_INDEX_SCHEMA AUTO_INDEX_SPACE_BUDGET507 rows selected.CDB$ROOTSYSalter session set container=pdb1; Session altered.CDB$ROOTSYSEXEC DBMS_AUTO_INDEX.CONFIGURE(AUTO_I

13、NDEX_MODE,IMPLEMENT);PL/SQL procedure successfully completed.CDB$ROOTSYSCDB$ROOTSYSselect * from DBA_AUTO_INDEX_CONFIG;PARAMETER_NAMEPARAMETER_VALUELAST_MODIFIEDMODIFIED_BY- - - - AUTO_INDEX_DEFAULT_TABLESPACEIMPLEMENT 17-FEB-19 10.03.59.000000 PM SYS 31373AUTO_INDEX_MODE AUTO_INDEX_REPORT_RETENTION

14、 AUTO_INDEX_RETENTION_FOR_AUTO AUTO_INDEX_RETENTION_FOR_MANUAL AUTO_INDEX_SCHEMA AUTO_INDEX_SPACE_BUDGET50PDB1create table test as select * from dba_objects; Table created.PDB1insert into test select * from test;72397 rows created.PDB1insert into test select * from test; 144794 rows created.PDB1upda

15、te test set object_id=rownum;2316704 rows updated.PDB1commit; Commit complete.PDB1select object_name from test where object_id=1; PDB1select object_type from test where object_id=123; PDB1select created from test where object_id=345构建测试数据，执行测试查询，驱动数据库的自我记录分析自治索引的实现过程STATUSselect * from DBA_AUTO_INDE

16、X_EXECUTIONS;EXECUTION_NAMEEXECUTION_STARTEXECUTION_ENDERROR_MESSAGE- - - - SYS_AI_2019-02-17/22:51:002019-02-17 22:51:00 2019-02-17 22:53:07COMPLETEDselect * from DBA_AUTO_INDEX_STATISTICS whereEXECUTION_NAME=SYS_AI_2019-02-17/22:51:00;EXECUTION_NAMESTAT_NAMEVALUE- - -SYS_AI_2019-02-17/22:51:00 SYS

17、_AI_2019-02-17/22:51:00 SYS_AI_2019-02-17/22:51:00 SYS_AI_2019-02-17/22:51:00 SYS_AI_2019-02-17/22:51:00 SYS_AI_2019-02-17/22:51:00 SYS_AI_2019-02-17/22:51:00 SYS_AI_2019-02-17/22:51:00 SYS_AI_2019-02-17/22:51:00 SYS_AI_2019-02-17/22:51:00 SYS_AI_2019-02-17/22:51:00Index candidatesIndexes created (v

18、isible) Indexes created (invisible) Indexes droppedSpace used in bytes Space reclaimed in bytes SQL statements verified SQL statements improvedSQL statements managed by SPMSQL plan baselines createdImprovement percentage11004508876802200100检查数据库自动任务执行自治索引的实现过程PDB1select index_name,command,statement

19、from DBA_AUTO_INDEX_IND_ACTIONSwhere execution_name=SYS_AI_2019-02-17/22:51:00 order by action_id;INDEX_NAMECOMMANDSTATEMENT- - -SYS_AI_18sc6rdkngxkh CREATE INDEXCREATE INDEX ”EN.SYS_AI_18sc6rdkngxkh ON ”MO.TEST(OBJECT_ID) SYS_AI_18sc6rdkngxkh REBUILD INDEXALTER INDEX ”EN.SYS_AI_18sc6rdkngxkhREBUILD

20、ONLINE SYS_AI_18sc6rdkngxkh ALTER INDEX VISIBLE ALTER INDEX ”EN.SYS_AI_18sc6rdkngxkh VISIBLEPDB1select index_name,index_type,tablespace_name,status,auto from dba_indexeswhere table_name=TEST;INDEX_NAMEINDEX_TYPETABLESPACE_NAMESTATUSAUT- - - - -SYS_AI_18sc6rdkngxkh NORMALUSERSVALIDYES以 AI 开头的自动索引已经被创

21、建出来自治索引的实现过程PDB1select object_name from test where object_id=1234; OBJECT_NAME- I_OLAP_TAB$|Execution Plan-Plan hash value: 159453698-| Id| Operation| Name| Rows| Bytes | Cost (%CPU)| Time-|0|SELECT STATEMENT|1|41|4(0)|00:00:01|1|TABLE ACCESS BY INDEX ROWID BATCHED|TEST|1|41|4(0)|00:00:01|*2|INDEX R

22、ANGE SCAN|SYS_AI_18sc6rdkngxkh|1|3(0)|00:00:01|-Predicate Information (identified by operation id):-2 - access(OBJECT_ID=1234)SQL 查询将使用新的索引加速执行自治索引的实现过程自治索引技术对于应用的调节21ATP 实现了与专业手动调整相同的性能 随着工作量的变化，ATP会保持调整Elapsed Time (sec)ExpertAutonomous 11721155Indexes UsedExpertAutonomous1733 4663ATP对Netsuite工作负载

23、起到的作用运行一个复杂的Netsuite负载，并将ATP与现有的专家调优进行比较17,542条SQL语句, 1,852张表, 8,151个索引 - 需要通过多年的优化来创建这些索引- 在运行ATP之前，所有的索引和统计信息会被删除案例: Insert Temperature Sensor ReadingsIn-Memory AppendInsert:DRAINER PROCESSES PERIODICALLYDRAIN BUFFER TO DISKIn-MemoryIngest BufferTimeTemp05:5052o05:5554o06:0054o06:0555oIoT TEMPERAT

24、URE SENSORTEMPERTURE READINGS物联网数据流的In-Memory加速新的in-memory插入算法声明表MEMOPTIMIZE FOR WRITE客户端使用标准SQL低延迟插入到 in-memory内存高吞吐量批量插入在后台异步完成极速性能在2 Socket服务器上每秒插入2500W记录在线操作维护的增强在18c和19c中在线功能进一步增强，减少联机DDL对正在运行的系统的影响多分区维护操作提升了并发性18c 和 19c 持续增强了智能、细粒度的游标失效功能，避免一篮子全 失效判定细粒度的无效化（DML和SELECT游标的处理方式不同）滚动无效（仍然可以使用现有的非最

25、佳的游标）CREATE/DROP/ALTER INDEX, TRUNCATE TABLE, Partition Maintenance Operations, DDLs on unusable and invisible indexes等超过资源管理器限制的执行计划将自动停止并隔离被隔离的执行计划将无法再次执行视图V$SQL中的新列QUARANTINED 表示一个执行计划不会再被使用AVOIDED_EXECUTION 被隔离后尝试执行的次数通过DBMS_SQLQ和 DBMS_RESOUCE_MANAGER包控制视图查询DBA_SQL_QUARANTINEQuarantineDBRM resou

26、rcelimit exceeded保护系统免受Runaway语句影响ADG 备库发出的DML支持自动重定向19c之前只能更新全局临时表19c开始，可以在备库更新常规表-Updates on Active Data Guard更新被重定向到主库主库执行更新操作，产生日志并发送到所有备库Active Data Guard上会话看到日志应用后的结果并反馈执行成功确保ADG会话的ACID属性重定向修改提交之前仅对当前会话可见提交后对所有会话可见适用于只读为主少量更新的应用通过参数 ADG_REDIRECT_DML 在系统级或者会 话级启用Update XActive Data GuardPrimary

27、在 ADG 上发出的 DML 重定向到主库执行，备库等待主库日志传递到备库并应用PRIMARYACTIVE STANDBYDML 12 DML IS REDIRECTED TO PRIMARY2 DML IS APPLIED TO PRIMARY3 DATA CHANGE IS STREAMED TO STANDBYDATA IS VI5SIBLE TO CLIENTADG 备库发出的DML支持自动重定向支持一个CDB中多个PDB的sharding允许整合需要单独数据库但共享同一系统的应用程序支持多表家族支持多个表家族，其中来自不同家族的数据驻留在同 一块中For system-managed

28、 sharding method现在可以将访问不同表家族的不同应用托管在一个分 片数据库中跨shards传播参数设置从分片目录集中管理和传播“ ALTER SYSTEM”参数设 置到所有分片Sharding增强Q2_2018 Q3_2018 Q4_2018Q1_2018Q3_2017 Q4_2017Q2_2017OBJECT STORAGESQLHOT DATACOLD DATAORDERSQ4_2016 Q1_2017UPDATESTABLE PARTITIONED BY QUARTER混合分区表进一步扩展了Oracle分区技术，混合指的是数据的 分布，一些分区可以位于数据库中，另一些可以是

29、位于数据库 外部的文件（比如操作系统文件或HDFS文件）。19c中将内部 分区（internal partitions）与外部分区（external partitions）特性结合起来，形成一个更通用的分区，称为混 合分区表。混合分区表Q2_2018 Q3_2018 Q4_2018Q1_2018Q3_2017 Q4_2017Q2_2017OBJECT STORAGESQLHOT DATACOLD DATAORDERSQ4_2016 Q1_2017UPDATESTABLE PARTITIONED BY QUARTERCREATE TABLE orders ( order_id number, o

30、rder_date DATE, ) EXTERNAL PARTITION ATTRIBUTES ( TYPE oracle_loader DEFAULT DIRECTORY data_dir ACCESS PARAMETERS (.) REJECT LIMIT unlimited)PARTITION BY RANGE(order_date)( partition q1_2015 values less than (2015-04-01) EXTERNAL LOCATION (order_q1_2015.csv),partition q2_2015 values less than (2015-

31、07-01), partition q3_2015 values less than (2015-10-01), partition q4_2015 values less than (2016-01-01);混合分区表PDB1CREATE DIRECTORY sales_data as /u01/app/oracle/oradata/extfiles; PDB1CREATE DIRECTORY sales_data_2016 as /u01/app/oracle/oradata/extfiles/2016; PDB1CREATE DIRECTORY sales_data_2017 as /u

32、01/app/oracle/oradata/extfiles/2017;PDB1CREATE TABLE hybrid_test2 (DATE NOT NULL, NUMBER(10,2)region varchar2(6) NOT NULL,time_idamount6 )7 EXTERNAL PARTITION ATTRIBUTES-必须加这个子句声明TYPE ORACLE_LOADERDEFAULT DIRECTORY sales_dataACCESS PARAMETERS( FIELDS TERMINATED BY ,(region,time_id DATE yyyy-mm-dd,am

33、ount)REJECT LIMIT UNLIMITED8(9101112131415)PARTITION BY RANGE (time_id)( PARTITION sales_2015 VALUES LESS THAN (TO_DATE(2016-01-01,yyyy-mm-dd) EXTERNAL, -空的外部分区PARTITION sales_2016 VALUES LESS THAN (TO_DATE(2017-01-01,yyyy-mm-dd)EXTERNAL DEFAULT DIRECTORY sales_data_2016 LOCATION (sales_2016.txt),PA

34、RTITION sales_2017 VALUES LESS THAN (TO_DATE(2018-01-01,yyyy-mm-dd)EXTERNAL DEFAULT DIRECTORY sales_data_2017 LOCATION (sales_2017.txt),PARTITION sales_2018 VALUES LESS THAN (TO_DATE(2019-01-01,yyyy-mm-dd) EXTERNAL LOCATION (sales_2018.txt),PARTITION sales_2019 VALUES LESS THAN (TO_DATE(2020-01-01,y

35、yyy-mm-dd)-内部分区 24 );混合分区表创建PDB1select /*+ gather_plan_statistics */ * from hybrid_test where time_id=to_date(20160102,yyyymmdd); REGION TIME_ID AMOUNT- - -EAST 2016-01-02 00:00:00 3000 SOUTH 2016-01-02 00:00:00 2120 WEST 2016-01-02 00:00:00 2140 NORTH 2016-01-02 00:00:00 1300PDB1select * from table

36、(dbms_xplan.display_cursor(format=IOSTATS PARTITION LAST) ; PLAN_TABLE_OUTPUT-SQL_ID 71z2djd7chmxh, child number 0-select /*+ gather_plan_statistics */ * from hybrid_test where time_id=to_date(20160102,yyyymmdd)Plan hash value: 2383463387-| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A

37、-Rows | A-Time | Buffers |-| 0 | SELECT STATEMENT | | 1 | | | | 4 |00:00:00.34 | 3213 | 1 | PARTITION RANGE SINGLE | | 1 | 82 | 2 | 2 | 4 |00:00:00.34 | 3213 |* 2 | EXTERNAL TABLE ACCESS FULL| HYBRID_TEST | 1 | 82 | 2 | 2 | 4 |00:00:00.34 | 3213 |-Predicate Information (identified by operation id):-

38、2 - filter(SYS_OP_XTNN(HYBRID_TEST.TIME_ID,HYBRID_TEST.REGION) AND TIME_ID=TO_DATE(2016-01-02 00:00:00, syyyy-mm-dd hh24:mi:ss)混合分区表查询收集统计信息作为常规DML的一部分（INSERT/UPDATE/MERGE）DML期间收集的统计数据需要快速且开销可忽略不计仅收集最基本的统计信息，以避免灾难性的SQL执行计划性能下降（例如，避 免超出范围的条件）Min, Max, num_rows, etc.延迟收集剩余的统计信息自动统计信息收集作业仍根据需要启动实时统计信息A

39、NY_VALUE()LISTAGG(DISTINCT)Bitmap based COUNT DISTINCTSQL 功能的增强增加新的关键字DISTINCT扩展LISTAGG聚合以进行重复消除对查询中每个组的行 进行排序，然后将值 连接为单个字符串DISTINCT在连接成单 个字符串之前消除重 复的值利用新的硬件级别功能使用按位运算可避免 排序明显的查询性能提升使用类似近似查询聚 集的概念提供选取随机值的能力消除了必须使用min/max 聚集函数的必 要性超越 min/max 显著的性能提升对后续的输入值不需要 进行比较对于包含GROUP BY子 句的查询的最佳选择03O r a c l e2

40、 0 c新特性Oracle 20c 新特性：原生的区块链表支持- Native Blockchain Tables多模：Oracle 数据库秉持多模化发展的理念，引入原生的区块链 表支持；原生：通过 CREATE Blockchain Table 创建；简化：Blockchain 表只能INSERT插入记录行被加密链接链是参与者可验证的融合：Blockchain 表可以和其他表关联进行事务或查询比分布式区块链相比容易得多BlockchainCREATE Blockchain TableTrade_Ledger;Oracle 20c 新特性：持久化内存存储支持- Persistent Memor

41、y storeOracle 一直在探索多级存储对于数据库性能的提升；在 20c 中，持久化内存存储被支持，提供微秒级I/O响应；新的 100Gb Ethernet + RoCE (RDMA over Converged Ethernet)2.5x faster networkingRoCE + Persistent Memory (PMEM) 提供极速性能存储服务器在闪存之上提供PMEM加速能力数据库访问PMEN使用RDMA而不是IO绕过网络和IO软件堆栈，将延迟降低10倍客户可以选择基于RoCE的Exadata或出于兼容性考虑继续使用基于InfiniBand的ExadataCompute S

42、erverStorage ServerHotWarmRDMAColdRoCEPersistentMemoryFLASH19 usec IO latencyOracle 20c 新特性：SQL 宏支持SQL Macro允许开发人员通过宏定义缩略复杂的处理逻辑支持 SCALAR 和 TABLE 两种SQL宏类型:SCALAR expressions used in SELECT list, WHERE/HAVING, GROUP BY/ORDER BY clausesTABLE expressions used in a FROM-clausecreate function clip(lo num

43、ber, x number, hi number) return varchar2 SQL_MACRO(SCALAR)is beginreturn least(greatest(x, lo), hi);end;/SELECT ename, CLIP (:lower, sal, :upper)FROM emp;两种新的分布聚类- “distribution” aggregates:偏度 - SKEWNESS峰度 - KURTOSIS提供有关给定分布形状的见解支持实例化视图，遵循与VARIANCE相同的语义新的位运算操作支持 - bitwise aggregatorsBIT_AND_AGGBIT_

44、OR_AGGBIT_XOR_AGG对 ANSI 2011 标准的部分支持；通过 GROUPS 语句扩展窗口边界；GROUPS通过计算窗口排序组来工作可以具有任意数量的任何排序类型的排序键GROUPS窗口不会在排序键中具有相同值的相邻行之间截 断窗口链接-根据另一个窗口定义一个窗口新特性 - NEW FeaturesOracle 20c 新特性：SQL 新特性和函数扩展- Extensions and new functionsCHECKSUM 函数维护数据完整性提供防止数据篡改的简单方法；更简单的关键字替换 DBMS_SQLHASH.GETHASH 函数；DBA不必再特别向希望进行此计算的用户

45、授予对DBMS_SQLHASH的权限；CHECKSUM FunctionExtensions to WINDOW clauseOracle 20c 新特性：广泛的ML算法和AutoML支持Frequently requested algorithms极限梯度提升树 - eXtreme Gradient Boosting Trees (XGBoost)分类，回归，排名，生存分析 - Classification, regression, ranking, survival analysis高度流行且功能强大的算法MSET-SPRT传感器，物联网数据源的异常检测 - Anomaly detecti

46、on for sensors, IoT data sources“多元状态估计技术”- “Multivariate State Estimation Technique”非线性，非参数异常检测ML技术 - A non-linear, non-parametric anomaly detectionML technique基于Oracle 实验室算法Oracle ML for SQL (OML4SQL)Python (OML4Py),R (OML4R)以前：通过服务驱动负载放置；服务隐式的打开PDB实例；PDB打开顺序依赖服务定义使用首选、可用性属性等定义；升级后的默认模型Services trigger PDB openOracle 20c 新特性：多租户细粒度的资源模型- New Resource Modeling schemeService