Automatic Workload Repository (AWR) in 11g
Oracle 11g New Features Tips by Burleson
July 8, 2008
Oracle 11g SQL
New Features Tips
The AWR is covered in greater detail in other
chapters. However, it is especially important for RAC in Oracle
11g as an aggregator of statistics that are important for horizontal
scalability. AWR collects service level metrics and passes
these metrics to the overall Workload Management framework, allowing
Oracle to make proper suggestions for the cluster as a whole.
This usage should be equated to object level
statistics; without those statistics, Oracle cannot properly decide
the best path to use when running your queries. With RAC, Oracle
must maintain good statistics to decide the best path to use when
making connections to Oracle.
Services are the backbone behind the Workload
Management framework in Oracle 11g. A service is a server-side
component that acts as a connection target. This means that
client-side connections to the cluster can connect to a service,
which can then be configured across the cluster. For instance, in a
four node RAC cluster, a service can be configured to use nodes 1
and 2 as the primary connection nodes, node 3 as a backup node, and
node 4 as an unusable node. Connections to this service will use
the service rules as defined by the Remote DBA.
In addition, services could be used with the
Resource Manager, allowing a Remote DBA to decide the level of CPU
resources each connection will be allowed. In Oracle 11g, Services
work as the backend to the new Connection Load Balancing features.
Fast Application Notification (FAN)
Fast Application Notification is simply a series
of published events by Oracle Notification Service (ONS). ONS
publishes events as a RAC service, including UP and DOWN events in
the case of server failure. FAN is built into Oracle’s integrated
client protocols, such as JDBC, ODP.NET, and OCI, so that programs
written to connect with these protocols can subscribe to FAN events
and use them to make cluster-wide decisions. For example,
several programs can be run against a two node RAC cluster,
including batch processes. The batch applications can be programmed
to respond to a FAN DOWN event so that if a node fails, the batch
process pauses until the node comes back online.
Another feature, Fast Connection Failover (FCF),
allows clients to respond to FAN events with quick failover to other
nodes in the event of an outage.
Load Balancing Advisory (LBA)
The Load Balancing Advisory was introduced in
Oracle 10gR2. It monitors the load across clustered nodes and
calculates percentages of incoming load for services. Percentage
values are, for each instance, configured as part of a service and
are published via FAN events and put into the AWR. This advisory
can be used with supported connection pools to make intelligent
OCI Runtime Connection Load Balancing
Any Oracle 11.1 or higher client connecting to
an Oracle 10gR2 database or higher will have OCI Runtime Connection
Load Balancing enabled by default. OCI Session Pools will connect
to an Oracle Service, and the Service will provide event
notifications to the client containing information published by the
Load Balancing Advisory. To use this feature, Oracle services must
be configured with load balancing goals. Services, the Load
Balancing Advisory, AWR, and FAN come together to provide this
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.