Oracle v$SQLTEXT案例

从 V$SQLTEXT 中追踪
 

在数据库出现瓶颈时，通常可以从 V$SESSIION_WAIT 找到那些正在等待资源的 Session，通过 Session 的 sid，联合 V$SESSION 和 V$SQLTEXT 视图可以捕获这些 Session 正在执行的SQL 语句。
 

以下是一个生产数据库的问题诊断和解决过程， 可以从中体会一下等待事件在解决问题中的指导作用。 该生产环境的操作系统为 Solaris 8，数据库版本为 9.1.7.4， 业务及开发人员报告系统运行缓慢，已经影响业务系统正常使用， 请求协助诊断。
数据库运行缓慢， 转换为数据库语言那就是数据库可能经历了等待，那么可以通过V$SESSION_WAIT（从 Oracle 10g， V$SESSION 视图可以取代 V$SESSION_WAIT 的这一诊断功能） 视图来入手。查询 V$SESSION_WAIT 获取各进程等待事件： 

对于本案例，通过以上输出发现存在大量 db file scattered read 及 db file sequential read 等待，并且全表扫描的等待都位于文件号为 17 的数据文件上。显然全表扫描等操作成为系统最严重的性能影响因素。 

捕获相关 SQL 

确定这些进程因为数据访问产生了等待， 可以考虑捕获这些 SQL 以发现问题。 这里用到了以下脚本 getsqlbysid.sql，该脚本通过已知 session 的 sid，联合 v$session、 v$sqltext 视图，获得相关 Session 正在执行的完整 SQL 语句。
SELECT sql_text FROM v$sqltext a
WHERE a.hash_value = (SELECT sql_hash_value FROM v$session b WHERE b.SID = '&sid')

 

ORDER BY piece ASC;
使用该脚本， 通过从 v$session_wait 中获得的等待全表或索引扫描的进程 SID，捕获问题
SQL：
SQL> @getsqlbysid
Enter value for sid: 18
old 5: where b.sid='&sid'
new 5: where b.sid='18'
SQL_TEXT
-----------------------------------------------------------------------
select i.vc2title,i.numinfoguid from hs_info i where i.intenabledflag = 1
and i.intpublishstate = 1 and i.datpublishdate <=sysdate and i.numcatalogguid = 2047
order by i.datpublishdate desc, i.numorder desc
SQL> /
Enter value for sid: 54
old 5: where b.sid='&sid'
new 5: where b.sid='54'
SQL_TEXT
-----------------------------------------------------------------------
select i.vc2title,i.numinfoguid from hs_info i where i.intenabledflag = 1
and i.intpublishstate = 1 and i.datpublishdate <=sysdate and i.numcatalogguid = 33
order by i.datpublishdate desc, i.numorder desc
SQL> /
Enter value for sid: 49
old 5: where b.sid='&sid'
new 5: where b.sid='49'
SQL_TEXT
-----------------------------------------------------------------------
select i.vc2title,i.numinfoguid from hs_info i where i.intenabledflag = 1
and i.intpublishstate = 1 and i.datpublishdate <=sysdate and i.numcatalogguid = 26
order by i.datpublishdate desc, i.numorder desc
 

对几个进程进行跟踪， 分别得到以上 SQL 语句，这些 SQL 可能就是问题产生的根源。以上语句如果良好编码应该使用绑定变量，但是目前这个不是我们关心的。

使用该应用用户连接， 通过 SQL*Plus 的 AUTOTRACE 功能检查以上 SQL 的执行计划：

通过执行计划看到以上查询使用了全表扫描，而该表这里有 22 万记录，全表扫描已经不再适合。检查该表，存在以下索引：

进一步的检查该表索引键值：

检查发现在 numcatalogguid 字段上并没有索引， 该字段具有很好的区分度，考虑在该字段创建索引以消除全表扫描。

观察系统状况， 原大量等待消失： 

至此，此问题得以解决。通过以上案例， 可以知道从等待事件进行追踪的诊断方法，这种方法在日常数据库诊断中很常用。 

更多v$sqltext信息请参考我另外一篇博客：Oracle v$SQLTEXT