The comments in the code are already clear, so you can understand what we are doing here..
Basically, we read a 11.2.0.4 AWR report, certain parts of it.. Then we populate our arrays.. Once our arrays are populated with AWR data, we check some performance indicators and make our comments and give our recommendations accordingly.
Here is the output of a demo run.. The system we analyze here had a hard parse problem.
#!/usr/bin/python
"""
AWR ANALYZER -- work in progress by Erman Arslan
Creation date : 07.02.2021
"""
#import modules
import re
#declarations
ins_load_array_2d=[]
all_lines=[]
load_profile_array=[]
cpu_core_count_array=[]
instance_efficiency_array=[]
dbtime_elapsed_array=[]
timed_model_array=[]
pattern1= re.compile("Load Profile", re.IGNORECASE)
pattern2= re.compile("Instance Efficiency Percentages", re.IGNORECASE)
pattern3= re.compile("~~~~~~~~~~~~~~", re.IGNORECASE)
pattern4= re.compile("Host Name", re.IGNORECASE)
pattern5= re.compile("Snap Id", re.IGNORECASE)
pattern6= re.compile("---------------- ", re.IGNORECASE)
pattern7= re.compile("Instance Efficiency Percentages", re.IGNORECASE)
pattern8= re.compile("Top 10 Foreground Events by Total Wait Time", re.IGNORECASE)
pattern9= re.compile("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~", re.IGNORECASE)
pattern10= re.compile("End Snap:", re.IGNORECASE)
pattern11= re.compile("Instance Efficiency Percentages", re.IGNORECASE)
pattern12= re.compile("~~~~~~~~~~~~~~", re.IGNORECASE)
pattern13= re.compile("Time Model Statistics", re.IGNORECASE)
pattern14= re.compile("Operating System Statistics", re.IGNORECASE)
pattern15= re.compile("------------------------------------------------------", re.IGNORECASE)
#functions
def read_awr(startword,endword,ignoreword):
concantrated_lines=[]
ins_load_string=""
i=0
global ins_load_array_2d
ins_load_array_2d=[]
for single_line in all_lines:
i=i+1
if startword.search(single_line) != None:
concantrated_lines.append(single_line.rstrip('\n'))
for i in range(i,len(all_lines)):
if endword.search(all_lines[i]) != None:
break
if endword.search(all_lines[i]) == None and ignoreword.search(all_lines[i]) == None:
ins_load_string += all_lines[i]
ins_load_array=re.split('\n',ins_load_string.strip())
for k in ins_load_array:
temp_array=re.split(r'\s{2,}',k.strip())
ins_load_array_2d.append(temp_array)
#MAIN.. OUR PROGRAM START HERE
awr_file='/home/oracle/awr_analyzer/awr_report.txt'
my_awr_file=open(awr_file, 'r')
for line in my_awr_file:
all_lines.append(line)
print "\nThis is just a DEMO! It is like POC for our ability to analyze AWR reports programatically!\n"
print "\nAnalyzing awr report file->", awr_file,"\n"
#reading the load profile section
read_awr(pattern1,pattern2,pattern3)
load_profile_array= ins_load_array_2d
#reading the cpu core counts section
read_awr(pattern4,pattern5,pattern6)
cpu_core_count_array=ins_load_array_2d
#reading the Instance Efficiency Section
read_awr(pattern7,pattern8,pattern9)
instance_efficiency_array=ins_load_array_2d
#Reading DB Time and Elapsed Section
read_awr(pattern10,pattern11,pattern12)
dbtime_elapsed_array=ins_load_array_2d
#Reading Time Model Section
read_awr(pattern13,pattern14,pattern15)
timed_model_array=ins_load_array_2d
#####################################################################
#We populated our array..
#Now let's check some the performance indicator values stored on those arrays
#and make some recommendations
######################################################################
#Getting values from time model section
total_db_cpu_time_temp=[string for string in timed_model_array if "DB CPU" in string]
total_db_parse_time_temp=[string for string in timed_model_array if "parse time elapsed" in string]
total_db_hardparse_time_temp=[string for string in timed_model_array if "hard parse elapsed time" in string]
total_sqlexecute_time_temp=[string for string in timed_model_array if "sql execute elapsed time" in string]
total_db_cpu_time=total_db_cpu_time_temp[0][1]
total_db_parse_time=total_db_parse_time_temp[0][1]
total_db_hardparse_time=total_db_hardparse_time_temp[0][1]
total_sqlexecute_time=total_sqlexecute_time_temp[0][1]
#Getting values from instance efficiency section
execute_to_parse=instance_efficiency_array[3][1]
parse_cpu_to_parse_elapsed=instance_efficiency_array[4][1]
#Getting values from load profile section
hard_parses=load_profile_array[13][1]
total_parses=load_profile_array[12][1]
total_executed_sql=load_profile_array[16][1]
#Getting values from db time elapsed section
elapsed_time=dbtime_elapsed_array[0][1]
db_time=dbtime_elapsed_array[1][1]
elapsed_time=elapsed_time.replace(' (mins)','')
db_time=db_time.replace(' (mins)','')
#Getting values from cpu core count section
core_count=cpu_core_count_array[0][2]
#Making some comments/recommendations
print "\nCOMMENTS and RECOMMENDATIONS"
print "--------------------------------"
if float(db_time) > float(elapsed_time)*float(core_count)*float(0.75):
print "-Note : We have",core_count," core/cores in this machine.\n*DB Time is pushing the limits.. Higher than %75 of the total resources..\n -Note : DB Time is the total Oracle CPU consumption plus + sum of non-idle wait time.\n "
if float(hard_parses)>float(total_parses)/10:
print "*We are doing lots of hard parses.. Hard parse / Total parse ratio is %", format(float(hard_parses)/float(total_parses)*100,'.2f')
print "*Total parse / Execute ratio is %", format(float(total_parses)/float(total_executed_sql)*100,'.2f')
print "*We spent %",parse_cpu_to_parse_elapsed," of our parse time on the CPU..."
print "*Parsing takes place in %",100-float(execute_to_parse)," of the executions.."
print "*%",format(float(total_db_hardparse_time)/float(total_sqlexecute_time)*100,'.2f')," of total sql elapsed time is spent on hard parsing..\n->Fixing this hard parse issue will have a positive effect on response time."
