ITPub博客

首页 > 数据库 > Oracle > 隐形转换导致全表扫描案例

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

原创 Oracle 作者:sky_dizzy001 时间:2014-02-01 15:00:16 0 删除 编辑
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  and ani=:3

检查索引情况
 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  and ani=:3 

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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 打opatch遇上BUG
请登录后发表评论 登录
全部评论

注册时间:2014-02-01

  • 博文量
    27
  • 访问量
    129568