[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'