awr-parser.sh – Script for parsing Oracle AWR Reports

The awr-parser.sh script (available here) is a tool for extracting data from Oracle AWR reports in order that it can be plotted in graphical form. The input is a set of AWR reports in TEXT format and the output is a CSV file. Once the CSV is generated, software such as Microsoft Excel can be used to create graphs like this:

Background

The Oracle Database is capable of many things, which is why so many of us have spent our whole careers getting paid just for persuading it to do the things our employers want. Since version 10g the database has also been able to record vast amounts of information about what it is doing, when it is doing it and how long it takes to get those things done. This information is stored in memory and written periodically to a place called the Automatic Workload Repository (or AWR). The period is configurable but by default it is every hour. At this point, the information is known as a snapshot.

Snapshots are kept in the AWR for a predefined amount of time (seven days by default) after which they are purged. During this time, a standard report known as the AWR Report can be https://flashdba.com/database/useful-scripts/awr-generator/ covering the period between any two available snapshots.

So what does this report tell us? Everything. Well, nearly everything – it’s something like one hundred pages long if you were crazy enough to try and print it out. Of course, all of the information can be retrieved independently by querying views within the database, but the beauty of AWR reports is that they are simple to generate and everybody knows what they contain. If you turn up somewhere new with your custom SQL, it’s likely the responsible DBA will not want to run it until they have inspected it, test it and possible made you sign off to say it won’t break anything.

Disclaimer

The tools and scripts found here are supplied for education purposes only and come with no warranty, either expressly or implied. Use at your own risk and please test thoroughly before use in a environment where you care about data and availability.

Neither I nor my employer accept any responsibility for damage caused through the use of these scripts and tools.

In addition, to take advantage of the information in the Automatic Workload Repository, you must obtain the relevant license(s) from Oracle Corporation. That includes licenses for Enterprise Edition as well as licenses for the Diagnostic Pack. Contact your local Oracle representative for details.

Prerequisites

The script is written using the BASH (Bourne-Again SHell) language and was designed to run on Linux-based systems such as Oracle Linux or Red Hat. It can however be run on Microsoft Windows systems with the use of the Cygwin Linux API (see later section for details). The bc (basic calculator) binary must be present in order for the script to execute successfully. Use a package manager tool such as yum to ensure that bc is present.

Download

You can download the script from my GitHub repository here:

http://github.com/flashdba/scripts

Input

The input for the awr-parser.sh script is a set of AWR reports in TEXT format – not in the default HTML format, as this is too hard to parse. The more AWR reports you have, the better – and they should be consecutive reports (i.e. based on consecutive snapshots) if you want any subsequent graphs to be meaningful.

I have a script for generating the relevant AWR reports in one single batch. You can find it here: AWR Generator.

Output

The output from awr-parser.sh is a CSV file, i.e. a file containing comma-separated values.

The following information is captured in the output CSV file:

• Filename : The filename of the AWR Report
• Database Name : The name of the database
• Instance Number : The instance number (used in RAC)
• Instance Name : The name of the instance
• Database Version : The Oracle database software version
• Cluster : Y/N flag indicating if this is a RAC cluster
• Hostname : The hostname of the database server
• Host OS : The host operating system (only in 11g and above reports)
• Num CPUs : The number of CPU cores as determined by Oracle
• Server Memory (GB) : The amount of physical memory in the database server
• DB Block Size : The database block size (2k, 4k, 8k, 16k or 32k)
• Begin Snap : The starting snapshot ID
• Begin Time : The starting snapshot timestamp
• End Snap : The ending snapshot ID
• End Time : The ending snapshot timestamp
• Elapsed Time (mins) : The elapsed time of the AWR Report
• DB Time (mins) : The amount of CPU time spent on database user calls
• Average Active Sessions : The average number of active foreground Oracle sessions
• Busy Flag : Y/N flag to indicate if DB Time > Elapsed Time*NUM_CPUs
• Logical Reads/sec : The average number of logical reads per second
• Block Changes/sec : The average number of block changes per second
• Read IOPS : The average read IOPS for the AWR period
• Data Write IOPS : The average write IOPS for the AWR period (excluding redo)*
• Redo IOPS : The average redo IOPS for the AWR period (LGWR writes)
• All Write IOPS : The average data write IOPS for the AWR period (excluding redo)
• Total IOPS : The average total number of IOPS (Read IOPS + All Write IOPS)
• Read Throughput (MiB/sec) : The average read throughput for the AWR period
• Data Write Throughput (MiB/sec) : The average write throughput for the AWR period (excluding redo)*
• Redo Throughput (MiB/sec) : The average redo throughput for the AWR period (LGWR writes)
• All Write Throughput (MiB/sec) : The average write throughput for the AWR period (data + redo)
• Total Throughput (MiB/sec) : The average total throughput for the period (read + all write)
• DB CPU Time (s) : The amount of DB time spent on CPU in seconds
• DB CPU %DBTime : The amount of DB time spent on CPU as a % of total DB Time
• Wait Class User I/O Waits : The number of times users had to wait on I/O
• Wait Class User I/O Time (s) : The time spent by users waiting on I/O
• Wait Class User I/O Latency (ms) : The average latency experienced by users waiting on I/O
• Wait Class User I/O %DBTime : The % of total DB Time spent waiting on user I/O
• User Calls/sec : Average number of user calls e.g. login, parse, fetch, or execute
• Parses/sec : The average number of SQL parse calls per second
• Hard Parses/sec : The average number of hard parses per second
• Logons/sec : The average number of user logons per second
• Executes/sec : Average number of calls (user and recursive) that executed SQL
• Transactions/sec : The average number of transactions completing per second
• Buffer Hit Ratio (%) : The percentage of buffer gets that were satisfied from cache
• In-Memory Sort Ratio (%) : The percentage of sort operations performed only in memory
• Log Switches (Total) : The total number of redo log switches
• Log Switches (Per Hour) : The calculated number of log switches per hour

The next section repeats the same format for each of the Top 5 Foreground Wait Events:

• Top5 EventN Name : Top 5 Timed Events: The name of the wait event
• Top5 EventN Class : Top 5 Timed Events: The class of the wait event
• Top5 EventN Waits : Top 5 Timed Events: The number of waits
• Top5 EventN Time (s) : Top 5 Timed Events: The time spent waiting
• Top5 EventN Average Time (ms) : Top 5 Timed Events: The average wait time
• Top5 EventN %DBTime : Top 5 Timed Events: Percentage of total DB time spent waiting

The next section repeats the same format as the Top 5 events above, but for specific I/O events:

• db file sequential read Waits : I/O waits for random reads into the buffer cache
• db file scattered read Waits : I/O waits for sequential reads into the buffer cache
• direct path read Waits : I/O waits for asynchronous reads by a single session
• direct path write Waits : I/O waits for asynchronous writes by a single session
• direct path read temp Waits : Similar to direct path read but for temporary blocks (e.g. sorts)
• direct path write temp Waits : Similar to direct path write but for temporary blocks (e.g. sorts)
• log file sync Waits : Sessions waiting on commits as redo data flushed to storage
• db file parallel write Waits : The database writer writing changed blocks to storage
• log file parallel write Waits : Waits for I/O as the redo buffer is flushed to the redo logs
• log file sequential read Waits : Waits for reads from redo logs

The next section contains information from the Operating System Statistics section – depending on the OS there may or may not be data present. All values are in hundredths of seconds:

• OS busy time : Time that a processor was busy executing user or kernel code
• OS idle time : Time that a processor has been idle
• OS iowait time : Time that a processor has been waiting for I/O to complete
• OS sys time : Time that a processor was busy executing kernel code
• OS user time : Time that a processor was busy executing user code
• OS cpu wait time : Time that processes have been ready and waiting for CPU
• OS resource mgr wait time : Time that processes have been waiting in Resource Manager

The next section returns to showing individual metrics:

• Data Guard Flag : Y/N flag to indicate if evidence found that Data Guard is in use
• Exadata Flag : Y/N flag to indicate if evidence found of this being Exadata

The next section repeats the same format for each of the following Foreground Wait Event Classes: Administrative, Application, Cluster, Commit, Concurrency, Configuration, Network, Other, Scheduler, System I/O. Note that the User I/O Wait Class is shown further up. Information for wait classes not in this list is not captured:

• Wait Class <name> Waits : The number of times background processes had to wait on I/O
• Wait Class <name>Time (s) : The time spent by background processes waiting on I/O
• Wait Class <name>Latency (ms) : The average latency experienced by background processes
• Wait Class <name>%DBTime : The % of total DB Time spent waiting on system I/O

The next section repeats the same format for each of the following Wait Event Histogram entries: db file sequential read, log file parallel write. Information for wait event histograms not in this list is not captured:

• Histogram <name> <1ms : The percentage of waits for this event which completed in under 1 millisecond
• Histogram <name> <2ms : The percentage of waits for this event which completed in under 2 milliseconds
• Histogram <name> <4ms : The percentage of waits for this event which completed in under 4 milliseconds
• Histogram <name> <8ms : The percentage of waits for this event which completed in under 8 milliseconds
• Histogram <name> <16ms : The percentage of waits for this event which completed in under 16 milliseconds
• Histogram <name> <32ms : The percentage of waits for this event which completed in under 32 milliseconds
• Histogram <name> <1s : The percentage of waits for this event which completed in under 1 second
• Histogram <name> >1s : The percentage of waits for this event which completed in over 1 second

* Pay attention to the values for write IOPS and throughput. The value of Data Write Throughput is the value for Total Write Throughput minus the value for Redo Write Throughput. However, I have recently discovered that this is incorrect, because I assumed (shame on me) that the statistics redo size showed the number of bytes written by redo. In fact, the number of bytes written by redo equals (redo size + redo wastage) * the number of multiplexed online redo logs. I need to fix this by taking the redo data either from the IOStat section of the report or the new IO Profile section in 11.2.0.4 and above reports. A similar story may apply for the corresponding IOPS values.

In addition, the description of Data Write IOPS / Throughput is unsatisfactory because it also includes the values for I/O caused by the log archive process, I/O to control files and so on. Perhaps it would be better described as Non-Redo Write IOPS/Throughput. In general, the two values of most interest are therefore Read IOPS/Throughput and Total Write IOPS/Throughput, which added together, make Total IOPS/Throughput.

Execution

The script requires the bash environment and is called with the following options and parameters:

$ ./awr-parser.sh

Usage: ./awr-parser.sh [ -n | -H ] [ -s | -p | -v ]  (wildcards are accepted)

 Version v1.02 (published on 05/01/2015)

  Script for parsing multiple AWR reports and extracting useful information
  Redirect stdout to a CSV file to import into Excel
  Errors and info are printed to stderr

  Options:
    -h   Help        (print help and version information)
    -H   Header      (print the header row only and then exit)
    -n   NoHeader    (do not print the header row in the CSV file)
    -p   Print       (print AWR report values to screen)
    -s   Silent      (do not print AWR processing information)
    -v   Verbose     (show extra AWR processing details)

  Example usage:
    ./awr-parser.sh awr*.txt > awr.csv

So, for example, a simple run would look like this:

$ ./awr-parser.sh SampleAWRs/*.txt > output.csv

Info : Parsing file SampleAWRs/awr_09h00_09h30.txt at 2014-12-01 15:43:00
Info : Parsing file SampleAWRs/awr_09h30_10h00.txt at 2014-12-01 15:43:58
Info : Parsing file SampleAWRs/awr_10h00_10h30.txt at 2014-12-01 15:44:53
Info : Parsing file SampleAWRs/awr_10h30_11h00.txt at 2014-12-01 15:45:51
Info : Parsing file SampleAWRs/awr_11h00_11h30.txt at 2014-12-01 15:46:51
Info : Parsing file SampleAWRs/awr_11h30_12h00.txt at 2014-12-01 15:47:45
Info : Parsing file SampleAWRs/awr_12h00_12h30.txt at 2014-12-01 15:48:38
Info : Parsing file SampleAWRs/awr_12h30_13h00.txt at 2014-12-01 15:49:35
Info : Parsing file SampleAWRs/awr_13h00_13h30.txt at 2014-12-01 15:50:31
Info : Parsing file SampleAWRs/awr_13h30_14h00.txt at 2014-12-01 15:51:26
Info : Parsing file SampleAWRs/awr_14h00_14h30.txt at 2014-12-01 15:52:24
Info : Parsing file SampleAWRs/awr_14h30_15h00.txt at 2014-12-01 15:53:47
Info : Parsing file SampleAWRs/awr_15h00_15h30.txt at 2014-12-01 15:55:07
Info : Parsing file SampleAWRs/awr_15h30_16h00.txt at 2014-12-01 15:56:12
Info : Parsing file SampleAWRs/awr_16h00_16h30.txt at 2014-12-01 15:57:14
Info : Parsing file SampleAWRs/awr_16h30_17h00.txt at 2014-12-01 15:58:14
Info : Parsing file SampleAWRs/awr_17h00_17h30.txt at 2014-12-01 15:59:16
Info : Parsing file SampleAWRs/awr_17h30_18h00.txt at 2014-12-01 16:00:20
Info : No more files found
Info :
Info : ______SUMMARY______
Info : Files found       : 18
Info : Files processed   : 18
Info : Processing errors : 0
Info :
Info : Completed successfully

Running with Cygwin

If you really must use Windows, you can try to run this using Cygwin. I got it working a long time ago but have not tested it for years. Here are the instructions I used to use – I make no promises that they still work:

1. Download the Cygwin installer executable “setup.exe” from the URL http://cygwin.com/install.html
2. Run the executable (Run as Administrator if using Windows 7 or Windows Vista)
3. At the installation screen click Next
4. Choose “Install from Internet” and click Next
5. At “Select Root Directory” screen click Next
6. At “Local Package Directory” screen click Next
7. Unless you are using a proxy click Next
8. Choose a download site or accept the default and then click Next
9. Open the “Math” package and change entry for “bc” from “Skip” to “Keep”
10. Click Next
11. Click Finish

Cygwin will now install. Run up the Cygwin terminal screen and use this to execute the script.