Oracle/Database/Oracle AWR
Jump to navigation
Jump to search
AWR (Automatic Workload Repository)
- "AWR (Automatic Workload Repository) is a built-in repository (in the SYSAUX tablespace) that exists in every Oracle Database. At regular intervals, the Oracle Database makes a snapshot of all of its vital statistics and workload information and stores them in the AWR. "
Automatic Performance Statistics:
- "To effectively diagnose performance problems, statistics must be available. Oracle generates many types of cumulative statistics for the system, sessions, and individual SQL statements. Oracle also tracks cumulative statistics on segments and services. When analyzing a performance problem in any of these scopes, you typically look at the change in statistics (delta value) over the period of time you are interested in. Specifically, you look at the difference between the cumulative value of a statistic at the start of the period and the cumulative value at the end."
- "This chapter focuses on the skills required for reading and interpreting an Automated Workload Repository (AWR) report, which is very similar to the STATSPACK elapsed-time report. The AWR elapsed-time report contains valuable information regarding the health of the Oracle instance, but considerable skill is required to fully understand and interpret each section."
Intro to Oracle's Automatic Workload Repository (AWR) — DatabaseJournal.com:
- "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."
- "See my notes on Reading Oracle AWR report and see the book Oracle Tuning: The Definitive Reference for a comprehensive treatment of AWR for Oracle tuning. Also see these tips for Oracle AWR reports:"
Oracle n-way Streams replication
- "For the most comprehensive treatment of Oracle SQL tuning with AWR, see the book Oracle Tuning: The Definitive Reference and Oracle SQL Tuning: The Definitive Reference.
- The AWR tables contain super-useful information about the time-series execution plans for SQL statements and this repository can be used to display details about the frequency of usage for table and indexes. This article will explore these AWR tables and expose their secrets."
Oracle Tuning with STATSPACK and AWR - Oracle Wiki:
- "The AWR tables are quite simple, extensions of the ancient bstat-estat utilities and the successor to STATSPACK. Whenever an AWR snapshot is requested, Oracle interrogates the in-memory x$ and v$ structures and stores the information in the appropriate Oracle dba_hist views. Having a historical collection over long periods of time gives the DBA the opportunity to accurately simulate and implement an optimal overall performance plan for the database instance.
The best feature of STATSPACK is that it stores Oracle performance information in a set of 25 tables that can be used to develop historical trends. By interrogating these tables, Oracle professionals can gain tremendous insight into the relative performance of their databases."
ORACLE-BASE - Automatic Workload Repository (AWR) in Oracle Database 10g:
- "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). "
ORACLE-BASE - Automatic Workload Repository (AWR) in Oracle Database 10g
- "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). "
Oracle Tuning with STATSPACK and AWR - Oracle Wiki:
- "The AWR tables are quite simple, extensions of the ancient bstat-estat utilities and the successor to STATSPACK. Whenever an AWR snapshot is requested, Oracle interrogates the in-memory x$ and v$ structures and stores the information in the appropriate Oracle dba_hist views. Having a historical collection over long periods of time gives the DBA the opportunity to accurately simulate and implement an optimal overall performance plan for the database instance. The best feature of STATSPACK is that it stores Oracle performance information in a set of 25 tables that can be used to develop historical trends. By interrogating these tables, Oracle professionals can gain tremendous insight into the relative performance of their databases."
Oracle n-way Streams replication:
- "For the most comprehensive treatment of Oracle SQL tuning with AWR, see the book Oracle Tuning: The Definitive Reference and Oracle SQL Tuning: The Definitive Reference. "
- "The AWR tables contain super-useful information about the time-series execution plans for SQL statements and this repository can be used to display details about the frequency of usage for table and indexes. This article will explore these AWR tables and expose their secrets."
- "The AWR tables contain super-useful information about the time-series execution plans for SQL statements and this repository can be used to display details about the frequency of usage for table and indexes. This article will explore these AWR tables and expose their secrets."
Automatic Performance Statistics - 5.3.5 Generating Automatic Workload Repository Reports:
- "An AWR report shows data captured between two snapshots (or two points in time). The AWR reports are divided into multiple sections. The HTML report includes links that can be used to navigate quickly between sections. The content of the report contains the workload profile of the system for the selected range of snapshots.
- The primary interface for generating AWR reports is Oracle Enterprise Manager. Whenever possible, you should generate AWR reports using Oracle Enterprise Manager, as described in Oracle Database 2 Day + Performance Tuning Guide. If Oracle Enterprise Manager is unavailable, you can generate AWR reports by running SQL scripts:"
- "AWR (Automatic Workload Repository) is a built-in repository (in the SYSAUX tablespace) that exists in every Oracle Database. At regular intervals, the Oracle Database makes a snapshot of all of its vital statistics and workload information and stores them in the AWR. "
- "This chapter focuses on the skills required for reading and interpreting an Automated Workload Repository (AWR) report, which is very similar to the STATSPACK elapsed-time report. The AWR elapsed-time report contains valuable information regarding the health of the Oracle instance, but considerable skill is required to fully understand and interpret each section.
- While there is only enough room in this chapter to cover the highlights, this important chapter should give users a good idea about what to look for in an AWR report and how to use this data to identify performance problems."
Intro to Oracle's Automatic Workload Repository (AWR) — DatabaseJournal.com:
- "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."
- "See my notes on Reading Oracle AWR report and see the book Oracle Tuning: The Definitive Reference for a comprehensive treatment of AWR for Oracle tuning. Also see these tips for Oracle AWR reports:"
Running the awrrpt.sql Report
Automatic Performance Statistics:
5.3.5.1 Running the awrrpt.sql Report
To generate an HTML or text report for a range of snapshot Ids, run the awrrpt.sql script at the SQL prompt:
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
First, you need to specify whether you want an HTML or a text report.
Enter value for report_type: text
Specify the number of days for which you want to list snapshot Ids.
Enter value for num_days: 2
After the list displays, you are prompted for the beginning and ending snapshot Id for the workload repository report.
Enter value for begin_snap: 150 Enter value for end_snap: 160
Next, accept the default report name or enter a report name. The default name is accepted in the following example:
Enter value for report_name: Using the report name awrrpt_1_150_160
The workload repository report is generated.
keywords
Oracle AWR