AWR报告可以告诉我们ORACLE的各项性能指标，但有些关键指标如db_time，趋势往往更能说明问题，所以如果用python实现抓取最近7天的关键指标，并生成excel图表，每天自动发出邮件，这样只要看一看趋势，就对整个库的总体性能及负载有底了，以下是具体实现代码：

点击(此处)折叠或打开

#-*- coding:utf-8 -*-
import cx_Oracle
import datetime
import smtplib
import tempfile
import xlsxwriter
from email.message import Message
from email.encoders import encode_base64
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
import ConfigParser
today = datetime.datetime.now()
xls_name = u'数据库性能趋势报告_%d_%d_%d.xlsx' % (today.year,today.month,today.day)
workbook = xlsxwriter.Workbook(xls_name) #创建一个Excel文件
format=workbook.add_format() #定义format格式对象
format.set_border(1) #定义format对象单元格边框加粗(1像素)的格式
format_title=workbook.add_format() #定义format_title格式对象
format_title.set_border(1) #定义format_title对象单元格边框加粗(1像素)的格式
format_title.set_bg_color('#cccccc') #定义format_title对象单元格背景颜色为
#'#cccccc'的格式
format_title.set_align('center') #定义format_title对象单元格居中对齐的格式
format_title.set_bold() #定义format_title对象单元格内容加粗的格式
format_ave=workbook.add_format() #定义format_ave格式对象
format_ave.set_border(1) #定义format_ave对象单元格边框加粗(1像素)的格式
format_ave.set_num_format('0.00') #定义format_ave对象单元格数字类别显示格式
conf=ConfigParser.ConfigParser()
conf.read('dbmon.ini')
sections=conf.sections()
db1 = cx_Oracle.connect('user', 'user', 'localhost/orcl')
cursor1 = db1.cursor()
cursor1.execute(sql)
row1=cursor1.fetchall()
clob = cursor1.var(cx_Oracle.CLOB)
for r1 in row1:
mon_item=r1[0]
#print mon_item
clob=r1[2].read()
sql=clob
#print sql
worksheet = workbook.add_worksheet(mon_item) #创建一个工作表对象
#设置列宽
worksheet.set_column('A:A',12)
worksheet.set_column('B:B',4)
worksheet.set_column('C:C',10)
worksheet.set_column('D:L',12)
#if sql=='':
sql="""select inst,
decode(h, 0, '0:00-8:00', '8:00-24:00') trange,
listagg(db_date, ',') within group(order by time_range) db_date,
listagg(db_time, ',') within group(order by time_range) db_time
from (select s.h,
s.inst,
to_char(s.begin_time, 'mmdd hh24') || '_' ||
to_char(s.end_time, 'hh24') time_range,
to_char(s.begin_time, 'yyyy-mm-dd') db_date,
(SELECT round((sum(e.value) - sum(b.value)) / 1000000 / 60, 2)
FROM DBA_HIST_SYS_TIME_MODEL e, DBA_HIST_SYS_TIME_MODEL b
WHERE e.SNAP_ID = s.end_snap
AND e.DBID = s.DBID
AND e.INSTANCE_NUMBER = s.INST
AND e.STAT_NAME = 'DB time'
and b.SNAP_ID = s.begin_snap
AND b.DBID = e.DBID
AND b.INSTANCE_NUMBER = e.instance_number
AND b.STAT_NAME = e.stat_name) db_time
from (select *
from (select s.snap_id begin_snap,
lead(snap_id, 1, 0) over(partition by s.dbid, s.instance_number order by snap_id) end_snap,
s.dbid,
s.instance_number inst,
cast(s.end_interval_time as date) begin_time,
lead(cast(s.end_interval_time as date), 1, null) over(partition by s.dbid, s.instance_number order by snap_id) end_time,
extract(hour from s.end_interval_time) h
from dba_hist_snapshot s
where (extract(hour from s.end_interval_time) = 8 or
extract(hour from s.end_interval_time) = 0)
order by s.snap_id) t
where t.end_snap != 0) s
order by s.inst, s.h, s.begin_snap)
group by inst, h
"""
i = 1
for sec in sections:
db_name=sec
usrname=conf.get(sec,"username")
#print(usrname)
usrpwd=conf.get(sec,"pwd")
#print(usrpwd)
dburl=conf.get(sec,"dburl")
#print(dburl)
db = cx_Oracle.connect(usrname,usrpwd,dburl)
#print('con_suc')
cursor = db.cursor()
cursor.execute(sql)
row=cursor.fetchall()
#写标题
dt1=row[0][2].split(',')
l1=[u'数据库名',u'节点',u'时段']+dt1
worksheet.write_row('A'+str(i), l1,format_title)
row_begin=i+1
for r in row:
#把结果行写入excel
i += 1
#print i
dt2=map(float,r[3].split(','))
l2=[db_name,r[0],r[1]]+dt2
worksheet.write_row('A'+str(i), l2,format)
row_end=i
chart = workbook.add_chart({'type': 'column'}) #创建一个图表对象
for cur_row in range(row_begin, row_end+1):
chart.add_series({
'categories': '='+mon_item+'!$D$'+str(row_begin-1)+':$L$'+str(row_begin-1), #作为图表数据标签(X轴)
'values': '='+mon_item+'!$D$'+str(cur_row)+':$L$'+str(cur_row),
'line': {'color': 'black'}, #线条颜色定义为black(黑色)
#'name': '=Sheet1!$A$'+str(cur_row), #引用业务名称为图例项
'data_labels': {'value': 1}, #显示数据标签
})
# Hide the chart legend since the keys are shown on the data table.
chart.set_legend({'position': 'none'})
chart.set_size({'width': 1000, 'height': 300}) #设置图表大小
chart.set_title ({'name': db_name+' - '+mon_item+u' - 趋势图'}) #设置图表（上方）大标题
chart.set_y_axis({'name': mon_item}) #设置y轴（左侧）小标题
i += 2
worksheet.insert_chart('A'+str(i), chart) #在Ai单元格插入图表
i += 17
cursor.close()
db.close()
cursor1.close()
db1.close()
workbook.close() #关闭Excel文档
msg = MIMEMultipart()
msg['From'] = 'yinchunchao'
msg['To'] = 'ycc@netease.com'
msg['Subject'] = u'数据库性能趋势报告_%d-%d-%d ' % (today.year, today.month,today.day)
attachment = MIMEBase('application', 'vnd.ms-excel')
attachment.set_payload(open(xls_name,'rb').read())
encode_base64(attachment)
attachment.add_header('Content-Disposition', 'attachment;filename=数据库性能趋势报告_%d_%d_%d.xlsx' % (today.year, today.month,today.day))
msg.attach(attachment)
emailserver = smtplib.SMTP("smtp.netease.com",timeout=30)
emailserver.login('ycc@netease.com','pwd')
emailserver.sendmail(msg['From'], msg['To'], msg.as_string())
emailserver.quit()

这里实现了数据库的配置化，指标则只实现了db_time，其实也很容易扩展并配置化的；
实际的使用效果还是不错的，不用每天去看几十份AWR了，但不会错过需要关注的库与问题，对趋势反映的库针对性处理就行了；

ITPub博客

python实现每天的数据库性能趋势图表邮件

最新文章