V$SQL檢視顯示結果異常的診斷

今天碰到一個奇怪的現在，在檢查會話執行的SQL時發現，V$SQL檢視中SQL_TEXT列中的資料是不正常的。

為了方便的定位問題，將顯示異常的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，也沒有連續顯示，因為連續的兩行並不連貫。

首先想到的就是Oracle的bug，因為除了這個解釋外，很難解釋這個現象。那麼如果確實是由於bug，導致V$SQL顯示不完整，那麼完整的SQL又是什麼呢，是否完整的SQL也會存在問題：

SQL> SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES
2 WHERE HASH_VALUE IN (SELECT HASH_VALUE FROM BAK_V$SQL)
3 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上查詢了半天，卻沒有發現類似的描述。

而且一直存在一個疑惑，沒有道理完全顯示是正常的，而顯示前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

手工新增換行資訊，就可以解決上面的問題。