oracle代码静态性能扫描

来源：互联网发布：mac book pro官网编辑：程序博客网时间：2024/05/21 20:30

oracle代码静态性能扫描

传统性能分析

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

代码静态性能扫描

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

具体流程如下：

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

代码

#coding=utf-8import cx_Oracleimport reimport hashlibdef 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,res3def 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 sqllistdef 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()

1 0