oracle 表查询慢 走索引,走索引扫描的慢查询

今天查看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>