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博客 ” ,链接:http://blog.itpub.net/13365316/viewspace-2123892/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13365316/viewspace-2123892/