定期抓取oracle数据库慢SQL保存为execl文件发邮件,需要有SQL_ID和SQL语句,方便开发定位处理。
docker容器集成oracle_client和Python3.6
docker pull ghcr.io/oracle/oraclelinux7-python:3.6-oracledb
#!/usr/bin/env python3
# coding:utf-8
import cx_Oracle
import os
from openpyxl import load_workbook
import smtplib
import time
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
import pandas as pd
import json
import requests
# 查询promethues数据,后期可以做报表发邮件(待开发)
def select_promethues(pr_address, expr):
url = pr_address + '/api/v1/query?query=' + expr
try:
return json.loads(requests.get(url=url).content.decode('utf8', 'ignore'))
except Exception as e:
print(e)
return {}
# SQL查询语句
def sql_data():
sql_yuju = [{"physical_disk": """SELECT distinct
A.SQL_ID as sql_id,
B.SQL_TEXT as sql_text,
A.DISK_READS AS reads,
A.EXECUTIONS AS exec,
round(A.DISK_READS / A.EXECUTIONS) AS reads_exec
FROM V$SQLAREA A, V$SQL B
WHERE A.DISK_READS > 1000
AND A.EXECUTIONS > 200
AND A.sql_id = B.sql_id
AND A.MODULE = 'JDBC Thin Client'
AND A.DISK_READS / A.EXECUTIONS > 500"""}, {"logical_read": """SELECT *
FROM (
SELECT distinct
A.SQL_ID as sql_id,
B.SQL_TEXT as sql_text,
ROUND(A.BUFFER_GETS / A.EXECUTIONS) AS gets_exec,
A.BUFFER_GETS as buffer_gets,
A.EXECUTIONS as exec
FROM V$SQLAREA A, V$SQL B
WHERE A.BUFFER_GETS > 1000
AND A.sql_id = B.sql_id
AND A.MODULE = 'JDBC Thin Client'
AND A.BUFFER_GETS / A.EXECUTIONS > 2000
AND A.EXECUTIONS > 200
ORDER BY gets_exec DESC)
WHERE ROWNUM <= 10"""}, {"full_access": """SELECT *
FROM (SELECT distinct C.SQL_ID AS sql_id,
D.SQL_TEXT as sql_text,
OBJECT_OWNER AS obj_owner,
OBJECT_NAME AS ogj_name,
ROUND(B.BYTES / 1024 / 1024, 0) AS size_m,
SUM(EXECUTIONS_DELTA) AS all_exec,
MAX(EXECUTIONS_DELTA) AS max_exec,
ROUND(SUM(EXECUTIONS_DELTA) * B.BYTES / 1024 / 1024 / 1024 / 800,0) AS all_io_large,
ROUND(MAX(EXECUTIONS_DELTA) * B.BYTES / 1024 / 1024 / 1024 / 40,0) AS max_io_large
FROM DBA_HIST_SQL_PLAN A,
(SELECT OWNER, SEGMENT_NAME, SUM(BYTES) BYTES FROM DBA_SEGMENTS GROUP BY OWNER, SEGMENT_NAME) B,
DBA_HIST_SQLSTAT C, V$SQL D
WHERE A.OPERATION = 'TABLE ACCESS'
AND A.OPTIONS = 'FULL'
AND A.OBJECT_OWNER IN (SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS = 'OPEN' AND USERNAME NOT IN ('SYS', 'SYSMAN', 'SYSTEM', 'PATROL', 'MDSYS', 'ORDDATA', 'OLAPSYS', 'EXFSYS'))
AND B.SEGMENT_NAME = A.OBJECT_NAME
AND B.OWNER = A.OBJECT_OWNER
AND B.BYTES > 1024 * 1024 * 10
AND C.SQL_ID = A.SQL_ID
AND C.PARSING_SCHEMA_NAME NOT IN ('SYS', 'SYSMAN', 'SYSTEM', 'PATROL', 'MDSYS', 'ORDDATA', 'OLAPSYS', 'EXFSYS')
AND EXECUTIONS_DELTA > 100
AND C.SNAP_ID > (SELECT MIN(SNAP_ID) FROM DBA_HIST_SNAPSHOT WHERE BEGIN_INTERVAL_TIME > SYSDATE - 7)
AND A.SQL_ID = D.SQL_ID
GROUP BY C.SQL_ID, D.SQL_TEXT, OBJECT_NAME, OBJECT_OWNER,B.BYTES
ORDER BY ROUND(B.BYTES / 1024 / 1024, 0) DESC, OBJECT_NAME)
WHERE ROWNUM <= 30"""}]
# execl文件的列的标题,需要和oracle查询出来的数据列要相对应,不然会报错
columns = {"physical_disk": ["sql_id", "sql_text", "reads", "exec", "reads_exec"],
"logical_read": ["sql_id", "sql_text", "gets_exec", "buffer_gets", "exec"],
"full_access": ["sql_id", "sql_text", "obj_owner", "ogj_name", "size_m", "all_exec", "max_exec","all_io_large", "max_io_large"]}
return sql_yuju, columns
def operate_oracle():
sql_yuju = sql_data()
cx_Oracle.init_oracle_client(lib_dir="/lib/oracle/21/client64/lib")
conn = cx_Oracle.connect(user="用户", password="密码", dsn="IP:端口/DSN", encoding="UTF-8")
# TNS_ADMIN目录
# conn = cx_Oracle.init_oracle_client(config_dir="/opt/oracle/your_config_dir")
for i in sql_yuju[0]:
for k, v in i.items():
columns_value = sql_yuju[1][k]
cursor = conn.cursor()
cursor.execute(str(v))
oracle_data = list(cursor)
filepath = write_execl(k, oracle_data, columns_value)
for sid in oracle_data:
exel_sql = "select * from table(dbms_xplan.display_cursor('" + sid[0] + "'))"
cursor.execute(exel_sql)
explain_data = list(cursor.fetchall())
sheetname = k + "_explain"
write_execl(sheetname, explain_data, ['columns_value'])
cursor.close()
conn.close()
return filepath
def write_execl(name, oracle_data, columns_list):
df = pd.DataFrame(oracle_data, columns=columns_list) # 最后转换得到的结果
now_time = time.strftime("%Y%m%d", time.localtime())
# 在excel表格的第1列写入, 不写入index
execl_name = "/oracle_data/" + "SlowSQL_execution_plan" + now_time + ".xlsx"
if not os.path.exists(execl_name):
df.to_excel(execl_name, sheet_name=name, index=False)
else:
f = pd.read_excel(execl_name, sheet_name=None, engine='openpyxl')
# str(list(f))是指获取文档的列标题,转成字符格式,判断是传入的列标题名称是否已经存在
if name in str(list(f)):
print(name)
# engine='openpyxl'这个很重要,哪怕你关闭了,在同次执行中,第二次打开的时候会报错。
df1 = pd.DataFrame(pd.read_excel(execl_name, sheet_name=name, engine='openpyxl'))
with pd.ExcelWriter(execl_name, engine='openpyxl') as writer:
book = load_workbook(execl_name)
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df_rows = df1.shape[0]
df.to_excel(writer, sheet_name=name, startrow=df_rows + 1, index=False, header=False)
writer.save()
writer.close()
else:
print("add" + name)
with pd.ExcelWriter(execl_name, engine='openpyxl', mode='a') as writer:
df.to_excel(writer, sheet_name=name, index=False)
writer.save()
writer.close()
return execl_name
def send_email(appendix):
mail_host = "smtp.qq.com"
mail_user = "123456@qq.com"
mail_pass = "123456"
sender = '123456@qq.com'
receivers = ['123456@qq.com']
now_time = time.strftime("%Y%m%d", time.localtime())
# 创建一个带附件的实例
message = MIMEMultipart()
message['From'] = Header(sender, 'utf-8')
message['To'] = ','.join(receivers)
# 邮件标题
subject = 'Oracle_TOP' + now_time
message['Subject'] = Header(subject, 'utf-8')
# 邮件正文内容
message.attach(MIMEText('Oracle_Top抓取并附有执行计划。', 'plain', 'utf-8'))
# 构造附件1,发送文件
att1 = MIMEText(open(appendix, 'rb').read(), 'base64', 'utf-8')
att1["Content-Type"] = 'application/octet-stream'
# 这里的filename可以任意写,写什么名字,邮件中显示什么名字
att1["Content-Disposition"] = "attachment; filename=" + "SlowSQL_execution_plan" + now_time + ".xlsx"
message.attach(att1)
try:
smtpObj = smtplib.SMTP()
smtpObj.connect(mail_host, 587)
smtpObj.login(mail_user, mail_pass)
smtpObj.sendmail(
sender, receivers, message.as_string())
print('Email sent successfully')
smtpObj.quit()
except smtplib.SMTPException as e:
print('error', e)
if __name__ == '__main__':
# pr_address = 'http://127.0.0.1:19090/'
# expr = "up"
# appendix = r'C:\Users\Zachary\Desktop\py_oracle_client\123113.xlsx'
# select_promethues(pr_address, expr)
# send_email(appendix)
filepath = operate_oracle()
send_email(filepath)