首页 > 数据库技术 > 详细

oracle sql语句跟踪及性能分析工具实现

时间:2017-06-27 14:30:05      阅读:321      评论:0      收藏:0      [点我收藏+]

在网上找了一大圈,没找着合适的工具来跟踪oracle一段时间的sql。

我们的场景是打算自动化跑遍所有场景(rft)+fiddler跟踪请求+后端跟踪sql,根据结果去分析慢的请求和sql,本来awr报告也能实现,但是每次都用awr比较麻烦,想的是能够简单点,直接定位到执行慢的sql或者耗cpu的sql。

这个工具抓取的sql不是实时的,是某段时间的sql,原理也很简单,比awr的sql要简单的多,试了一把,跟awr给出的数据是一致的。

最终效果:准备操作时开启跟踪,结束操作后关闭跟踪,然后工具会提供这段时间的sql及相关指标,包括磁盘读次数、cpu、sql执行时间、解析次数、执行次数等,根据需要可以把跟踪结果保存到数据库或者导出到excel。

视图介绍

用到的视图:v$sqlarea

这个视图存放了近期执行过的sql,如果想要达到awr的效果,可以再结合dba_hist_sqlstat等相关视图进行查询。

本次用到的相关字段:

SQL_FULLTEXT:完整的sql语句,类型为lob字段,要获取内容可以使用DBMS_LOB.SUBSTR(Sql_Fulltext,2000,1)进行读取,获取sql前2000个字符;

PARSING_SCHEMA_NAME:执行sql的用户;

MODULE:执行sql的应用程序,如IIS为w3wp.exe,plsql为PL/SQL Developer;

PARSE_CALLS:解析次数,包括软解析和硬解析;

EXECUTIONS:sql执行次数;

BUFFER_GETS:读内存次数;

DISK_READS:读磁盘次数(可以结合读内存次数计算命中率);

CPU_TIME:cpu时间,微秒;

ELAPSED_TIME:sql执行时间,微秒;

LAST_ACTIVE_TIME:sql上次的活动时间。

由于v$sqlarea存放的数据都为累加值,所以要计算本次的数据的话,需要减去之前的旧数据。

实现原理

开始执行操作前,备份v$sqlarea,存放到oldtable;

获取当前系统时间,存放到sysdate;

结束操作后,备份v$sqlarea,存放到newtable。至此,准备工作就做完了,接下来就是整合数据了。

 

根据newtable中的last_active_time>sysdate获取本次执行过的sql,然后相关的指标数据都是newtable减去oldtable(两张表的关联字段为hash_value和address,sql_id应该也可以)。

要注意newtable中新出现的sql,所以用left join并用nvl转换一下空值。如果关心其他指标,可以根据实际情况添加字段。

如果要避免其他因素的干扰,可以只显示符合要求的sql,比如PARSING_SCHEMA_NAME=登陆用户或者MODULE=w3wp.exe等。

核心sql:

sql = "SELECT * FROM (SELECT n.parsing_schema_name AS SCHEMA,n.module AS MODULE, DBMS_LOB.SUBSTR(n.Sql_Fulltext,2000,1) AS SQL_TEXT,n.parse_calls-nvl(o.parse_calls,0) AS PARSE_CALLS,n.buffer_gets-nvl(o.buffer_gets,0) AS BUFFER_GETS,n.disk_reads-nvl(o.disk_reads,0) AS DISK_READS,n.executions-nvl(o.executions,0) AS EXECUTIONS,round((n.cpu_time-nvl(o.cpu_time,0))/1000000,2) AS CPU_TIME,round((n.cpu_time-nvl(o.cpu_time,0))/((n.executions-nvl(o.executions,0))*1000000),2) AS CPU_TIME_PER_EXE,round((n.elapsed_time-nvl(o.elapsed_time,0))/((n.executions-nvl(o.executions,0))*1000000),2) AS ELAPSED_TIME_PER_EXE " +
"FROM "+NewTable+" n " +
"LEFT JOIN "+OldTable+" o ON o.hash_value = n.hash_value AND o.address = n.address " +
"WHERE n.last_active_time > to_date(‘" + Sysdate + "‘,‘yyyy/MM/dd hh24:mi:ss‘) " +
"AND(n.executions - nvl(o.executions,0)) > 0 " +
"ORDER BY ELAPSED_TIME_PER_EXE DESC) WHERE ROWNUM<=20";

最终效果(c#版):

技术分享

 

oracle sql语句跟踪及性能分析工具实现

原文:http://www.cnblogs.com/Invokerr/p/7084786.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!