ORACLE 11g新特性中文版

Oracle 11g 新特性 摘自ITPUB的love_zz的文章 http://www.itpub.net/712880.html 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磁盤組。   ·          ADDM   ADDM 在10g被引入。11g中，ADDM不僅可以給單個執行個體建議，還可以對整個RAC（即資料庫級别）給出建議。另外，還可以将一些訓示（directive）加入ADDM，使之忽略一些你不關心的資訊。   ·          AWR 基線（AWR Baselines）   AWR 基線得到了擴充。可以為一些其他使用到的特性自動建立基線。預設會建立周基線。   2.    PLSQL 部分   ·          結果集緩存（Result Set Caching）   這一特性能大大提高很多程式的性能。在一些MIS系統或者OLAP系統中，需要使用到很多"select count(*)"這樣的查詢。在之前，我們如果要提高這樣的查詢的性能，可能需要使用物化視圖或者查詢重寫的技術。在11g，我們就隻需要加一個的提示就可以将結果集緩存住，這樣就能大大提高查詢性能。當然，在這種情況下，我們可能還要關心另外一個問題：完整性。因為在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_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.    其他部分   ·          增強的壓縮技術   可以最多壓縮2/3的空間。   ·          高速推進技術   可以大大提高對檔案系統的資料讀取速度。   ·          增強了DATA Guard   可以建立standby資料庫的快照，用于測試。結合資料庫重演技術，可以實作模拟生成系統負載的壓力測試   ·          線上應用更新   也就是熱更新檔——安裝更新或打更新檔不需要重新開機資料庫   ·          資料庫修複建議器   可以在錯誤診斷和解決方案實施過程中指導DBA。   ·          邏輯對象分區   可以對邏輯對象進行分區，并且可以自動建立分區以友善管理超大資料庫（Very Large Databases VLDBs）   ·          新的高性能的LOB基礎結構   ·          新的PHP驅動 Oracle 11g new Features   At 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 features

We 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: 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 - 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 – 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 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 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. 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
• 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 11g OCI-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"), and similarly, 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 query mechanisms for XML including XQuery and SQL XML, emerging standards for querying 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, and maintaining 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 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
• 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 will you to specify trigger firing order.

Oracle11g High Availability & RAC new features Oracle 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.  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.
• 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 further optimized. 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 PeopleSoft Corp. -- 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 of Siebel 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 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, 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: 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

• The 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 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 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 database version 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 ref cursor can become a DBMS_SQL cursor and vice versa. DBMS_SQL now supprts user 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 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.

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.
• 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. Currently, they are working to a capture performance overhead of 5%, so you could conceivably capture real production workloads. Source: Dr. Tim Hall