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-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()