Introduction
For those of
you who’ve used the longstanding statspack, you’ll find a lot that is familiar
with AWR. and for those new to this method of performance tuning, you’ll
be happy to learn about all the additions that make AWR better, and easier to
work with.
Improvements Over Statspack
The old statspack
had to be installed manually. Although not a complicated setup, having
AWR already there makes it that much easier to work with. Also, statspack
snapshots tended to be a bit of a load on the server, whereas AWR offloads the
work to special processes, so it affects performance less. In addition,
AWR reports are much easier to dig through, and AWR with ADDM supports alerts
as well. Statspack couldn’t be easily managed via a GUI, whereas with AWR
you can configure, and then view reports through OEM.
AWR
continues to use snapshots to collect data on your running database, and you
can still build reports via SQL script. With AWR, you’ll also be able to
use a PL/SQL package or OEM to generate and display your reports–so you have
more flexibility there as well.
Capturing Snapshots
The default
out-of-the-box configuration is set to half hour intervals and seven days of
history. For many scenarios, you can probably just leave this
alone. If you do decide to change the snapshot interval, keep in mind
that there are now other components in Oracle that are reliant on this AWR
data. For instance ADDM (Active Database Diagnostic Monitor) might not
give you proper analysis if you changed the snapshot interval to twice a day,
but had a performance problem over a specific hour of the day. In
addition, the various advisors available starting with Oracle 10g, such as SQL
Advisor, Undo Advisor, and Segment Advisor also rely on this AWR data, so that
is something to keep in mind if you decide to change the snapshot interval.
Generating Reports
The AWR
scripts are found in the usual
$ORACLE_HOME/rdbms/admin directory.
If you’re just doing a report on this instance, run awrrpt.sql from your
SQL*Plus session. By default, the report will be for the last three days
of information. If you want to change this setting, you can use the awrinput.sql
script.
The awrinput.sql
script can also be used to change the retention period. By default, this
is set to seven days only, but you may want to collect six or eight weeks worth
of data, in order to cover your business cycle properly, and be able to
diagnose issues that far in the past.
Using Oracle Enterprise Manager
Under OEM,
look for "Automatic Workload Repository" under the administration
menu. Use the "Edit" button to see the "Edit
Settings" page, and to change the retention period, or the snapshot
interval.
You may also
choose to use the underlying DBMS_WORKLOAD_REPOSITORY pl/sql package to
configure, and use AWR. Use the SQL*Plus describe command to get function
names, and call interfaces.
What’s Active Session History?
Way back in
2005, I read Shee, Deshpande, and Gopalakrishnan’s superb Oracle Press title
"Oracle Wait Interface: A Practical Guide to Performance Diagnostics &
Tuning". Not only did the book introduce me to on-the-spot session
and wait based tuning, but it really illuminated the intricacies of the
important views such as v$session_wait. It was through these views that
you could peer into what the database was doing at the very moment in time you
queried it.
Farther on
in the book they discuss some of the then limitations on those views, such as a
lack of a real history of data. Even with the new v$session_wait_history
view you only get the last ten wait events that a particular session
encountered.
Chapter four
of the book discusses monitoring and collection methods, and even introduces a
method, using a logoff trigger, which collects the session’s statistics, and
dumps them in a table for later analysis. At the time I was intrigued,
and began work on my own sort of instant analysis tool called "otop"
based on the longstanding UNIX fixture, "top". The UNIX tool
looks at processes that the OS is running, and allows sorting by CPU, disk I/O
or memory usage. It’s great because it gives you current information,
like you’re peering into the mind of the machine! My idea was to build
the same type of tool for Oracle, allowing sessions to be viewed in the same
way, and providing drilldowns and so on and so forth. My proof of concept
turned into a very basic working version; though it took a lot more work than I
had time for then. I also rolled it into a Collaborate and NY Oracle User
Group presentation, which went over quite well.
As 10g came
out, it became clear that Oracle themselves were addressing this limitation,
with the creation of Active Session History. So in essence, ASH allows
you to get really granular, and look at things happening at the session level,
identifying problems caused by a particular session, be they too much CPU usage
(such as a big sort), or too much memory (perhaps the SQL is retrieving too
many rows), or disk I/O (because of lack of an index). Those are some
examples. In the OWI book, they call it "flashback session",
and I think that really illustrates well what ASH provides to you, the database
administrator.
New in 11g
11g adds a
few nice features to AWR, which are worth noting. One is with respect to
baselines. It adds moving window baselines, and an even better feature
called repeating baselines. These repeating baselines allow you to define
a regular timeframe, such as Sunday night from 11-12 when a large and
problematic job is running. You can then better assess issues that are
happening due to that job, with the performance data you’ve collected.
11g also introduces Adaptive Metric Thresholds. Rather than fixed alert
thresholds that may not always be relevant, adaptive ones allow Oracle to set
thresholds using your baseline AWR data. This is good, for instance if
you have a new database, without much data to build on.
Conclusion
Oracle’s
Automatic Workload Repository improves quite a lot on it’s previous statspack
(which is still available for those diehards out there). It is installed
automatically, has some fairly reasonable default settings, and brings us
Active Session History as well. Next time we’ll discuss the advisors,
namely ADDM, and show you how those can help you in your tuning efforts.
»
See All Articles by Columnist Sean Hull
