Multiple awr report parser in perl

      10 Comments on Multiple awr report parser in perl

Hi there,
The idea came to me while running an audit to identify bottlenecks in an Oracle infrastructure composed of two servers with many single instances.
Due to lack of available time to do the work, I  decided to develop a small utility which would help me to get a full picture of the infrastructure load in a 30 minutes window basis.
That represent nearly:
12 instances in PROD
20 instances in TEST/DEV
For most of the databases, the snapshot time was set to 30 minutes while some where set to 60 min.
That makes: (24 * 2 * 7) = 12 = 4032 reports for production and (24 * 2 * 7) = 20 = 6720 reports for test/dev
So in total, 10752 reports to analyse and cross-check…
Script to generate reports is here: gen_awr
I have been told by my college @ludodba that @flashdba has already build something but as I have almost completed the script and some other ideas where coming, I decided to continue spending some time on this utility.
Our version might differ a little bit but here are some key points of my target version (at the moment):

  • Not fully covering RAC
  • Only from 11g
  • Almost same data captured
  • Do the sorting of the output file on the snapshot time
  • Adjust window time to a given period (ex: normalise 30 and 60 minutes based reports)
  • Fast (336 reports in less than 15 seconds.)

Coming next is

  • Rac support
  • SQL statistics (by elapse,cpu,read,…)
  • Specific counter extraction from multiple csv files to facilitate charting
  • Specific counters aggregation for charting
  • Integration with R

Here is an example:
[code language=”sql”]
tvd_perl -o . -s audit_dwh/bidbhaprd -t 30
Parsing file audit_dwh/bidbhaprd/awrrpt_1_41607_41608_20141123_23_30_20141124_00_00.txt

Parsing file audit_dwh/bidbhaprd/awrrpt_1_41942_41943_20141130_23_00_20141130_23_30.txt
Processed 336 files
Saving result…
Writing header…
Sorting by snapshot date/time…
Writing data…
Ouput is here: ORAPRD01_bidbhaprdP
Script can be downloaded here:parse_awr
List of columns here:ColsList
Once completed, I will try to fully document the stuff:-)
Here is the script to consolidate one column for charting:Consolidate_awr
Simple example:
[code language=”sql”]
tvd_perl -s out -c 14 -o out.csv
Parsing file INSTDB1_instdb1.csv
Parsing file INSTDB2_instdb2.csv
Parsing file INSTDB3_instdb3.csv
Parsing file INSTDB4_instdb4.csv
Parsing file INSTDB5_instdb5.csv
Parsing file INSTDB6_instdb6.csv
Parsing file INSTDB7_instdb7.csv
Parsing file INSTDB8_instdb8.csv
Parsing file INSTDB9_instdb9.csv
Parsing file INSTDB10_instdb10.csv
Parsing file INSTDB11_instdb11.csv
Writing result in out.csv
The output is here:out
And the simple chart. We can clearly see who is doing stuff…


Leave a Reply

Your email address will not be published. Required fields are marked *