Intro to Oracle's Automatic Workload Repository (AWR)
October 14, 2008
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.
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.
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 sessions 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.
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