/
awr-parser.sh
executable file
·2102 lines (2018 loc) · 115 KB
/
awr-parser.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
#!/bin/bash
#
# AWR Parser script : tool for extracting data from Oracle AWR reports
#
# See GitHub repository at https://github.com/flashdba/scripts
#
# ###########################################################################
# # #
# # Copyright (C) {2014,2015,2016} Author: flashdba (http://flashdba.com) #
# # #
# # This program is free software; you can redistribute it and/or modify #
# # it under the terms of the GNU General Public License as published by #
# # the Free Software Foundation; either version 2 of the License, or #
# # (at your option) any later version. #
# # #
# # This program is distributed in the hope that it will be useful, #
# # but WITHOUT ANY WARRANTY; without even the implied warranty of #
# # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the #
# # GNU General Public License for more details. #
# # #
# # You should have received a copy of the GNU General Public License along #
# # with this program; if not, write to the Free Software Foundation, Inc., #
# # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. #
# # #
# ###########################################################################
#
AWRSCRIPT_VERSION="1.02"
AWRSCRIPT_LASTUPDATE="04/12/2016"
#
# Used to parse multiple AWR reports and extract useful information
# The input is an AWR workload repository report **in TEXT format**
# - HTML format reports will be ignored, as will STATSPACK reports
# The output is a CSV file which can be used in Microsoft Excel etc
#
# Usage: ./awr-parser.sh [ -n | -H ] [ -s | -p | -v ] <awr-filename.txt> (wildcards are accepted)
#
# 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)
# -X DEBUG *** HIDDEN OPTION FOR DEBUGGING ***
#
# Return Values:
# 0 Complete Success (all files processed)
# 1 Partial Success (some files processed)
# 2 Failure (no files processed)
#
# Example:
# ./awr-parser.sh -v awrrpt*.txt > output.csv
#
# Version History
#
# 1.00 01/12/2014 flashdba Initial release (open sourced from AWR-Analyze.sh)
# 1.01 05/01/2015 flashdba Added ability to read Wait Event Histogram sections
# 1.02 04/12/2016 flashdba Bugfix: Exadata statistics cause incorrect read of physical read/write throughput
# I have recently learnt that the way I calculate Redo write IOPS/Throughput is incorrect. The Oracle statistic "redo size" does
# not take into account multiplexing of online redo logs, nor does it include the value of "redo wastage". For this reason the
# parsed values for Redo Write IOPS/Throughput and Data Write IOPTS/Throughput are likely to be incorrect. The fix (TODO) will
# be to read the values for Redo from the IOStat section of later AWR reports, or the IO Profile section of >= 11.2.0.4
# A note about the use of bc - the Basic Calculator
# This script uses bc in numerous places so if running via Cygwin ensure bc is installed (it is not part of the default install)
# When reducing the scale of numbers in bc, the default behaviour is simply to drop the extra decimal places insetad of rounding
# To make bc round we have to use a hack where we add half a decimal point and then divide by one
# For example, when reducing to one decimal place we add 0.05 then divide by 1 causing bc to implement the scale reduction
# Alternatively, when reducing to three decimal places we add 0.0005 then divide by 1...it's not pretty, but it works just fine
# Define counters and flags
ERRORCNT=0 # Count number of files where processing errors occurred
FILECNT=0 # Count number of files where processing was attempted
HEADERROW=1 # 0 = No, 1 = Yes, 2 = Only
SILENT=0 # 0 = No, 1 = Yes
PRINT_REPORT_INFO=0 # 0 = No, 1 = Yes
VERBOSE=0 # 0 = No, 1 = Yes
DEBUG=0 # 0 = No, 1 = Yes
# Define constants
EXIT_SUCCESS=0
EXIT_PARTIAL_SUCCESS=1
EXIT_FAILURE=2
AWR_NOTFOUND=0
AWR_PENDING=1
AWR_FOUND=2
# Define section terminators in AWR reports (a string of 61 hyphens)
# Two variables are needed deending on whether we are searching by line or word (see comments on the IFS variable)
OLD_AWR_SECTION_TERMINATOR_BY_WORD="-------------------------------------------------------------"
OLD_AWR_SECTION_TERMINATOR_BY_LINE=" -------------------------------------------------------------"
# In 12c format reports the section terminators are different - there are only 54 hyphens
NEW_AWR_SECTION_TERMINATOR_BY_WORD="------------------------------------------------------"
NEW_AWR_SECTION_TERMINATOR_BY_LINE=" ---------------------------------------------"
# Define field terminator for use with debugging the contents of variables
ENDCHAR=""
# Define functions for printing messages - echodbg includes call to translate the # character into horizontal tab then expand back to spaces (makes debug easier to read)
echoerr() { echo "Error: $@" 1>&2; let ERRORCNT++; }
echoinf() { [[ "$SILENT" = 1 ]] || echo "Info : ${@}${ENDCHAR}" 1>&2; }
echoprt() { echo "Info : ${@}${ENDCHAR}" 1>&2; }
echovrb() { [[ "$VERBOSE" = 1 ]] && echo "Info : ${@}${ENDCHAR}" 1>&2; }
echodbg() { [[ "$DEBUG" = 1 ]] && echo "Debug: ${@}" | tr '#' '\t' | expand -t 15 1>&2; }
echocsv() { echo "$@"; }
# Function for printing usage information
usage() {
if [ "$#" -gt 0 ]; then
echo "Error: $@" 1>&2
fi
echo "Usage: $0 [ -n | -H ] [ -s | -p | -v ] <awr-filename.txt> (wildcards are accepted)" 1>&2
echo "" 1>&2
echo " Version v${AWRSCRIPT_VERSION} (published on $AWRSCRIPT_LASTUPDATE)"
echo "" 1>&2
echo " Script for parsing multiple AWR reports and extracting useful information"
echo " Redirect stdout to a CSV file to import into Excel" 1>&2
echo " Errors and info are printed to stderr" 1>&2
echo "" 1>&2
echo " Options:" 1>&2
echo " -h Help (print help and version information)" 1>&2
echo " -H Header (print the header row only and then exit)" 1>&2
echo " -n NoHeader (do not print the header row in the CSV file)" 1>&2
echo " -p Print (print AWR report values to screen)" 1>&2
echo " -s Silent (do not print AWR processing information)" 1>&2
echo " -v Verbose (show extra AWR processing details)" 1>&2
echo "" 1>&2
echo " Example usage:" 1>&2
echo " $0 awr*.txt > awr.csv" 1>&2
echo "" 1>&2
exit $EXIT_FAILURE
}
# Function for setting the Internal Field Seperator so that lines read from the AWR file are word-delimited
# This means that spaces, tabs and newlines are all used as field seperators
set_ifs_to_word_delimited() {
IFS_DELIMITER="WORD"
IFS=$' \t\n' && echodbg " Setting IFS to SPACE, TAB and NEWLINE"
}
# Function for setting the Internal Field Seperator so that lines read from the AWR file are line-delimited
# This means that only newlines are used as field seperators, while spaces and tabs are not
set_ifs_to_line_delimited() {
IFS_DELIMITER="LINE"
IFS=$'\n' && echodbg " Setting IFS to NEWLINE"
}
# Function for determining the width and location of columns within each AWR section
# It is passed the header row (a set of hyphens) as input and updates a set of variables accordingly
process_header_row() {
echodbg "Entering process_header_row() with argument $1"
# Clear the column variables down
unset AWRCOL1 AWRCOL2 AWRCOL3 AWRCOL4 AWRCOL5 AWRCOL6 AWRCOL7 AWRCOL8 AWRCOL9 AWRCOL10
if [ -z "$1" ]; then
echodbg "process_header_row() called with zero arguments - doing nothing"
PROCESS_HEADER_ROW="NO_DATA"
return $EXIT_FAILURE
else
# Set IFS to be word delimited
[[ "$IFS_DELIMITER" = "LINE" ]] && IFS=$' \t\n'
HEADERROW_ARRAY=($1)
HEADERROW_COLUMNS="${#HEADERROW_ARRAY[@]}"
# If necessary change the IFS back to line delimited
[[ "$IFS_DELIMITER" = "LINE" ]] && IFS=$'\n'
# If there are less than six words in this header then abandon attempt to process this section
if [ "$HEADERROW_COLUMNS" -lt "6" ]; then
echovrb "Failed during process_header_row() because header did not have enough arguments ($HEADERROW_COLUMNS)"
PROCESS_HEADER_ROW="FAILED"
return $EXIT_FAILURE
elif [ "$HEADERROW_COLUMNS" -gt "10" ]; then
echovrb "Failed during process_header_row() because header had too many arguments ($HEADERROW_COLUMNS)"
PROCESS_HEADER_ROW="FAILED"
return $EXIT_FAILURE
else
echodbg "Analyzing header row with $HEADERROW_COLUMNS columns"
fi
# Variable for use in while loop iterating through columns in header row
AWRCOLNUM=1
# Use a counter to keep track of the column locations
COL_COUNTER=1
# Begin calculating column locations
while [ "$AWRCOLNUM" -le "$HEADERROW_COLUMNS" ]; do
eval AWRCOL${AWRCOLNUM}="${COL_COUNTER}-$(($COL_COUNTER + ${#HEADERROW_ARRAY[${AWRCOLNUM} - 1]} - 1))"
COL_COUNTER=$(($COL_COUNTER + ${#HEADERROW_ARRAY[${AWRCOLNUM}-1]} + 1))
let AWRCOLNUM++
done
echodbg "Computed column widths: AWRCOL1=$AWRCOL1, AWRCOL2=$AWRCOL2, AWRCOL3=$AWRCOL3, AWRCOL4=$AWRCOL4, AWRCOL5=$AWRCOL5, AWRCOL6=$AWRCOL6, AWRCOL7=$AWRCOL7, AWRCOL3=$AWRCOL8, AWRCOL4=$AWRCOL9, AWRCOL5=$AWRCOL10"
PROCESS_HEADER_ROW="SUCCESS"
return $EXIT_SUCCESS
fi
}
# Function for processing AWR header section and extracting information about the system
process_awr_report() {
# Read the AWR report looking for key sections containing required information
# Note that different versions of Oracle have different formats for the AWR header
# For the first two sections we have to look for a header and then set a flag (to $AWR_FOUND)
# This flag then triggers specific behaviour over the next defined number of lines
# For example, the phrase "DB Name" indicates the start of the DB Details section
# We therefore know that two lines later will contain the DB name, instance num, version etc
# After this the report is divided up into sections, each of which has its own handler below
# There are two methods of parsing the data depending on the way it is formatted in the report
# For one method (e.g. the Profile section) we read each word on the line into its own array element
# We then pick the data out based on its position relative to the number of words in the line
# The other method is to pick data out based on start and end character positions (e.g. the Top 5 section)
# For this method we read the entire line into one array element and parse it using the cut tool
# Switching between these two methods is a case of changing the Internal Field Seperator (IFS) variable
# IFS is either set to space, tab and newline (word method) or just newline (character method)
# Define a set of variables
ROWCNT=0 # Counter for incrementing as rows of each report are read
TOP5_LINENUM=0 # Counter for incrementing as wait details in Top 5 section are read
AWR_FORMAT="Unknown" # Variable to hold the format version of the AWR report
AWRLINE_SKIP=0 # When value n is greater than zero the next n non-empty lines will be skipped
AWRSECTION_SKIP=0 # When value n is greater than zero the next n AWR sections will be skipped
BAILOUT_COUNTER=0 # Counter for incrementing when searching for the header row of certain sections
BAILOUT_LIMIT=10 # Threshold beyond which hander will abandon searching for the header row
FOUND_SYS_DETAILS=$AWR_NOTFOUND # This is set to AWR_FOUND when the database system details are found
FOUND_HOST_DETAILS=$AWR_NOTFOUND # This is set to AWR_FOUND when the host system details are found
FOUND_TOP5_EVENTS=$AWR_NOTFOUND # This is set to AWR_FOUND when the Top 5 foreground event details are found (Top 10 in 12c)
FOUND_FG_WAIT_CLASS=$AWR_NOTFOUND # This is set to AWR_FOUND when the foreground wait class details are found
FOUND_FG_WAIT_EVENTS=$AWR_NOTFOUND # This is set to AWR_FOUND when the foreground wait event details are found
FOUND_BG_WAIT_EVENTS=$AWR_NOTFOUND # This is set to AWR_FOUND when the background wait event details are found
FOUND_HG_WAIT_EVENTS=$AWR_NOTFOUND # This is set to AWR_FOUND when the wait event histogram details are found
TOTAL_WAIT_TIME=0 # Counter used in wait class section to sum wait times if DB TIME not present in report
AWR_SECTION="AWRProfile" # Current section - influences which handler routine will be used to process data
echodbg "State change AWR_SECTION: reset to Profile"
# Reset the AWR section terminator varibles to those that match 10g and 11g
AWR_SECTION_TERMINATOR_BY_WORD="$OLD_AWR_SECTION_TERMINATOR_BY_WORD"
AWR_SECTION_TERMINATOR_BY_LINE="$OLD_AWR_SECTION_TERMINATOR_BY_LINE"
# Process the file one line at a time using the read commant - each line will be read into the array AWRLINE and processed
# For the first section of the AWR report (the Profile) we want each word on a line to live in a seperate element of an array
# We therefore need to set the IFS (Internal Field Seperator) variable to recognise spaces and tabs as delimiters
# Later on, when processing other sections, we will change this so that only new lines are recognised as delimiters
set_ifs_to_word_delimited
# Begin reading file line by line placing the contents into an array variable
while read -r -a AWRLINE; do
# Increment row counter
let ROWCNT++
# Remove carriage returns and form feed characters from first element of array to aid in pattern matching later on
AWRLINE[0]=$(echo ${AWRLINE[0]} | tr -d '\r\f')
# If line is empty (after removal of control characters) then do not process
if [ "${#AWRLINE[@]}" = 1 ] && [ -z "${AWRLINE[0]}" ]; then
echodbg " Skipping blank line#line $ROWCNT Size=${#AWRLINE[@]} IFS=$IFS_DELIMITER =#${ENDCHAR}${AWRLINE[@]}${ENDCHAR}"
continue
# Check for situation where report was generated using SELECT output FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT)
# and user did not set heading to 0 resulting in repeated header lines throughout report - skip these lines
elif [ "${#AWRLINE[@]}" -le 2 ] && [ "${AWRLINE[0]:0:6}" = "OUTPUT" ]; then
# The next line will be a set of SQLPlus underline characters (usually hyphens) which need to be ignored
# Therefore increment the counter which controls line skipping
echodbg " SQLPlus header at line $ROWCNT - ignoring this plus next line =#${ENDCHAR}${AWRLINE[@]}${ENDCHAR}"
let AWRLINE_SKIP++
continue
# If AWRLINE_SKIP greater than zero then decrement and ignore current line
elif [ "$AWRLINE_SKIP" -gt 0 ]; then
echodbg " Skipping line $ROWCNT due to AWRLINE_SKIP counter ($AWRLINE_SKIP) =#${ENDCHAR}${AWRLINE[@]}${ENDCHAR}"
let AWRLINE_SKIP--
continue
# Check for the standard AWR section terminator (a string of 61 hyphen characters) when IFS is set to word delimited
elif [ "$IFS_DELIMITER" = "WORD" ] && [ "${AWRLINE[0]}" = "$AWR_SECTION_TERMINATOR_BY_WORD" ]; then
echodbg " In $AWR_SECTION#line $ROWCNT Size=${#AWRLINE[@]} IFS=$IFS_DELIMITER =#${ENDCHAR}${AWRLINE[@]}${ENDCHAR}"
if [ "$AWRSECTION_SKIP" -gt 0 ]; then
let AWRSECTION_SKIP--
echodbg "Found AWR section terminator at $ROWCNT - decrementing AWRSECTION_SKIP counter to $AWRSECTION_SKIP"
elif [ "$AWR_SECTION" != "SearchingForNextSection" ]; then
echovrb "Found AWR section terminator at $ROWCNT - marking end of section $AWR_SECTION"
echodbg "State change AWR_SECTION: $AWR_SECTION -> SearchingForNextSection"
AWR_SECTION="SearchingForNextSection"
# Switch Internal Field Seperator to only recognise newlines as delimiters
set_ifs_to_line_delimited
fi
continue
# Check for the standard AWR section terminator (a string of 61 hyphen characters) when IFS is set to line delimited
elif [ "$IFS_DELIMITER" = "LINE" ] && [ "${AWRLINE[0]:0:71}" = "$AWR_SECTION_TERMINATOR_BY_LINE" ]; then
echodbg " In $AWR_SECTION#line $ROWCNT Size=${#AWRLINE[@]} IFS=$IFS_DELIMITER =#${ENDCHAR}${AWRLINE[@]}${ENDCHAR}"
if [ "$AWRSECTION_SKIP" -gt 0 ]; then
let AWRSECTION_SKIP--
echodbg "Found AWR section terminator at $ROWCNT - decrementing AWRSECTION_SKIP counter to $AWRSECTION_SKIP"
elif [ "$AWR_SECTION" != "SearchingForNextSection" ]; then
echovrb "Found AWR section terminator at $ROWCNT - marking end of section $AWR_SECTION"
echodbg "State change AWR_SECTION: $AWR_SECTION -> SearchingForNextSection"
AWR_SECTION="SearchingForNextSection"
# Switch Internal Field Seperator to only recognise newlines as delimiters
set_ifs_to_line_delimited
fi
continue
# If AWRSECTION_SKIP greater than zero then ignore current line
elif [ "$AWRSECTION_SKIP" -gt 0 ]; then
echodbg " Skipping line $ROWCNT due to AWRSECTION_SKIP counter ($AWRSECTION_SKIP) =#${ENDCHAR}${AWRLINE[@]}${ENDCHAR}"
continue
# If debug is enabled print the whole line
else
echodbg " In $AWR_SECTION#line $ROWCNT Size=${#AWRLINE[@]} IFS=$IFS_DELIMITER =#${ENDCHAR}${AWRLINE[@]}${ENDCHAR}"
fi
# Begin processing code - different sections of the report have different handling routines which are enabled through the AWR_SECTION variable
################################################################## Searching For Next Section ##################################################################
if [ "$AWR_SECTION" = "SearchingForNextSection" ]; then
# Routine for searching for the start of the next known section and identifying the correct handler routine
# This section is also responsible for correctly settings the Internal Field Seperator prior to entering the next handler routine
case "${AWRLINE[0]:0:40}" in
"Top 5 Timed Events "*)
# Start of the Top 5 Timed Events section found in 10g format AWR reports
echovrb "Start of 10g Top 5 section found at line $ROWCNT"
echodbg "State change AWR_SECTION: $AWR_SECTION -> Top5Foreground"
AWR_SECTION="Top5Foreground"
# There are three more rows of header to skip before processing this section
AWRLINE_SKIP=2
# Reset the bailout counter so that we can search for the header row of this section
BAILOUT_COUNTER=0
# Set Internal Field Seperator to read entire line into single array element
set_ifs_to_line_delimited
continue
;;
"Top 5 Timed Foreground Events"*)
# Start of the Top 5 Foreground Timed Events section found in 11g format AWR reports
echovrb "Start of 11g Top 5 section found at line $ROWCNT"
echodbg "State change AWR_SECTION: $AWR_SECTION -> Top5Foreground"
AWR_SECTION="Top5Foreground"
# Set Internal Field Seperator to read entire line into single array element
set_ifs_to_line_delimited
# There are five more rows of header to skip before processing this section
AWRLINE_SKIP=4
# Reset the bailout counter so that we can search for the header row of this section
BAILOUT_COUNTER=0
continue
;;
"Top 10 Foreground Events by Total Wait T")
# Start of the Top 10 Foreground Events section found in newer format 11g and 12c AWR reports
# To process this we use the same handler as the Top 5 Events found in older 10g and 11g reports
echovrb "Start of Top 10 section found at line $ROWCNT"
echodbg "State change AWR_SECTION: $AWR_SECTION -> Top5Foreground"
AWR_SECTION="Top5Foreground"
# Set Internal Field Seperator to read entire line into single array element
set_ifs_to_line_delimited
# There are four more rows of header to skip before processing this section
AWRLINE_SKIP=3
# Reset the bailout counter so that we can search for the header row of this section
BAILOUT_COUNTER=0
continue
;;
"Cache Sizes"*)
# Start of the Cache Sizes section
echovrb "Start of CacheSizes section found at line $ROWCNT"
echodbg "State change AWR_SECTION: $AWR_SECTION -> CacheSizes"
AWR_SECTION="CacheSizes"
# Set Internal Field Seperator to read each word into a different element of the array
set_ifs_to_word_delimited
# There is one more row of header to skip before processing this section
AWRLINE_SKIP=1
continue
;;
"Time Model Statistics "*)
# Start of the Time Model Statistics section
echovrb "Start of Time Model Statistics section found at line $ROWCNT"
# We only need information from this section if this is a 10g format AWR Report
if [ "$AWR_FORMAT" != "10" ]; then
echovrb "Ignoring Time Model Statistics because AWR_FORMAT $AWR_FORMAT not 10"
AWRSECTION_SKIP=1
continue
fi
echodbg "State change AWR_SECTION: $AWR_SECTION -> TimeModelStatistics"
AWR_SECTION="TimeModelStatistics"
# Set Internal Field Seperator to read each word into a different element of the array
set_ifs_to_word_delimited
# There are six more rows of header to skip before processing this section
AWRLINE_SKIP=6
continue
;;
"Operating System Statistics "*)
# Start of the Operating System Statistics section
echovrb "Start of Operating System Stats section found at line $ROWCNT"
echodbg "State change AWR_SECTION: $AWR_SECTION -> OperatingSystemStats"
AWR_SECTION="OperatingSystemStats"
# Set Internal Field Seperator to read each word into a different element of the array
set_ifs_to_word_delimited
# Depending on the AWR format there are more rows of header to skip before processing the Operating System Stats section
if [ "$AWR_FORMAT" = "10" ]; then
AWRLINE_SKIP=2
else
AWRLINE_SKIP=5
fi
continue
;;
"Wait Class "*)
# If this is not 10g then ignore
if [ "$AWR_FORMAT" != "10" ]; then
echovrb "Ignoring Wait Class header because AWR_FORMAT $AWR_FORMAT not 10"
continue
fi
# Start of Foreground Wait Class section in 10g versions
echovrb "Start of Foreground Wait Class section found at line $ROWCNT"
echodbg "State change AWR_SECTION: $AWR_SECTION -> ForegroundWaitClass"
AWR_SECTION="ForegroundWaitClass"
# Set the FOUND_FG_WAIT_CLASS variable to AWR_FOUND for use with 10g calculation of DB Time in post-processing section
FOUND_FG_WAIT_CLASS=$AWR_FOUND
# Set Internal Field Seperator to read entire line into single array element
set_ifs_to_line_delimited
# There are nine more rows of header to skip before processing the Foreground Wait Class section
AWRLINE_SKIP=7
continue
;;
"Foreground Wait Class "*)
# Start of Foreground Wait Class section in 11g and 12c versions
echovrb "Start of Foreground Wait Class section found at line $ROWCNT"
echodbg "State change AWR_SECTION: $AWR_SECTION -> ForegroundWaitClass"
AWR_SECTION="ForegroundWaitClass"
# Set Internal Field Seperator to read entire line into single array element
set_ifs_to_line_delimited
# There are nine more rows of header to skip before processing the Foreground Wait Class section
AWRLINE_SKIP=7
continue
;;
"Wait Events "*)
# Start of the Foreground Wait Events section in 10g versions
echovrb "Start of Foreground Wait Events section found at line $ROWCNT"
echodbg "State change AWR_SECTION: $AWR_SECTION -> ForegroundWaitEvents"
AWR_SECTION="ForegroundWaitEvents"
# Set Internal Field Seperator to read entire line into single array element
set_ifs_to_line_delimited
# There are nine more rows of header to skip before processing the Foreground Wait Class section
AWRLINE_SKIP=3
# Reset the bailout counter so that we can search for the header row of this section
BAILOUT_COUNTER=0
continue
;;
"Foreground Wait Events "*)
# Start of the Foreground Wait Events section in versions later than 10g
echovrb "Start of Foreground Wait Events section found at line $ROWCNT"
echodbg "State change AWR_SECTION: $AWR_SECTION -> ForegroundWaitEvents"
AWR_SECTION="ForegroundWaitEvents"
# Set Internal Field Seperator to read entire line into single array element
set_ifs_to_line_delimited
# There are eight more rows of header to skip before processing the Foreground Wait Class section
AWRLINE_SKIP=3
# Reset the bailout counter so that we can search for the header row of this section
BAILOUT_COUNTER=0
continue
;;
"Background Wait Events "*)
# Start of the Background Wait Events section
echovrb "Start of Background Wait Events section found at line $ROWCNT"
echodbg "State change AWR_SECTION: $AWR_SECTION -> BackgroundWaitEvents"
AWR_SECTION="BackgroundWaitEvents"
# Set Internal Field Seperator to read entire line into single array element
set_ifs_to_line_delimited
# Depending on the AWR format there are more rows of header to skip before processing the Background Wait Events section
AWRLINE_SKIP=3
# Reset the bailout counter so that we can search for the header row of this section
BAILOUT_COUNTER=0
continue
;;
"Wait Event Histogram "*)
# Start of the Wait Event Histogram section
echovrb "Start of Wait Event Histogram section found at line $ROWCNT"
echodbg "State change AWR_SECTION: $AWR_SECTION -> WaitEventHistogram"
AWR_SECTION="WaitEventHistogram"
# Set Internal Field Seperator to read entire line into single array element
set_ifs_to_line_delimited
# Depending on the AWR format there are more rows of header to skip before processing the Background Wait Events section
AWRLINE_SKIP=8
# Reset the bailout counter so that we can search for the header row of this section
BAILOUT_COUNTER=0
continue
;;
"Instance Activity Stats "*)
# Start of the Instance Activity Stats section in 10g and 11g format AWR reports
echovrb "Start of Instance Activity Stats section found at line $ROWCNT"
echodbg "State change AWR_SECTION: $AWR_SECTION -> InstanceActivityStats"
AWR_SECTION="InstanceActivityStats"
# Set Internal Field Seperator to read entire line into single array element
set_ifs_to_line_delimited
# Depending on the AWR format there are more rows of header to skip before processing the Instance Activity Stats section
if [ "$AWR_FORMAT" = "10" ]; then
AWRLINE_SKIP=2
else
AWRLINE_SKIP=3
fi
continue
;;
"SQL ordered by"*)
# One of the SQL sections - these should be ignored because punctuation characters in SQL can cause problems
echodbg "Found start of SQL ordered by section at line $ROWCNT - setting AWRSECTION_SKIP"
# There are eight more rows of header to skip before processing the Foreground Wait Class section
let AWRSECTION_SKIP++
continue
;;
"Other Instance Activity Stats "*)
# Start of the Other Instance Activity Stats section in 12c format AWR reports
# Use the normal Instance Activity Stats handler
echovrb "Start of Other Instance Activity Stats section found at line $ROWCNT"
echodbg "State change AWR_SECTION: $AWR_SECTION -> InstanceActivityStats"
AWR_SECTION="InstanceActivityStats"
# Set Internal Field Seperator to read entire line into single array element
set_ifs_to_line_delimited
# Depending on the AWR format there are more rows of header to skip before processing the Instance Activity Stats section
AWRLINE_SKIP=3
continue
;;
"Instance Activity Stats - Thread Activit")
# Start of the Instance Activity Stats - Thread Activity section which contains the Log Switches information
echovrb "Start of Instance Activity Stats - Thread Activity section found at line $ROWCNT"
echodbg "State change AWR_SECTION: $AWR_SECTION -> ThreadActivity"
AWR_SECTION="ThreadActivity"
# Set Internal Field Seperator to read each word into a different element of the array
set_ifs_to_word_delimited
# Depending on the AWR format there are more rows of header to skip before processing the Instance Activity Stats section
AWRLINE_SKIP=3
continue
;;
esac
# If we make it here, we are searching for the start of a new section and haven't found it, so move on to next line
continue
fi
################################################################## AWR Profile Section ##################################################################
if [ "$AWR_SECTION" = "AWRProfile" ]; then
# Routine for handling the AWR Profile section of the report
if [ "$FOUND_SYS_DETAILS" = "$AWR_PENDING" ]; then
# This line contains the database system details
FOUND_SYS_DETAILS=$AWR_FOUND
# Extract data
PROFILE_DBNAME=${AWRLINE[0]}
PROFILE_INSTANCENAME=${AWRLINE[2]}
PROFILE_INSTANCENUM=${AWRLINE[3]}
if [ "$AWR_FORMAT" = "10" ]; then
PROFILE_DBVERSION=${AWRLINE[4]}
PROFILE_CLUSTER=$(echo ${AWRLINE[5]} | cut -c1-1)
if [ -n "${AWRLINE[6]}" ]; then
# AWR reports in Oracle version 10 have the hostname at the end of the DB details line
# The call to tr is necessary to remove the trailing return character
DB_HOSTNAME=$(echo ${AWRLINE[6]} | tr -d '\r')
DB_HOST_OS="Unknown"
else
echovrb "Unable to find Hostname at end of System Details section"
fi
echodbg "Sys Details: DBName=$PROFILE_DBNAME, InstName=$PROFILE_INSTANCENAME, InstNum=$PROFILE_INSTANCENUM, Ver=$PROFILE_DBVERSION, Cluster=$PROFILE_CLUSTER, Hostname=$DB_HOSTNAME, OS=$DB_HOST_OS"
else
PROFILE_DBVERSION=${AWRLINE[6]}
PROFILE_CLUSTER=$(echo ${AWRLINE[7]} | cut -c1-1)
echodbg "Sys Details: DBName=$PROFILE_DBNAME, InstName=$PROFILE_INSTANCENAME, InstNum=$PROFILE_INSTANCENUM, Ver=$PROFILE_DBVERSION, Cluster=$PROFILE_CLUSTER"
fi
continue
elif [ "$FOUND_HOST_DETAILS" = "$AWR_PENDING" ]; then
# This line contains the host system details
FOUND_HOST_DETAILS=$AWR_FOUND
# Call function to extract data
DB_HOSTNAME=$(echo "${AWRLINE[0]}"| cut -c1-16|sed -e 's/^[ \t]*//;s/[ \t]*$//')
DB_HOST_OS=$(echo "${AWRLINE[0]}"| cut -c17-49|sed -e 's/^[ \t]*//;s/[ \t]*$//')
DB_HOST_MEM=$(echo "${AWRLINE[0]}"| cut -c69-79|sed -e 's/^[ \t]*//;s/[ \t]*$//')
echodbg "Host Details: Hostname=$DB_HOSTNAME, OS=$DB_HOST_OS, HostMem=$DB_HOST_MEM"
# Return Internal Field Seperator to reading each word into a different array element
set_ifs_to_word_delimited
continue
fi
# Examine first two words
case ${AWRLINE[@]:0:2} in
"DB Name")
# Found Database System Details
[[ "$FOUND_SYS_DETAILS" = "$AWR_FOUND" ]] && continue
echovrb "Start of Database System details at line $ROWCNT"
# AWR reports from 11g on have an extra "Startup Time" column in the profile
if [ "${AWRLINE[@]:7:1}" = "Startup" ]; then
AWR_FORMAT=11
# Later on we will discover if it is actually a 12c report rather than 11g as we assume initially
else
# Assume this is version 10 format
AWR_FORMAT=10
fi
echovrb "Using AWR Format $AWR_FORMAT"
# Skip next line as this is part of the header
AWRLINE_SKIP=1
FOUND_SYS_DETAILS=$AWR_PENDING
continue
;;
"Host Name")
# Found Host System Details
[[ "$FOUND_HOST_DETAILS" = "$AWR_FOUND" ]] && continue
echovrb "Start of Host System details at line $ROWCNT"
# Skip next line as this is part of the header
AWRLINE_SKIP=1
FOUND_HOST_DETAILS=$AWR_PENDING
# Set Internal Field Seperator to read full line into first array element
set_ifs_to_line_delimited
continue
;;
"Begin Snap:")
# Found details for start of snapshot
AWR_BEGIN_SNAP=${AWRLINE[2]}
AWR_BEGIN_TIME="${AWRLINE[3]} ${AWRLINE[4]}"
echodbg "AWR_BEGIN_SNAP = ${ENDCHAR}$AWR_BEGIN_SNAP${ENDCHAR}"
echodbg "AWR_BEGIN_TIME = ${ENDCHAR}$AWR_BEGIN_TIME${ENDCHAR}"
continue
;;
"End Snap:")
# Found details for end of snapshot
AWR_END_SNAP=${AWRLINE[2]}
AWR_END_TIME="${AWRLINE[3]} ${AWRLINE[4]}"
echodbg "AWR_END_SNAP = ${ENDCHAR}$AWR_END_SNAP${ENDCHAR}"
echodbg "AWR_END_TIME = ${ENDCHAR}$AWR_END_TIME${ENDCHAR}"
continue
;;
"Elapsed:"*)
# Found details for elapsed time
AWR_ELAPSED_TIME=$(echo ${AWRLINE[1]} |sed -e 's/,//g')
echodbg "AWR_ELAPSED_TIME = ${ENDCHAR}$AWR_ELAPSED_TIME${ENDCHAR}"
# Calculate elapsed time in seconds for use when working out throughput values
if [ -n "$AWR_ELAPSED_TIME" ]; then
if [ "$AWR_ELAPSED_TIME" = 0 ]; then
echovrb "Found zero value for AWR_ELAPSED_TIME"
AWR_ELAPSED_TIME_SECS=0
else
echodbg "Calling BC: echo 'scale=3; $AWR_ELAPSED_TIME * 60' | bc -l"
AWR_ELAPSED_TIME_SECS=`echo "scale=3; $AWR_ELAPSED_TIME * 60" | bc -l`
fi
echodbg "AWR_ELAPSED_TIME_SECS = ${ENDCHAR}$AWR_ELAPSED_TIME_SECS${ENDCHAR}"
continue
fi
;;
"DB Time:")
# Found details for DB time
AWR_DB_TIME=$(echo ${AWRLINE[2]} |sed -e 's/,//g')
echodbg "AWR_DB_TIME = ${ENDCHAR}$AWR_DB_TIME${ENDCHAR}"
# If Elapsed Time known then calculate Average Active Sessions (AAS)
if [ -n "$AWR_ELAPSED_TIME" ]; then
if [ "$AWR_ELAPSED_TIME" = 0 ]; then
echoinf "Cannot calculate Average Active Sessions due to zero AWR_ELAPSED_TIME value"
else
echodbg "Calling BC: AWR_AAS=echo 'scale=1; $AWR_DB_TIME / $AWR_ELAPSED_TIME' | bc -l"
AWR_AAS=`echo "scale=1; $AWR_DB_TIME / $AWR_ELAPSED_TIME" | bc -l`
fi
else
echoinf "Found DB Time but not Elapsed Time - unable to calculate Average Active Sessions"
fi
echodbg "AWR_ELAPSED_TIME = ${ENDCHAR}$AWR_ELAPSED_TIME${ENDCHAR}"
continue
;;
"Buffer Cache:")
# Found Cache Sizes section containing database block size
# The call to tr is necessary to remove the trailing return character
DB_BLOCK_SIZE=$(echo ${AWRLINE[${#AWRLINE[@]}-1]} | tr -d '\r')
echodbg "DB_BLOCK_SIZE = ${ENDCHAR}$DB_BLOCK_SIZE${ENDCHAR}"
continue
;;
"Redo size:")
# Found details for redo bytes per second in the 10g and 11g format AWR reports
# Because this number is occasionally very large Oracle may print it in scientific notation so we have to convert this if neccessary
REDO_WRITE_THROUGHPUT=$(echo ${AWRLINE[2]} |sed -e 's/,//g'| sed -e 's/E+/\*10\^/')
# Convert to MB/sec
if [ -n "$REDO_WRITE_THROUGHPUT" ]; then
if [ "$REDO_WRITE_THROUGHPUT" != 0 ]; then
echodbg "Calling BC: REDO_WRITE_THROUGHPUT=echo 'scale=2; (\$(echo 'scale=6; $REDO_WRITE_THROUGHPUT / 1048576' | bc -l)+0.005)/1' | bc -l"
REDO_WRITE_THROUGHPUT=`echo "scale=2; ($(echo "scale=6; $REDO_WRITE_THROUGHPUT / 1048576" | bc -l)+0.005)/1" | bc -l`
fi
fi
echodbg "REDO_WRITE_THROUGHPUT = ${ENDCHAR}${REDO_WRITE_THROUGHPUT}${ENDCHAR}"
continue
;;
"Redo size")
# Found details for redo bytes per second in the 12c format AWR reports
# Because this number is occasionally very large Oracle may print it in scientific notation so we have to convert this if neccessary
REDO_WRITE_THROUGHPUT=$(echo ${AWRLINE[3]} |sed -e 's/,//g'| sed -e 's/E+/\*10\^/')
# Convert to MB/sec
if [ -n "$REDO_WRITE_THROUGHPUT" ]; then
if [ "$REDO_WRITE_THROUGHPUT" != 0 ]; then
echodbg "Calling BC: REDO_WRITE_THROUGHPUT=echo 'scale=2; (\$(echo 'scale=6; $REDO_WRITE_THROUGHPUT / 1048576' | bc -l)+0.005)/1' | bc -l"
REDO_WRITE_THROUGHPUT=`echo "scale=2; ($(echo "scale=6; $REDO_WRITE_THROUGHPUT / 1048576" | bc -l)+0.005)/1" | bc -l`
fi
fi
echodbg "REDO_WRITE_THROUGHPUT = ${ENDCHAR}${REDO_WRITE_THROUGHPUT}${ENDCHAR}"
# We now know that this is a 12c format AWR report so update the AWR_FORMAT variable accordingly
AWR_FORMAT=12
echovrb "Amending AWR Format to $AWR_FORMAT"
# Amend the section terminator variables accordingly
AWR_SECTION_TERMINATOR_BY_WORD="$NEW_AWR_SECTION_TERMINATOR_BY_WORD"
AWR_SECTION_TERMINATOR_BY_LINE="$NEW_AWR_SECTION_TERMINATOR_BY_LINE"
continue
;;
"Logical reads:")
# Found details for logical reads per second in the 10g and 11g format AWR reports
AWR_LOGICAL_READS=$(echo ${AWRLINE[2]} |sed -e 's/,//g')
echodbg "AWR_LOGICAL_READS = ${ENDCHAR}${AWR_LOGICAL_READS}${ENDCHAR}"
continue
;;
"Logical read")
# Found details for logical reads per second in the 12c format AWR reports
AWR_LOGICAL_READS=$(echo ${AWRLINE[3]} |sed -e 's/,//g')
echodbg "AWR_LOGICAL_READS = ${ENDCHAR}${AWR_LOGICAL_READS}${ENDCHAR}"
continue
;;
"Block changes:")
# Found details for block changes per second
AWR_BLOCK_CHANGES=$(echo ${AWRLINE[2]} |sed -e 's/,//g')
echodbg "AWR_BLOCK_CHANGES = ${ENDCHAR}${AWR_BLOCK_CHANGES}${ENDCHAR}"
continue
;;
"User calls:")
# Found details for user calls per second
AWR_USER_CALLS=$(echo ${AWRLINE[2]} |sed -e 's/,//g')
echodbg "AWR_USER_CALLS = ${ENDCHAR}${AWR_USER_CALLS}${ENDCHAR}"
continue
;;
"Parses:"*)
# Found details for parses per second in the 10g and 11g format AWR reports
AWR_PARSES=$(echo ${AWRLINE[1]} |sed -e 's/,//g')
echodbg "AWR_PARSES = ${ENDCHAR}${AWR_PARSES}${ENDCHAR}"
continue
;;
"Parses"*)
# Found details for parses per second in the 12c format AWR reports
AWR_PARSES=$(echo ${AWRLINE[2]} |sed -e 's/,//g')
echodbg "AWR_PARSES = ${ENDCHAR}${AWR_PARSES}${ENDCHAR}"
continue
;;
"Hard parses:")
# Found details for hard parses per second in the 10g and 11g format AWR reports
AWR_HARD_PARSES=$(echo ${AWRLINE[2]} |sed -e 's/,//g')
echodbg "AWR_HARD_PARSES = ${ENDCHAR}${AWR_HARD_PARSES}${ENDCHAR}"
continue
;;
"Hard parses")
# Found details for hard parses per second in the 12c format AWR reports
AWR_HARD_PARSES=$(echo ${AWRLINE[3]} |sed -e 's/,//g')
echodbg "AWR_HARD_PARSES = ${ENDCHAR}${AWR_HARD_PARSES}${ENDCHAR}"
continue
;;
"Logons:"*)
# Found details for logons per second
AWR_LOGONS=$(echo ${AWRLINE[1]} |sed -e 's/,//g')
echodbg "AWR_LOGONS = ${ENDCHAR}${AWR_LOGONS}${ENDCHAR}"
continue
;;
"Executes:"*)
# Found details for executes per second in the 10g and 11g format AWR reports
AWR_EXECUTES=$(echo ${AWRLINE[1]} |sed -e 's/,//g')
echodbg "AWR_EXECUTES = ${ENDCHAR}${AWR_EXECUTES}${ENDCHAR}"
continue
;;
"Executes"*)
# Found details for executes per second in the 12c format AWR reports
AWR_EXECUTES=$(echo ${AWRLINE[2]} |sed -e 's/,//g')
echodbg "AWR_EXECUTES = ${ENDCHAR}${AWR_EXECUTES}${ENDCHAR}"
continue
;;
"Transactions:"*)
# Found details for transactions per second
# The call to tr is necessary to remove the trailing return character
AWR_TRANSACTIONS=$(echo ${AWRLINE[1]} |sed -e 's/,//g' | tr -d '\r')
echodbg "AWR_TRANSACTIONS = ${ENDCHAR}${AWR_TRANSACTIONS}${ENDCHAR}"
continue
;;
"Buffer Hit")
# Found buffer hit and in-memory sort ratios
BUFFER_HIT_RATIO=${AWRLINE[3]}
echodbg "BUFFER_HIT_RATION = ${ENDCHAR}${BUFFER_HIT_RATION}${ENDCHAR}"
# The call to tr is necessary to remove the trailing return character
INMEMORY_SORT_RATIO=$(echo ${AWRLINE[7]} | tr -d '\r')
echodbg "INMEMORY_SORT_RATIO = ${ENDCHAR}${INMEMORY_SORT_RATIO}${ENDCHAR}"
continue
;;
"Instance Efficiency")
# End of AWR Profile section
echovrb "End of AWR Profile section found at line $ROWCNT"
echodbg "State change AWR_SECTION: $AWR_SECTION -> InstanceEfficiency"
AWR_SECTION="InstanceEfficiency"
# Set Internal Field Seperator to read each word into a different element of the array
set_ifs_to_word_delimited
# Skip the following row which is a header
AWRLINE_SKIP=1
continue
;;
esac
continue
fi # End of handler routine for profile section of AWR report
################################################################## Instance Efficiency ##################################################################
if [ "$AWR_SECTION" = "InstanceEfficiency" ]; then
# Routine for handling the Instance Efficiency section of the report
# Examine two words
case ${AWRLINE[@]:0:2} in
"Buffer Hit")
# Found buffer hit and in-memory sort ratios
BUFFER_HIT_RATIO=${AWRLINE[3]}
echodbg "BUFFER_HIT_RATIO = ${ENDCHAR}${BUFFER_HIT_RATIO}${ENDCHAR}"
# The call to tr is necessary to remove the trailing return character
INMEMORY_SORT_RATIO=$(echo ${AWRLINE[7]} | tr -d '\r')
echodbg "INMEMORY_SORT_RATIO = ${ENDCHAR}${INMEMORY_SORT_RATIO}${ENDCHAR}"
echovrb "End of Instance Efficiency section found at line $ROWCNT"
echodbg "State change AWR_SECTION: $AWR_SECTION -> SearchingForNextSection"
AWR_SECTION="SearchingForNextSection"
# Switch Internal Field Seperator to only recognise newlines as delimiters
set_ifs_to_line_delimited
continue
;;
*)
# Catch all other lines and ignore
continue
;;
esac
fi
################################################################## Top 5 Foreground Events ##################################################################
if [ "$AWR_SECTION" = "Top5Foreground" ]; then
# Routine for handling the Top 5 section of the report
if [ "$FOUND_TOP5_EVENTS" = "$AWR_NOTFOUND" ]; then
# We have just begun processing the Top 5 section so first we need to discover the header row (a set of dashes showing the column headings)
if [ "${AWRLINE[0]:0:6}" = "------" ]; then
echodbg "Searching for header row... found"
FOUND_TOP5_EVENTS=$AWR_FOUND
process_header_row "${AWRLINE[0]}"
if [ "$PROCESS_HEADER_ROW" != "SUCCESS" ]; then
echovrb "Unable to determine width of rows in Top 5 / Top 10 Foreground Events section"
echodbg "State change AWR_SECTION: $AWR_SECTION -> SearchingForNextSection"
AWR_SECTION="SearchingForNextSection"
# Switch Internal Field Seperator to only recognise newlines as delimiters
set_ifs_to_line_delimited
# Give up trying to process this section
if [ "$AWR_FORMAT" = "12" ]; then
AWRLINE_SKIP=10
else
AWRLINE_SKIP=5
fi
fi
else
let BAILOUT_COUNTER++
if [ "$BAILOUT_COUNTER" -eq "$BAILOUT_LIMIT" ]; then
echodbg "Failed to find header row (BAILOUT_COUNTER hit threshold = $BAILOUT_COUNTER)"
echovrb "Unable to determine width of rows in Top 5 / Top 10 Foreground Events section"
echodbg "State change AWR_SECTION: $AWR_SECTION -> SearchingForNextSection"
AWR_SECTION="SearchingForNextSection"
# Switch Internal Field Seperator to only recognise newlines as delimiters
set_ifs_to_line_delimited
# Give up trying to process this section
if [ "$AWR_FORMAT" = "12" ]; then
AWRLINE_SKIP=10
else
AWRLINE_SKIP=5
fi
else
echodbg "Searching for header row... not found (BAILOUT_COUNTER = $BAILOUT_COUNTER)"
fi
fi
continue
else
# Start processing the Top 5 section
let TOP5_LINENUM++
# If we are past the fifth line of the Top 5 then the section is over so exit the section handler
# For the Top 10 sections in 12c format reports we do not care about events above the top 5
if [ "$TOP5_LINENUM" -gt "5" ]; then
# End of Top 5 section
echovrb "End of Top 5 section found at line $ROWCNT"
echodbg "State change AWR_SECTION: $AWR_SECTION -> SearchingForNextSection"
AWR_SECTION="SearchingForNextSection"
# Switch Internal Field Seperator to only recognise newlines as delimiters
set_ifs_to_line_delimited
continue
fi
fi
# The variables AWRCOLn are set in the process_header_row function call in the preceding if statement
if [ "${AWRLINE[0]:0:6}" = "DB CPU" -o "${AWRLINE[0]:0:6}" = "CPU tim" ]; then
# This is the DB CPU line which has a different format to the rest
echodbg "Top 5 line $TOP5_LINENUM is DB CPU"
TOP5EVENT_NAME="DB CPU"
TOP5EVENT_WAITS=""
TOP5EVENT_TIME=$(echo "${AWRLINE[0]}"| cut -c${AWRCOL3}|sed -e 's/^[ \t]*//;s/[ \t]*$//;s/,//g')
TOP5EVENT_AVERAGE=""
TOP5EVENT_PCT_DBTIME=$(echo "${AWRLINE[0]}"| cut -c${AWRCOL5}|sed -e 's/^[ \t]*//;s/[ \t]*$//;s/,//g')
TOP5EVENT_CLASS=""
else
# This is a normal Top 5 line so handle accordingly
TOP5EVENT_NAME=$(echo "${AWRLINE[0]}"| cut -c${AWRCOL1}|sed -e 's/^[ \t]*//;s/[ \t]*$//')
TOP5EVENT_WAITS=$(echo "${AWRLINE[0]}"| cut -c${AWRCOL2}|sed -e 's/^[ \t]*//;s/[ \t]*$//;s/,//g')
TOP5EVENT_TIME=$(echo "${AWRLINE[0]}"| cut -c${AWRCOL3}|sed -e 's/^[ \t]*//;s/[ \t]*$//;s/,//g')
TOP5EVENT_AVERAGE=$(echo "${AWRLINE[0]}"| cut -c${AWRCOL4}|sed -e 's/^[ \t]*//;s/[ \t]*$//;s/,//g')
TOP5EVENT_PCT_DBTIME=$(echo "${AWRLINE[0]}"| cut -c${AWRCOL5}|sed -e 's/^[ \t]*//;s/[ \t]*$//;s/,//g')
TOP5EVENT_CLASS=$(echo "${AWRLINE[0]}"| cut -c${AWRCOL6}|sed -e 's/^[ \t]*//;s/[ \t]*$//')
if [ -n "$TOP5EVENT_WAITS" ]; then
# Check the values extracted are valid numbers
if [[ "$TOP5EVENT_TIME" != *[!0-9,.]* ]]; then
if [[ "$TOP5EVENT_WAITS" != *[!0-9,]* ]]; then
if [ "$TOP5EVENT_WAITS" = 0 ]; then
echovrb "Found zero waits for Top 5 wait event $TOP5EVENT_NAME"
else
# See comments in header section for explanation of the way bc is used in calculating the average on the next lines
echodbg "Calling BC: TOP5EVENT_AVERAGE=echo 'scale=3; (\$(echo 'scale=7; ($TOP5EVENT_TIME / $TOP5EVENT_WAITS) * 1000' | bc -l)+0.0005)/1' | bc -l"
TOP5EVENT_AVERAGE=`echo "scale=3; ($(echo "scale=7; ($TOP5EVENT_TIME / $TOP5EVENT_WAITS) * 1000" | bc -l)+0.0005)/1" | bc -l`
fi
else
echodbg "Unable to parse Top 5 wait event $TOP5EVENT_NAME due to non-numeric string found in TOP5EVENT_WAITS: $TOP5EVENT_WAITS"
fi
else
echodbg "Unable to parse Top 5 wait event $TOP5EVENT_NAME due to non-numeric string found in TOP5EVENT_TIME: $TOP5EVENT_TIME"
fi
fi
fi
echodbg "Top 5 Line $TOP5_LINENUM: Name=$TOP5EVENT_NAME, Waits=$TOP5EVENT_WAITS, Time=$TOP5EVENT_TIME, Ave=$TOP5EVENT_AVERAGE, PctDBTime=$TOP5EVENT_PCT_DBTIME, Class=$TOP5EVENT_CLASS"
# Now place discovered values into the correct array of variables based on TOP5_LINENUM
#echodbg "eval TOP5EVENT${TOP5_LINENUM}_NAME='$TOP5EVENT_NAME'"
eval TOP5EVENT${TOP5_LINENUM}_NAME='$TOP5EVENT_NAME'
#echodbg "eval TOP5EVENT${TOP5_LINENUM}_NAME='$TOP5EVENT_WAITS'"
eval TOP5EVENT${TOP5_LINENUM}_WAITS='$TOP5EVENT_WAITS'
#echodbg "eval TOP5EVENT${TOP5_LINENUM}_NAME='$TOP5EVENT_TIME'"
eval TOP5EVENT${TOP5_LINENUM}_TIME='$TOP5EVENT_TIME'
#echodbg "eval TOP5EVENT${TOP5_LINENUM}_NAME='$TOP5EVENT_AVERAGE'"
eval TOP5EVENT${TOP5_LINENUM}_AVERAGE='$TOP5EVENT_AVERAGE'
#echodbg "eval TOP5EVENT${TOP5_LINENUM}_NAME='$TOP5EVENT_PCT_DBTIME'"
eval TOP5EVENT${TOP5_LINENUM}_PCT_DBTIME='$TOP5EVENT_PCT_DBTIME'
#echodbg "eval TOP5EVENT${TOP5_LINENUM}_NAME='$TOP5EVENT_CLASS'"
eval TOP5EVENT${TOP5_LINENUM}_CLASS='$TOP5EVENT_CLASS'
fi # End of handler routine for Top 5 section of AWR report
################################################################## Cache Sizes ##################################################################
if [ "$AWR_SECTION" = "CacheSizes" ]; then
# Routine for handling the Cache Sizes section of the report - only called with 12c format reports
# Examine two words
case ${AWRLINE[@]:0:2} in
"Buffer Cache:")
# Found Cache Sizes section containing database block size
# The call to tr is necessary to remove the trailing return character
DB_BLOCK_SIZE=$(echo ${AWRLINE[${#AWRLINE[@]}-1]} | tr -d '\r')
echodbg "DB_BLOCK_SIZE = ${ENDCHAR}$DB_BLOCK_SIZE${ENDCHAR}"
continue
;;
"Shared Pool"*)
# End of Cache Sizes section
echovrb "End of Cache Sizes section found at line $ROWCNT"
echodbg "State change AWR_SECTION: $AWR_SECTION -> SearchingForNextSection"
AWR_SECTION="SearchingForNextSection"
# Switch Internal Field Seperator to only recognise newlines as delimiters
set_ifs_to_line_delimited
;;
*)
# Catch all other lines and ignore
continue
;;
esac
fi
################################################################## Time Model Statistics ##################################################################
if [ "$AWR_SECTION" = "TimeModelStatistics" ]; then
# Routine for handling the Time Model Statistics section of the report (only called for reports in 10g format)
# Examine first two words
case ${AWRLINE[@]:0:2} in
"DB CPU")
# Remember to strip out carriage returns and commas
AWR_DB_CPU=$(echo "${AWRLINE[2]}" | tr -d '\r,')
AWR_DB_CPU_PCT_DBTIME=$(echo "${AWRLINE[3]}" | tr -d '\r,')
echodbg "AWR_DB_CPU = ${ENDCHAR}${AWR_DB_CPU}${ENDCHAR}"
echodbg "AWR_DB_CPU_PCT_DBTIME = ${ENDCHAR}${AWR_DB_CPU_PCT_DBTIME}${ENDCHAR}"
;;
*)
# Ignore other lines
continue
esac
continue
fi
################################################################## Operating System Stats ##################################################################
if [ "$AWR_SECTION" = "OperatingSystemStats" ]; then
# Routine for handling the Operating System Stats section of the report
# Examine first word
case ${AWRLINE[@]:0:1} in
"BUSY_TIME")
# Remember to strip out carriage returns and commas
OS_BUSY_TIME=$(echo "${AWRLINE[1]}" | tr -d '\r,')
echodbg "OS_BUSY_TIME = ${ENDCHAR}${OS_BUSY_TIME}${ENDCHAR}"
;;
"IDLE_TIME")
# Remember to strip out carriage returns and commas
OS_IDLE_TIME=$(echo "${AWRLINE[1]}" | tr -d '\r,')
echodbg "OS_IDLE_TIME = ${ENDCHAR}${OS_IDLE_TIME}${ENDCHAR}"
;;
"IOWAIT_TIME")
# Remember to strip out carriage returns and commas
OS_IOWAIT_TIME=$(echo "${AWRLINE[1]}" | tr -d '\r,')
echodbg "OS_IOWAIT_TIME = ${ENDCHAR}${OS_IOWAIT_TIME}${ENDCHAR}"
;;
"SYS_TIME")