[20141013]奇怪的sql語句.txt

[20141013]奇怪的sql語句.txt

--今天看生產系統執行計劃,發現很奇怪.好在以前遇到類似的問題,很快定位,不過還是要求開發修正程式.
--那scott.emp表來說明:

select * from emp where
empno = 7369 and
         ename='SMITH';

--注意裡面有^M,表示ascii=0x0d.如果

$ cat a.sql
select * from emp where
         ename='SMITH';

SCOTT@testdg> @a
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

SCOTT@testdg> @dpc '' ''
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  4a7a2a2uanf4v, child number 0
-------------------------------------
         ename='SMITH'e empno = 7369 and

Plan hash value: 3649078246

--------------------------------------------------------------------------
| Id  | Operation                   | Name         | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |        |     1 (100)|
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP          |      1 |     1   (0)|
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP_EMPNO |      1 |     0   (0)|
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME"='SMITH')
   2 - access("EMPNO"=7369)

--看到的sql語句很奇怪.

SCOTT@testdg> select sql_text from v$sql where sql_id='4a7a2a2uanf4v';
SQL_TEXT
------------------------------------------------------------
ename='SMITH'emp where empno = 7369 and

SCOTT@testdg> select sql_fulltext from v$sql where sql_id='4a7a2a2uanf4v';
SQL_FULLTEXT
----------------------------------------------------------------------------------------------------
select * from emp where
         ename='SMITH'

--看到的sql程式碼很奇怪.使用dump很容易發現存在問題.看看sql_fulltext的輸出,僅僅看到ename='SMITH'的條件,很容易誤判.
--我看了一些PB的程式碼,發現有些開發~r,沒有~n.導致出現這種情況.

--關鍵是如何找到還有那些.我簡單寫了指令碼如下:

SCOTT@testdg> select sql_id,replace(sql_fulltext,chr(13),'') c60 from v$sqlarea where instr(replace(sql_fulltext,chr(13)||chr(10),''),chr(13))>=1;
SQL_ID        C60
------------- ------------------------------------------------------------
4a7a2a2uanf4v select * from emp where
              empno = 7369 and         ename='SMITH'