python抓取oracle数据库慢SQL

定期抓取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)