利用Python监控Oracle当前跑的TOP SESSION数据库落落的专栏 专注SQL调优 性能调优-

利用linux top命令抓出Oracle当前正在跑的进程，记录进程消耗的%CPU，%MEM，RES

根据PID查询当前跑的SQL，以及当前等待事件，监控程序每3秒执行一次

 import cx_Oracle import os import time os.environ['NLS_LANG']='SIMPLIFIED CHINESE_CHINA.UTF8' command="top -bi -n 1 | grep oracle | grep -v top | grep -v _orcl | awk '"+'{print $1 "," $9 "," $7"," $10}'+"'" #你需要将_orcl换成_sid con=cx_Oracle.connect('scott', 'tiger', "192.168.56.10/orcl:pooled", cclass="PYTHON",                             purity=cx_Oracle.ATTR_PURITY_SELF, encoding="UTF-8") cur=con.cursor() cur.arraysize = 100 sql='select decode(sql_id,null,a.program,sql_id),a.event from v$session a, v$process b where a.paddr=b.addr and b.spid=' while True:  date = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())  with os.popen(command, "r") as p:    process = p.read()  if process=='':      print(str(date)+' System is idle')  elif process!='':   line=process.splitlines()   for rows in line:      row=rows.split(",")      cur.execute(sql+row[0])      for x in cur:        sql_id=str(x[0])        event=str(x[1])        print(str(date)+','+row[0]+','+row[1]+'%CPU,'+ row[3]+'%MEM,'+str(round(int((row[2]))/1024))+'MB,'+sql_id+','+event)  time.sleep(3) cur.close() con.close()

代码运行示例：

 [oracle@server ~]$ python3 monitor.py 2020-05-18 13:41:07 System is idle 2020-05-18 13:41:11 System is idle 2020-05-18 13:41:14,3023,75.0%CPU,23.0%MEM,439MB,34149m3vuwxuh,direct path write temp 2020-05-18 13:41:17,3023,68.8%CPU,23.2%MEM,443MB,34149m3vuwxuh,db file scattered read 2020-05-18 13:41:20,3023,58.8%CPU,23.2%MEM,444MB,34149m3vuwxuh,db file scattered read 2020-05-18 13:41:24,3023,62.5%CPU,23.2%MEM,444MB,34149m3vuwxuh,direct path write temp 

第一列是当前时间，第二列是PID，第三列是CPU使用率，第四列是内存使用率，

第五列是当前耗费内存，第六列SQL_ID，第七列等待事件

可以将上面python代码稍作改动，将查询的信息保存到CSV文件中，早上再导入数据库分析