Some of the named advisors are actual advisors.
Some of the advisors are management interfaces
to specific settings. How does one access these
advisors? The advisors and Checkers can be
reached via the Advisor Central link on the home
page of Database Control (look near the bottom
center of the home page).
At Advisor Central, links at the top of the page
will send the DBA to the top-level pages for the
selected topic. The advisors include:
-
ADDM – analyze current or past performance
-
SQL Advisors – SQL Access, SQL Tuning, and
SQL Repair Advisors
-
Memory Advisors – SGA and PGA advisors
-
Automatic Undo Management – Undo Advisor
(retention and tablespace sizing advice)
-
MTTR Advisor – advice on instance recovery,
media recovery and flash recovery
-
SQL Performance Analyzer – Optimizer Upgrade
Simulation, Parameter Change, Guided
Workflow, and existing tasks
-
Data Recovery Advisor – view and manage
failures
-
Segment Advisor – Automatic Segment Advisor
information, get advice on tablespaces or
schema objects
ADDM
ADDM, or the Automatic Database Diagnostic
Monitor, works in conjunction with the Automatic
Workload Repository, or AWR. The AWR stores
performance statistics, and these statistics are
then used for problem detection and self-tuning.
Much like STATSPACK,
which is essentially what it is but better,
snapshots are taken at regular intervals.
After the performance data has been collected,
ADDM analyzes it. AWR is the hunter-gatherer,
and ADDM is the thinker. The built-in
functionality includes automated tasks which run
in maintenance windows.
One well-known maintenance window is the Oracle
after-hours
GATHER_STATS_JOB. The name of the
job is
GATHER_STATS_JOB .
Referencing the Performance Tuning Guide, this
job is “created automatically at database
creation time and is managed by the Scheduler.
The Scheduler runs this job when the maintenance
window is opened. By default, the maintenance
window opens every night from 10 P.M. to 6 A.M.
and all day on weekends.”
The automated tasks infrastructure, known as
AutoTask, schedules routine maintenance tasks.
These tasks include steps needed to perform or
update optimizer statistics gathering, the
Automatic Segment Advisor, and the SQL Tuning
Advisor. A list of common problems ADDM can
detect include:
-
CPU bottlenecks
-
Poor connection management
-
Excessive parsing
-
Lock contention
-
I/O capacity
-
Undersizing of Oracle memory structures
-
High load SQL statements
-
High PL/SQL and Java time
-
High checkpoint load and causes
-
RAC-specific issues
Running an ADDM or AWR report (the difference in
the names is analogous to how users blur the
difference between database and instance at
times) can be accomplished via Database Control
click and point functionality
or by manually running one or more SQL scripts
located in
$ORACLE_HOME/rdbms/admin.
The Diagnostic Pack scripts, of which ADDM falls
under for licensing, can all be run as a
command-line API. Many will prompt for input
related to instance, dates, times, output format
and snapshot coverage. The script file names and
purposes are shown below. All scripts are .sql
files.
|
Name
|
Purpose
|
|
awrrpt
|
Creates the main AWR report, based on
STATSPACK
|
|
awrrpti
|
Comparison between snapshots
|
|
addmrtp
|
Runs ADDM analysis on pair of AWR
snapshots
|
|
addmrpti
|
Same as above, but used for RAC
instances
|
|
ashrpt
|
Runs the Active Session History report,
calls ashrpti
|
|
ashrpti
|
Worker script that supports ashrpt
|
|
awrddrpt
|
Runs Workload Respository Compare
Periods report
|
|
awrddrpi
|
Worker script that supports awrddrpt
|
|
awrsqrpt
|
Runs Workload for a particular SQL
statement
|
|
awrsqrpi
|
Worker script that supports awrsqrpt
|
|
awrextr
|
Extracts AWR info, for use with/by
Oracle Support
|
|
awrload
|
Loads AWR data, for use with/by Oracle
Support
|
|
awrinfo
|
Outputs general AWR info
|
|
spawrrac
|
Server Performance RAC report
|
The pattern on the scripts is that a script
ending in -rpt typically defaults to the current
DBID and instance, collects information via
prompts, and passes that input to its
corresponding -rpi script. The -rpi scripts can
be run directly, but one will have to sort out
the required/expected parameters. Some of the
scripts are for overall and some are for
comparison between snapshots or the repository.
Many of the scripts also expect to be run as the
SYS user.
