We have seen how the snapshot of memory and thread at a specific moment depicts the entire picture and aids in identifying the performance bottleneck in the heap dump and thread dump analysis sections. Similar information may be found in the Automatic Workload Repository (also known as the AWR) report regarding the bottleneck database.
Figure 01: AWR Report
Dump vs. AWR:
The Automatic Workload Repository report (heap or thread) and dump reports are different in that the former are actual snapshots taken at a certain time, whereas the latter are comparison reports of two snapshots taken at various timestamps.
AWR Report Overview:
Typically, AWR snapshots are taken hourly and saved in the Oracle data dictionary. They include wait interface statistics, top SQL, memory, and I/O information. A snapshot ID is used to identify each snapshot. The AWR report method takes the cumulative data from two snapshots and subtracts the cumulative data from the earlier snapshot from the later snapshot to produce a delta report that displays the statistics and information pertinent for the required time period.
The system automatically collects hourly AWR snapshots, but you can change the frequency based on your needs. The snapshot IDs can also be used by a user to create a report for a certain time period.
The ‘DBA_HIST_SNAPSHOT’ view contains the snapshots. You can manually generate the snapshot using the following technique if you wish to generate it at a certain time instead of the default snapshot generating time.
‘DBA_HIST_SNAPSHOT’ allows you to access the most recent snapshot. In the same window, you can also see the snapshots that were created by default.
The Enterprise Edition includes materials for AWR reporting. By running the relevant SQL script, you may build the report and inspect all the resources under $ORACLE_HOME/rdbms/admin.
The Automatic Workload Repository report is available in text or HTML file formats.
Important SQL scripts
- Report from the Automatic Workload Repository: awrrpt.sql
- Report from AWR Global: awrgrpt.sql
- Addmrpt.sql is the ADDM (Automatic Database Diagnostic Monitor) Report.
- Report for a single AWR SQL statement: awrsqrpt.sql
- awrddrpt.sql is the AWR Diff Report.
- Ashrpt.sql is the ASH (Active Session History) Report.
- Awrgdrpt.sql, the AWR Global Diff Report
Features of AWR
The report from the Automatic Workload Repository offers:
- Delay-causing wait-events
- highlights queries with long execution or elapsed times
- An analysis of CPU and memory usage
- Blocking sessions are just one of several crucial statistics that will be covered in later posts.
Making an AWR report
- To begin, access the database.
- Execute the command SQL> @ $ORACLE_HOME/rdbms/admin/awrrpt.sql to generate the report. The output is shown below:
Figure 02: AWR Report Generation
- ‘HTML’, ‘Text’, or ‘Active-HTML’ are the three inputs that are good for choosing the report type. ‘HTML’ is the default value. Without any input, if you hit “enter,” a report in “HTML” format will automatically be generated.
Figure 03: AWR Report Format
- In order to view the list of snapshots, you must next enter the number of days (num_days). All snapshot IDs and information for the specified number of days will be included in the result. ‘1’ is used as an input in the example to examine the list of snapshots created in 1 day.
Figure 04: Snapshot List
- After obtaining the list of snapshots, you must decide the time frame you want to create the report for. In the same row, the ‘Snap Started’ column displays the snapshot’s timestamp and the ‘Snap Id’ column displays the related snapshot ID. The Snap Id must be chosen from the list and entered in the ‘Enter value for begin_snap’ input box.
Figure 05: begin_snap value
- The value of ‘begin_snap’ is 4730; see the screenshot below. The Snap Id of a snapshot that needs to be compared with Begin_Snap is now the next input, labeled “Enter the value for end_snap.” 4734 (snapshot ID of the time remaining until you require the AWR report) is the entered value displayed in the snapshot that follows.
Figure 06: AWR Report end snap value
- The report name must then be specified. The default proposed name, which is denoted by the underscore (_), consists of the keyword “award,” the index number, the begin snap ID, the end snap ID, and the report format. If you’d like, you can give a suitable name for the report instead of providing one.
Figure 07: AWR Report Name
- When you enter a name and press the enter key, the report-generating process begins, and you’ll get the notice below when it’s finished.
Figure 08: Final Step
9. Type “exit” and look at the created report at the path below:
Exiting with SQL> [oracle@test]pwd /home/oracle
