走索引掃描的慢查詢

dbhelper發表於2014-11-26
今天檢視awr報告的時候,發現一條sql語句異常。

Elapsed Time (s)

Executions

Elapsed Time per Exec (s)

%Total

%CPU

%IO

SQL Id

SQL Module

SQL Text

6,621.05

2

3,310.52

2.35

10.09

93.14

JDBC Thin Client

SELECT MEMO_ID FROM MEMO W...


sql語句很簡單。
SELECT MEMO_ID FROM MO1_MEMO WHERE MEMO_ID > :1  
AND SYS_CREATION_DATE>= (SELECT MAX(SYS_CREATION_DATE) FROM MO1_MEMO WHERE MEMO_ID = :2 )
ORDER BY MEMO_ID ASC

檢視awr中對應的執行計劃,都走了索引,但是從執行計劃來看查取的資料很多。
Plan hash value: 3859108387
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |             |       |       |       |   176K(100)|          |       |       |
|   1 |  SORT ORDER BY                         |             |  1042K|    14M|    23M|   176K  (1)| 00:35:23 |       |       |
|   2 |   PARTITION RANGE ALL                  |             |  1042K|    14M|       |   171K  (1)| 00:34:18 |     1 |   289 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID   |     MEMO    |  1042K|    14M|       |   171K  (1)| 00:34:18 |     1 |   289 |
|*  4 |     INDEX RANGE SCAN                   |     MEMO_PK |  3752K|       |       |  3693   (1)| 00:00:45 |     1 |   289 |
|   5 |     SORT AGGREGATE                     |             |     1 |    15 |       |            |          |       |       |
|   6 |      PARTITION RANGE ALL               |             |     1 |    15 |       |    87   (0)| 00:00:02 |     1 |   289 |
|   7 |       TABLE ACCESS BY LOCAL INDEX ROWID|     MEMO    |     1 |    15 |       |    87   (0)| 00:00:02 |     1 |   289 |
|*  8 |        INDEX RANGE SCAN                |     MEMO_PK |     1 |       |       |    87   (0)| 00:00:02 |     1 |   289 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("SYS_CREATION_DATE">=)
   4 - access("MEMO_ID">:1)
   8 - access("MEMO_ID"=:2)

這是一個億級的大表。索引情況如下,可以從執行計劃看出,是走主鍵掃描的。
INDEX_NAME                     TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST                               TABLE_TYPE     STATUS   NUM_ROWS   LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
MEMO_1IX                                  FUNCTION-BASED NORMAL NONUNIQUE YES SYS_NC00031$,ENTITY_TYPE_ID,APP_ID          TABLE      N/A      416981360 23-OCT-14 N       
MEMO_2IX                                  NORMAL     NONUNIQUE YES MEMO_EXTERNAL_ID                            TABLE      N/A      391718776 23-OCT-14 N
MEMO_PK                                   NORMAL     UNIQUE    YES MEMO_ID,APP_ID,ENTITY_KEY,PERIOD_KEY        TABLE      N/A      416983187 23-OCT-14 N
但是因為查取的資料量太大導致查詢速度相對較慢。                                               
如果想看到查詢中對應的繫結變數值。使用sql_monitor是一個不錯的選擇。

如果sql語句還在執行,可以直接使用如下的sql語句得到實際的執行情況。
col comm format a200
SELECT dbms_sqltune.report_sql_monitor(
sql_id => 'xxxxxx',
report_level => 'ALL',
type=>'TEXT'
) comm 
FROM dual;

目前得到的繫結變數值是:
Binds
Name Position Type Value
:1 1 NUMBER 0
:2 2 NUMBER 7199


從執行的sql語句可以基本判定按照目前的繫結變數會輸出所有的資料。memo_id在生產中是肯定會大於0的。所以第一個繫結變數就沒有任何作用。第二個雖然用到了但是返回的欄位卻不是索引欄位。結果在查詢中要掃描整個表。幾乎輸出了所有的資料。
按照一個正常的操作來說,返回所有的記錄也是沒有意義的,對客戶端的資料處理也是挑戰。
所以使用索引不一定語句查詢的快,但是如果想讓這個查詢快,使用並行也是不建議的,這個還是需要來做一些基本的限定。要不給資料庫和應用來說都是效能問題。
最後給開發的建議是提供一個id 的區間值,這樣走索引也是選擇性的。
SELECT MEMO_ID
  FROM MO1_MEMO
 WHERE MEMO_ID > 0
   AND SYS_CREATION_DATE >=
       (SELECT MAX(SYS_CREATION_DATE) FROM MEMO WHERE MEMO_ID = 7199)
   and memo_id < 17199
 ORDER BY MEMO_ID ASC

這種實現也是合乎業務和資源使用情況的。
生產環境中驗證了一下,只要1秒鐘。可以資料區間略大些,時間稍微長一些,但是也是秒級。
9808 rows selected.
Elapsed: 00:00:01.14
SQL> 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8494287/viewspace-1346971/,如需轉載,請註明出處,否則將追究法律責任。

相關文章