Oracle 11g Important new Features

Following are some important Oracle database 11g New features:

AMM - Oracle automated memory management (enhanced in 11g).

ASM - Oracle automated storage management (enhanced in 11g).

Oracle SQL*Access advisor - This tool makes intelligent recommendations for "missing" indexes and materialized views

Oracle 11g fully-automated SQL tuning - The DBA defines a SQL workload, and Oracle runs them on the live system, automatically optimizing the SQL via SQL profiles.

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.

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.

Automated Storage Load balancing - Oracle’s Automatic Storage Management (ASM) 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.

Automatic Diagnostic Repository - When critical errors are detected, Oracle automatically creates an “incident” ticket, notifying the DBA instantly.

Enhanced ILM - Information Lifecycle Management (ILM) has been around for decades, but Oracle has made a push to codify the approach in 11g.

Table-level control of CBO statistics refresh threshold :

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').

Three New Arguments :
* 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.

• 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.

• 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).

• 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.

• 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.

• 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.

• 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 - The 11g REF partitioning allows you to partition a table based on the values of columns within other tables.

• 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 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.

• 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. 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 where by 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 10g release 2 we see a RAC load balancing advisor utility. Oracle says that the 11g RAC load balancing advisor is only available with clients which use .NET, ODBC, or the Oracle Call Interface (OCI).

• ADDM for RAC - Oracle will incorporate RAC into the automatic database diagnostic monitor, for cross-node advisories.

• Interval Partitioning - 11g "interval Partitioning makes it easier to manage partitions: "Wouldn't it be nice if you could just tell Oracle you wanted to partition every month and it would create the partitions for you? That is exactly what interval partitioning does.

Here is an example:

create table selling_stuff_daily
( prod_id number not null,
cust_id number not null ,
sale_dt date not null,
qty_sold number(3) not null ,
unit_sale_pr number(10,2) not null ,
total_sale_pr number(10,2) not null ,
total_disc number(10,2) not null)
partition by range (sale_dt)
interval (numtoyminterval(1,'MONTH'))
( partition p_before_1_aug_2007 values
less than (to_date('01-08-2007','dd-mm-yyyy')));

Note the interval keyword. This defines the interval that you want each partition to represent. In this case, Oracle will create the next partition for dates less than 02-08-2007 when the first record that belongs in that partition is created."

• ADR command-line tool - The Oracle Automatic Diagnostic repository (ADR) has a new command-line interface dubbed ADRCI, the ADR Command Interface.
ADRCI can be used to access the 11g alert log:

$adrci
adrci> set editor vi
adrci> show alert ( it will open alert in vi editor )
adrci> show alert -tail ( Similar to Unix tail command )
adrci> show alert -tail 200 ( Similar to Unix Command tail -200 )
adrci> show alert -tail -f ( Similar to Unix command tail -f )

To list all the "ORA-" error run following command
adrci> show alert -P "MESSAGE_TEXT LIKE '%ORA-%'"

• 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.

• Oracle 11g RAC Grid provisioning - The Oracle grid control provisioning pack allows you to "blow-out" a RAC node without the time-consuming install, using a pre-installed "footprint". 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.

• Data Guard - Standby snapshot - The new standby snapshot feature allows you to encapsulate a snapshot for regression testing. You can collect a standby snapshot and move it into your QA database, ensuring that your regression test uses real production data.

• Quick Fault Resolution - Automatic capture of diagnostics (dumps) for a fault.