获取Oracle执行次数等于一的语句(硬解析状况)

我们知道硬解析是一个非常耗资源的动作，尤其是在OLTP系统中如果未使用绑定变量导致硬解析很严重(每秒30次以上)，数据库性能会严重的下降，这时我们需要找到相应的语句来进行优化

原理:通过v$sql视图中找出所有execute 执行次数等于一的并且前面40个字符相同的语句

开发环境

操作系统:CentOS 7.3

Python版本 :2.7

Django版本: 1.10.5

操作系统用户:oracle

建立页面的步骤

我们还是通过这张图的步骤来说明如何建立页面

Alt text

urls.py页面

首先是monitor/urls.py，这节不需要修改这个文件

urlpatterns = [
    url(r'^$', views.index, name='index'),
    url(r'^oracle_command/$',views.oracle_command, name='oracle_command'),
    url(r'^commandresult/$',views.commandresult, name='commandresult'),
]

oracle_command为执行Oracle命令的页面

commandresult为执行完Oracle命令显示结果的页面

views.py

下面为commandresult对应的函数在views.py里面的写法

    elif command_content=='check_executions':
        try:
            db = cx_Oracle.connect(username+'/'+password+'@'+ipaddress+':'+port+'/'+tnsname ,mode=cx_Oracle.SYSDBA)
        except Exception , e:
            content= (ipaddress+' is Unreachable,The reason is '+ str(e)).strip()
            return HttpResponse(content)
        else:
            cursor = db.cursor()
            row=getexecutions(cursor)
            cursor.close()
            db.close()
            title='执行次数等于一语句-'+ipaddress+'-'+tnsname
            tr=['SQL语句','次数','模块']
            dic ={'title':title,'tr':tr,'row':row}
            return render_to_response('oracle_command_result_3.html',dic)

  1. 首先获取到表单中的数据，如 ipaddress,tnsname以及执行的命令

  2. 然后通过ipaddress,tnsname从oraclelist数据库中查找获得用户名密码用于连接

  3. 再判断命令内容，如果是check_executions

  4. 则执行函数getexecutions获取v$sql视图中执行次数等于一的语句，并以SQL语句前40个字符分组，并按数量降序排列，详情看具体代码

  5. 最后把页面的标题以及数据放到dic变量中传到 oracle_command_result_3.html模板文件中

check_executions函数

这里的check_executions函数获取执行次数等于一的语句，详情看具体代码

monitor/command/getoraclecommandresult.py

def getexecutions(cursor):
    fp=open('/home/oracle/mysite/monitor/command/oracle_command/getexecutions.sql','r')
    fp1=fp.read()
    s=cursor.execute(fp1)
    fp.close()
    row=s.fetchall()
    return row

getexecutions.sql

这个SQL获取v$sql视图中执行次数等于一的语句，并以SQL语句前40个字符分组，并按数量降序排列

select substr (sql_text,0, 40), count (*),max(module)
  from v$sql
 where executions = 1
 group by substr (sql_text,0, 40)
 order by count (*) desc

template文件

这里我们使用oracle_command_result_3.html文件来显示

oracle_command_result_3.html

<div id='newadd'>
<h3>{{title}}</h3>
<table class="table">
  <thead>
    <tr>
{% for i in tr %}
        <th>{{i}}</th>
{%endfor%}
    </tr>
  </thead>
{% for a,b,c in row %}
<tbody>
            <tr>
                <td>{{a}}</td>
                <td>{{b}}</td>
                <td>{{c}}</td>
            </tr>
</tbody>
{% endfor %}
</table>
</div>

该模板是一个表格，通过将传过来的变量显示在前端页面

实际效果

http://10.65.202.218:8081/monitor/oracle_command/

Alt text

上面的结果第一条显示出未使用变量的语句有7304个条目在shared pool中，造成空间的浪费

源码地址

源码请查看我的GitHub主页

https://github.com/bsbforever/wechat_monitor

这期讲述了如何获取执行次数等于一的语句，接下来就是通过查找出来的语句来查看其具体来自哪里什么用户执行的