走索引扫描的慢查询(r3笔记45天)
今天查看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 | 0cdthzpx2jn4q | 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>