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