查看帖子 软件研发|项目外包|人才外包|软件技术咨询培训
在线客服
　　　　　　　
版区名称：数据库　　　　　　　版主： 林飞 快速返回
发言人：
Rainny（ 在线 ）
等级：精灵

注册时间：2009年2月13日
文章主题： ORACLE 11G新特性2
作者:Rainny
日期：2009-02-10

五,性能(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.
See Also:Oracle Database Performance Tuning Guide and Oracle Database PL/SQL Packages and Types Reference for details
(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
————————————————
 
快速返回
回复主题：
回复内容：