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
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…
Latest posts by Jacques (see all)
- 8 Nodes Cluster Upgrade from 220.127.116.11 to 18.104.22.168 - May 12, 2019
- RAC Database upgrade from 22.214.171.124 to 126.96.36.199 - May 12, 2019
- Managing Standby RedoLogs - May 12, 2019
- Cannot create database on ODA X6-2m after changing default port from 1521 to 13200 - January 8, 2018
- Setting up TAF with Data Guard: news from error ORA-16456 - December 13, 2016