隐形转换导致全表扫描案例

0414_SCDB数据库缓慢原因
1.检查系统情况，发现以下进程可疑：
|  PID       %CPU     Size      Res     Res      Res     Char    RAM      Paging         Command                                   |
|            Used       KB      Set     Text     Data     I/O     Use   io   other repage                                          |
| 1339444    98.4   176256   105352    91324    14028       19    1%      0      0      0 oracle       

2.检查该线程对应数据库session状态：
SQL> select s.sid,s.serial#,s.username,s.SCHEMANAME,s.machine,s.TERMINAL,a.sql_text
  2         from v$sqlarea a,v$session s,v$process p
  3          where a.hash_value=s.sql_hash_value
  4               and  s.paddr=p.addr
  5               and  p.spid in (1339444);
  SID    SERIAL# USERNAME                       SCHEMANAME                     MACHINE                                                          TERMINAL                       SQL_TEXT
---------- ---------- ------------------------------ ------------------------------ ---------------------------------------------------------------- ------------------------------ --------------------------------------------------------------------------------
1 863 22859 DB_MONITOR DB_MONITOR nhpc-31-36 unknown select session_id, start_time, stop_time from mn_trunk_detail_in_dup where session_id=:1 

3.检查该条语句执行计划：
SQL> explain plan for select session_id, start_time, stop_time from DB_MONITOR.MN_TRUNK_DETAIL_IN_DUP where session_id=:1   
  2  ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2795657260
--------------------------------------------------------------------------------
------------
| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)
| Time     |
--------------------------------------------------------------------------------
------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                        |     1 |    41 |  1855   (1)
| 00:00:23 |
|*  1 |  TABLE ACCESS FULL| MN_TRUNK_DETAIL_IN_DUP |     1 |    41 |  1855   (1)
| 00:00:23 |
--------------------------------------------------------------------------------
------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
  1 - filter("SESSION_ID"=:1)


13 rows selected.

4.检查该表数据量：
SQL> select count(*) from DB_MONITOR.MN_TRUNK_DETAIL_IN_DUP;


  COUNT(*)
----------
    612737

5.检查该表索引情况：
SQL> select index_name,table_name from dba_indexes where table_owner='DB_MONITOR' and table_name='MN_TRUNK_DETAIL_IN_DUP';
no rows selected
SQL> select index_name,table_name from dba_indexes where table_name='MN_TRUNK_DETAIL_IN_DUP';
no rows selected
由上可以看出，该表上面没有建任何索引，导致SQL走全表扫描，执行缓慢，将情况通知客户后，由客户添加该字段索引后得到解决。

6.继续检查，发现又有可疑进程：
            Name            PID  CPU%  PgSp Owner           PageIn        0  PAGING SPACE
             oracle      2437244  24.2  14.1 grid 

查找对应对应session:
SQL> select s.sid,s.serial#,s.username,s.SCHEMANAME,s.machine,s.TERMINAL,a.sql_text
  2         from v$sqlarea a,v$session s,v$process p
  3          where a.hash_value=s.sql_hash_value
  4               and  s.paddr=p.addr
  5               and  p.spid in (1339444);
  SID    SERIAL# USERNAME                       SCHEMANAME                     MACHINE                                                          TERMINAL                       SQL_TEXT
---------- ---------- ------------------------------ ------------------------------ ---------------------------------------------------------------- ------------------------------ --------------------------------------------------------------------------------
1 325 52742 DB_MONITOR DB_MONITOR nhpc-31-36 unknown update mn_acd_detail_dup set once_resolved='0' where inbound_time>:1  and inbound_time<:2 ani=":3</span">

检查索引情况
 SQL> select table_owner,table_name,index_name from dba_indexes where table_name in ('MN_ACD_DETAIL_DUP');


TABLE_OWNER                    TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------ ------------------------------
DB_MONITOR                   MN_ACD_DETAIL_DUP              IDX_ACD_DETAL_INBOUND_TIME
DB_MONITOR                   MN_ACD_DETAIL_DUP              IDX_ACD_DETAL_TMP_SESSION_ID

发现是有索引，但经toad检查，该条sql语句执行计划为全表扫描，同时对session进行跟踪，发现异常：
SQL ID: c99j4d4f12h1w
Plan Hash: 1342417265
update mn_acd_detail_dup set once_resolved='0' 
where
 inbound_time>:1  and inbound_time<:2 ani=":3 </span">

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     1039      0.00       0.03          0          0          0           0
Execute   1040    160.78     166.60         17   12633921        789         753
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2079    160.78     166.63         17   12633921        789         753

在1000次执行中，进行了12633921次查询，这也证明该条语句走的是全表扫描，怀疑是进行了隐式转换或者传错变量，将此情况通知客户，经客户在应用层查找原因为：
在oracle 9i 以前是不支持timestatmp格式的，因此那时候java时间类型为timestamp对应oracle中的date类型，在oracle9i以后，oracle开始支持timestamp时间类型，同时在oracle 9i,10g jdbc有个参数，可以把timestamp时间格式转换成date时间格式，可以支持走date格式的索引，但11g以后不支持这个参数，导致之前的timestamp格式走不了date类型的索引，从而导致全表扫描，sql语句执行缓慢。
通过客户强行将该date字段转换为timestamp字段后解决该问题

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29468144/viewspace-1076706/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29468144/viewspace-1076706/