风云再起oracle database12新特性和最佳实践

1、Oracle Database 12c盖国强，云和恩墨Who am I!盖国强云和恩墨创始!国内第个Oracle ACE及ACE总监；致于技术与oo o技术已经ITPUB的主要倡导者之;了12本技术书籍；和张乐奕共创 Oracle用户组 - ACOUG,开展持续的公益活动；!云和恩墨 数据资产端到端解决案服务商!汇聚 Oracle ACE 总监（6位），Oracle ACE（2位），SQL赛冠军，以及数位OCM，同时具备MySQL、DB2、中间件；!为包括电信、融、保险、能源等业300多家客户提供服务和解决案；恩墨学院致于为企业培养数据才，开设OCM、MySQL、Hadoop、PostgreS

2、QL全位数据课程；ORACLE Database in ChinaCHINA RDBMS CompetitiveORACLE 5-year (2011-2015) consecutivelyMarketplace, CY2015market leadership in CHINA Sustainably grows with annually +50%market shareOthers,ORACLE DB 5-Year CAGR 11.5% > DBTeradata, 7.5%market AVG 11.1%3.1%56.0%China continuously drives glob

3、al DBSAP, 8.1%business: the 6th ORACLE DB businesscountry (the 5th out of US), 0.4% share increase in 2015TECHNOLOGYMicrosoft,Database9.5%Database on Solaris Database on Linux Database on SAPData WarehousingEmbedded DatabaseIBM,Database Security15.9%DatabaseShareSource: RDBMS, IDC WW Software 2015H2

4、 Tracker Data, Apr 2016 Update343.9%43.6%52.0%World WideEMEAAPAC(excl. JAPAN)Oracle 2015: All About CloudIntegratedEasy MigrationSecurityOracle 2014: All About CloudCurrent Industry Trends : Dev OpsSoftware defined infrastructureContinuous DeliveryCloser collaboration between developers and operatio

5、ns6Current Industry Trends : In Memory$Continued decreasing cost of memoryIncreasing demand for Real Time AnalyticsNon Volatile Memory becomes more widely available7Oracle 12c Designed for the Cloud Agility Rapid provisioning, cloning, movement Elasticity Scale-out CPU cores, storage, memory Cloud-s

6、cale Operations Manage many databases as-oneBy : Andrew Mendelsohn企业数据架构的痛点-合久必分 分久必合 TCO企业数据架构的痛点-烟囱式建设村落现实与 客户在IT 建设中累积了大量的数据系统，分散割裂的部署导致了成本提升、运维复杂； 迫切希望通过数据整合和集中，降低软硬件成本，节省Oracle数据库License；IT运维，降低运维复杂度； 随着硬件能力的逐步提升，使得分散系统的整合归并成为可能；企业数据架构的痛点-烟囱式建设村落现实与 客户在IT 建设中累积了大量的数据系统，分散割裂的部署导致了成本提升、运维复杂； 迫切希望

7、通过数据整合和集中，降低软硬件成本，节省Oracle数据库License； IT运维，降低运维复杂度； 随着硬件能力的逐步提升，使得分散系统的整合归并成为可能；数据整合与集中-用量化的数据指导决策依赖数据库的详细统计数据建模Oracle 12.1.0.2 Stat 1178 Event 1650 进容量规划和预测Oracle 11.2.0.4 Stat679 Event 1367 以量化数据指导整合迁移的资源配置数据整合与集中-用量化的数据指导决策依赖数据库的详细统计数据建模Oracle 12.1.0.2 Stat 1178 Event 1650 进容量规划和预测Oracle 11.2.0.4

8、 Stat679 Event 1367 以量化数据指导整合迁移的资源配置数据整合与集中-从村落集约化管理解决方案 通过全面的数据采样与分析，进行数据整合，容量评估，安全和高可用评估； 结合业务现状、数据库现状、软硬件现状，为用户制定数据整合方案； 指导用户实施数据整合和集中；成果 通过整合模型，进行科学的容量和性能推演，制定了科学的整合方案； 将客户现有的数十套数据库，整合为1、2、3套数据库环境； 降低了70% 80%的总体成本，大大了用户的运维管理；12c 多租户 快速的数据库分分合合GLOEAP LegendGL OE AP12c 多租户 快速的数据库分分合合GL-1GL-2AP-1AP

9、-2PO-1PDBs CDBs PDBs CDBs GLOEAPPO PDBs non-CDBs snapshot CREATE PLUGGABLE DATABASEnewpdb FROM salespdb12c 多租户 快速的数据库分分合合 Agility SaaSCloud Scale OperationsSoftware as a ServiceRelease 252 PDBs per CDB provisioning and cloning Saas Instant SaaS architecture12.1 Cnd I/O Management Unplug/plug databas

10、es No app changes requiredRelease 4096 PDBs per CDB Hot cloning and refreshShared application objects12.2 Memory management Online tenant relocation Online tenant relocation12c 多租户 简洁快速的切换12c 多租户 简洁快速的切换12c 多租户 简洁快速的切换LISTENER CONNECTION FORWARDING Extension of SCAN technologyQUIESCE SOURCE PDB drai

11、n/killINCREMENTAL REDO APPLICATIONHOT CLONE local undo & redo apply12c 多租户 简洁快速的切换 TNS LISTENER REMOTE HOT CLONEPOOEOEGLAPCDB2CDB1INITIAL BULK REDO TRANSFER SINCE HOT CLONE SCNOPTIMIZED FILE TRANSPORT;LOCAL UNDODATAINCREMENTAL REDO APPLYREDOREDOUNDOcreate pluggable database OEfrom OECDB1_dblink

12、reDlAoTcAateLOCALTNS LISTENER12c 多租户 在Oracle内部的最佳实践17Applications consolidated on a single Container Database. 9 migrated and 8 new applications50%Less downtime during patching31.5%Faster backups with 3.3 times data growth0Unplanned Outages since production.DBA team delighted !Exadata 提供强劲的基础设施支撑Exa

13、data X6 Fastest for All WorkloadsFastest OLTP new extreme flash5.6 Million 8K OLTP Read IOPS5.2 Million 8K OLTP Write IOPS250 us IO latency at 2.4 Million IOPS Fastest Data Warehousing301 GB/sec analytic throughput / rackFastest In-Memory DatabaseQueries billions of rows per sec/core8-socket server

14、" 144 cores, up to 6TB DRAMSpeed Allows Small Exadata to Replace Huge Servers plus Huge ArrayszData 弹性分布式解决方案“IE”Virtual SANFC SAN 20+ IOEnmo Light StorageSolutions and Services Integrated to Work Together内存：12c 的内存数据库选件BOTH Normal Buffer CacheNew In-Memory FormatOLTPOLAPIn-Flash SALESSALESinme

15、mory_size =GBRow FormatColumn FormatALTER ALTER ALTERTABLE TABLE TABLEsales sales salesINMEMORY;NO INMEMORY; CELLMEMORY;In-Flash Columnar scansSALES内存加速：缓存与Keep常驻内存 Oracle Buffer Cache LRU KEEP 11gBy-Pass Buffer Cache 10949 - "Disable autotune direct path read for full table scan" “_serial

16、_direct_read” parameter which forces the full scan on the big table to be a direct read内存加速：12c支持全库缓存 Force Full Database Caching Oracle CDBPDB SecureFiles / BasicFiles LOB12c 内存数据库：易用赢得市场SQL in Silicon：SQLPerformanceSecurityHardware for common range comparisons“How many between start-date and end-d

17、a in siliconSoftin SiliconDecompressionEnginesSub-microsecond Cluster MessagesResults placed directly in processor cacheCapacityCommunicationApplication Data IntegritywareIn-Memory Accelerationte?”EnginesSQL:SELECT count(*)WHERE lo_orderdate = d_datekeyAND lo_partkey = 1059538AND d_year_monthnum BET

18、WEEN 201311 AND 201312;SQL in Function：SQL “How many distinct individuals visited our website last week?” COUNT DISTINCT APPROX_COUNT_DISTINCT() 5x to 50x + times fasterAPPROX_PERCENTILE /APPROX_MEDIAN Find the value for a given percentile 6-13X faster with error typically < 1% Accuracy > 97%

19、(with 95% confidence) Queries automatically re-written to use approximate functions approx_for_aggregation = TRUEFunction in SQL：SQLSchema-levelPragma UDF 3.9xWith 3.8xPre-12.1 schema-levelBaselineBy : Thomas Kyte1.0c#tkyte%CDB1> with2 function Is_Number3 (x in varchar2) return varchar2 is4 Plsql

20、_Num_Error exception;5 pragma exception_init(Plsql_Num_Error, -06502);6 begin7 if (To_Number(x) is NOT null) then8 return 'Y'9 else10 return ''11 end if;12 exception13 when Plsql_Num_Error then14 return 'N'15 end Is_Number;16 select rownum, x, is_number(x) is_num from t 17 /S

21、QL in APP：性能问题主要源于SQL 基于前端开发的SQL审核服务 实现优化前置 SQL是一项专业的技能 Oracle SQL Language Reference 2000 由于开发的技能差异、变化频繁，很多SQL隐患在开发环节被埋入系统； SQL审核通过专业的工具和SQL服务，守住上线关卡，实现规范落地；The image cannot be displayed. Your computer may not have enough memory to open the image, or the image may have been corrupted. Restart your

22、computer, and then open the file again. If the red x still appears, you may have t ete the image and then insert it again.The image cannot be displayed. Your computer may not have enough memory to open the image, or the image may have been corrupted. Restart your computer, and then open the file300%

23、SQL执行效率大幅提升应用系统效率大幅提升again. If the red x still appears, you may have t ete the image and then insert it again.系统充分利用以表为，搜集SQL的Access Path。通过对其进行综合性的分析，设计出最佳索引。SQL中子使用的是否合理实现以最少的索引个数满足最多要求的目标。表连接方式及顺序是否合理执行计划是否有效SQL编写是否符合统一标准DevOps in Oracle：SQL审核防患未然新业务上线- 40次大上线，确保近800个新SQL平稳上线， 无一出现性能问题工具 -Z3发现近10

24、0个TOP SQL,主动优化、消除了现网系统中性能瓶颈主机资源-CPU、IO保持平稳，保证了在业务高峰期、账期有充足的硬件资源ORACLE数据库-除日常维护之外数据库零停机重大调整-调整前严密的测试、调整中二线专家保障、调整后实时跟踪# #其在生中，大多Oracle的系出都是SQL 致 的。但是大多 DBA，尤其是偏的DBASQL并不擅，些DBA承据和定性的，而他些可能又能力。原本SQL的量是的，但目前的是，人管不了，人不擅。所以系出的候，就需要人“救火”，而事或事后救火往往是已遭受了失。- Dev & Ops 的隔阂 - 理解应用 理解性能 SQL审核 去完成那20%最有价值的工作 全表扫描的并不单纯Ops DBA的思考 - 空与不空 明心见性 SQL审核 去完成那20%最有