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欄位後解決該問題

隱形轉換導致全表掃描案例

相關文章