Oracle 11g features for DBA's

Oracle 11g DBA new features

• Enhanced ILM - Information Lifecycle Management (ILM) has been around for decades, but Oracle has made a push to codify the approach in 11g.  Read more about Oracle 11g ILM here:  Inside Oracle 11g ILM - Information lifecycle management.
   
• Table-level control of CBO statistics refresh threshold - (source Lutz Hartmann) When Oracle automatically enables statistics collection, the default "staleness" threshold of 10% can now be changed with the dbms_stats.set_table_prefs procedure:

exec dbms_stats.set_table_prefs(’HR’, EMPS’, ‘STALE_PERCENT’, ‘15′)

 There are three new arguments to the set_table_prefs procedure, designed to allow the DBA more control over the freshness of their statistics:

stale_percent - overrides the one-size-fits-all value of 10%

incremental - Incremental statistics gathering for partitions

publish - Allows the DBA to test new statistics before publishing them to the data dictionary

This is an important 11g new feature because the DBA can now control the quality of optimizer statistics at the table level, thereby improving the behavior of the SQL optimizer to always choose the “best” execution plan for any query.

• File Group Repository - Oracle introduced an exciting new feature in 10gr2 dubbed the Oracle File Group Repository (FGR).  The FGR allows the DBA to define a logically-related group of files and build a version control infrastructure.  The working of the Oracle file group repository were created to support Oracle Streams, and they mimic the functionality of an IBM mainframe generation data group (GDG), in that you can specify relative incarnations of the file sets (e.g. generation 0, generation -3).
   
• 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. Oracle is moving toward more workload-based optimization, adjusting SQL execution plans based on existing server-side stress.  This can be very useful for Oracle regression testing. 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
   
• 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.  Also see Oracle 11g function-based virtual columns.  Source: Source: Mark Rittman
   
• REF partitioning - The 11g REF partitioning allows you to partition a table based on the values of columns within other tables. 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. 
   
• 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  The ADR can be accessed via OEM or a command-line interface.
   
• Hangman Utility – The Hang Manager (hangman) utility is a new 11g tool to detect database bottlenecks.  An extension of the dba_waiters and dba_blockers views, the hangman tables have a “hang chain” that allow the DBA to find the source of “hangs”, such as the “deadly embrace” where mutually blocking locks or latches hang a process.   In 11g, the hangman utility is installed on all RAC nodes by default, allowing for easier inter-node hang diagnostics.
   
• Health Monitor (HM) utility - The Health Monitor utility is an automation of the dbms_repair corruption detection utility.  When a corruption-like problem happens, the HR utility will checks for possible corruption within database blocks, redo log blocks, undo segments, or dictionary table blocks.
   
• 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.
   
• Invisible indexes - Rich Niemiec claims that the new 11g "invisible indexes" are a great new feature.  It appears that the invisible indexes will still exist, that they can just be marked as "invisible" so that they cannot be considered by the SQL optimizer. With the overhead of maintaining the index intact, I don't see why this is very useful.  Also see 11g Function-based columns.