Oracle DBA手记之“V$SQL视图显示结果异常的诊断”
本文节选自《Oracle DBA手记——数据库诊断案例与性能优化实践》第2章“Yangtingkun的DBA工作手记” （作者：杨廷琨）
V$SQL视图显示结果异常的诊断
有一次碰到一个很奇怪的问题，在检查会话所执行的SQL时，发现V$SQL视图中SQL_TEXT列中的数据是不正常的。
由于V$SQL是动态性能视图，里面保存的是当前共享池中加载的SQL语句，所以如果这个SQL不是执行很频繁的话，那么它很可能会被替换出共享池。或者数据库意外重启，也会导致这个SQL彻底丢失。那么首要任务就是保留现场，一旦错误不可再现，那么所有的问题就都无从查起了。
将显示异常的V$SQL记录备份到了BAK_V$SQL表中，首先看一下异常的SQL语句：
SQL> SELECT SQL_TEXT FROM
BAK_V$SQL;
SQL_TEXT
----------------------------------------------------------------------------------------
info.CONTRACT_ITEM_ID,info.BUYER_ORG_ID
a
el未承诺'DISCOUNT_STEP is null or
INC.NUM_STEP is null then
to_char(INC.DISCOUNT_STEP *
100,'0.0') || '%'劭勐?' ||
'未 CASH.CASH is null and CASH.CASH_THIRTY is
null then
'现款:' || to_char(CASH.CASH * 100,'0.0'
'30日结款:'SH_THIRTY is not
null then
case whend end as CASH_DISCOUNT,0,'0.0') ||
'%;'
en info.modify_date >
inc.modify_date and info.modify_date > cash.modify_date then info.mo
显然这个SQL语句是不正常的，语句中甚至连select、insert、update、delete命令都不包括。但是这个SQL又不完全是乱码，从显示的部分看，大部分是有一定逻辑在里面的。
观察这个SQL，第一感觉像是V$SQL视图中显示了部分SQL，而没有从SQL语句的开头部分开始显示。而且即使是部分SQL，也不是连续显示的，因为连续的两行并不连贯。
首先想到的就是Oracle的Bug，因为除了这一点，暂时没有想到其他的原因来解释这个现象。那么如果确实是由于Bug，导致V$SQL显示不完整，那么完整的SQL又是什么呢，是否完整的SQL也会存在问题呢。刚才一直在查询V$SQL视图，下面不妨查询一下V$SQLTEXT_WITH_NEWLINES视图，看看这个视图中的结果是否也是不正常的。并且可以对比两个视图的结果，看看从中能否找到一点线索。
SQL> SELECT SQL_TEXT FROM
V$SQLTEXT_WITH_NEWLINES
2
WHERE HASH_VALUE IN (SELECT HASH_VALUE FROM BAK_V$SQL) ORDER BY PIECE;
SQL_TEXT
----------------------------------------------------------------
SELECT * FROM (
SELECT ROWNUM as numrow, yy.*
from ( select
info.record_id,
info.CONTRACT_ITEM_ID,info.BUYER_ORG_ID
……………
WHERE numrow >= 1
32 rows selected.
出乎意料的是，从V$SQLTEXT_WITH_NEWLINES中查询，发现SQL的结果是正常的，而且V$SQL中显示的内容在V$SQLTEXT_WITH_NEWLINES中都可以找到，只不过不是连续的。从这一点上看，似乎更可以肯定是Bug了。不过还存在几个疑点：
首先在Metalink上没有发现类似的描述，难道存在一个还没有发现的Bug。
其次对比两个视图的结果，二者的差异完全没法解释。V$SQL视图中的SQL_TEXT截取了全部SQL语句的前1000个字节，而V_SQLTEXT_WITH_NEWLINES视图则包含全部的SQL内容，但是查询结果是分行显示的。没有道理显示结果是完全正常的，事实上前1000个字节就出现了错误。而且错误出现得那么离谱，很多信息都是跳着显示的。
关键是找不到引发这个Bug的原因。如果确实是显示问题，那么应该所有的SQL都会有问题。如果仅仅是这个SQL有问题，那么多半问题出在这个SQL本身。
V$SQL中的SQL_TEXT字段长度为1000，对于长度大于1000的SQL，会显示前面1000个字符。从V$SQLTEXT_WITH_NEWLINES视图的结果看，SQL的长度肯定超过了1000，但从V$SQL中的查询结果来看，长度应该远远小于1000。
查询一下V$SQL中SQL_TEXT的具体长度：
SQL> SELECT
LENGTH(SQL_TEXT) FROM BAK_V$SQL;
LENGTH(SQL_TEXT)
----------------
980
长度为980，这个长度倒是对的，可是查询出来的内容却很少，再次查询，将长度和内容一起显示：
SQL> SELECT
LENGTH(SQL_TEXT), SQL_TEXT FROM BAK_V$SQL;
LENGTH(SQL_TEXT)
----------------
SQL_TEXT
-----------------------------------------------------------------------------------------
980
info.CONTRACT_ITEM_ID,info.BUYER_ORG_ID
a
el未承诺'DISCOUNT_STEP is null or
INC.NUM_STEP is null then
to_char(INC.DISCOUNT_STEP *
100,'0.0') || '%'劭勐?' ||
'未 CASH.CASH is null and CASH.CASH_THIRTY is
null then
'现款:' || to_char(CASH.CASH * 100,'0.0'
'30日结款:'SH_THIRTY is not null then
case whend end as CASH_DISCOUNT,0,'0.0') ||
'%;'
en info.modify_date >
inc.modify_date and info.modify_date > cash.modify_date then info.mo
这个格式不是很美观，设置COL调整一下输出的格式：
SQL> COL SQL_TEXT FORMAT
A70 WRAP
SQL> SELECT
LENGTH(SQL_TEXT), SQL_TEXT FROM BAK_V$SQL;
LENGTH(SQL_TEXT) SQL_TEXT
----------------
----------------------------------------------------------------------
info.T ROWNUM as numrow, yy.* from ( select
info.CONTRACT_ITEM_ID,info.BUYER_ORG_ID
as BUYER_OR
when INC.DISCOUNT_STEP is null or
INC.NUM_
'起付诺'EP is null then
to_char(额:' ||
to_char(INC.NUM_STEP) || '万,折扣率:' ||
end as NUM_DISCOUNT,T_STEP * 100,'0.0')
|| '%'
when CASH.CASH is null and
CASH.CASH_THIR
case信? is null then
'现款:' || to_char(CSH.CASH is not null then
end''e
ASH.CASH * 100,'0.0') || '%;'
'en CASH.CASH_THIRTY is not null
then
30日结款:' ||
to_char(CASH.CASH_THIRTY * 100,'0.0') || '%;'
case whend end as CASH_DISCOUNT,
en info.modify_date >
inc.modify_date and info.modify_date > cash.modi
fy_date then info.mo
奇怪的事情出现了，不仅SQL_TEXT的长度内容被覆盖掉了，而且SQL_TEXT的内容并没有从SQL_TEXT的栏位开始，而是从一行的开始位置开始的。更关键的是，查询的内容已经发生了变化。
到这里已经可以确定问题的原因了，为了更加精确的定位问题，将SQL_TEXT中的内容进行DUMP：
SQL> SELECT
DUMP(SUBSTR(SQL_TEXT, 1, 100), 16) FROM BAK_V$SQL;
DUMP(SUBSTR(SQL_TEXT,1,100),16)
-----------------------------------------------------------------------------------------
Typ=1 Len=100:
20,53,45,4c,45,43,54,20,2a,20,46,52,4f,4d,20,28,20,d,20,53,45,4c,45,43,54,20,52,4f,57,4e,55,4d,20,61,73,20,6e,75,6d
,72,6f,77,2c,20,79,79,2e,2a,20,66,72,6f,6d,20,28,20,73,65,6c,65,63,74,20,d,20,69,6e,66,6f,2e,
72,65,63,6f,72,64,5f,69,64,2c,d,20,20
,20,20,20,20,20,20,69,6e,66,6f,2e,43,4f,4e,54,52,41
SQL> SELECT
SUBSTR(SQL_TEXT, 1, 100) FROM BAK_V$SQL;
SUBSTR(SQL_TEXT,1,100)
-----------------------------------------------------------------------------------------
info.CONTRAumrow, yy.* from ( select
从DUMP文件中已经可以清晰地看到问题的原因了，SQL语句中仅使用了ASCII(0XD)回车符，而没有使用ASCII(0XA)换行。这会导致在Unix和Linux环境下，第二行的数据仍然从第一行的第一列位置开始输出，这样就会覆盖第一行本来的内容。
一个简单的例子如下：
SQL> SELECT 'AB' ||
CHR(13) || 'C' FROM DUAL;
'AB'
----
CB
1 row selected.
SQL> SELECT 'AB' ||
CHR(10) || CHR(13) || 'C' FROM DUAL;
'AB'|
-----
AB
C
1 row selected.
正是这个原因造成了V$SQL中显示不正常，而V$SQLTEXT_WITH_NEWLINES中由于每行只有64个字符，因此还没有被覆盖就切换到下一条记录了。
了解了问题的原因，剩下的事情就简单了：
SQL> SELECT
REPLACE(SQL_TEXT, CHR(13), CHR(10) || CHR(13)) FROM BAK_V$SQL;
REPLACE(SQL_TEXT,CHR(13),CHR(10)||CHR(13))
----------------------------------------------------------------------------------------
SELECT * FROM (
SELECT ROWNUM as numrow, yy.* from ( select
info.record_id,
info.CONTRACT_ITEM_ID,info.BUYER_ORG_ID
as BUYER_ORGID,
case
.
.
.
end end as CASH_DISCOUNT,
case when info.modify_date >
inc.modify_date and info.modify_date > cash.modify_date then info.mo
手工添加换行信息，就可以解决上面的问题。看似是Bug的问题，其实并不是Bug引起的，而是不同操作系统的差异导致了这个问题。在Windows环境中，一个回车符就足够了，但是对于Unix和Linux环境，还需要一个换行符。
------------相关链接-------------
