ORACLE 11G新特性2

最新推荐文章于 2024-07-12 11:00:18 发布

茄肥猫

最新推荐文章于 2024-07-12 11:00:18 发布

阅读量980

点赞数

分类专栏： ORACLE 文档文章标签： oracle 11g

ORACLE 文档专栏收录该内容

224 篇文章 1 订阅

订阅专栏

ORACLE 11G 在 2007 年 7 月 11 日发布，距现在有一年有余了，其最新版本是 ORACLE 11.1.0.6.0. 以下简称为 11G .

伴随每一次新版本的发行， ORACLE 都会发布相应的文档。 11G 的文档库大约有 1G ，将近 200 个文档。下面是根据我们的实际情况，我整理出的可能和我们相关的 11G 的一些新功能。更全面的有关 11G 新功能请参看： Oracle® Database New Features Guide 11g Release 1 (11.1) Part Number B28279-03 。

对于 ORACLE 数据库产品的改进和增强， ORACLE 11G 文档中用了：

8 个 ability 来描述其强大功能：

manageability,availability,reliability,scalability,diagnosability,usability ,interoperability,portability

7 个 simp 来形容其简单易用 (easy-to-use) ：

simple,simplification,simplify,simplifying,simplified,simply ,simpler

5 个 auto 来表达其管理的智能 (intelligence) 和自动化：

automate,automation,,automatic,automated,automatically

并且用一个比较有意思的词汇来定义其前瞻性数据库管理概念： proactive

一,管理(manageability)

1.自动化(automation)

(1)自动的内存管理

ORACLE 11G 进一步完善了自动的内存管理机制 . 内存管理一直是 DBA 重要的一项数据库管理任务，并且随着环境的改变，这项任务常常令 DBA 感到头疼。 ORACLE 一直不懈地在为 DBA 减轻管理负担。从 ORACLE 9I 开始，推出自动的 PGA 管理（ PGA_AGGREGATE_TARGET ） , 再到 10G 推出自动的 SGA 管理 (SGA_TARGET), 直至 11G ，整个的 ORACLE 内存管理都完全自动化了。 DBA 只需设定一个参数： memory_target,ORACLE 能自动管理和调节 SGA 和 PGA 的内存分配。

Automatic memory management(rainny)

This is a new initialization parameter in Oracle Database 11g to automate the memory allocation. By default, Database Configuration Assistant now uses MEMORY_TARGET instead of specifying individual values for SGA_TARGET and PGA_AGGREGATE_TARGET. The memory management page of Database Configuration Assistant has new option to select automatic memory management.

(2)自动的健康检查

在 11G ， ORACLE 进一步增强了自动管理功能。 ORACLE 能够自动地对数据库进行健康检查，并对有可能导致数据库在将来出现问题的一些因素给 DBA 发送告警信息，并针对潜在问题给出一些建议。这种管理方式就是 ORACLE 提出的所谓的前瞻性的数据库管理 (proactive).

在之前， DBA 往往是在数据库出现问题时介入，对问题进行处理。这种管理方式是响应式的管理 (reactive). 当数据库出现问题时，即使 DBA 介入，并且顺利的解决问题，也难免给业务运行带来影响。我们往往希望将一些问题在变严重之前解决掉，从而避免发生大的灾难性事件。 ORACLE 所谓的前瞻性数据库管理即是这么一种理念。仔细深入，你发现从 ORACLE 10G 开始， ORACLE 数据库加入了很多前瞻性管理的组件和工具。

Automatic Health Monitoring(rainny)

Automatic Health Monitoring proactively checks the health of the database and identifies any issues affecting the database. Problems are detected even before users run into them and before they cause widespread damage. The results of Automatic Health Monitoring are reflected in a simple meter (the Health Meter) designed to allow the DBA to quickly gauge the health of the database. With the help of Automatic Health Monitoring, a DBA can get a comprehensive picture of the current health of the database as well as help on how to remedy the problems affecting the database.

Automatic Health Monitoring proactively detects problems early in their life-cycle and notifies the DBA of the user impact and recommended action to take. Depending on the component or situation, Automatic Health Monitoring can either quarantine the offending component/resource or provide a fix to a problem. This feature helps businesses minimize their downtime and plan ahead for outages.

See Also:Oracle Database Administrator's Guide for details

(3)自动地隔离损坏的回滚段

在 11G ，当某一回滚段发生损坏时， ORALCE 能自动地将这些回滚段隔离，从而避免后续的事务用到这些损坏的回滚段。

Automatic Quarantine of Corrupt Undo Segments(rainny)

This feature automatically quarantines corrupt undo segments and prevents future transactions from using the same undo segments.

This enhancement limits corruption propagation and thus limits the damage caused on the system.

2.管理工具

(1)企业管理器的增强：增加LOGMINER接口

在 9I ，基于 C/S 架构的企业管理器中有提供 LOGMiner 的 GUI ，从 10G 开始， ORACLE 的企业管理器是基于 WEB(B/S 架构 ) 的了，所以，在 10G ， LOG MINER 的 GUI 从企业管理器中移除。用户如果想要进行日志挖掘 , 只能调用 ORACLE 提供的接口程序： PL/SQL 包。这让一部分想要进行日志查询的用户感到非常不方便。到了 11G ， ORACLE 终于对用户的这一意见作出响应，在其基于浏览器的企业管理器 (ORACLE ENTERPRISE MANAGER 简称 OEM) 中增加了 LOGMINER 的图形界面。用户可以通过这个 GUI 进行向导式的日志查询 ( 不论是将数据库配置成 DATABASE CONSOLE 还是 GRID CONTROL ，用户都可以从 OEM 中获取日志挖掘的图形接口 ) 。

Browser-Based Enterprise Manager Integrated Interface for LogMiner(rainny)

This feature now makes it possible to use the browser-based Oracle Enterprise Manager Database Control interface for LogMiner. In prior releases, administrators were required to use the standalone Java Console to use LogMiner. The Console was not integrated with the rest of Enterprise Manager and was cumbersome to install. With this new interface, administrators have a task-based, intuitive approach to using LogMiner.

This new feature improves the manageability of LogMiner. The task-based work flows enable log mining and are integrated with Flashback Transactions.

二,可用性(availability)

1.高可用性 (high availability简称HA)

(1)DATAGUARD的增强

物理STANDBY数据库的实时查询功能

在 ORACLE 10G ，要对 STANDBY 数据库进行查询，必须将 STANDBY 切换过来，在查询时， STANDBY 数据库没办法同时应用从 PRIMARY 数据库传过来的重做日志。到了 11G ，这一问题已经解决。 STANDBY 数据库可以一边应用重做日志，一边进行实时的查询。

这个功能非常实用，有了这一新功能， STANDBY 数据库不但可以给我们提供冗余的数据保护，而且我们还可以将 STANDBY 数据库用于一些 READ-ONLY 系统，如：报表系统 (report system), 业务智能 (BI) ，决策支持（ DSS ）系统等，从而分担主数据库 (PRIMARY DATABASE) 的工作量，进而提升了数据库系统的整体性能。

拿我们的 XPC 系统来说，我们的所有查询报表其数据源都可设为 STANDBY 数据库，而不必连接到主数据库，主数据库专门用于事务处理， STANDBY 数据库专门用于报表查询，这样的话就分工很明确，减轻了主数据库的工作量，增加了整个系统的吞吐量，进而提升整个 XPC 系统的性能。

Real-Time Query Capability of Physical Standby Database(rainny)

It is now possible to query a physical standby database while Redo Apply is active.

This new capability increases your return on investment in Data Guard technology because a physical standby database can now be used to offload queries from the primary database in addition to providing data protection.

三,备份和恢复(Backup and Recovery)

1.闪回技术的增强

闪回事务

ORACLE 从 9I 开始推出闪回技术 (flashback), 顾名思议，闪回表达的意思是快速的恢复。在之前，要将数据恢复 ( 或者说回退 ) 到之前的某一个时间点 ( 版本 ) ，需要动用表空间时间点恢复 (tablespace point-in-time recovery 简称 TSPITR) 或数据库时间点恢复 (database point-in-time recovery 简称 DBPITR) 。不论是 TSPITR 还是 DBPITR ，其操作难度都非常大，步骤繁琐复杂，需要操作者具备一定的备份恢复知识。闪回技术的推出就是简化时间点恢复的步骤和降低操作的难度。你可以把它比喻为我们日常使用的操作系统的垃圾站，都是提供一种回退的机制。 ORALCE 9I 只推出闪回查询 (flashback query), 用于查询数据在之前某一时间点 ( 基于时间点或系统更改号 SCN) 的值，我们也称之为行版本 (row version) 查询 .ORACLE 10G 进一步增强和发展了闪回技术，推出了闪回数据库 (flashback database), 闪回表 (flashback table), 闪回删除 (flashback drop 即我们通常所称的 DDL 回退 ) ，这使得将数据库回退到某一个时间点或更正用户错误变得简单而方便 ( 如果你曾经有过因为用户不小心错删一个重要表而绞尽脑汁的痛苦经历，你就能体会到 10G 的 flashback drop 的可贵之处 ). 闪回查询，闪回表，闪回删除，闪回数据库的具体使用请参阅我的文档：

n 1_ 通过闪回查询 (flashback query) 来恢复被误删的数据 .doc

n 2_ 通过 flashback table 来将表闪回到某一个时间点 .doc

n 3_ 通过 flashback drop 来闪回被误删除的表 .doc

n 4_ 通过 flashback database 来将数据库闪回到某一个时间点 .doc

到了 11G ， ORACLE 进一步增强了闪回技术，推出闪回事务 (flashback transaction), 我认为这个是非常实用的一个功能。 Flashback transaction 使我们可以回退事务，即使事务已经提交。这对于更正一些用户错误非常有用 . 比如，用户不小心执行了一些事务，并且在这些事务里对数据库的数据做了一些更改，当用户提交事务后，发现这些更改是错误的，想要回退这些更改，这时候， FLASHBACK TRANSACTION 就发挥用场。 DBA 只需要简单地将这些事务闪回，就可以把用户犯下的错误更正过来。在之前的版本中如 ORALCE 10G ，如果对数据库做了一些配置的话，虽然也可以综合采用 ORACLE 10G 的 FLASH BACK DATABASE ， FLASHBACK TABLE ， FLASH QUERY 等功能实现同样的目的，但操作步骤繁多，复杂，需要 DBA 对相关知识和技术有一定的了解。 11G 的这一新功能大大减轻了操作的复杂度，从而对于用户错误更正这一 DBA 经常碰到的任务变得简单。

Flashback Transaction(rainny)

Flashback transaction is a new feature that can easily back out a transaction and its dependent transactions. This recovery operation utilizes undo data to create and execute the corresponding, compensating transactions that revert the affected data back to its original state.

This feature increases availability during logical recovery by easily and quickly backing out a specific transaction or set of transactions and their dependent transactions, with one command, while the database remains online.

2.逻辑备份

数据泵

人们一直对 ORACLE 导出工具导出的 DUMP 文件的 SIZE 抱怨，认为它很占磁盘空间。虽然，在之前的版本中， ORACLE 也提供压缩的功能，但 DUMP 文件的压缩比例太小。到了 11G ， ORACLE 进一步改进了 DUMP 文件的压缩功能，加大了压缩比例，从而减小了磁盘占用空间，进一步节省了数据库备份的成本。

Compressed Dump File Sets(rainny)

Data Pump provides the ability to compress the metadata of a dump file set, which can shrink the dump files by 10-15%.

In this release, Data Pump adds the ability to compress the entire dump file set, including data and metadata.

This feature lets you specify compression for metadata, row data, or the entire dump file set.

四,数据库问题诊断(diagnosability)

1.灾难事件打包服务（IPS）

当 ORACLE 数据库出现问题或发生灾难事件时，我们往往要收集一些相关的信息如：告警日志，跟踪文件，转储信息等等一些信息给 DBA 或 ORACLE SUPPORT 人员进行问题分析，以提出解决问题的方案。而对于一些没有多少 ORACLE 经验的技术人员来说，要收集这些信息，必须在 DBA 或 ORACLE SUPPORT 人员的指导下进行，并且完成这些工作须具备一定的 ORACLE 专业知识。到了 11G ，这一工作变得简单而自动化。当数据库发生错误或灾难时， ORACLE 会自动将与错误事件相关的信息（如例程的告警日志内容，进程跟踪文件的内容，发生错误的数据库对象的内部结构等）打包放入到一个事先设定好的目录（这个目录称之为自动的诊断信息存储库 Automatic Diagnostic Repository ，简称 ADR ），并且将这些打包好的资料通过 FTP 等方式传送给 DBA 或 ORACLE SUPPORT 人员。

ADR 的作用好比飞机上的 “ 黑匣子 ” ，当数据库发生灾难时， ORACLE SUPPORT 可以通过 ADR 来找到发生问题的原因，从而提出解决方案。

Incident Packaging Service (IPS)(rainny)

Providing appropriate information to Oracle Support or Development is a tedious and sometimes time-consuming task for users. IPS provides a facility that extracts diagnostic and test case data associated with product exceptions (incidents) from Automatic Diagnostic Repository (ADR) and packages the data for transport to Oracle.

IPS also provides mechanisms to automatically generate test cases, such as SQL test cases, so that support and development personnel can easily reproduce the problems in-house for analysis and resolution.

See Also:Oracle Database Administrator's Guide for details

2.自动诊断信息存储库(ADR)

正如前面所说， ADR 好比就是 ORACLE 数据库中的 “ 黑匣子 ” ，此处不再赘述

Automatic Diagnostic Repository(rainny)

Automatic Diagnostic Repository (ADR) is a new system-managed repository for storing and organizing trace files and other error diagnostic data. ADR provides a comprehensive view of all the serious errors encountered by the database and maintains all relevant data needed for problem diagnostics and their eventual resolution.

Automatic Diagnostic Repository (ADR) provides a uniform and consistent mechanism to store, format, and locate all database diagnostic information. Customers can now correlate errors across various components such as Oracle RAC, Oracle Clusterware, OCI, Net, processes, and so forth. ADR automatically generates incidents for serious errors and provides incident management functionality. As a result, ADR significantly reduces time to problem resolution time for customers.

See Also:Oracle Database Administrator's Guide for details

3.DIAGNOSTIC_DEST参数取代background dump, user dump, and core dump参数

这个还是和我前面提到的 ADR 有关。到了 11G ，你只需设定 DIAGNOSTIC_DEST 参数，所有的后台跟踪文件，用户跟踪文件，核心跟踪文件都统一转储到 DIAGNOSTIC_DEST 目录。你不需要再分别设定： background_dump_dest ， user_dump_dest ， core_dump_dest 参数了。将所有诊断信息统一放到一个目录的好处是：方便管理打包。当数据库出了问题时， ORACLE SUPPORT 人员在第一时间可以获得与数据库灾难相关的诊断信息。

ORACLE_BASE and Diagnostic Destination configuration(rainny)

The directory that you specify when you are prompted for ORACLE_BASE by Oracle Universal Installer is stored in the Oracle home inventory. Database Configuration Assistant uses this value to derive the default database locations and the DIAGNOSTIC_DEST parameter. The diagnostic destination location contains all ADR directories (diagnostic files such as Alert logs and so on). Starting with Oracle Database 11g Release 1 (11.1), the initialization parameter settings for background dump, user dump, and core dump destinations are replaced by the Diagnostic Destination.

4.SUPPORT工作台

这又是一个 11G 提供的新工具，用于全面的查看各种数据库诊断信息。并将这些信息打包传送给 ORACLE SUPPORT 人员。

Support Workbench(rainny)

The Support Workbench provides an easy-to-use interface that presents database health-related incidents on the system to the DBAs in a timely manner along with information on how to manage the incidents. It also assists DBAs in viewing diagnostic information from multiple Oracle products (such as Net, clients, ASM, Oracle RAC, etc.), running health checks, packaging incident data to Oracle Support, and managing incidents.

The Support Workbench significantly reduces problem resolution time for customers by providing a simple workflow interface to view and diagnose incident data and package it for Oracle Support.

See Also:Oracle Database 2 Day DBA for details

5.活动会话历史的增强

活动会话历史 (ASH) 是描述活动会话的相关信息的接口， DBA 可以通过 ASH 了解到很多活动会话 ( 用户进程 ) 的信息。 ASH 是 ORALCE 10G 开始推出的，在 11G 得到增强，显示的信息更加全面，详细。

Enhanced Active Session History(rainny)

The Active Session History infrastructure has been enhanced to allow for improved database performance diagnosis and monitoring.

This feature includes enhancements to row source information for query progress monitoring and time model statistics.

See Also:Oracle Database Performance Tuning Guide for details

五,性能(performance)

1.性能优化(performance tuning)

(1)自动的SQL优化功能

事实上，从 10G 开始， ORACLE 就推出了顾问框架（ Advisory Framework ） . 顾问框架由 4 个组件所组成：

sql tuning 顾问 : 为有性能问题的 sql 语句提供优化建议
sql access 顾问 : 根据 SCHEMA 对象最新的统计信息，对 SQL 语句的访问路径给出最佳建议
segment 顾问 : 监控对象 ( 主要是指表和索引 ) 空间问题和分析增长趋势
undo 顾问 : 提供有关回滚表空间管理的建议。比如，根据数据库需要支持闪回到指定的时间或其它回滚确保目标 (undo guarantee) 来给出建议，让 DBA 设定相关的实例参数以及配置合理的表空间 SIZE 来确保达到这一回滚目标。

ORACLE 11G 进一步增强了数据库自动管理 , 自我优化的功能。所有的这些功能，都是通过一个叫自动数据库诊断监视器的工具 (Automatic Database Diagnostic Monitor 简称 ADDM) 来实现的 . 当 ADDM 侦测到问题 SQL 时，它会自动的调用相应的顾问框架组件如 sql tuning advisor 来对 SQL 进行自动的优化或给出优化建议。

Automatic SQL Tuning with Self-Learning Capabilities(rainny)

This feature takes automatic SQL tuning to the next level by adding self-learning capabilities to it.

Oracle Database now automatically detects high-load SQL statements and then tunes them automatically as needed in the maintenance window by creating appropriate SQL Profiles. It also issues proactive advice to create new access structures such as indexes that will significantly improve the performance of the high-load SQL statements.

See Also:Oracle Database Performance Tuning Guide for details

(2)分区顾问

SQL Access Advisor 增加了对表和索引的分区顾问。当表或者索引的 SIZE 增加到一定程度时，可以调用 SQL Access Advisor 对如何分区进行评估和分析，以获取最佳的性能。

Partition Advisor(rainny)

The SQL Access Advisor has been enhanced to include advice on how to partition tables, materialized views, and indexes in order to improve performance of SQL statements.

Oracle Database offers a wide variety of partitioning options whose proper use requires expertise and time. This feature makes it easy for all users to use partitioning in a way that is most suitable for their environments by giving intelligent and accurate advice on exactly how to partition a particular object for optimal performance.

See Also:Oracle Database Performance Tuning Guide for details

(3)SQL执行计划管理

从 10G 开始， ORACLE 就推出了一个新的数据库对象： sql profile. 当优化器生成 SQL 语句的执行计划时，会参考 PROFILE 来生成最佳的计划。事实上这为 ORACLE 的 SQL 计划管理埋下伏笔。到了 11G ， ORACLE 能够为某一 SQL 语句管理多个计划。当数据库有变动时，或者 SQL 语句产生新的计划时，新的执行计划不一定会立即采用。优化器或根据各种因素，采用一个最佳的执行计划，这确保了 SQL 语句性能的稳定性。

SQL Plan Management(rainny)

The SQL plan management feature enables the optimizer to maintain a history of execution plans for a SQL statement. Using the execution plan history, the optimizer is able to detect a new plan representing a plan change for a SQL statement. When the optimizer detects a new plan, it stores the new plan and marks it for performance evaluation and uses the old (currently known good) plan. The optimizer uses the new plan only after its performance is verified to be better than that of the old plan.

Execution plan change for a SQL statement can cause severe performance degradation on a system. Execution plan changes occur due to system changes such as a new optimizer version, refresh of optimizer statistics, and optimizer parameter changes. This new SQL plan management feature prevents performance regressions caused by execution plan changes.

(4)查询结果缓存

查询结果缓存能够大大提升 ORACLE SQL 查询的性能。当然，前提是你发布的是完全一样的 SQL 语句。这对于那些需反复运行多次的 SQL 语句的性能非常有益。

Query Result Cache(rainny)

A separate shared memory pool is now used for storing and retrieving cached results. Query retrieval from the query result cache is faster than rerunning the query. Frequently executed queries will see performance improvements when using the query result cache.

The new query result cache enables explicit caching of results in database memory. Subsequent queries using the cached results will experience significant performance improvements.

See Also:Oracle Database Performance Tuning Guide for details

(5)PL/SQL函数结果缓存

我们知道查询结果缓存 (query result cache) 技术使数据库 SQL 查询的性能获益，同样， 11G 新推出的 PL/SQL 函数结果缓存技术也可以提升 PL/SQL 程序的性能。

PL/SQL Function Result Cache(rainny)

New in Oracle Database 11g is the ability to mark a PL/SQL function to indicate that its result should be cached to allow lookup, rather than recalculation, on the next access when the same parameter values are called. This function result cache saves significant space and time. Oracle does this transparently using the input value as the lookup key. The cache is system-wide so that all distinct sessions invoking the function benefit. If the result for a given set of values changes, you can use constructs to invalidate the cache entry so that it is properly recalculated on the next access. This feature is especially useful when the function returns a value that is calculated from data selected from schema-level tables. For such uses, the invalidation constructs are simple and declarative.

Concurrent, multi-user applications that use this feature experience better response times. Applications that implement a session-private scheme consume significantly less memory by using this feature and, therefore, experience improved scalability.

See Also:Oracle Database PL/SQL Language Reference for details

(6)Invisible索引

Invisible 索引表示暂时让某一个索引不可见 ( 针对优化器不可见 ) ，这提供一个机制，让我们测试某个索引对 SQL 语句的性能是否有影响，从而评估是否有必要创建此索引。

Invisible Indexes

An invisible index is an alternative to making an index unusable or even to drop it. An invisible index is maintained for any DML operation but is not used by the optimizer unless you explicitly specify the index with a hint.

Applications often have to be modified without being able to bring the complete application offline. Create invisible indexes temporarily for specialized non-standard operations, such as online application upgrades, without affecting the behavior of any existing application. Furthermore, invisible indexes can be used to test the removal of an index without dropping it right away, thus enabling a grace period for testing in production environments.

See Also:Oracle Database Administrator's Guide for details

2.性能测试(performance test)

(1)数据库重演(Database replay)

我们经常碰到这种情况：当我们将开发数据转换为生产数据库，或对生产数据库进行了升级 ( 包括软件，硬件的升级 ) ，或对数据库的结构进行了变更，改变了一些数据库对象以后，数据库性能明显降低，或者出现了之前没有碰到过的问题 .

Database Replay 提供一种机制，用于在真正的变更数据库 ( 针对上面所列的变更事件 ) 之前，先对此次变更对数据库产生的影响进行评估，给出报告以标明潜在的问题，并且提供解决问题的建议。

Database Replay 这个新功能捕获实际的生产环境中的工作量，并将之部署到测试环境，以模拟客户的实际环境。通 Database Replay ，开发人员在自己的测试环境中就可以测试变更对数据库的影响，进而将问题解决在真正发生之前。

Database Replay （ rainny ）

Before making changes, such as hardware or software upgrades, companies typically conduct extensive testing to validate the changes. However, when the change is made on the production system, problems are often encountered because the testing was not performed on a realistic workload. Tools are available in the market to construct "synthetic workloads" involving multiple users. However, they are not able to adequately simulate the complexities of a real-world workload, such as interactions between concurrent activities and unpredictability of the workload profile.

The Database Replay feature addresses this need by enabling users to perform real-world testing by capturing the actual database workload on the production system and replaying it on the test system. It also provides analysis and reporting to highlight potential problems (for example, errors encountered and divergence in performance) and recommend ways to remedy the problems.

See Also:Oracle Database Performance Tuning Guide for details

(2)SQL性能分析器和SQL重演(SQL Replay)

这又是一项 PERFORMANCE TEST 方面的增强。感觉 11G 在性能优化和性能测试上花了不少功力。我们经常会遭受这样的困扰 : 当我们对数据库做了一些改变，比如对数据库进行了升级，改了一些实例参数，或增删了一些索引，原来运行好好的 SQL 语句性能明显下降。 11G 针对这种情况，推出了 SQL 性能分析器，它在你要做出改变之前，能够让你捕获你将要应用到的生产环境的真实工作量，如生产数据库，然后将这些捕获到的信息装载到你的测试环境，让你能够对你将要做出的改变对数据库性能的影响提前有一个分析和评估。事实上这就是 11G 的 SQL 重演功能。 Sql replay 和 DABASE REPLAY 的原理差不多，只不过 SQL REPLAY 只关注 SQL 语句。

SQL Performance Analyzer(rainny)

SQL performance regression is always a concern during system changes such as database upgrades, initialization parameter changes, and addition or dropping of indexes. The SQL Performance Analyzer feature alleviates this concern by providing an easy way to assess the impact of a change on the performance of SQL statements by comparing and contrasting their response times before and after the change. SQL Performance Analyzer allows you to capture the SQL workload from the source system, such as the production database, and to replay it on the test system where the change has been applied.

Any differences in response time of SQL statements, execution plan regressions, and so forth, are reported and precise recommendations on how to tune the under-performing SQL statements are provided.

See Also:Oracle Database Performance Tuning Guide for details

(3)SQL TEST CASE BUILDER

这个对于 QA 和开发人员都非常有用。我们经常会碰到这种情况，某些 SQL 在开发数据库上跑没有问题，但在客户的数据库出现问题。开发人员要在自己的数据库中重现这些问题，需要搭建很多环境，并且要模拟客户的操作。这些工作非常繁琐，并且客户数据库中的很多实际环境如工作量，各种软硬件配置等，并不是那么容易模拟的。到了 11G ， ORACLE 提供一个新的工具： SQL Test Case Builder ，专门用于重建一些测试案例。

SQL Test Case Builder(rainny)

SQL Test Case Builder is a PL/SQL package that gathers all of the information at the customer site that is needed to reproduce a problem on a different system.

Obtaining a reproducible test case is the single most important factor in the speed of incident resolution. The SQL Test Case Builder gathers as much information as possible related to a SQL incident and packages it in a way that allows the problem to be reproduced.

See Also:Oracle Database Performance Tuning Guide for details

六,SQL增强

1.只读表(Read-Only Tables)

在之前的版本中，如果对于某个表，要想让它变成 READ ONLY ，只能通过给某个 USER 只赋 SELECT 权限来实现，然而对于表的拥有者（ OWNER ） , 则始终是可读写的。在 11G ，这一问题得以解决。我们可以将表切换为 READ ONLY ，这样的话，连表的拥有者也不可以修改表中的数据了。

Read-Only Tables(rainny)

Oracle Database 11g introduces new ALTER TABLE syntax. For example:

ALTER TABLE <name> READ ONLY

and

ALTER TABLE <name> READ WRITE

The operating system sets the precedent to make a file read-only even for its owner. Earlier, a table could be made read-only (by granting only SELECT on it) to users other than the owner of the table. With this feature, the owner too can be prevented from doing unintended DML to a table.

2.PIVOT和UNPIVOT函数

11G 新增加的 PIVOT 函数用于将多个行的值转换为单行多个字段，即我们所说的行列转换。 UNPIVOT 是 PIVOT 的逆向函数，实现将列转换为行。这两个函数在商务智能 (Business Intelligence 简称 BI) 和数据仓库中比较有用。 SQL SERVER 2008 有提供对应的函数。

SQL PIVOT and UNPIVOT Operators(Rainny)

The PIVOT and UNPIVOT operators are extensions to the table expression in the FROM clause of a SELECT statement. PIVOT spreads values from multiple rows into multiple columns, aggregating data in the process. PIVOT is commonly used to create a result set with more columns and fewer rows than the source data. The PIVOT operator supports multiple pivot columns, multiple aggregates, wildcards, and aliasing. UNPIVOT moves data in the opposite direction from PIVOT. For each input row, UNPIVOT moves values from multiple columns into multiple output rows. The process creates a result set with fewer columns and more rows than the source data. UNPIVOT supports multiple unpivot columns, multiple measure columns, and aliasing.

PIVOT can create aggregated cross-tabular output that condenses many rows into a compact result set. For example, input data holding sales of 1 month per row can be pivoted into output holding 12 months per row, with each month in its own column. Another use of PIVOT is to combine multiple input rows into a single output row, enabling inter-row comparison without a table self-join. UNPIVOT reshapes data into a format useful for further relational operations. For example, if a source data set presents 12 months of sales values per row, UNPIVOT can reshape each source row into 12 output rows, each holding 1 month of sales data. The unpivoted results can, in turn, be manipulated with much simpler and more efficient SQL than required for the source data set.

See Also:Oracle Database Data Warehousing Guide for details

七,实用工具

1.SQL*Plus BLOB支持

在之前， SQL*PLUS 中不能够显示 BLOB 字段的内容，到了 11G ，这一问题得到解决，我们可以在 SQL*PLUS 中查询 BLOB 字段了。

SQL*Plus BLOB Support(rainny)

Queries in SQL*Plus now support BLOB columns.

This feature allows you to verify the contents of BLOB columns.

See Also:SQL*Plus User's Guide and Reference for details

2.数据库升级助手的增强

增加了 EXPRESS （快捷版）的升级选项：如果现在是使用 ORALCE 11G 的 EXRRESS 版，可以通过 DBUA 平稳升级到 ENTERPRISE 版。

Express Edition upgrade(rainny)

For single-instance databases, Oracle Database Upgrade Assistant configuration utility enables you to upgrade from Oracle Database Express Edition (Oracle Database XE) to Oracle Database 11g. The XE database files reside under the path ORACLE_BASE/oradata/XE. These files must be copied to a new location as the user may remove the XE Home after upgrade.

3.企业管理器的增强：

在 11G ，基于 WEB 的企业管理器功能变得更加全面。增加了很多新的接口和界面。

对等待事件的显示更加详细。

Wait Activity Detail Enhancement(rainny)

Until now, Oracle Enterprise Manager displayed the Wait Activity Drilldown detail in a chart format.

This feature provides more detailed Wait Activity information for the client, service, module, and action pages in Oracle Enterprise Manager.

See Also:Oracle Database 2 Day + Performance Tuning Guide for details

八,安全(Security)

1.表空间加密

这也是 11G 在数据库安全方面的增强。它得以让用户可以对重要的表空间的数据块进行加密处理。这是 ORACLE 虚拟私有数据库（ virtual private database 简称 VPD ）技术的增强。

Tablespace Encryption(rainny)

Tablespace encryption is an enhancement to the Oracle Advanced Security Transparent Data Encryption solution. Using tablespace encryption, customers can encrypt an entire tablespace, encrypting all data within the tablespace. When the database accesses the tablespace, the relevant data blocks are transparently decrypted for the application.

Transparent Data Encryption tablespace encryption provides an alternative to Transparent Data Encryption column encryption by enabling encryption of an entire tablespace. This eliminates the need for granular analysis of applications to determine which columns to encrypt, especially for applications with a large number of columns containing personally identifiable information (PII). Using tablespace encryption, customers can encrypt entire tables, eliminating the need to identify which columns contain personally identifiable information (PII). Customers who have small amounts of data to encrypt can continue to use the Transparent Data Encryption column encryption solution.

See Also:Oracle Database Advanced Security Administrator's Guide for details

OK, 仅从我们上面点到几个方面就可以看出， ORACLE 11G 在可管理性，可用性，可靠性等方面似乎都做得非常完美 (perfect), 不可否认， ORACLE 的开发团队对于其每一个新版本数据库都作出了辛勤的努力，但最后我还是要不忘提醒一下，对于一个新的软件产品来说 , 其第二个 Release 更有可能成为一个稳定的版本。 Oracle 11G 推出时间还不长，到目前为止还没有发布其第二个 release, 可说是比较年轻 . 况且对于新产品的 new feature list, 我们要带着审慎的态度来看待，有些新功能有可能花哩花哨，但实用性不大。用户需根据自己的实际情况及一定的测试再决定是否要采用 ORACLE 11G 或升级到 ORACLE 11G.

附录：

前面我们说过， ORACLE 的文档有将近 200 个，那么，要如何来阅读这些文档呢？下面是我的推荐：

入门级文档： 了解 ORACLE 体系结构和各种概念

Oracle Database Concepts

参考类文档： 下面的这些文档不需死记硬背，只需在用到时查字典一样的查看

Oracle Database Reference

Oracle Database SQL Language Reference

Oracle Database PL/SQL Language Reference

Oracle Database PL/SQL Packages and Types Reference

SQL*Plus User's Guide and Reference

Oracle Database Backup and Recovery Reference

Oracle Database Error Messages

需仔细阅读的文档：

Oracle Database Administrator's Guide

Oracle Database Backup and Recovery User's Guide

其它文档： oracle 所涉及到的技术领域非常庞大 , 当你碰到某一方面的问题时，你可以阅读那个技术领域具体的 guide 文档。举例，如果你碰到高级复制的问题，你可以参阅： Oracle Database Advanced Replication 和 Oracle Streams Replication Administrator's Guide

ORACLE 11G 在 2007 年 7 月 11 日发布，距现在有一年有余了，其最新版本是 ORACLE 11.1.0.6.0. 以下简称为 11G .

对于 ORACLE 数据库产品的改进和增强， ORACLE 11G 文档中用了：

8 个 ability 来描述其强大功能：

manageability,availability,reliability,scalability,diagnosability,usability ,interoperability,portability

7 个 simp 来形容其简单易用 (easy-to-use) ：

simple,simplification,simplify,simplifying,simplified,simply ,simpler

5 个 auto 来表达其管理的智能 (intelligence) 和自动化：

automate,automation,,automatic,automated,automatically

并且用一个比较有意思的词汇来定义其前瞻性数据库管理概念： proactive

一,管理(manageability)

1.自动化(automation)

(1)自动的内存管理

Automatic memory management(rainny)

(2)自动的健康检查

Automatic Health Monitoring(rainny)

See Also:Oracle Database Administrator's Guide for details

(3)自动地隔离损坏的回滚段

在 11G ，当某一回滚段发生损坏时， ORALCE 能自动地将这些回滚段隔离，从而避免后续的事务用到这些损坏的回滚段。

Automatic Quarantine of Corrupt Undo Segments(rainny)

This feature automatically quarantines corrupt undo segments and prevents future transactions from using the same undo segments.

This enhancement limits corruption propagation and thus limits the damage caused on the system.

2.管理工具

(1)企业管理器的增强：增加LOGMINER接口

Browser-Based Enterprise Manager Integrated Interface for LogMiner(rainny)

This new feature improves the manageability of LogMiner. The task-based work flows enable log mining and are integrated with Flashback Transactions.

二,可用性(availability)

1.高可用性 (high availability简称HA)

(1)DATAGUARD的增强

物理STANDBY数据库的实时查询功能

Real-Time Query Capability of Physical Standby Database(rainny)

It is now possible to query a physical standby database while Redo Apply is active.

See Also:

Oracle Data Guard Concepts and Administration for details

三,备份和恢复(Backup and Recovery)

1.闪回技术的增强

闪回事务

n 1_ 通过闪回查询 (flashback query) 来恢复被误删的数据 .doc

n 2_ 通过 flashback table 来将表闪回到某一个时间点 .doc

n 3_ 通过 flashback drop 来闪回被误删除的表 .doc

n 4_ 通过 flashback database 来将数据库闪回到某一个时间点 .doc

Flashback Transaction(rainny)

2.逻辑备份

数据泵

Compressed Dump File Sets(rainny)

Data Pump provides the ability to compress the metadata of a dump file set, which can shrink the dump files by 10-15%.

In this release, Data Pump adds the ability to compress the entire dump file set, including data and metadata.

This feature lets you specify compression for metadata, row data, or the entire dump file set.

四,数据库问题诊断(diagnosability)

1.灾难事件打包服务（IPS）

ADR 的作用好比飞机上的 “ 黑匣子 ” ，当数据库发生灾难时， ORACLE SUPPORT 可以通过 ADR 来找到发生问题的原因，从而提出解决方案。

Incident Packaging Service (IPS)(rainny)

See Also:Oracle Database Administrator's Guide for details

2.自动诊断信息存储库(ADR)

正如前面所说， ADR 好比就是 ORACLE 数据库中的 “ 黑匣子 ” ，此处不再赘述

Automatic Diagnostic Repository(rainny)

See Also:Oracle Database Administrator's Guide for details

3.DIAGNOSTIC_DEST参数取代background dump, user dump, and core dump参数

ORACLE_BASE and Diagnostic Destination configuration(rainny)

4.SUPPORT工作台

这又是一个 11G 提供的新工具，用于全面的查看各种数据库诊断信息。并将这些信息打包传送给 ORACLE SUPPORT 人员。

Support Workbench(rainny)

The Support Workbench significantly reduces problem resolution time for customers by providing a simple workflow interface to view and diagnose incident data and package it for Oracle Support.

See Also:Oracle Database 2 Day DBA for details

5.活动会话历史的增强

Enhanced Active Session History(rainny)

The Active Session History infrastructure has been enhanced to allow for improved database performance diagnosis and monitoring.

This feature includes enhancements to row source information for query progress monitoring and time model statistics.

See Also:Oracle Database Performance Tuning Guide for details

五,性能(performance)

1.性能优化(performance tuning)

(1)自动的SQL优化功能

事实上，从 10G 开始， ORACLE 就推出了顾问框架（ Advisory Framework ） . 顾问框架由 4 个组件所组成：

Automatic SQL Tuning with Self-Learning Capabilities(rainny)

This feature takes automatic SQL tuning to the next level by adding self-learning capabilities to it.

See Also:Oracle Database Performance Tuning Guide for details

(2)分区顾问

Partition Advisor(rainny)

The SQL Access Advisor has been enhanced to include advice on how to partition tables, materialized views, and indexes in order to improve performance of SQL statements.

See Also:Oracle Database Performance Tuning Guide for details

(3)SQL执行计划管理

SQL Plan Management(rainny)

(4)查询结果缓存

Query Result Cache(rainny)

The new query result cache enables explicit caching of results in database memory. Subsequent queries using the cached results will experience significant performance improvements.

See Also:Oracle Database Performance Tuning Guide for details

(5)PL/SQL函数结果缓存

我们知道查询结果缓存 (query result cache) 技术使数据库 SQL 查询的性能获益，同样， 11G 新推出的 PL/SQL 函数结果缓存技术也可以提升 PL/SQL 程序的性能。

PL/SQL Function Result Cache(rainny)

See Also:Oracle Database PL/SQL Language Reference for details

(6)Invisible索引

Invisible Indexes

See Also:Oracle Database Administrator's Guide for details

2.性能测试(performance test)

(1)数据库重演(Database replay)

Database Replay （ rainny ）

See Also:Oracle Database Performance Tuning Guide for details

(2)SQL性能分析器和SQL重演(SQL Replay)

SQL Performance Analyzer(rainny)

Any differences in response time of SQL statements, execution plan regressions, and so forth, are reported and precise recommendations on how to tune the under-performing SQL statements are provided.

See Also:Oracle Database Performance Tuning Guide for details

(3)SQL TEST CASE BUILDER

SQL Test Case Builder(rainny)

SQL Test Case Builder is a PL/SQL package that gathers all of the information at the customer site that is needed to reproduce a problem on a different system.

See Also:Oracle Database Performance Tuning Guide for details

六,SQL增强

1.只读表(Read-Only Tables)