SQL*Plus
SQL*Plus also has useful capabilities that ease RAC management.
These include both
v$ views and built in tools.
v$ and gv$ Views
Any Oracle instance has
v$ views, which read from
x$ views, which can read from a combination of base
tables, RAM areas, processes, or the control file.
gv$ views are just the same, except they span across all
nodes of the cluster.
Each
gv$ view has an INST_ID column that notes the instance
on which the record belongs.
v$instance
is a very commonly used view that shows quick details about the Oracle
instance.
gv$instance
shows status on all nodes of the cluster and is a great way to know
which nodes are up or down!
v$sql_plan
and
v$sqlarea
can be used to find queries on a variety of criteria: join methods,
index usage, buffer gets or disk reads and more.
Using
gv$sql_plan
and
gv$sqlarea
, it is possible to find out which nodes certain SQL has affected.
Another useful view is
gv$session.
This view gives information about all connected sessions across
every Oracle node. When
coupled with
gv$session_wait
, it is possible to find specific details about the wait interface by
session. With
gv$session,
gv$session_wait, and
gv$sqlarea
it is possible to really delve deep into object usage and bottlenecks.
Tip: If
the database was created manually with the CREATE DATABASE
command, one will have to create the cluster views using the
CATCLUST.SQL script.
Run this script as SYSDBA.
It is located in $ORACLE_HOME/rdbms/admin.
In addition to the
gv$ views, Oracle 11g offers three built-in statistics
and analysis tools that are RAC aware:
AWR, ADDM, and ASH.
Automatic Workload Repository (AWR)
Using Enterprise Manager or the
awrrpt.sql script, a DBA can generate an AWR report that
contains specific RAC information.
AWR is automatically configured to take snapshots of each
instance in the cluster.
Using this snapshot information, a report can be generated that gives
instance-specific information along with cluster-wide information.
For instance, using AWR in a RAC environment, it is possible to easily
find the ratio of local vs. cache fusion block gets for a given
snapshot period. This
kind of data can help a DBA diagnose performance or stability issues.
Automatic Database Diagnostic Monitor (ADDM)
ADDM was introduced in Oracle 10g.
It takes the AWR statistics a step further by analyzing the
data and creating a report of findings; plain English solutions to
system issues.
In Oracle 11g, ADDM has been extended to include RAC.
As such, it provides information on the entire cluster
including latency on the cluster interconnect, global cache hot
blocks, and other RAC specific topics that span multiple nodes. ADDM
can still create single instance reports or database wide reports
which can span all instances accessing a single RAC database.
Active Session History (ASH)
Active Session History (ASH) contains data specific to active sessions
connected to the Oracle instances of a cluster.
Every session that performs work will have statistics, wait
events, and work details saved into the ASH framework. From this
framework ASH reports can be generated, showing session performance
for a given time period.
These reports are highly beneficial with tuning wait events that
happen during a specific time.
In Oracle 11g RAC, ASH reports contain specific RAC information in the
form of Top Cluster Events
and Top Remote Instance
sections.
The Top Cluster Events
section of an ASH report will contain all events during the given
timeframe that are specific to Oracle RAC.
Tip: If
cluster wait events contribute heavily to overall system wait,
this section is useful to drill down into the specific cluster
waits causing the overall issue.
Sometimes it is good to know which instance in a cluster is waiting
the longest during a specific period.
The Top Remote Instance
section breaks down cluster wait events by instance number which makes
it easy to understand which nodes performed specific amounts of work.
SQL*Plus Conclusion
There are many tools that can be used right from the SQL* prompt in a
RAC environment. It is
worth noting that most of these tools can also be accessed through
Enterprise Manager for those that do not like to work at the command
prompt. AWR, ADDM, and
ASH specifically are highly supported through Enterprise Manager in
the Performance tab and Advisor Central.
