Inside 11g New Remote DBA Features Oracle 11g New Features Tips by Burleson Consulting July 15, 2008

Oracle 11g SQL New Features Tips

At Oracle Openworld 2006, Oracle announced some exciting new features of Oracle 11g, and they promise 482 new Oracle 11g features.  Here is a list of some of the new Oracle 11g features:

• 11g SQL new features

• 11g language support new features

• 11g PL/SQL new features

• 11g Remote DBA features

• 11g RAC new features & enhancements

• 11g performance features

• 11g security new features

• 11g Enterprise Manager new features

• 11g RMAN features

• 11g data mining

In this chapter, the new features in Oracle Database 11g that are the most important to the Remote DBA and developer staff will be covered in more detail.

Oracle 11g Remote DBA New Features

Partitioning in Oracle has played a key role in the support of very large databases and data warehousing requirements.  One of the shortcomings of its use is that partition management has traditionally been assigned as a routine Remote DBA task.  Oracle 11g has introduced a new partitioning enhancement called Interval Partitioning that allows for the automatic creation of partitions based on a predefined interval of the partition key.  This new feature is an extension to range partitioning and will likely prove to be a godsend for many Remote DBAs whom are responsible for the manual creation of new range partitions.

System Partitioning is a new feature that allows application level control for partitioning a table and indexes.  The table or index is defined with partitions but does not contain a partition key.  All operations on system partitioned objects need to explicitly state the partition.

When tables contain a clear parent-child relationship, reference partitioning allows the partitioning scheme of the child table to be inherited from the parent table based on a referential constraint.  This eliminates the need to include duplicate columns in the child table.  Since partition operations on the parent table automatically cascade to the child table, it also removes the need to manage partitions for the child table.

When the optimal partition key is derived from the values of other columns, Virtual Column-Based Partitioning supports partitioning based on that expression (virtual column).  This eliminates the need for inefficient workarounds to achieve the same functionality such as including an additional physical column in the table and using triggers or additional application code to populate the additional column. 

Composite Partitioning Enhancements - Prior to 11g, composite partitioning was limited to range-list and range-hash.  With this release, all composite partitioning options are now available including range-range, list-range, list-list, and list-hash. These partitioning strategies are also accessible with interval and virtual column-based partitioning.  Also beginning with Oracle 11g, the performance of partition pruning has been enhanced by the use of bloom filtering instead of subquery pruning and this is an example of Enhanced Partition Pruning Capabilities.  Partition pruning is now activated for any join involving a partitioned object.

Information lifecycle management (ILM) - has been around for decades and has become a more important issue as the volume of data has historically grown with each passing year.  Most companies regard their data storage as one of the most valuable assets in an organization.  Oracle 11g is an ideal solution for enhanced ILM since the Oracle database is able to store all different data formats in a single location.  This is advantageous since having all of the data in one database allows for simple implementation of an organization’s ILM policy as well as a significant cost savings.

To meet the growing needs of organizations for better data retention policies, Oracle 11g features the ILM Assistant, a GUI designed to help users determine the best ILM policies.  This GUI can help determine when it is appropriate to purge or move qualifying data to less expensive storage devices.   The 11g ILM assistant is also capable of simulating different data storage models to help the user determine the most appropriate ILM solution.  By leveraging Oracle’s existing architecture with new data management features in Oracle 11g, such as enhanced partitioning and advanced data compression, the ILM assistant provides a simple tool for the management of multi-tiered data. 

Invisible indexes - is a new 11g feature that allows an index to be maintained by the database but ignored by the optimizer unless explicitly specified.  Invisible indexes are useful for testing the removal of an index and for online application upgrades. Another new feature, improved table compression, has been useful for Remote DBAs in a data warehousing environment where bulk loads are common.  This feature offers significant savings in disk storage, I/O, and redo.  Oracle 11g has enhanced this feature for use in OLTP environments by introducing the option to use compression for all DML operations including inserts, updates, and deletes. 

Improved NFS Performance/Management - is a new method Oracle 11g has introduced for accessing NFS V3 servers directly using an internal “Direct NFS Client” as part of the Oracle Database kernel.  This improves performance and manageability by allowing Oracle-specific optimizations to be utilized. The direct NFS client eliminates the dependency on the operating system cache and allows for asynchronous I/O.  Additionally, it removes the need to manually configure and tune most of the NFS client parameters.

Database Resident Connection Pooling (DRCP) - Oracle 11g has introduced Database Resident Connection Pooling (DRCP) to support server-side connection pooling.  Prior to this feature, only multithreaded applications were eligible to take advantage of session sharing capability.  With DRCP, different application processes can share sessions on the same machine and also across a multitude of machines.  This feature greatly increases the scalability of applications that cannot be deployed as multithreaded, such as PHP applications.  This feature also benefits multi-threaded applications by relieving the overhead of maintaining a persistent connection to the database during idle periods. 

Oracle Call Interface (OCI) Consistent Client Cache - is a new caching feature that is complementary to the server result cache. The client result cache allows OCI clients to fetch result sets directly from a cache stored in the client’s process memory instead of having the server execute queries repeatedly.  This feature is enabled at the database level and since it is an inherent mechanism, it is transparently available to all OCI-based clients without requiring any changes to the application.  This can provide significant improvements in response times, scalability, and the consumption of database resources. 

Oracle 11g also introduces a new feature that allows virtual columns to be used in database tables.  A virtual column is a column that is actually an expression stored in the table’s metadata, such as “create table v1 (d1 number, d2 number, d3 as (d1+d2) virtual)” where column d3 appears to be a normal column in the table v1 but does not use disk space since it exists only in metadata.  Virtual columns can be indexed, used as a partitioning key, and can contain optimizer statistics. 

Prior to 11g, XML storage was limited to being either CLOB (unstructured) or schema-based (structured).  CLOB storage consumes large amounts of disk space and has poor relational data access.  Schema-based storage is efficient for disk space and relational access but requires substantial overhead for schema registration, schema changes, and file ingestion. Oracle 11g introduces a new storage option called Binary XML that combines the benefits from both CLOB and schema-based storage.  Binary XML offers fast file ingestion, efficient disk space and relational access, and ease of maintenance for schema changes.

In order to improve the process of receiving help from Oracle Support, Oracle 11g introduces a new feature called Incident Package Service (IPS) that provides a tool to extract information about incidents (exceptions) from the Automatic Diagnostic Repository (ADR).  This utility can be used to gather and submit all of the information about an incident to Oracle support.  IPS is available from both a command line utility and the Enterprise Manager.

NOTE: Rampant author Laurent Schneider has some additional insight into creating an Oracle Automatic Diagnostic Repository (ADR). 

In some situations, it is necessary to restrict changes to a table without exception.  Prior to 11g, a read-only table could be achieved by creating a table under a restricted account and granting select privileges to the appropriate users.  Using this method, it is still possible for the owner of the table to unintentionally modify the table.  11g has introduced a much simpler method for enabling read only tables called Enhanced Read Only tables that protects the table from unintentional DML by the table’s owner.  

 

This is an excerpt from the new book Oracle 11g New Features: Expert Guide to the Important New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain, Brian Carr. You can buy it direct from the publisher for 30% off.