Oracle have provided many performance gathering and reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle8i introduced the Statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).
The AWR is used to collect performance statistics including:
Wait events used to identify performance problems.
Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
Object usage statistics.
Resource intensive SQL statements.
The repository is a source of information for several other Oracle 10g features including:
Automatic Database Diagnostic Monitor
SQL Tuning Advisor
Undo Advisor
Segment Advisor
By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using:
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 30); -- Minutes. Current value retained if NULL.
END;
/
The changes to the settings are reflected in the DBA_HIST_WR_CONTROL view. Typically the retention period should capture at least one complete workload cycle. If you system has monthly archive and loads a 1 month retention time would be more beneficial that the default 7 days. An interval of "0" switches off snapshot collection, which in turn stops much of the self-tuning functionality, hence this is not recommended. Automatic collection is only possible if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If the value is set to BASIC manual snapshots can be taken, but they will be missing some statistics.
SQL> select * from DBA_HIST_WR_CONTROL;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------- ------------------- ----------
1436980507 +00000 00:30:00.0 +00040 00:00:00.0 DEFAULT
here snap interval is 30 minutes and retention persiod is 40 days.
Extra snapshots can be taken and existing snapshots can be removed using:
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id => 22,
high_snap_id => 32);
END;
/
Snapshot information can be queried from the DBA_HIST_SNAPSHOT view.
SQL> desc DBA_HIST_SNAPSHOT;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
STARTUP_TIME NOT NULL TIMESTAMP(3)
BEGIN_INTERVAL_TIME NOT NULL TIMESTAMP(3)
END_INTERVAL_TIME NOT NULL TIMESTAMP(3)
FLUSH_ELAPSED INTERVAL DAY(5) TO SECOND(1)
SNAP_LEVEL NUMBER
ERROR_COUNT NUMBER
SQL> select max(begin_interval_time) - min(begin_interval_time) from DBA_HIST_SNAPSHOT;
MAX(BEGIN_INTERVAL_TIME)-MIN(BEGIN_INTERVAL_TIME)
---------------------------------------------------------------------------
+000000040 10:00:12.668
A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing like:
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 210,
end_snap_id => 220,
baseline_name => 'batch baseline');
END;
/
The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted:
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_baseline (
baseline_name => 'batch baseline',
cascade => FALSE); -- Deletes associated snapshots if TRUE.
END;
/
Baseline information can be queried from the DBA_HIST_BASELINE view.
SQL> desc DBA_HIST_BASELINE;
Name Null? Type
----------------------------------------- -------- ----------------------------
DBID NOT NULL NUMBER
BASELINE_ID NOT NULL NUMBER
BASELINE_NAME VARCHAR2(64)
START_SNAP_ID NUMBER
START_SNAP_TIME TIMESTAMP(3)
END_SNAP_ID NUMBER
END_SNAP_TIME TIMESTAMP(3)
The following workload repository views are available:
V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
V$METRIC - Displays metric information.
V$METRICNAME - Displays the metrics associated with each metric group.
V$METRIC_HISTORY - Displays historical metrics.
V$METRICGROUP - Displays all metrics groups.
DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
DBA_HIST_BASELINE - Displays baseline information.
DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
DBA_HIST_SNAPSHOT - Displays snapshot information.
DBA_HIST_SQL_PLAN - Displays SQL execution plans.
DBA_HIST_WR_CONTROL - Displays AWR settings.
Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows:
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.
SECOND METHOD
==========================
SELECT output
FROM TABLE(dbms_workload_repository.awr_report_text (37933856,1,2900,2911 ));
The sample output below shows the typical report generated for AWR data. The output displays shows the four arguments to the awr_report_text stored procedure:
§ The database ID is 37933856.
§ The instance number for RAC is 1.
§ The starting snapshot number is 2900.
§ The ending snapshot number is 2911.
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.
The automated workload repository administration tasks have been included in Enterprise Manager. The "Automatic Workload Repository" page is accessed from the main page by clicking on the "Administration" link, then the "Workload Repository" link under the "Workload" section. The page allows you to modify AWR settings or manage snapshots without using the PL/SQL APIs
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii
Automatic Workload Repository
Learn to use the new feature that collects database performance statistics and metrics for analysis and tuning, shows the exact time spent in the database, and even saves session information
When you have a database performance problem, what is the first thing you do to address it? One common approach is to see if a pattern exists: Answering questions such as "Is the same problem recurrent?", "Does it occur during a specific time period?", and "Is there a link between two problems?" will almost always lead to a better diagnosis.
As a DBA you probably have invested in a third-party or homegrown tool to collect elaborate statistics during database operation and derive performance metrics from them. In a crisis, you access those metrics for comparisons to the present. Replaying these past events can shed light on current problems, so continuously capturing relevant statistics becomes important for performance analysis.
For some time, Oracle's solution in this area has been its built-in tool, Statspack. While it can prove invaluable in certain cases, it often lacks the robustness required by performance troubleshooting exercises. Oracle Database 10g offers a significant improvement: the Automatic Workload Repository (AWR). The AWR installs along with the database and captures not only statistics, but the derived metrics as well.
A Quick Test Drive AWR capability is best explained quickly by the report it produces from collected statistics and metrics, by running the script awrrpt.sql in the $ORACLE_HOME/rdbms/admin directory. This script, in its look and feel, resembles Statspack; it shows all the AWR snapshots available and asks for two specific ones as interval boundaries. It produces two types of output: text format, similar to that of the Statspack report but from the AWR repository, and the default HTML format, complete with hyperlinks to sections and subsections, providing quite a user-friendly report. Run the script and take a look at the report now to get an idea about capabilities of the AWR.
Implementation Now let's explore how AWR is designed and structured. Basically, AWR is an Oracle built-in tool that collects performance related statistics and derives performance metrics from them to track a potential problem. Unlike Statspack, snapshots are collected automatically every hour ( or as defined) by a new background process called MMON and its slave processes. To save space, the collected data is automatically purged after 7 days. Both the snapshot frequency and retention time can be modified by the user. To see the present settings, you could use:
select snap_interval, retention
from dba_hist_wr_control;
SNAP_INTERVAL RETENTION
------------------- -------------------
+00000 01:00:00.0 +00007 00:00:00.0
This SQL shows that the snapshots are taken every hour and the collections are retained 7 seven days. To change the settings--say, for snapshot intervals of 20 minutes and a retention period of two days--you would issue the following. The parameters are specified in minutes.
begin
dbms_workload_repository.modify_snapshot_settings (
interval => 20,
retention => 2*24*60
);
end;
AWR uses several tables to store the collected statistics, all stored under the SYS schema in the new special tablespace named SYSAUX, and named in the format WRM$_* and WRH$_*. The former type stores metadata information such as the database being examined and the snapshots taken, and the latter type holds the actual collected statistics. (As you might have guessed, H stands for "historical" and M stands for "metadata.") There are several views with the prefix DBA_HIST_ built upon these tables, which can be used to write your own performance diagnosis tool. The names of the views directly relate to the table; for example, the view DBA_HIST_SYSMETRIC_SUMMARY is built upon the table WRH$_SYSMETRIC_SUMMARY.
The AWR history tables capture a lot more information than Statspack, including tablespace usage, filesystem usage, even operating system statistics. A complete list of these tables can be seen from the data dictionary through:
Using the Statistics So far we have seen what AWR collects; now let's see what it does with the data. Most performance problems do not exist in isolation, but rather leave tell-tale signs that will lead to the eventual root cause of the problem. Let's use a typical tuning exercise: You notice that the system is slow and decide to look into the waits. Your examination reveals that the "buffer busy wait" is very high. What could be the problem? There are several possibilities: there could be a monotonically increasing index, a table so packed that a single block is asked to be loaded to memory very quickly, or some other factors. In any case, first you want identify the segment in question. If it's an index segment, you could decide to rebuild it; change it to a reverse key index; or convert it to a hash-partitioned index introduced in Oracle Database 10g. If it's a table, you could consider changing storage parameters to make it less dense or move it over to a tablespace with automatic segment space management. Your plan of attack is generally methodical and usually based your knowledge of various events and your experience in dealing with them. Now imagine if the same thing were done by an engine - an engine that captures metrics and deduces possible plans based on pre-determined logic. Wouldn't your job be easier? That engine, now available in Oracle Database 10g, is known as Automatic Database Diagnostic Monitor (ADDM). To arrive at a decision, ADDM uses the data collected by AWR. In the above discussion, ADDM can see that the buffer busy waits are occurring, pull the appropriate data to see the segments on which it occurs, evaluate its nature and composition, and finally offer solutions to the DBA. After each snapshot collection by AWR, the ADDM is invoked to examine the metrics and generate recommendations. So, in effect you have a full-time robotic DBA analyzing the data and generating recommendations proactively, freeing you to attend to more strategic issues. To see the ADDM recommendations and the AWR repository data, use the new Enterprise Manager 10g console on the page named DB Home. To see the AWR reports, you can navigate to them from Administration, then Workload Repository, and then Snapshots. We'll examine ADDM in greater detail in a future installment. You can also specify alerts to be generated based on certain conditions. These alerts, known as Server Generated Alerts, are pushed to an Advanced Queue, from where they can be consumed by any client listening to it. One such client is Enterprise Manager 10g, where the alerts are displayed prominently.
Time Model When you have a performance problem, what comes to mind first to reduce the response time? Obviously, you want to eliminate (or reduce) the root cause of the factor that adds to the time. How do you know where the time was spent--not waiting, but actually doing the work? Oracle Database 10g introduces time models for identifying the time spent in various places. The overall system time spent is recorded in the view V$SYS_TIME_MODEL. Here is the query and its output.
STAT_NAME VALUE
------------------------------------- --------------
DB time 58211645
DB CPU 54500000
background cpu time 254490000
sequence load elapsed time 0
parse time elapsed 1867816
hard parse elapsed time 1758922
sql execute elapsed time 57632352
connection management call elapsed time 288819
failed parse elapsed time 50794
hard parse (sharing criteria) elapsed time 220345
hard parse (bind mismatch) elapsed time 5040
PL/SQL execution elapsed time 197792
inbound PL/SQL rpc elapsed time 0
PL/SQL compilation elapsed time 593992
Java execution elapsed time 0
bind/define call elapsed time 0
Note the statistic named DB Time, which represents the time spent in the database since the instance startup. Run the sample workload and select the statistic value from the view again. The difference should represent the time spent in the database for that workload. After another round of tuning, perform the same analysis and that difference will show the change in DB Time after the tuning, which can be compared to first change to examine the effect of the tuning exercise on the database time. In addition to the database time, the V$SYS_TIME_MODEL view shows a whole lot of other statistics, such as time spent in different types of parsing and even PL/SQL compilation. This view shows the overall system times as well; however, you may be interested in a more granular view: the session level times. The timing stats are captured at the session level as well, as shown in the view V$SESS_TIME_MODEL, where all the stats of the current connected sessions, both active and inactive, are visible. The additional column SID specifies the SID of the sessions for which the stats are shown. In previous releases, this type of analysis was impossible to get and the user was forced to guess or derive from a variety of sources. In Oracle Database 10g, getting this information is a snap.
Active Session History
=====================
The view V$SESSION in Oracle Database 10g has been improved; the most valuable improvement of them all is the inclusion of wait events and their duration, eliminating the need to see the view V$SESSION_WAIT. However, since this view merely reflects the values in real time, some of the important information is lost when it is viewed later. For instance, if you select from this view to check if any session is waiting for any non-idle event, and if so, the event in question, you may not find anything because the wait must have been over by the time you select it. Enter the new feature Active Session History (ASH), which, like AWR, stores the session performance statistics in a buffer for analysis later. However, unlike AWR, the storage is not persistent in a table but in memory, and is shown in the view V$ACTIVE_SESSION_HISTORY. The data is polled every second and only the active sessions are polled. As time progresses, the old entries are removed to accommodate new ones in a circular buffer and shown in the view. To find out how many sessions waited for some event, you would use
select session_id||','||session_serial# SID, n.name, wait_time, time_waited
from v$active_session_history a, v$event_name n
where n.event# = a.event#
This command tells you the name of the event and how much time was spent in waiting. If you want to drill down to a specific wait event, additional columns of ASH help you with that as well. For instance, if one of the events the sessions waited on is buffer busy wait, proper diagnosis must identify the segments on which the wait event occurred. You get that from the ASH view column CURRENT_OBJ#, which can then be joined with DBA_OBJECTS to get the segments in question. ASH also records parallel query server sessions, useful to diagnose the parallel query wait events. If the record is for a parallel query slave process, the SID of the coordinator server session is identified by QC_SESSION_ID column. The column SQL_ID records the ID of the SQL statement that produced the wait event, which can be joined with the V$SQL view to get the offending SQL statement. To facilitate the identification of the clients in a shared user environment like a web application, the CLIENT_ID column is also shown, which can be set by DBMS_SESSION.SET_IDENTIFIER. Since ASH information is so valuable, wouldn't it be nice if it were stored in a persistent manner similar to AWR? Fortunately, it is; the information is flushed to the disk by the MMON slave to the AWR table, visible through the view DBA_HIST_ACTIVE_SESS_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.
Manual Collection
Snapshots are collected automatically by default, but you can also collect them on demand. All AWR functionality has been implemented in the package DBMS_WORKLOAD_REPOSITORY. To take a snapshot, simply issue:
execute dbms_workload_repository.create_snapshot
It immediately takes a snapshot, recorded in the table WRM$_SNAPSHOT. The metrics collected are for the TYPICAL level. If you want to collect more detailed statistics, you can set the parameter FLUSH_LEVEL to ALL in the above procedure. The stats are deleted automatically but can also be deleted manually by calling the procedure drop_snapshot_range().
Baseline A typical performance tuning exercise starts with a capturing a baseline set of metrics, making changes, and then taking another baseline set. These two sets can be compared to examine the effect of the changes made. In AWR, the same kind of analogy can be implemented for existing snapshots taken. Suppose a particularly resource intensive process named apply_interest ran between 1:00 and 3:00PM, corresponding to snapshot IDs 56 through 59. We could define a baseline named apply_interest_1 for these snapshots:
exec dbms_workload_repository.create_baseline (56,59,'apply_interest_1')
This action marks the snapshots 56 through 59 as part of a baseline named above. Checking for existing baselines:
select * from dba_hist_baseline;
DBID BASELINE_ID BASELINE_NAME START_SNAP_ID END_SNAP_ID
---------- ----------- -------------------- ------------- -----------
4133493568 1 apply_interest_1 56 59
After a few tuning steps, we can create another baseline--called, say apply_interest_2--and compare the metrics for only those snapshots related to these two baselines. Isolating snapshots to only a few sets like this helps in studying the effects of tuning on performance metrics. You can drop the baselines after the analysis using the procedure drop_baseline(); the snapshots will be preserved. Also, when the purge routine kicks in to delete the old snapshots, the ones related to baselines are not purged, allowing for further analysis.
Reading the AWR Report
This section contains detailed guidance for evaluating each section of an AWR report. An AWR report is very similar to the STATSPACK report from Oracle9i, and it contains vital elapsed-time information on what happened during particular snapshot range. The data in an AWR or STATSPACK report is the delta, or changes, between the accumulated metrics within each snapshot.
The main sections in an AWR report include:
Report Summary: This gives an overall summary of the instance during the snapshot period, and it contains important aggregate summary information.
Cache Sizes (end): This shows the size of each SGA region after AMM has changed them. This information can be compared to the original init.ora parameters at the end of the AWR report.
Load Profile: This important section shows important rates expressed in units of per second and transactions per second.
Instance Efficiency Percentages: With a target of 100%, these are high-level ratios for activity in the SGA.
Shared Pool Statistics: This is a good summary of changes to the shared pool during the snapshot period.
Top 5 Timed Events: This is the most important section in the AWR report. It shows the top wait events and can quickly show the overall database bottleneck.
Wait Events Statistics Section: This section shows a breakdown of the main wait events in the database including foreground and background database wait events as well as time model, operating system, service, and wait classes statistics.
Wait Events: This AWR report section provides more detailed wait event information for foreground user processes which includes Top 5 wait events and many other wait events that occurred during the snapshot interval.
Background Wait Events: This section is relevant to the background process wait events.
Time Model Statistics: Time mode statistics report how database-processing time is spent. This section contains detailed timing information on particular components participating in database processing.
Operating System Statistics: The stress on the Oracle server is important, and this section shows the main external resources including I/O, CPU, memory, and network usage.
Service Statistics: The service statistics section gives information about how particular services configured in the database are operating.
SQL Section: This section displays top SQL, ordered by important SQL execution metrics.
SQL Ordered by Elapsed Time: Includes SQL statements that took significant execution time during processing.
SQL Ordered by CPU Time: Includes SQL statements that consumed significant CPU time during its processing.
SQL Ordered by Gets: These SQLs performed a high number of logical reads while retrieving data.
SQL Ordered by Reads: These SQLs performed a high number of physical disk reads while retrieving data.
SQL Ordered by Parse Calls: These SQLs experienced a high number of reparsing operations.
SQL Ordered by Sharable Memory: Includes SQL statements cursors which consumed a large amount of SGA shared pool memory.
SQL Ordered by Version Count: These SQLs have a large number of versions in shared pool for some reason.
Instance Activity Stats: This section contains statistical information describing how the database operated during the snapshot period.
Instance Activity Stats (Absolute Values): This section contains statistics that have absolute values not derived from end and start snapshots.
Instance Activity Stats (Thread Activity): This report section reports a log switch activity statistic.
I/O Section: This section shows the all important I/O activity for the instance and shows I/O activity by tablespace, data file, and includes buffer pool statistics.
Tablespace IO Stats
File IO Stats
Buffer Pool Statistics
Advisory Section: This section show details of the advisories for the buffer, shared pool, PGA and Java pool.
Buffer Pool Advisory
PGA Aggr Summary: PGA Aggr Target Stats; PGA Aggr Target Histogram; and PGA Memory Advisory.
Shared Pool Advisory
Java Pool Advisory
Buffer Wait Statistics: This important section shows buffer cache waits statistics.
Enqueue Activity: This important section shows how enqueue operates in the database. Enqueues are special internal structures which provide concurrent access to various database resources.
Undo Segment Summary: This section gives a summary about how undo segments are used by the database.
Undo Segment Stats: This section shows detailed history information about undo segment activity.
Latch Activity: This section shows details about latch statistics. Latches are a lightweight serialization mechanism that is used to single-thread access to internal Oracle structures.
Latch Sleep Breakdown
Latch Miss Sources
Parent Latch Statistics
Child Latch Statistics
Segment Section: This report section provides details about hot segments using the following criteria:
Segments by Logical Reads: Includes top segments which experienced high number of logical reads.
Segments by Physical Reads: Includes top segments which experienced high number of disk physical reads.
Segments by Buffer Busy Waits: These segments have the largest number of buffer waits caused by their data blocks.
Segments by Row Lock Waits: Includes segments that had a large number of row locks on their data.
Segments by ITL Waits: Includes segments that had a large contention for Interested Transaction List (ITL). The contention for ITL can be reduced by increasing INITRANS storage parameter of the table.
Dictionary Cache Stats: This section exposes details about how the data dictionary cache is operating.
Library Cache Activity: Includes library cache statistics describing how shared library objects are managed by Oracle.
SGA Memory Summary: This section provides summary information about various SGA regions.
init.ora Parameters: This section shows the original init.ora parameters for the instance during the snapshot period.
STATSPACK and AWR Statistics Comparison
The AWR repository holds all of the statistics available in STATSPACK as well as some additional statistics which are not. The following information on statistics is specific to those stored in the AWR that are not part of the STATSPACK:
1)STATSPACK does not store the Active Session History (ASH) statistics available in the AWR dba_hist_active_sess_history view. The ASH allows DBAs to perform time-series analyses of wait events for a particular session history.
2)An important difference between STATSPACK and the AWR is that STATSPACK does not store history for new metric statistics introduced in Oracle10g. The key AWR views, dba_hist_sysmetric_history and dba_hist_sysmetric_summary , help the DBA build time-series reports for important database performance metrics such as Total Time Waited or Response Time Per Txn.
The AWR also contains views such as dba_hist_service_stat , dba_hist_service_wait_class and dba_hist_service_name , which store history for performance cumulative statistics tracked for specific services.
3)The latest version of STATSPACK included with Oracle10g contains a set of specific tables, which track history of statistics that reflect the performance of the Oracle Streams feature. These tables are stats$streams_capture , stats$streams_apply_sum , stats$buffered_subscribers , stats$rule_set , stats$propagation_sender , stats$propagation_receiver and stats$buffered_queues . The AWR does not contain the specific tables that reflect Oracle Streams activity; therefore, if a DBA relies heavily on the Oracle Streams feature, it would be useful to monitor its performance using STATSPACK utility.
The internal structures of the Oracle10g Automatic Workload Repository (AWR) and the information it stores have already been presented, but it is important to understand how the AWR evolved from STATSPACK. The AWR is similar to STATSPACK in that it takes time-based snapshots of all important performance tuning v$ dynamic views and stores these snapshots in its repository.
Along with the AWR, Oracle10g delivers a new version of STATSPACK; the utility that is well known and highly recommended from previous releases of Oracle starting with 8.1.6.
Introduced in Oracle 10g, the AWR is a more advanced and convenient feature that has many additional, useful features. It is much like a next generation of STATSPACK. Of course, AWR is more automated and stores more information than STATSPACK.
Furthermore, performance data gathered by the AWR is extensively used by a number of automatic facilities such as the Automatic Database Diagnostic Monitor (ADDM), SQL Tuning Advisor, etc. Thus, Oracle Corporation documentation recommends the use of the AWR functionality over that of STATSPACK in Oracle10g databases.
The STATSPACK utility is still functional and can be used by Oracle DBAs the same way it has been used in previous releases. A look at the structure of the last version of STATSPACK and its general differences from the AWR is a good place to start.
There is one more significant difference between the AWR and STATSPACK. The AWR supports the creation and ability to work with baselines. A baseline is a set of statistics that is defined by a beginning and ending pair of snapshots. A baseline can be created using the dbms_workload_repository.create_baseline procedure:
After two baselines have been created, statistics related to those two baselines can be compared. The AWR automatically preserves the snapshots, which are part of any existing baseline.
STATSPACK does not support the functionality of baselines; however, the Ion tool provides Oracle DBAs with the ability to create and work with baselines. Using the Ion GUI interface, a current statistic’s behavior can be compared with a baseline.
Conclusion
In this chapter, our comparison of the AWR to the STATSPACK utility shows that the AWR presents a much more comprehensive and advanced tool when compared with STATSPACK.
The AWR gathers and stores history for an extended set of performance data that is available in Oracle10g. The main points of this chapter include:
§ The great benefit of the AWR is that it requires minimal administration efforts from the Oracle DBA.
§ The AWR gives Oracle DBAs a powerful tool for performance tuning and trend analysis. It is simple enough to be used as a monitoring tool by junior DBAs yet powerful enough to be used as an advanced data mining source for detailed time-series analysis, trend identification and capacity planning.
§ The AWR forms an analysis base for Oracle10g intelligent self-tuning features such as ADDM, the SQL Tuning Advisor, Automatic Segment Management and ASM
This thread is intended to compare old good free STATSPACK tool (still available and supported in Oracle11g) and new AWR (Automatic Workload Repository) introduced in Oracle10g release 1 (10.1.0) that requires separate licensing even if you just want to query its DBA_HIST views.
Just for start:
AWR is build-in to Oracle database kernel and does not require any configuration. It is running immediately after you create your database. Even if you do not license it, it is running and consuming resources to gather system statistics once per hour to its internal repository.
STATSPACK must be installed to your database manually and requires some configuration like scheduling its STATSPACK.SNAP as a job in order to automate statistic gathering.
>> 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.
>> 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.
>> 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.
>> 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
>> 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.
>>
T
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.
he internal structures of the Oracle10g Automatic Workload Repository (AWR) and the information it stores have already been presented, but it is important to understand how the AWR evolved from STATSPACK. The AWR is similar to STATSPACK in that it takes time-based snapshots of all important performance tuning v$ dynamic views and stores these snapshots in its repository.
Along with the AWR, Oracle10g delivers a new version of STATSPACK; the utility that is well known and highly recommended from previous releases of Oracle starting with 8.1.6.
Introduced in Oracle 10g, the AWR is a more advanced and convenient feature that has many additional, useful features. It is much like a next generation of STATSPACK. Of course, AWR is more automated and stores more information than STATSPACK.
Furthermore, performance data gathered by the AWR is extensively used by a number of automatic facilities such as the Automatic Database Diagnostic Monitor (ADDM), SQL Tuning Advisor, etc. Thus, Oracle Corporation documentation recommends the use of the AWR functionality over that of STATSPACK in Oracle10g databases.
The STATSPACK utility is still functional and can be used by Oracle DBAs the same way it has been used in previous releases. A look at the structure of the last version of STATSPACK and its general differences from the AWR is a good place to start.
There is one more significant difference between the AWR and STATSPACK. The AWR supports the creation and ability to work with baselines. A baseline is a set of statistics that is defined by a beginning and ending pair of snapshots. A baseline can be created using thedbms_workload_repository.create_baseline procedure:
The internal structures of the Oracle10g Automatic Workload Repository (AWR) and the information it stores have already been presented, but it is important to understand how the AWR evolved from STATSPACK. The AWR is similar to STATSPACK in that it takes time-based snapshots of all important performance tuning v$ dynamic views and stores these snapshots in its repository.
Along with the AWR, Oracle10g delivers a new version of STATSPACK; the utility that is well known and highly recommended from previous releases of Oracle starting with 8.1.6.
Introduced in Oracle 10g, the AWR is a more advanced and convenient feature that has many additional, useful features. It is much like a next generation of STATSPACK. Of course, AWR is more automated and stores more information than STATSPACK.
Furthermore, performance data gathered by the AWR is extensively used by a number of automatic facilities such as the Automatic Database Diagnostic Monitor (ADDM), SQL Tuning Advisor, etc. Thus, Oracle Corporation documentation recommends the use of the AWR functionality over that of STATSPACK in Oracle10g databases.
The STATSPACK utility is still functional and can be used by Oracle DBAs the same way it has been used in previous releases. A look at the structure of the last version of STATSPACK and its general differences from the AWR is a good place to start.
There is one more significant difference between the AWR and STATSPACK. The AWR supports the creation and ability to work with baselines. A baseline is a set of statistics that is defined by a beginning and ending pair of snapshots. A baseline can be created using the dbms_workload_repository.create_baseline procedure: