走索引掃描的慢查詢
今天檢視awr報告的時候,發現一條sql語句異常。
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
但是因為查取的資料量太大導致查詢速度相對較慢。
Binds
從執行的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>
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;
目前得到的繫結變數值是:如果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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- AppBoxFuture: 二級索引及索引掃描查詢資料APP索引
- 有索引卻走全表掃描的實驗分析索引
- 查詢全表掃描的sqlSQL
- ORACLE全表掃描查詢Oracle
- 索引全掃描和索引快速全掃描的區別索引
- MongoDB慢查詢與索引MongoDB索引
- Oracle中存取資料掃描Table及索引的方式(全表掃描,索引掃描等)Oracle索引
- 持續近7個小時的索引掃描的查詢優化分析索引優化
- 查詢全表掃描語句
- SQL SERVER中什麼情況會導致索引查詢變成索引掃描SQLServer索引
- SQL Server索引查詢/掃描沒有出現key lookup的案例淺析SQLServer索引
- 【Oracle】 索引的掃描方式Oracle索引
- MySQL中的全表掃描和索引樹掃描MySql索引
- 【MySQL】全索引掃描的bugMySql索引
- oracle 全表掃描,索引範圍掃描與塊的理解Oracle索引
- 解讀Oracle 索引掃描Oracle索引
- MySQL索引原理及慢查詢優化MySql索引優化
- 筆記 mongo查詢慢日誌,建立索引筆記Go索引
- MySQL索引原理及慢查詢最佳化MySql索引
- 使用索引快速全掃描(Index FFS)避免全表掃描的若干場景索引Index
- 技術分享 | 為什麼 SELECT 查詢選擇全表掃描,而不走索引?索引
- (轉)索引掃描還是全表掃描(Index Scan Or Full Table Scan)索引Index
- 轉)索引掃描還是全表掃描(Index Scan Or Full Table Scan)索引Index
- mysql索引覆蓋掃描優化MySql索引優化
- 使用索引掃描來進行排序索引排序
- 慢查詢
- 為什麼我使用了索引,查詢還是慢?索引
- Oracle優化-索引原理[注意索引跳躍式掃描!Oracle優化索引
- PostgreSQL技術內幕(七)索引掃描SQL索引
- stopkey對索引掃描的影響測試TopK索引
- MySQL 慢查詢MySql
- MySQL慢查詢MySql
- Redis 慢查詢Redis
- 詭異的”慢查詢“
- 【索引】Oracle查詢指定索引提高查詢效率索引Oracle
- 全表掃描的cost 與 索引掃描Cost的比較 – 無直方圖(10.1.0.3以後)索引直方圖
- mysql效能優化-慢查詢分析、優化索引和配置MySql優化索引
- 基於AI+資料驅動的慢查詢索引推薦AI索引