oracle代码静态性能扫描

oracle代码静态性能扫描

传统性能分析

传统的SQL性能分析手段，主要是针对oracle代码的动态分析，也就是通过oracle后台的各种性能视图，捕获执行过的SQL执行语句以及执行情况，包括执行时间、cost、等待事件等等，并以此作为SQL上线前SQL审核的依据。但是我们在测试过程中肯定会遇到以下这种情况，oracle的代码没有被完全测试覆盖，部分代码没有被调用过。由于oracle存储过程中的代码与实际的测试案例不能做到有效的关联，因此这种情况普遍存在，并且影响着投产后的数据库性能稳定。

代码静态性能扫描

代码静态扫描的目的就是利用oracle获取静态执行计划的方法，利用文本规则判断的技术，实现批量方式的代码静态性能扫描

具体流程如下：

1. 获取PACKAGE BODY，FUNCTION，PROCEDURE的名字，可以从USER_OBJECT视图中获取，我是从ddl对象监控清单中获取变化过的程序名字。
2. 从USER_SOURCE视图中拼出程序代码。之前希望利用11g的LISTAGG函数拼，但是这个函数有长度限制，因此通过python脚本拼接。
3. 从程序代码中找出sql，这里有个限制，动态拼接的sql是无法正常识别的，因此这里只扫描正常的sql语句。由于程序中大都使用了变量作为参数，而识别变量的规则相对复杂，因此这里偷懒，利用ORACLE的报错代码ORA-00904:”XXX”: invalide identifier，识别每次执行报错之后的XXX字段，并用:XXX替换成变量模式，通过不断的try exception最终得到可以正常执行的sql
4. 通过explain plan for的方式获取执行计划
5. 利用python正则表达是做规则的分析，例子中只做了执行计划和没有where条件两种规则，其他规则后续慢慢更新。

代码

#coding=utf-8
import cx_Oracle
import re
import hashlib



def GetSqlPlan(conn,sql):
   res3=[]
   try:
       sqlhash = (str(len(sql))+hashlib.new("md5", sql.encode("utf-8")).hexdigest())[0:30]
       sql2 = "explain plan set statement_id='"+sqlhash+"' for " + sql
       cursor2 = conn.cursor()
       cursor2.execute(sql2.encode('utf-8'))
       sql3 = "select * from table(dbms_xplan.display('PLAN_TABLE','"+sqlhash+"','ALL'))"
       cursor2.execute(sql3)
       res3 = cursor2.fetchall()
       print (sqlhash,'\n',sql)
       for y in res3:
           print(y[0])
   except cx_Oracle.DatabaseError as msg:
       e,=msg.args
       oerr_code=e.code
       oerr_text =e.message
       if oerr_code ==904:
           oerr_col=re.match('ORA-(.*?): (.*?):.*',oerr_text).group(2).replace("\"","")
           #print (oerr_col)
           sql=sql.replace(oerr_col,":"+oerr_col)
           #print (sql)
           GetSqlPlan(conn,sql)
   return sql,res3



def SearchPackBody(conn,object_name,object_type):
   sql1 ="SELECT UPPER(TRIM(s.TEXT)) FROM user_source s  WHERE s.TYPE='"+object_type+"' AND s.NAME='"+object_name+"' ORDER BY S.name, S.line"
   cursor1 = conn.cursor()
   cursor1.execute(sql1)
   res1 = cursor1.fetchall()
   sqlbody=[]
   for x in res1:
       sqlbody.append(x[0])
   sqlbody="".join(sqlbody)
   sqltext=sqlbody.replace('\n',' ')
   sqllist=[]
   for startstr in ["SELECT","UPDATE","MERGE","DELETE","INSERT"]:
       sqltmp=re.findall(r' '+startstr+'(.*?);',sqltext)
       for x in sqltmp:
           sql=startstr+x
           sqllist.append(sql)
           #print("@@@@@@",object_name)
           a,b=GetSqlPlan(conn,sql)
           RuleCheck(a,b)
   return sqllist

def SearchAll():
   conn = cx_Oracle.connect('test/test#123@127.0.0.1/testdb')
   sql="SELECT OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ( 'PACKAGE BODY','PROCEDURE','FUNCTION') AND OWNER='CIIS'"
   cursor = conn.cursor()
   cursor.execute(sql)
   res = cursor.fetchall()
   for y in res:
       SearchPackBody(conn,y[0],y[1])

def RuleCheck(sql,plan):
   #没有where条件
   if  (re.search('FROM',sql) and not re.search('WHERE',sql)):
       print("没有WHERE条件语句",sql)
   #执行计划扫描
   for x in plan :
       if (re.search('TABLE ACCESS FULL',x[0])):
           print("全表扫描",x[0])
       if (re.search('NESTLOOP', x[0])):
           print("NESTLOOP连接", x[0])



if __name__ == '__main__':
   SearchAll()