oracle11g新特性(精)

Oracle 11g 新特性Oracle 11g现在已经开始进行beta测试，预计在2007年底要正式推出。

和她以前其他产品一样，新一代的oracle又将增加很多激动人心的新特性。

下面介绍一些11g 的新特性。

1.数据库管理部分•数据库重演(Database Replay)这一特性可以捕捉整个数据的负载，并且传递到一个从备份或者standby数据库中创建的测试数据库上，然后重演负责以测试系统调优后的效果。

•SQL重演(SQL Replay)和前一特性类似。

但是只是捕捉SQL负载部分，而不是全部负载。

•计划管理（Plan Management）这一特性允许你将某一特定语句的查询计划固定下来，无论统计数据变化还是数据库版本变化都不会改变她的查询计划。

•自动诊断知识库（Automatic Diagnostic Repository ADR）当Oracle探测到重要错误时，会自动创纪一个事件（incident），并且捕捉到和这一事件相关的信息，同时自动进行数据库健康检查并通知DBA。

此外，这些信息还可以打包发送给Oracle支持团队。

•事件打包服务(Incident Packaging Service)如果你需要进一步测试或者保留相关信息，这一特性可以将与某一事件相关的信息打包。

并且你还可以将打包信息发给oracle支持团队。

•基于特性打补丁（Feature Based Patching）在打补丁包时，这一特性可以使你很容易区分出补丁包中的那些特性是你正在使用而必须打的。

企业管理器（EM）使你能订阅一个基于特性的补丁服务，因此企业管理器可以自动扫描那些你正在使用的特性有补丁可以打。

•自动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磁盘组。

•ADDMADDM在10g被引入。

11g中，ADDM不仅可以给单个实例建议，还可以对整个RAC（即数据库级别）给出建议。

另外，还可以将一些指示（directive）加入ADDM，使之忽略一些你不关心的信息。

•AWR 基线（AWR Baselines）AWR基线得到了扩展。

可以为一些其他使用到的特性自动创建基线。

默认会创建周基线。

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;•对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_Warning11g中。

可以通过设置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.其他部分•增强的压缩技术可以最多压缩2/3的空间。

•高速推进技术可以大大提高对文件系统的数据读取速度。

•增强了DATA Guard可以创建standby数据库的快照，用于测试。

结合数据库重演技术，可以实现模拟生成系统负载的压力测试•在线应用升级也就是热补丁——安装升级或打补丁不需要重启数据库•数据库修复建议器可以在错误诊断和解决方案实施过程中指导DBA。

•逻辑对象分区可以对逻辑对象进行分区，并且可以自动创建分区以方便管理超大数据库（Very Large Databases VLDBs）•新的高性能的LOB基础结构•新的PHP驱动Oracle 11g new FeaturesAt Oracle Openworld 2006, Oracle announced some exciting new features of Oracle 11g, and they promise 482 new Oracle11g features. Here is my list which I continually update as new Oracle11g new feature information is released:•11g SQL new features•11g language support new features•11g PL/SQL New Features•11g DBA features•11g RAC new features & enhancements•11g performance features•11g security new features•11g Enterprise Manager new featuresWe are also seeing upcoming books on Oracle 11g new features. Let's get started with the Oracle11g new features for general database administration:Oracle 11g DBA new features•Interval partitioning for tables - This is a new 11g partitioning scheme that automatically creates time-based partitions as new data is added. Source: MarkRittman This is a marvelous one ! You can now partition by date, one partition permonth 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 - Staring in Oracle 10g release 2, Oracle JDBC and provide connection pool load balancing facilities through integration withth e new “load balancing advisory” tool.This replaces the more-cumbersomelistener-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 foroptimal load balancing. Shared disk storage resources can alternatively be assignedto individual databases and easily moved from one database to another as processing requirements change.•Data Guard Load Balancing– 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: LewisCunningham•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 forregular OLTP work. Also supports column add/drop. Mark Rittman•Faster DML triggers - 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 - Kevin Closson has some great notes on Oracle 11g improvement in Networked Attached Storage(NAS). "I’ve alreadyblogged that 11g “might” have an Oracle-provided NFS client. Why is this? It’sbecause 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] sessioncreation. 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 ofcreating 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 theworkload in prod and apply it in development. Source: Laurent Schneider •Scalability Enhancements - The features in 11g focused on scalability andperformance can be grouped into four areas: Scalable execution, scalable storage, scalable availability and scalable management. Mark Rittman•Scalable execution - Scalable execution 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 11gOCI-based clients. 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"), andsimilarly, virtual indexes that are based on functions REF partitioning, allowing you to partition a table based on the partition scheme of another. Allows you to partition an order_items table based off of the order_date column in an orders table. Source: Source: Mark Rittman• A "super" object-oriented DDL keyword - 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 - Starting in 11g, you can store XML either as a CLOB or a binary data type, adding flexibility. Oracle11g will support querymechanisms for XML including XQuery and SQL XML, emerging standards forquerying XML data stored inside tables.•Data Guard supports "Flashback Standby".•New Trigger features - A new type of "compound" trigger will have sections for BEFORE, ROW and AFTER processing, very helpful for avoiding errors, andmaintaining states between each section.•Partitioning - partitioning by logical object and automated partition creation. •LOB's - New high-performance LOB features.•Automatic Diagnostic Repository (ADR) - When critical errors are detected, they automatically create an “incident”. Information relating to the incident isautomatically captured, the DBA is notified and certain health checks are runautomatically. This information can be packaged to be sent to Oracle support (seefollowing). Source: Dr. Tim Hall•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 wholepackage 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 thefeatures you are using. EM will allow you to subscribe to a feature based patchingservice, so EM automatically scans for available patches for the features you areusing. 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 will you to specify trigger firing order.Oracle11g High Availability & RAC new featuresOracle continues to enhanced 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. Ellisonfirst promised this feature in 2002, and it is a very challenging and complex 11g newfeature.•Oracle RAC load balancing advisor - Starting in 10gr2 we see a RAC load balancing advisor utility.•ADDM for RAC -•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 furtheroptimized. Source: Mark Rittman•Oracle 11g RAC Grid provisioning - Oracle 11g OEM has have easy server blade installs where a binary footprint is tar'ed to the server blade and configured, without a cumbersome install process.•Hot patching - Zero downtime patch application.•Standby snapshot - For the purpose of regression testing.•Quick Fault Resolution - Automatic capture of diagnostics (dumps) for a fault.OEM - Enterprise Manager Oracle 11g new features:•Interfaces to Applications - This note says "Oracle says that extending Enterprise Manager's capabilities are part of the firm's promise to seamlessly integrate the spoils of its many acquisitions -- including the purchases of Siebel Systems and PeopleSoftCorp. -- into a single platform."•OEM Easy de-install - This will uninstall both successful and unsuccessful Oracle installs.•Database repair wizard - A GUI to guide beginners through the steps to diagnose and repair Oracle issues.•Better OEM Grid tools - Another new Oracle11g feature may be improved RAC and Grid monitoring, especially on the cache fusion interconnect.•Enterprise Manager interfaces to foreign applications - This note says "Oracle says that extending Enterprise Manager's capabilities are part of the firm's promise to seamlessly integrate the spoils of its many acquisitions -- including the purchases ofSiebel Systems and PeopleSoft Corp. -- into a single platform."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 thatyou 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. Thisapparently 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 improveperformance. No change to your code is required. Now you have no reason for notmaking everything a subroutine! Source: Lewis CunninghamOracle 11g PL/SQL New Features•PL/SQL "continue" keyword - This will allow a C-Like continue in a loop, to bypass any "else" Boolean conditions. A nasty PL/SQL GOTO is no longer required to exit a Boolean within a loop.•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: LewisCunningham•Improved PL/SQL stored procedure invalidation mechanism - A new 11g features will be fine grained dependency tracking, reducing the number of objects whichbecome 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 callinga stored procedure from SQL.Oracle 11g SQL New Features•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 both SQL and PL/SQL results for super-fast subsequent retrieval.•XML SQL queries - Oracle11g will support query mechanisms for XML including XQuery and SQL XML, emerging standards for querying XML data stored insidetables.•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 statisticscreation Mark Rittman•SQL execution Plan Management - Oracle 11g SQL will allow you to fix execution plans (explain plan) for specific statements, regardless of statistics or databaseversion changes. Source: Dr. Tim Hall•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 refcursor can become a DBMS_SQL cursor and vice versa. DBMS_SQL now supprtsuser defined types and bulk operations. Source: Lewis Cunningham•Fully Automatic SQL Tuning - The 10g automatic tuning advisor makes tuning suggestions in the form of SQL profiles that will improve performance. You can tell11g to automatically apply SQL profiles for statements where the suggested profilegive 3-times better performance that the existing statement. The performancecomparisons are done by a new administrative task during a user-specifiedmaintenance window.•Improved SQL Access Advisor - The 11g SQL Access Advisor gives partitioning advice, including advice on the new interval partitioning. Interval partitioning is anautomated version of range partitioning, where new equally-sized partitions areautomatically created when needed. Both range and interval partitions can exist for asingle table, and range partitioned tables can be converted to interval partitionedtables.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 betuned automatically by setting one parameter. You literally tell Oracle how muchmemory it has and it determines how much to use for PGA, SGA and OS Processes.Maximum and minimum thresholds can be set.•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 andare 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. Ahidden 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 bydefault.•Adaptive Metric Baselines - Notification thresholds in 10g were based on a fixed point. In 11g, notification thresholds can be associated with a baseline, so thenotification 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, withcompression, 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 remainingtoday, protecting against insider threat and meeting regulatory compliancerequirements.•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 aredependant 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. Currently, they areworking to a capture performance overhead of 5%, so you could conceivably capture real production workloads. Source: Dr. Tim Hall。