RDBMS -- Analyze AWR reports (part by part /section by section) using Python

I like dealing with Python nowadays. By using Python (I used to use perl for these types of things), I am trying to address some issues, especially in environments with Oracle products.  

In this blog post, I will share a python code (it is not finished yet.. it is not final) that can be used to read AWR reports (AWR reports in text format).. 

This code is written just for making a POC, for showing how we can read AWR reports section by section, and load the data that we read into the arrays and use that AWR data for making recommendations. 

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."