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

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

点击(此处)折叠或打开

1. #-*- coding:utf-8 -*-
   
2. import cx_Oracle
   
3. import datetime
   
4. import smtplib
   
5. import tempfile
   
6. import xlsxwriter
   
7. from email.message import Message
   
8. from email.encoders import encode_base64
   
9. from email.mime.base import MIMEBase
   
10. from email.mime.multipart import MIMEMultipart
    
11. import ConfigParser
    
12. 
    
13. 
    
14. today = datetime.datetime.now()
    
15. 
    
16. 
    
17. xls_name = u'数据库性能趋势报告_%d_%d_%d.xlsx' % (today.year,today.month,today.day)
    
18. 
    
19. 
    
20. workbook = xlsxwriter.Workbook(xls_name) #创建一个Excel文件
    
21. 
    
22. 
    
23. format=workbook.add_format() #定义format格式对象
    
24. format.set_border(1) #定义format对象单元格边框加粗(1像素)的格式
    
25. 
    
26. 
    
27. format_title=workbook.add_format() #定义format_title格式对象
    
28. format_title.set_border(1) #定义format_title对象单元格边框加粗(1像素)的格式
    
29. format_title.set_bg_color('#cccccc') #定义format_title对象单元格背景颜色为
    
30.                                        #'#cccccc'的格式
    
31. format_title.set_align('center') #定义format_title对象单元格居中对齐的格式
    
32. format_title.set_bold() #定义format_title对象单元格内容加粗的格式
    
33.  
    
34. format_ave=workbook.add_format() #定义format_ave格式对象
    
35. format_ave.set_border(1) #定义format_ave对象单元格边框加粗(1像素)的格式
    
36. format_ave.set_num_format('0.00') #定义format_ave对象单元格数字类别显示格式
    
37. 
    
38. 
    
39. conf=ConfigParser.ConfigParser()
    
40. conf.read('dbmon.ini')
    
41. sections=conf.sections()
    
42. 
    
43. 
    
44. 
    
45. db1 = cx_Oracle.connect('user', 'user', 'localhost/orcl')
    
46. cursor1 = db1.cursor()
    
47. cursor1.execute(sql)
    
48. row1=cursor1.fetchall()
    
49. 
    
50. 
    
51. 
    
52. 
    
53. clob = cursor1.var(cx_Oracle.CLOB)
    
54. 
    
55. 
    
56. for r1 in row1:
    
57.     mon_item=r1[0]
    
58.     #print mon_item
    
59.     clob=r1[2].read()
    
60.     sql=clob
    
61.     #print sql
    
62.     
    
63.     worksheet = workbook.add_worksheet(mon_item) #创建一个工作表对象
    
64.     #设置列宽
    
65.     worksheet.set_column('A:A',12)
    
66.     worksheet.set_column('B:B',4)
    
67.     worksheet.set_column('C:C',10)
    
68.     worksheet.set_column('D:L',12)
    
69. 
    
70. 
    
71.     #if sql=='':
    
72.         sql="""select inst,
    
73.                decode(h, 0, '0:00-8:00', '8:00-24:00') trange,
    
74.                listagg(db_date, ',') within group(order by time_range) db_date,
    
75.                listagg(db_time, ',') within group(order by time_range) db_time
    
76.           from (select s.h,
    
77.                        s.inst,
    
78.                        to_char(s.begin_time, 'mmdd hh24') || '_' ||
    
79.                        to_char(s.end_time, 'hh24') time_range,
    
80.                        to_char(s.begin_time, 'yyyy-mm-dd') db_date,
    
81.                        (SELECT round((sum(e.value) - sum(b.value)) / 1000000 / 60, 2)
    
82.                           FROM DBA_HIST_SYS_TIME_MODEL e, DBA_HIST_SYS_TIME_MODEL b
    
83.                          WHERE e.SNAP_ID = s.end_snap
    
84.                            AND e.DBID = s.DBID
    
85.                            AND e.INSTANCE_NUMBER = s.INST
    
86.                            AND e.STAT_NAME = 'DB time'
    
87.                            and b.SNAP_ID = s.begin_snap
    
88.                            AND b.DBID = e.DBID
    
89.                            AND b.INSTANCE_NUMBER = e.instance_number
    
90.                            AND b.STAT_NAME = e.stat_name) db_time
    
91.                   from (select *
    
92.                           from (select s.snap_id begin_snap,
    
93.                                        lead(snap_id, 1, 0) over(partition by s.dbid, s.instance_number order by snap_id) end_snap,
    
94.                                        s.dbid,
    
95.                                        s.instance_number inst,
    
96.                                        cast(s.end_interval_time as date) begin_time,
    
97.                                        lead(cast(s.end_interval_time as date), 1, null) over(partition by s.dbid, s.instance_number order by snap_id) end_time,
    
98.                                        extract(hour from s.end_interval_time) h
    
99.                                   from dba_hist_snapshot s
    
100.                                  where (extract(hour from s.end_interval_time) = 8 or
     
101.                                        extract(hour from s.end_interval_time) = 0)
     
102.                                  order by s.snap_id) t
     
103.                          where t.end_snap != 0) s
     
104.                  order by s.inst, s.h, s.begin_snap)
     
105.          group by inst, h
     
106.         """
     
107. 
     
108. 
     
109.     i = 1
     
110. 
     
111. 
     
112.    
     
113.     for sec in sections:
     
114.         db_name=sec
     
115.         usrname=conf.get(sec,"username")
     
116.         #print(usrname)
     
117.         usrpwd=conf.get(sec,"pwd")
     
118.         #print(usrpwd)
     
119.         dburl=conf.get(sec,"dburl")
     
120.         #print(dburl)
     
121. 
     
122. 
     
123.         db = cx_Oracle.connect(usrname,usrpwd,dburl)
     
124.         #print('con_suc')
     
125.         cursor = db.cursor()
     
126.         cursor.execute(sql)
     
127.         
     
128.         row=cursor.fetchall()
     
129.           
     
130.         #写标题
     
131.         dt1=row[0][2].split(',')
     
132.         l1=[u'数据库名',u'节点',u'时段']+dt1
     
133.         worksheet.write_row('A'+str(i), l1,format_title)
     
134.         row_begin=i+1
     
135.         for r in row:
     
136.             #把结果行写入excel
     
137.             i += 1
     
138.             #print i
     
139.             dt2=map(float,r[3].split(','))
     
140.             l2=[db_name,r[0],r[1]]+dt2
     
141.             worksheet.write_row('A'+str(i), l2,format)
     
142.         row_end=i
     
143. 
     
144. 
     
145. 
     
146. 
     
147.         chart = workbook.add_chart({'type': 'column'}) #创建一个图表对象
     
148.         
     
149.        
     
150.         for cur_row in range(row_begin, row_end+1):
     
151.              chart.add_series({
     
152.             'categories': '='+mon_item+'!$D$'+str(row_begin-1)+':$L$'+str(row_begin-1), #作为图表数据标签(X轴)
     
153.             'values': '='+mon_item+'!$D$'+str(cur_row)+':$L$'+str(cur_row),
     
154.             'line': {'color': 'black'}, #线条颜色定义为black(黑色)
     
155.             #'name': '=Sheet1!$A$'+str(cur_row), #引用业务名称为图例项
     
156.              'data_labels': {'value': 1}, #显示数据标签
     
157.             })
     
158.         
     
159.          
     
160.         # Hide the chart legend since the keys are shown on the data table.
     
161.         chart.set_legend({'position': 'none'})
     
162.         chart.set_size({'width': 1000, 'height': 300}) #设置图表大小
     
163.         chart.set_title ({'name': db_name+' - '+mon_item+u' - 趋势图'}) #设置图表（上方）大标题
     
164.         chart.set_y_axis({'name': mon_item}) #设置y轴（左侧）小标题
     
165. 
     
166. 
     
167.         i += 2
     
168.         worksheet.insert_chart('A'+str(i), chart) #在Ai单元格插入图表
     
169.         i += 17
     
170.         cursor.close()
     
171.         db.close()
     
172.         
     
173. cursor1.close()
     
174. db1.close()
     
175. workbook.close() #关闭Excel文档
     
176. 
     
177. 
     
178. 
     
179. 
     
180. msg = MIMEMultipart()
     
181. msg['From'] = 'yinchunchao'
     
182. msg['To'] = 'ycc@netease.com'
     
183. msg['Subject'] = u'数据库性能趋势报告_%d-%d-%d ' % (today.year, today.month,today.day)
     
184. 
     
185. 
     
186. attachment = MIMEBase('application', 'vnd.ms-excel')
     
187. attachment.set_payload(open(xls_name,'rb').read())
     
188. encode_base64(attachment)
     
189. attachment.add_header('Content-Disposition', 'attachment;filename=数据库性能趋势报告_%d_%d_%d.xlsx' % (today.year, today.month,today.day))
     
190. msg.attach(attachment)
     
191. 
     
192. 
     
193. emailserver = smtplib.SMTP("smtp.netease.com",timeout=30)
     
194. emailserver.login('ycc@netease.com','pwd')
     
195. emailserver.sendmail(msg['From'], msg['To'], msg.as_string())
     
196. emailserver.quit()

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13365316/viewspace-2123892/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13365316/viewspace-2123892/