Oracle 11g new Features[@more@]
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:
Let's get started with the Oracle11g new features for general database administration:
Interval partitioning for tables - This is a new 11g partitioning scheme that automatically creates time-based partitions as new data is added. Source: 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 table Data Type "simple_integer" - A new 11g datatype dubbed simple_integer is introduced. The simgle_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.
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 (NAS). "I抳e already blogged that 11g 搈ight?have an Oracle-provided NFS client. Why is this? It抯 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.
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.
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.
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:
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 搃ncident? 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:
Enhanced Read only tables -
Table trigger firing order - Oracle 11g PL/SQL will you to specify trigger firing order.
Oracle continues to enhanced Real Application Clusters in Oracle11g and we see some exciting new features in RAC manageability and enhanced performance:
PHP - Improved PHP driver for Oracle.
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).
Compilers - Improved native Java & PL/SQL compilers.
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
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.
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 and Java - 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 and Java code, and a 10%-30% boost for code containing SQL.
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
Enhanced PL/SQL warnings - The 11g PL/SQL compiler will issue a warning for a "when others" with no raise.
Named notation - Named notation is now supported when calling a stored procedure from SQL.
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. 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.
- 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:
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 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