1、背景
自从2009年SUN公司被Oracle公司以来，因为Mysql在2008年被SUN公司收购，导致Mysql实际拥有者是Oracle公司。Mysql虽然是瑞典MySQL AB公司开发，但一直是作为开源数据库领头者存在。所以，MySQL归属于Oracle公司后，更多人坚持使用Oracle老版本，典型的版本是10g和11g。
10g和11g的g表示grid，就是网格计算的意思，2003年前后网格计算是个时髦的技术名词，后来的云计算的发展离不开网格计算的铺垫。10g和11g是Oracle数据库的巅峰之作。真正大规模应用的版本总是滞后新版本很多，记得2006年我在Z公司还在大规模使用8i，到目前为止（2022年），还是有很多大型应用系统使用10g和11g。本文的缘由产生自我新介入某老产品，该产品还在使用10g版本，我需要温习和总结之。我接手后可能会迁移到11g，暂时不打算迁移到12C，除了迁移成本考虑外，还有中美贸易摩擦的考虑，最终迁移目标可能是开源数据库或者国产数据库。
让我们回顾一下11g及之前的Oracle数据库软件主要版本，如下：
- 1997年6月，Oracle第8版发布。Oracle8支持面向对象的开发及新的多媒体应用。
- 1998年9月，Oracle公司正式发布Oracle 8i。i表示Internet。
- 2001年6月，Oracle发布了Oracle 9i。
- 2003年9月，Oracle发布了Oracle 10g。
- 2007年7月11日，Oracle发布了Oracle 11g。
上面只列出了老板本，最新版本是12，最近oracle改变了版本发布策略，每年发布一个小版本，所以21c也就是12.x。
下面是2017年之后Oracle数据库产品路标图：
Oracle在数据库行业的地位，看DB-Engines数据库流行度2022年7月排行榜：
2、10g新功能清单
总体上改进了数据库性能和可伸缩性，并兼容发布时所有主要平台，支持64 位 Windows 和 Linux 的版本，新功能清单如下表：
（1）网格计算支撑
Oracle对网格计算的描述如下：网格计算的基本思想是用户不需关心数据的位置，也不需考虑哪台计算机处理他的请求。算应该是一种日常用品，就像电力网或电话网络一样。换言之，网格计算主要是供应硬件和软件资源。从严格的技术观点来看，供应并不一定获得性能上的提高。然而，从业务的角度而言，供应将给予用户更好的性能。 因为资源可以根据业务优先级或需求供应给适当的应用程序，所以利用相同的资源，用户能够获得更高的性能。
这看起来就是云计算的主要特征之一。
（2）资源占用优化
提供强大的内置工具和自我管理的功能，自动并动态地管理大部分的配置并调整可用资源。
（3）动态参数调整
SGA动态自动管理：
Oracle认为，系统全局区 (SGA) 是存储区域，由所有用户共享，它包含某个特定 Oracle数据库实例的数据和控制信息。SGA 内部划分为几个存储组件，分别代表为满足每类内存分配需求而使用的内存池，例如存储最近使用的数据块，或记录数据库的变化等需求。即使在有咨询机构帮助的情况下，通过调整这些缓存的大小而优化性能也并不简单，总会有风险。因此，只要管理员指定了SGA大小，10g数据库管理系统会自行动态管理SGA池内存分配。
自动调整检查点：
Oracle认为，检查点是将内存中修改的数据与数据库中的数据文件同步的手段。Oracle定期将检查点之间修改过的数据写入数据文件，这种做法的要求之一就是需要足够的可用内存，以提高为将要进行的操作寻找空闲内存的执行性能。管理员在MTTR顾问（一种向导程序，可以预报由不同MTTR 目标值引发的物理写入数量）帮助下通过设置检查点相关初始化参数(FAST_START_MTTR_TARGET) 来指定预期崩溃恢复时间 (MTTR)。因此，10数据库关系系统自动预测MTTR，并利用I/O空闲时间把内存数据写入到数据文件中，从而获取优良的恢复时间。
（4）PL/SQL优化
PL/SQL 是 Oracle 的过程语言，由 SQL 扩充而来，集合了SQL和结构化程序语言的优点。大量使用 PL/SQL 存储过程可以改善性能并优化内存用量（当然也存在过分依赖DBMS的缺点）。PL/SQL 编译器经过重新编写，新的编译器包括代码生成器和全局代码优化器，能够改善大部分PL/SQL程序的性能，尤其是计算密集型 PL/SQL 程序，对于一个单纯的 PL/SQL 程序来说，其性能比 Oracle9i Database Release 2 超出大约 2 倍，PL/SQL 可执行代码的大小缩减到 30%，而动态堆栈大小缩减 2 倍。
10g还能自动识别 PL/SQL 结构的合法类，而且PL/SQL 模块可编译成本地代码，消除了解释程序所消耗的资源，控制程序和异常处理也比9i快得多。
（5）查询优化
查询优化对于关系数据库的性能，特别是对于执行复杂 SQL 语句的性能很重要。10g 使用基于成本的优化策略，先给出多个计划，然后针对消耗的资源多少来选择最佳计划。
SQL语句自动转换
优化程序会自动把原始SQL转换为多条更有效率的SQL语句。
执行计划
执行计划描述了 SQL 处理的所有执行步骤，如访问表的顺序；如何将这些表连接在一起；以及是否通过索引来访问这些表。
并行执行
并行执行体系结构允许任何 SQL 语句以任何等级的并行度执行。
成本模型
成本模型包含关于 Oracle 数据库数据结构的详细内容、对象级和系统统计数据的访问方法，以及性能信息。性能信息包括对象级统计数据、系统统计数据。对象级统计数据收集关于数据库中对象（表、索引以及实体化视图）的信息，例如 B 树索引的级数或表的列中不同值的数目（基数），数据值直方图也可用于得到准确的列数据分布的估计值。系统统计数据描述在典型工作量情况下的活动中收集到的硬件组件 （处理器、内存、存储器和网络）的性能特性。默认的成本模型是“CPU+IO”，这种模型比纯“IO”模型优化效率如下图：
动态执行优化
根据业务优先权、当前数据库工作量、硬件性能对查询过程进行运行时动态调整，以便于获得更佳运行时性能，即使不能够获得理想的 CPU 或内存资源。自动调整查询的并行度，动态为每个查询分配恰当数量的内存，并使用资源管理器根据资源计划中的指示在查询之中分配资源。从而，使查询优化程序获得更为精确的成本和大小模型。
（6）全表扫描优化
10g极大提高了全表扫描的性能，最大可以提升70%，如下图：
索引编排表加入列表分区信息
索引编排表把索引信息和数据存储放在一起，可为包含主关键字的精确匹配和/或范围搜索的查询提供表数据的快速访问。10g的索引编排表支持全表功能，包括分区和并行查询。
（7）概要管理
实体化视图扩展
实体化视图用来汇总、预先计算、复制和分发数据的模式对象，适用于很多计算环境，例如数据仓库、决策支持，以及分布式或移动计算。实体化视图为存储多维和概要计算提供机制，查询优化程序使用实体化视图可以显著提高查询性能。
实体化视图在其主表中的数据更改时必须刷新，而完全刷新将重新执行实体化视图的查询，从主表完全重新计算实体化视图的内容。由于完全刷新可能要求时间非常长，所以很多数据仓库环境要求快速、增量的刷新，以满足其操作目标。
快速刷新使用各种更新实体化视图的增量算法来将主表中新的和更新的数据列入考虑，提供常规的快速刷新机制，在对主表进行常规DML 操作（例如 UPDATE 或直接载入操作）时使用；以及分区敏感快速刷新机制，它遵循基表分区的维护操作或 DML 更改。10g 将快速刷新的支持机制扩展到了更多种实体化视图中，分区敏感快速刷新将扩展到列表分区或使用 ROWID 作为分区标识的基表的实体化视图中。10g 也通过使用功能相关性和查询重写扩展快速刷新，当用户根据维度层次定义实体化视图时，发现与基表中受影响的分区相关的实体化视图的受影响分区，并根据其他实体化视图或基表生成有效的刷新表达式。
查询重写增强
查询重写是一项查询优化技术，能够转换根据表和视图来编写的用户查询，从而从实体化视图中获取数据来提高执行速度。
当查询要求详细记录的概要时，查询优化程序自动识别何时可以并应该使用一个现有实体化视图满足该要求。优化程序直接重写查询，以便使用实体化视图，而不是基础表。这使查询性能显著提高，因为实体化视图在执行和在数据库中存储结果之前在数据库上有预先计算的连接和聚合操作。将查询重写为使用实体化视图可以避免每次使用查询时都进行详细记录的汇总操作。Oracle 具有一系列强有力的实体化视图重写技术，允许每个实体化视图能被用在尽可能多的查询类型中。在 Oracle 数据库 10g 中，查询重写可以使用多个实体化视图。所以，有更多的查询符合查询重写条件，并且改善查询反应时间。
（8）海量数据支持
10g 可以支持极大的数据库，达到 8 exabytes（8 百万兆兆字节）数据。
分区数量增加
分区常用用量的日益增长以及数据仓库大小的不断增长使具有上万个分区的数据库结构的创建成为可能。在 10g 中，显著改善了分区对象的可伸缩性和内存用量，以保证这些数目对这些对象上的操作的性能影响有限。
作为一个例子，下图显示了进行 10g 消耗时间比Oracle9i 缩减了大约 56%。
散列分区全局索引
全局索引通常用在在线事务处理 OLTP 系统在大型表上都有很多索引。Oracle 电子商务套件在其很多大型表中都有十几个甚至更多的索引。全局分区索引更灵活，因为分区的程度和分区键是独立于表的分区方法的。10g 用户可以在表、分区表，以及索引编排表上散列分区索引。这为具有大量并行插入的应用程序改善了吞吐量。
并行单独游标
10g 查询并行执行模型从从属 SQL 模型移到并行独立游标模型。一个独立游标包含所有并行执行需要的信息，并将用在全部并行执行处理中。顺序执行变为并行执行，以及共享内存消耗的减少，并行独立游标的性能大幅度提升。
高速数据移动
10g 为数据的提取、加载以及转换提供了新的功能，以促进建立和刷新大型数据仓库或多个数据中心的效率。对于数据的批量移动，10g 提供了表跨平台传输支持，允许大量数据从一个平台快速分离，然后与另一个平台上的数据库重新连接。10g还引入了新数据泵工具。Oracle数据泵是一个高速、并行的基础架构，实现了数据和元数据从一个数据库到另一个数据库的快速迁移。数据泵导出导入的设计大大加强了原始导出导入的性能。下图比较了原始导出导入工具和新数据泵工具的损耗时间，数据量为1GB，行数9.3M，操作为单流数据移动。
另外，使用 PARALLEL 参数能指定导入导出活动执行的最大线程数，从而获得更好的卸载加载性能。对象选择更加灵活，能基于对象和对象类型进行细粒度选择。新数据泵工具还支持“网络”模式。PL/SQL 程序包甚至可以利用接口写自己的数据移动工具。还加强了导出/导入环境管理，不论作业是由用户自愿停止还是发生了无法预见的情况而造成作业中止，数据泵操作都可以重新启动，用户可以决定给定作业要消耗多少磁盘空间、估计完成所需的时间。管理员可以从多个位置通过分离和重新连接到长时间运行的作业来监控作业，而且可以修改作业的特定属性，例如并行性和转储文件可用性。
（9）其他新特性
- 多台oracle服务器虚拟成一台，内部支持负载均衡
- 10g安装速度比9i少了一半
- 新增基于浏览器的企业管理器(Enterprise Manager)。
- ASM文件系统会自动管理增加或删除的硬盘。
- 快速纠正人为错误的闪回(Flashback)查询和恢复，可以恢复数据库、表甚至记录。
- Data Pump用于高速导入、导出数据，比传统方法导出速度快两倍以上，导入速度快15--45倍.
- Fine-Grained Auditing，精益审计，记录一切对敏感数据的操作
- Tablespace支持跨平台复制，极大的提高数据仓库加载速度。
- 流(Streams)复制，实现低系统消耗、双向(double-direction)、断点续传(resume from break point)、跨平台(cross platform)、跨数据源的复杂复制。
- Data Guard增加了逻辑备份功能，备份数据库日常可以运行于只读状态，充分利用备份数据库。
- 支持许多新EE选件，“Database Vault”用于加强数据库内部管理，Audit Vault用于数据库活动的审计，Warehouse Builder Enterprise ETL和Warehouse Builder Data Quality是数据仓库构建高级功能。
- 提供基于浏览器的小应用开发工具Oracle Application Express(原名HTML DB)，支持10g和9i R2。
- 支持Intel Itanium 2 处理器，支持windows 64位操作系统。
3、11g新功能清单
2007年11月，Oracle 11g正式发布，功能大大增强。11g是Oracle公司30年来发布的最重要的数据库版本，主要功能如下图：
其中之一是自动内存管理，如下图：
（1）数据库管理
计划管理（Plan Management）
这一特性允许你将某一特定语句的查询计划固定下来，无论统计数据变化还是数据库版本变化都不会改变她的查询计划。
自动诊断知识库（Automatic Diagnostic Repository ADR）
当Oracle探测到重要错误时，会自动创纪一个事件（incident），并且捕捉到和这一事件相关的信息，同时自动进行数据库健康检查并通知DBA。此外，这些信息还可以打包发送给Oracle支持团队。
事件打包服务(Incident Packaging Service)
如果你需要进一步测试或者保留相关信息，这一特性可以将与某一事件相关的信息打包。并且你还可以将打包信息发给oracle支持团队。
自动SQL优化(Auto SQL Tuning)
10g的自动优化建议器可以将优化建议写在SQL profile中。而在11g中，你可以让oracle自动将能3倍于原有性能的profile应用到SQL语句上。性能比较由维护窗口中一个新管理任务来完成。
访问建议器（Access Advisor）
11g的访问建议器可以给出分区建议，包括对新的间隔分区（interval partitioning）的建议。间隔分区相当于范围分区（range partitioning）的自动化版本，她可以在必要时自动创建一个相同大小的分区。范围分区和间隔分区可以同时存在于一张表中，并且范围分区可以转换为间隔分区。
自动内存优化（Auto Memory Tuning）
在9i中，引入了自动PGA优化；10g中，又引入了自动SGA优化。到了11g，所有内存可以通过只设定一个参数来实现全表自动优化。你只要告诉oracle有多少内存可用，她就可以自动指定多少内存分配给PGA、多少内存分配给SGA和多少内存分配给操作系统进程。当然也可以设定最大、最小阈值。
资源管理器（Resource Manager）
11g的资源管理器不仅可以管理CPU，还可以管理IO。你可以设置特定文件的优先级、文件类型和ASM磁盘组。
自动数据库诊断监视器ADDM
ADDM在10g被引入。11g中，ADDM不仅可以给单个实例建议，还可以对整个RAC（即数据库级别）给出建议。另外，还可以将一些指示（directive）加入ADDM，使之忽略一些你不关心的信息。
自动负载仓库基线（AWR Baselines）
AWR基线得到了扩展。可以为一些其他使用到的特性自动创建基线。默认会创建周基线。
数据库重演(Database Replay)
这一特性可以捕捉整个数据的负载，并且传递到一个从备份或者standby数据库中创建的测试数据库上，然后重演负责以测试系统调优后的效果。
SQL重演(SQL Replay)
和前一特性类似。但是只是捕捉SQL负载部分，而不是全部负载。
（2）PLSQL
结果集缓存（Result Set Caching）
这一特性能大大提高很多程序的性能。在一些MIS系统或者OLAP系统中，需要使用到很多"select count(*)"这样的查询。在之前，我们如果要提高这样的查询的性能，可能需要使用物化视图或者查询重写的技术。在11g，我们就只需要加一个/*+result_cache*/的提示就可以将结果集缓存住，这样就能大大提高查询性能。当然，在这种情况下，我们可能还要关心另外一个问题：完整性。因为在oracle中是通过一致性读来保证数据的完整性的。而显然，在这种新特性下，为提高性能，是从缓存中的结果集中读取数据，而不会从回滚段中读取数据的。关于这个问题，答案是完全能保证完整性。因为结果集是被独立缓存的，在查询期间，任何其他DML语句都不会影响结果集中的内容，因而可以保证数据的完整性。
对象依赖性改进
在11g之前，如果有函数或者视图依赖于某张表，一旦这张表发生结构变化，无论是否涉及到函数或视图所依赖的属性，都会使函数或视图变为invalid。在11g中，对这种情况进行了调整：如果表改变的属性与相关的函数或视图无关，则相关对象状态不会发生变化。
正则表达式的改进
在10g中，引入了正则表达式。这一特性大大方便了开发人员。11g，oracle再次对这一特性进行了改进。其中，增加了一个名为regexp_count的函数。另外，其他的正则表达式函数也得到了改进。
新SQL语法 =>
我们在调用某一函数时，可以通过=>来为特定的函数参数指定数据。而在11g中，这一语法也同样可以出现在sql语句中了。例如，你可以写这样的语句：
select f(x=>6) from dual;
精益访问控制FGAC
对TCP包（utl_tcp、utl_smtp…）支持FGAC(Fine Grained Access Control)
增加了只读表read-only table
在以前，我们是通过触发器或者约束来实现对表的只读控制。11g中不需要这么麻烦了，可以直接指定表为只读表。
触发器执行效率提高了
内部单元内联（Intra-Unit inlining）：在C语言中，你可以通过内联函数（inline）或者宏实现使某些小的、被频繁调用的函数内联，编译后，调用内联函数的部分会编译成内联函数的函数体，因而提高函数效率。在11g的plsql中，也同样可以实现这样的内联函数了。
设置触发器顺序
可能在一张表上存在多个触发器。在11g中，你可以指定它们的触发顺序，而不必担心顺序混乱导致数据混乱。
混合触发器（compound trigger）
这是11g中新出现的一种触发器。她可以让你在同一触发器中同时具有申明部分、before过程部分、after each row过程部分和after过程部分。
创建无效触发器(Disabled Trigger)
11g中，开发人员可以可以闲创建一个invalid触发器，需要时再编译她。
在非DML语句中使用序列（sequence）
在之前版本，如果要将sequence的值赋给变量，需要通过类似以下语句实现：
select seq_x.next_val into v_x from dual;
在11g中，不需要这么麻烦了，下面语句就可以实现：
v_x := seq_x.next_val;
PLSQL_Warning
11g中。可以通过设置PLSQL_Warning=enable all，如果在"when others"没有错误爆出就发警告信息。
PLSQL的可继承性
可以在oracle对象类型中通过super（和java中类似）关键字来实现继承性。
编译速度提高
因为不在使用外部C编译器了，因此编译速度提高了。
改进了DBMS_SQL包
其中的改进之一就是DBMS_SQL可以接收大于32k的CLOB了。另外还能支持用户自定义类型和bulk操作。
增加了continue关键字
在PLSQL的循环语句中可以使用continue关键字了（功能和其他高级语言中的continue关键字相同）。
新的PLSQL数据类型——simple_integer
这是一个比pls_integer效率更高的整数数据类型。
（3）其他（英文资料间或少许中文翻译）
As of 2007, the commercial database market is very mature and expectations are high. All of the leading databases do a good job in storing and retrieving data, and customers are now demanding self-tuning databases, intelligent engines that detect and correct sub-optimal conditions.客户需要更智能的数据库引擎
Oracle 11g is the clear leader in this area. Oracle has invested heavily into self-tuning capabilities including automated storage and memory management and intelligent tuning advisors. Now in 11g, Oracle closes the loop and offers intelligent automation tools to create a self-healing database. The most important 11g new automation features include a SQL tuning advisor that automatically tunes SQL statements.11g是商用数据库领域的佼佼者，尤其是自动存储和内存管理、智能调整优化建议等功能。
Automatic Memory Tuning自动内存调整 - Automatic PGA tuning was introduced in Oracle 9i. Automatic SGA tuning was introduced in Oracle 10g. In 11g, all memory can be tuned automatically by setting one parameter. PGA Update: Oracle technology is constantly changing, so don't miss my new notes on updates to Oracle PGA behavior. Also see these important notes on over-riding the Oracle PGA defaults.
SQL Performance Analyzer (Fully Automatic SQL Tuning) SQL性能优化器 - Using SPA, you can tell 11g to automatically apply SQL profiles for statements where the suggested profile give 3-times better performance that the existing statement. The performance comparisons are done by a new administrative task during a user-specified maintenance window. See Inside the 11g SQL Performance Advisor.
Automated Storage Load balancing 自动存储负荷均衡 - Oracle’s Automatic Storage Management (ASM) now enables a single storage pool to be shared by multiple databases for optimal load balancing. Shared disk storage resources can alternatively be assigned to individual databases and easily moved from one database to another as processing requirements change.
Automatic Diagnostic Repository 自动诊断仓库 - When critical errors are detected, Oracle automatically creates an ?incident? ticket, notifying the DBA instantly.
Let's get started with the Oracle11g new features for general database administration:
Oracle 11g DBA new features
Enhanced ILM 增强信息生命周期管理 - Information Lifecycle Management (ILM) has been around for decades, but Oracle has made a push to codify the approach in 11g. Read more about Oracle 11g ILM here: Inside Oracle 11g ILM - Information lifecycle management.
Table-level control of CBO statistics refresh threshold 基于成本优化器的刷新临界点的表级控制 - (source Lutz Hartmann) When Oracle automatically enables statistics collection, the default "staleness" threshold of 10% can now be changed with the dbms_stats.set_table_prefs procedure:
exec dbms_stats.set_table_prefs(‘HR‘, ‘EMPS‘, ‘STALE_PERCENT‘, ‘15′)
There are three new arguments to the set_table_prefs procedure, designed to allow the DBA more control over the freshness of their statistics:
stale_percent - overrides the one-size-fits-all value of 10%
incremental - Incremental statistics gathering for partitions
publish - Allows the DBA to test new statistics before publishing them to the data dictionary
This is an important 11g new feature because the DBA can now control the quality of optimizer statistics at the table level, thereby improving the behavior of the SQL optimizer to always choose the ?best? execution plan for any query.
File Group Repository 文件组库 - Oracle introduced an exciting new feature in 10gr2 dubbed the Oracle File Group Repository (FGR). The FGR allows the DBA to define a logically-related group of files and build a version control infrastructure. The working of the Oracle file group repository were created to support Oracle Streams, and they mimic the functionality of an IBM mainframe generation data group (GDG), in that you can specify relative incarnations of the file sets (e.g. generation 0, generation -3).
Interval partitioning for tables 表的间隔分区 - This is a new 11g partitioning scheme that automatically creates time-based partitions as new data is added. Source: Mark Rittman This is a marvelous one ! You can now partition by date, one partition per month for example, with automatic partition creation. Source: Laurent Schneider
New load balancing utilities 新负载均衡工具集 -There are several new load balancing utilities in 11g (first introduced in 10gr2):
- Web server load balancing 应用服务器负载均衡 - The web cache component includes Apache extension to load-balance transactions to the least-highly-loaded Oracle HTTP server (OHS).
- RAC instance load balancing RAC实例负载均衡 - Staring in Oracle 10g release 2, Oracle JDBC and ODP.NET provide connection pool load balancing facilities through integration with the new ?load balancing advisory? tool. This replaces the more-cumbersome listener-based load balancing technique.
- Automated Storage Load balancing 自动存储负载均衡 - Oracle’s Automatic Storage Management (SAM) now enables a single storage pool to be shared by multiple databases for optimal load balancing. Shared disk storage resources can alternatively be assigned to individual databases and easily moved from one database to another as processing requirements change.
- Data Guard Load Balancing DG负载均衡 - Oracle Data Guard allows for load balancing between standby databases.
- Listener Load Balancing 侦听器负载均衡- If advanced features such as load balancing and automatic failover are desired, there are optional sections of the listener.ora file that must be present
New table Data Type "simple_integer" 新增表数据类型 - A new 11g datatype dubbed simple_integer is introduced. The simple_integer data type is always NOT NULL, wraps instead of overflows and is faster than PLS_INTEGER. Source: Lewis Cunningham
Improved table/index compression 改进的表/索引压缩 - Segment compression now works for all DML, not just direct-path loads, so you can create tables compressed and use them for regular OLTP work. Also supports column add/drop. Mark Rittman
Faster DML triggers 更快的DML触发器 - DML triggers are up to 25% faster. This especially impacts row level triggers doing updates against other tables (think Audit trigger). Source: Lewis Cunningham
Improved NFS data file management 改进的NFS数据文件管理 - Kevin Closson has some great notes on Oracle 11g improvement in Networked Attached Storage (NAS). "I’ve already blogged that 11g ?might? have an Oracle-provided NFS client. Why is this? It?s because Oracle knows full well that taking dozens of commodity servers and saddling them up with multi-protocol connectivity is a mess.
Server-side connection pooling 服务器侧连接池 - In 11g server-side connection pooling, an additional layer to the shared server, to enable faster [actually to bypass] session creation. Source: Laurent Schneider Server-side connection pooling allows multiple Oracle clients to share a server-side pool of sessions (USERIDs must match). Clients can connect and disconnect (think PHP applications) at will without the cost of creating a new server session - shared server removes the process creation cost but not the session creation cost. Mark Rittman
RMAN UNDO bypass 恢复管理器旁路重做日志 - RMAN backup can bypass undo. Undo tablespaces are getting huge, but contain lots of useless information. Now RMAN can bypass those types of tablespace. Great for exporting a tablespace from backup. Source: Laurent Schneider
Capture/replay database workloads 捕获/重放数据库负荷 - Sounds appealing. You can capture the workload in prod and apply it in development. Oracle is moving toward more workload-based optimization, adjusting SQL execution plans based on existing server-side stress. This can be very useful for Oracle regression testing. Source: Laurent Schneider
Scalability Enhancements 可扩展性增强 - The features in 11g focused on scalability and performance can be grouped into four areas: Scalable execution, scalable storage, scalable availability and scalable management. Mark Rittman
Virtual columns 虚拟列 - Oracle 11g virtual table columns are columns that are actually functions ("create table t1 (c1 number, c2 number, c3 as (c1+c2) virtual"), and similarly, virtual indexes that are based on functions. Also see Oracle 11g function-based virtual columns. Source: Source: Mark Rittman
REF partitioning 引用分区 - The 11g REF partitioning allows you to partition a table based on the values of columns within other tables. Source: Mark Rittman
A "super" object-oriented DDL keyword 新面向对象DDL关键词super - This is used with OO Oracle when instantiating a derivative type (overloading), to refer to the superclass from whence the class was derived.
Oracle 11g XML data storage XML数据存储 - Starting in 11g, you can store XML either as a CLOB or a binary data type, adding flexibility. Oracle11g will support query mechanisms for XML including XQuery and SQL XML, emerging standards for querying XML data stored inside tables.
New Trigger features 新触发器类型compound - A new type of "compound" trigger will have sections for BEFORE, ROW and AFTER processing, very helpful for avoiding errors, and maintaining states between each section.
Partitioning 分区 - partitioning by logical object and automated partition creation.
LOB's - New high-performance LOB features. 新的高性能LOB大对象
- Automatic Diagnostic Repository (ADR)自动诊断库 - When critical errors are detected, they automatically create an ?incident?. Information relating to the incident is automatically captured, the DBA is notified and certain health checks are run automatically. This information can be packaged to be sent to Oracle support (see following). Source: Dr. Tim Hall The ADR can be accessed via OEM or a command-line interface.Hangman Utility挂起管理工具集 - The Hang Manager (hangman) utility is a new 11g tool to detect database bottlenecks. An extension of the dba_waiters and dba_blockers views, the hangman tables have a ?hang chain? that allow the DBA to find the source of ?hangs?, such as the ?deadly embrace? where mutually blocking locks or latches hang a process. In 11g, the hangman utility is installed on all RAC nodes by default, allowing for easier inter-node hang diagnostics.
- Health Monitor (HM) utility健康监控器工具集 - The Health Monitor utility is an automation of the dbms_repair corruption detection utility. When a corruption-like problem happens, the HR utility will checks for possible corruption within database blocks, redo log blocks, undo segments, or dictionary table blocks.
- Incident Packaging Service (IPS)事故打包服务 - This wraps up all information about an incident, requests further tests and information if necessary, and allows you to send the whole package to Oracle Support. Source: Dr. Tim Hall
- Feature Based Patching基于补丁的特性 - All one-off patches will be classified as to which feature they affect. This allows you to easily identify which patches are necessary for the features you are using. EM will allow you to subscribe to a feature based patching service, so EM automatically scans for available patches for the features you are using. Source: Dr. Tim Hall
- New Oracle11g Advisors新建议器 - New 11g Oracle Streams Performance Advisor and Partitioning Advisor. Source: Mark Rittman
Enhanced Read only tables 只读表增强
Table trigger firing order 表触发器点火次序 - Oracle 11g PL/SQL and beyond will allow you to specify trigger firing order with the "precedes" and "follows" clauses. See trigger firing order tips
Invisible indexes 不可见索引 - Rich Niemiec claims that the new 11g "invisible indexes" are a great new feature. It appears that the invisible indexes will still exist, that they can just be marked as "invisible" so that they cannot be considered by the SQL optimizer. With the overhead of maintaining the index intact, I don't see why this is very useful. Also see 11g Function-based columns.
Oracle11g High Availability & RAC new features
Oracle continues to enhance Real Application Clusters in Oracle11g and we see some exciting new features in RAC manageability and enhanced performance:
Oracle 11g RAC parallel upgrades - Oracle 11g promises to have a rolling upgrade features whereby RAC database can be upgraded without any downtime. Ellison first promised this feature in 2002, and it is a very challenging and complex 11g new feature.
Oracle RAC load balancing advisor - Starting in 10gr2 we see a RAC load balancing advisor utility. Oracle says that the 11g RAC load balancing advisor is only available with clients which use .NET, ODBC, or the Oracle Call Interface (OCI).
ADDM for RAC - Oracle will incorporate RAC into the automatic database diagnostic monitor, for cross-node advisories.
Interval Partitioning - Robert Freeman notes that 11g "interval Partitioning makes it easier to manage partitions:
"Wouldn't it be nice if you could just tell Oracle you wanted to partition every month and it would create the partitions for you? That is exactly what interval partitioning does. Here is an example:
create table selling_stuff_daily
( prod_id number not null, cust_id number not null
, sale_dt date not null, qty_sold number(3) not null
, unit_sale_pr number(10,2) not null
, total_sale_pr number(10,2) not null
, total_disc number(10,2) not null)
partition by range (sale_dt)
interval (numtoyminterval(1,'MONTH'))
( partition p_before_1_jan_2007 values
less than (to_date('01-01-2007','dd-mm-yyyy')));
Note the interval keyword. This defines the interval that you want each partition to represent. In this case, Oracle will create the next partition for dates less than 02-01-2007 when the first record that belongs in that partition is created."
ADR command-line tool - Virag Sharma notes that the Oracle Automatic Diagnostic repository (ADR) has a new command-line interface dubbed ADRCI, the ADR Command Interface. Sharma notes that ADRCI can be used to access the 11g alert log:
$adrci
adrci> set editor vi
adrci> show alert ( it will open alert in vi editor )
adrci> show alert -tail ( Similar to Unix tail command )
adrci> show alert -tail 200 ( Similar to Unix Command tail -200 )
adrci> show alert -tail -f ( Similar to Unix command tail -f )
To list all the "ORA-" error run following command
adrci> show alert -P "MESSAGE_TEXT LIKE '%ORA-%'"
Optimized RAC cache fusion protocols - moves on from the general cache fusion protocols in 10g to deal with specific scenarios where the protocols could be further optimized. Source: Mark Rittman
Oracle 11g RAC Grid provisioning - The Oracle grid control provisioning pack allows you to "blow-out" a RAC node without the time-consuming install, using a pre-installed "footprint". See Oracle 11g RAC Provisioning Pack tips.
Hot patching - Zero downtime patch application.
Data Guard - Standby snapshot - The new standby snapshot feature allows you to encapsulate a snapshot for regression testing. You can collect a standby snapshot and move it into your QA database, ensuring that your regression test uses real production data.
Quick Fault Resolution - Automatic capture of diagnostics (dumps) for a fault.
-ocr and voting disk are in asm
-No need to take backup of voting disk
-GSD is offline
-Gloabl AWR
-scan listern
-one node rac
-oracle restart
-HAS
-server pooling
Oracle 11g programming language support New Features
PHP - Improved PHP driver for Oracle.
Compilers - Improved native Java & PL/SQL compilers.
Oracle 11g XML Enhancements - Oracle 11g will also support Content Repository API for Java Technology (JSR 170). Oracle 11g has XML "duality", meaning that you can also embed XML directives inside PL/SQL and embed PL/SQL inside XML code. Oracle 11g XML will also support schema-based document Type Definitions (DTD's), to describe internal structure of the XML document.
Scalable Java - The next scalable execution feature is automatic creation of "native" Java code, with just one parameter for each type with an "on/off" value. This apparently provides a 100% performance boost for pure Java code, and a 10%-30% boost for code containing SQL. Mark Rittman
Improved sequence management - A new features of Oracle 11g will bypass DML (sequence.nextval) and allow normal assignments on sequence values.
Intra-unit inlining. In C, you can write a macro that gets inlined when called. Now any stored procedure is eligible for inlining if Oracle thinks it will improve performance. No change to your code is required. Now you have no reason for not making everything a subroutine! Source: Lewis Cunningham
Oracle 11g PL/SQL New Features
PL/SQL "continue" keyword - This will allow a "C-Like" continue in a loop, skipping an iteration to bypass any "else" Boolean conditions. A nasty PL/SQL GOTO statement is no longer required to exit a Boolean within a loop. Oracle professional Jurgen Kemmelings has an excellent PL/SQL example of the PL/SQL continue clause in-action:
begin
for i in 1..3
loop
dbms_output.put_line(?i=?||to_char(i));
if ( i = 2 )
then
continue;
end if;
dbms_output.put_line(?Only if i is not equal to 2′);
end loop;
end;
Disabled state for PL/SQL - Another 11g new feature is a "disabled" state for PL/SQL (as opposed to "enabled" and "invalid" in dba_objects).
Easy PL/SQL compiling - Native Compilation no longer requires a C compiler to compile your PL/SQL. Your code goes directly to a shared library. Source: Lewis Cunningham
Improved PL/SQL stored procedure invalidation mechanism - A new 11g features will be fine grained dependency tracking, reducing the number of objects which become invalid as a result of DDL.
Scalable PL/SQL - The next scalable execution feature is automatic creation of "native" PL/SQL (and Java code), with just one parameter for each type with an "on/off" value. This apparently provides a 100% performance boost for pure PL/SQL code, and a 10%-30% performance boost for code containing SQL. Mark Rittman
Enhanced PL/SQL warnings - The 11g PL/SQL compiler will issue a warning for a "when others" with no raise.
Stored Procedure named notation - Named notation is now supported when calling a stored procedure from SQL.
Oracle 11g SQL New Features
New "pivot" SQL clause - The new "pivot" SQL clause will allow quick rollup, similar to an MS-Excel pivot table, where you can display multiple rows on one column with SQL. MS SQL Server 2005 also introduced a pivot clause. Laurent Schneider notes that the new SQL "pivot" syntax is great for converting rows-to-columns and columns-to-rows.
The /*+result_cache*/ SQL hint - This suggests that the result data will be cached in the data buffers, and not the intermediate data blocks that were accessed to obtain the query results. You can cache SQL and PL/SQL results for super-fast subsequent retrieval. The "result cache" ties into the "scalable execution" concept. There are three areas of the result cache:
The SQL query result cache - This is an area of SGA memory for storing query results.
The PL/SQL function result cache - This result cache can store the results from a PL/SQL function call.
The OCI client result cache - This cache retains results from OCI calls, both for SQL queries or PL/SQL functions.
Scalable Execution - This 11g feature consists of a number of features, the first of which is query results caching; this feature automatically caches the results of an SQL query as opposed to the data blocks normally cached by the buffer cache, and works both client (OCI) and server side - this was described as "buffer cache taken to the next level". The DBA sets the size of the results cache and turns the feature on at a table level with the command "alter table DEPT cache results", the per-process cache is shared across multiple session and at the client level, is available with all 11g OCI-based clients. Mark Rittman
XML SQL queries - Oracle11g will support query mechanisms for XML including XQuery and SQL XML, emerging standards for querying XML data stored inside tables.
- SQL Replay - Similar to the previous feature, but this only captures and applies the SQL workload, not total workload. Source: Dr. Tim Hall
- Improved optimizer statistics collection speed - Oracle 11g has improved the dbms_stats performance, allowing for an order of magnitude faster CBO statistics creation. Oracle 11g has also separated-out the "gather" and "publish" operations, allowing CBO statistics to be retained for later use. Also, Oracle 11g introduces multi-column statistics to give the CBO the ability to more accurately select rows when the WHERE clause contains multi-column conditions or joins.
- SQL execution Plan Management - Oracle 11g SQL will allow you to fix execution plans (explain plan) for specific statements, regardless of statistics or database version changes. See Inside the 11g SQL Performance Advisor.Dynamic SQL. DBMS_SQL is here to stay. It's faster and is being enhanced. DBMS_SQL and NDS can now accept CLOBs (no more 32k limit on NDS). A ref cursor can become a DBMS_SQL cursor and vice versa. DBMS_SQL now supprts user defined types and bulk operations. Source: Lewis Cunningham
- SQL Performance Advisor - You can tell 11g to automatically apply SQL profiles for statements where the suggested profile give 3-times better performance that the existing statement. The performance comparisons are done by a new administrative task during a user-specified maintenance window.
- Improved SQL Access Advisor - The 11g SQL Access Advisor gives partitioning advice, including advice on the new interval partitioning. Interval partitioning is an automated version of range partitioning, where new equally-sized partitions are automatically created when needed. Both range and interval partitions can exist for a single table, and range partitioned tables can be converted to interval partitioned tables.
- Oracle 11g SQL tuning transformations
11g Performance tuning optimization new features:
- Automatic Memory Tuning - Automatic PGA tuning was introduced in Oracle 9i. Automatic SGA tuning was introduced in Oracle 10g. In 11g, all memory can be tuned automatically by setting one parameter. You literally tell Oracle how much memory it has and it determines how much to use for PGA, SGA and OS Processes. Maximum and minimum thresholds can be set. This is controlled by the Oracle 11g memory_target parameter.
- Resource Manager - The 11g Resource Manager can manage I/O, not just CPU. You can set the priority associated with specific files, file types or ASM disk groups.
- ADDM - The ADDM in 11g can give advice on the whole RAC (database level), not just at the instance level. Directives have been added to ADDM so it can ignore issues you are not concerned about. For example, if you know you need more memory and are sick of being told it, you can ask ADDM not to report those messages anymore.
- Faster sorting - Starting in 10gr2 we see an improved sort algorithm, ?Oracle10gRw introduced a new sort algorithm which is using less memory and CPU resources. A hidden parameter _newsort_enabled = {TRUE|FALSE} governs whether the new sort algorithm will be used.?
- AWR Baselines - The AWR baselines of 10g have been extended to allow automatic creation of baselines for use in other features. A rolling week baseline is created by default.
- Adaptive Metric Baselines - Notification thresholds in 10g were based on a fixed point. In 11g, notification thresholds can be associated with a baseline, so the notification thresholds vary throughout the day in line with the baseline.
Oracle 11g security & auditing new features
Enhanced Password - Pete Finnigan notes some new Oracle 11g security features "[Oracle 11g] will have case sensitive passwords and also the password algorithm has changed to SHA-1 instead of the old DES based hashing used."
Oracle SecureFiles - replacement for LOBs that are faster than Unix files to read/write. Lots of potential benefit for OLAP analytic workspaces, as the LOBs used to hold AWs have historically been slower to write to than the old Express .db files. Mark Rittman Securefiles are a huge improvement to BLOB data types. Faster, with compression, encryption. Source: Laurent Schneider
Oracle 11g audit vault - Oracle Audit Vault is a new feature that will provide a solution to help customers address the most difficult security problems remaining today, protecting against insider threat and meeting regulatory compliance requirements.
Proxy connect for SQL*Plus - New with 10r2 proxy identification in SQL*Plus, the "connect" command has been enhanced to allow for a "proxy", to aid applications that always connect with the same user ID:
connect sapr3[scott]/tiger
FGAC for UTL_SMTP, UTL_TCP and UTL_HTTP. You can define security on ports and URLs. Source: Lewis Cunningham
Fine Grained Dependency Tracking (FGDT). This means that when you add a column to a table, or a cursor to a package spec, you don't invalidate objects that are dependant on them. Sweet! Source: Lewis Cunningham
Database Workload Replay - Oracle "Replay" allows the total database workload to be captured, transferred to a test database created from a backup or standby database, then replayed to test the affects of an upgrade or system change.
You specify the SQL tuning sets similar to the 10g offering and use the dbms_sqlpa package (SQL performance analyzer) to manage the SQL each "analyzer task" with dbms_sqlpa procedures (create_analysis_task, cancel_analysis_task, drop_analysis_task, reset_analysis_task, report_analysis_task, resume_analysis_task, interrupt_analysis_task).
Currently, they are working to a capture performance overhead of 5%, so you could conceivably capture real production workloads. Source: Dr. Tim Hall
Oracle 11g new RMAN features
This blog by George Trujillo notes many new 11g features:
A Virtual Private Catalog can now make sure an RMAN user can only see databases they are authorized to use.
Archive log management for Streams and Data Guard
Network aware DUPLICATE
Optimized undo backup
Improved corrupt block detection
Java, PL/SQL, XML, .NET, PHP, APEX
PL/SQL Native Compilation Without Needing a Third-Party C Compiler.
Native PL/SQL use of Seq.Nextval in a PL/SQL program.
PL/SQL can use CLOBs to get around 32KB limitation of SQL character strings.
Enhanced PL/SQL warnings and error messages.
PLSTIMER identifies hotspots and performance tuning opportunities in PL/SQL.
New package DBMS_HPROF controls the recording of raw PLSTIMER data.
Continued support of standards with JDBC 4.0 and JVM 5.0. Capability to upgrade to JVM 6.0 will be available in the future.
Oracle JVM JIT supports transparent native Java compilation without a C Compiler.
Significant performance improvements with JDBC performance especially with Advanced Queuing.
JDBC supports server side result cache alone with OCI client side result cache. Additional things I liked included the "big" improvements in the JIT and RAC support for JDBC.
New command line interface to OracleJVM making it a lot easier to work with the JDK in the database.
Support for database resident JARs.
JDBC support for starting and shutting down the database.
Performance enhancements to XPath query for Java.
XML applications can now process larger XML documents by loading and saving .
DOM nodes in memory and using a page manager for physical binary data management.
Unified Java API for XML allows mid-tier Java programs to leverage lazily loading by allowing a disconnected mode of operation that allows a XMLType to be used with a session pool model of connection management.
HTML-DB 11g enhancements
APEX will be standard with an embedded PL/SQL gateway.
APEX will have an application packager for packing and deployment of APEX applications.
Data Pump Enhancements to use when you're moving data
Compression and encryption enhancements
Support for XML Schemas and schema-based tables.
Transportable partitions
Change assurance (important for saving money during testing and migration)
- Change assurance new features of Database Replay and SQL Replay can play a large role in reducing costs, testing and issues when migrating to Oracle Database 11g. This is one of the best new features in this release.
- Database replay - captures actual production workload and replays it on a separate system.
- SQL performance analyzer - finds and fixes SQL performance degradations
- 10gR2 will have a patch set, that will allow the replay to occur.
Secure Files (Next generation LOBS)
- Eliminates need for file systems. Very fast access of files.
- Just as fast as file systems with all the capabilities of the Oracle database (it?s a new LOB type). It is very fast at accessing the files from a file system.
- Store all your data in the database with one consistent:, Security and auditing model
- Backup and recovery mechanism
- Storage management (ASM)
- Transaction and concurrency model
- Interface and protocol
- Values added services like encryption, compression, and de-duplication
Oracle 11g Business Intelligence & OLAP
Mark Rittman notes some of the upcoming features with Oracle 11g BI suite and OLAP:
Integration of materialized view query rewrite with OLAP - Rittman notes:
"However, in 10g OLAP although you could register a view over aggregated data in an analytic workspace with query rewrite, ?normal? queries that used SUM(), AVG() and other aggregation functions, together with GROUP BY, wouldn?t get rewritten as the SQL views used over analytic workspaces were based on fully-solved cubes, i.e. the view contained all levels of aggregation and measures were already aggregated. . .
Now, with this forthcoming release of Oracle OLAP, you can just check a box in Analytic Workspace Manager to enable queries against the source tables for your analytic workspace to be re-written against the summary data in your analytic workspace, and your OLAP cube will then act in the same way as a regular materialized view, meaning the two technologies (in theory) will be interchangeable as a way of summarizing warehouse data. "
Oracle 11g data mining
With the release of the first book on ODM "Oracle Data Mining", we see increased interest in data mining within 11g and there are rumors that ODM will be greatly enhanced, moving data mining objects into the dictionary and improving the interface for complex analytics.
ASM new features in 11g
-asmca introduction
-disk can be force mount/delete
-metadata backup
-asm and cluster in same software
-asm disk check
-fast start mirror resync
-sysasm role
RMAN new features in 11g
-rman undo bypass
-backup of flash recovery area to disk
-virtual catalog
-recovery advisor
-Duplicate database from backup with out connecting to source database
-Undrop tablespace
Data Guard new features in 11g
- Fast-Start Failover for Maximum Performance Mode in a Data Guard Configuration
- Compression of Redo Traffic (Only for Gap Resolution) Over the Network in a Data Guard Configuration
- Real-Time Query Capability of Physical Standby Database
- Fast Role Transitions in a Data Guard Configuration
- Data Guard Integration, Simplification, and Performance
- Dynamic Setting of Oracle Data Guard SQL Apply Parameters
- Enhanced Data Guard Broker Based Management Framework
- Histogram for Redo Transport Response Time in a Data Guard Configuration
- Snapshot Standby
- Strong Authentication for Data Guard Redo Transport
- Enhanced DDL Handling in Oracle Data Guard SQL Apply
- Enhanced Oracle RAC Switchover Support for Logical Standby Databases
- Data Guard Logical Apply Completeness
- Oracle Scheduler Support in Data Guard SQL Apply
- Support Transparent Data Encryption (TDE) with Data Guard SQL Apply
- Support XMLType Data Type (Only CLOB) in Data Guard SQL Apply
- Virtual Private Database (VPD) Support in Data Guard SQL Apply
