Multiple awr report parser in perl

      2 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:

tvd_perl parse_awr.pl -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...
Done!

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:

tvd_perl Consolidate_awr.pl -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
Done!

The output is here:out

And the simple chart. We can clearly see who is doing stuff…

chart

Cheers

jko

 

 

 

 

The following two tabs change content below.

Jacques

I am Oracle Certified Master 11g & 12c database architect with significant experience in heterogeneous environments, and strong ability to lead complex and critical projects requiring multiple technical implementations. at Trivadis SA

2 thoughts on “Multiple awr report parser in perl

  1. Camille Diesendorf

    Hello there

    SEO Link building is a process that requires a lot of time fo jko-licorne.com
    If you aren’t using SEO software then you will know the amount of work load involved in creating accounts, confirming emails and submitting your contents to thousands of websites in proper time and completely automated.

    With THIS SOFTWARE the link submission process will be the easiest task and completely automated, you will be able to build unlimited number of links and increase traffic to your websites which will lead to a higher number of customers and much more sales for you.
    With the best user interface ever, you just need to have simple software knowledge and you will easily be able to make your own SEO link building campaigns.

    The best SEO software you will ever own, and we can confidently say that there is no other software on the market that can compete with such intelligent and fully automatic features.
    The friendly user interface, smart tools and the simplicity of the tasks are making THIS SOFTWARE the best tool on the market.

    IF YOU’RE INTERESTED, CONTACT ME ==> seosubmitter@mail.com

    Regards,
    Camille

    Reply

Leave a Reply

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