pb 执行存储过程带参数_ORACLE带参数TOP SQL执行还原

在前面的文章中有介绍过ORACLE中TOP SQL的查询方法：

select to_char(a.start_time, 'yyyy-mm-dd hh24:mi:ss') as 开始执行时间,

a.username as 用户名,

a.sql_id,

a.elapsed_seconds as "用时(秒)",

b.module as 调用客户端, --为空或者JDBC Thin Client，PL/SQL Developer，TOAD 12.1.0.22等，表示调用的客户端

b.sql_text as SQL内容

from v$session_longops a, v$sqlarea b

where a.start_time > sysdate - 0.5 / 24 --半小时之内的数据

and a.username not in ('SYS') --排除系统用户

and a.elapsed_seconds >= 30 --执行时间超过指定值

and a.sql_id = b.sql_id

order by 1 desc;

这个查询方法能查出执行特别慢的SQL，但是查出来的SQL，参数部分只有占位符，没有具体值，没办法直接执行。比如象下面这样的：

SELECT * FROM cs_custinfo WHERE nvl ( dr, :"SYS_B_0" ) = :"SYS_B_1" and code like :"SYS_B_2" and name like :"SYS_B_3" and pk_org = :"SYS_B_4" and enablestate = :"SYS_B_5"

要想手动执行这个SQL，需要针对每个参数手工填入值，由于不知道参数值，SQL只能边猜测边构造，效率低不说，如果查询效率跟参数取值有关，还可能得出错误的结论。

其实在上面查询中用到的v$sqlarea表中是有保存了SQL的参数值的，这就是v$sqlarea. bind_data，但是这个字段值不能直接使用，因为保存的是类似下面这样的值：BEDA0B2004005DF0AF1500063FC002160180C002160180F001800354022525F40107D003541A25C1D9B7DACAD0CFE9BAC6CAA2CEEFC1F7D3D0CFDEB9ABCBBE25F0012003541447524F55504F5247545950453030303030303030F0012003540132

要解析这个字段，就需要使用DBMS_SQLTUNE.EXTRACT_BINDS函数：

select DBMS_SQLTUNE.EXTRACT_BINDS('BEDA0B2004005DF0AF1500063FC002160180C002160180F001800354022525F40107D003541A25C1D9B7DACAD0CFE9BAC6CAA2CEEFC1F7D3D0CFDEB9ABCBBE25F0012003541447524F55504F5247545950453030303030303030F0012003540132') from dual;

在PLSQL DEVELOPER里结果会是一个集合：

点击集合，会出来下面的信息：

有用的主要是三个字段：

POSITION:参数位置

DATATYPE_STRING:参数类型

VALUE_STRING:参数值

有了以上信息，我们就很容易还原上面查询出来的TOP　SQL了：

SELECT *

FROM cs_custinfo

WHERE nvl(dr, 0) = 0

and code like '%%'

and name like '%临汾市祥浩盛物流有限公司%'

and pk_org = 'GROUPORGTYPE00000000'

and enablestate = '2'

注意参数类型是数字，参数值就直接用；参数类型是字符串，参数值要加上单引号；其他的相信大家能举一反三自己推导。

完善后的TOP　SQL查询如下：

select to_char(a.start_time, 'yyyy-mm-dd hh24:mi:ss') as 开始执行时间,

a.username as 用户名,

a.sql_id,

a.elapsed_seconds as "用时(秒)",

b.module as 调用客户端, --为空或者JDBC Thin Client，PL/SQL Developer，TOAD 12.1.0.22等，表示调用的客户端

b.sql_text as SQL内容 ,

DBMS_SQLTUNE.EXTRACT_BINDS(b.bind_data) 绑定变量参数值

from v$session_longops a, v$sqlarea b

where a.start_time > sysdate - 0.5 / 24 --半小时之内的数据

and a.username not in ('SYS') --排除系统用户

and a.elapsed_seconds >= 5 --执行时间超过指定值

and a.sql_id = b.sql_id

order by 1;

我的文章都是自己在实际工作中解决问题的经验总结，如果这篇文章对您有帮助，希望您能关注点赞转发，谢谢！