Inside 11g New Remote DBA Features
Oracle 11g New Features Tips by Burleson
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
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
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
NOTE: Rampant author Laurent Schneider has some additional insight
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
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.