【求助】【急】关于Oracle10g里的Order by速度慢的问题

Question

表结构：
CREATE TABLE TB_RID_TRACE_2013
(
  ID             VARCHAR2(50) NOT NULL CONSTRAINT PK_TB_RID_TRACE_2013 PRIMARY KEY,
  SESSION_ID     VARCHAR2(50),
  LOGIN_AT       DATE,
  LOGOUT_AT      DATE,
  ID_TYPE        VARCHAR2(32),
  ID_CODE        VARCHAR2(100),
  ID_NAME        VARCHAR2(100),
  AREA_CODE      VARCHAR2(15),
  SERVICE_CODE   VARCHAR2(32),
  SERVICE_WAN_IP VARCHAR2(32),
  SERVICE_LAN_IP VARCHAR2(32),
  SERVICE_MAC    VARCHAR2(32),
  HOST_NAME      VARCHAR2(100)
)
PARTITION BY RANGE (LOGIN_AT)
(
PARTITION TB_RID_TRACE_2013_PART_01 VALUES LESS THAN (TO_DATE('2013-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_02 VALUES LESS THAN (TO_DATE('2013-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_03 VALUES LESS THAN (TO_DATE('2013-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_04 VALUES LESS THAN (TO_DATE('2013-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_05 VALUES LESS THAN (TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_06 VALUES LESS THAN (TO_DATE('2013-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_07 VALUES LESS THAN (TO_DATE('2013-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_08 VALUES LESS THAN (TO_DATE('2013-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_09 VALUES LESS THAN (TO_DATE('2013-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_10 VALUES LESS THAN (TO_DATE('2013-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_11 VALUES LESS THAN (TO_DATE('2013-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_12 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
)
NOLOGGING;该表上创建的索引：
CREATE INDEX IDX_2013_ID_CODE ON TB_RID_TRACE_2013 (ID_CODE DESC);CREATE INDEX IDX_2013_LOGIN_AT ON TB_RID_TRACE_2013 (LOGIN_AT DESC);CREATE INDEX IDX_2013_LOGOUT_AT ON TB_RID_TRACE_2013 (LOGOUT_AT DESC);CREATE INDEX IDX_2013_SERVICE_CODE ON TB_RID_TRACE_2013 (SERVICE_CODE DESC);
目前表里有18000000条数据，系统里是使用ExtJS分页显示列表。问题是执行普通SQL速度很快。但是如果SQL里包含Order By后速度就变得爆慢。以下是我执行的SQL和时间以及执行计划的内容：

SQL1,执行时间：0.172秒100条数据
SELECT * FROM TB_RID_TRACE_2013 T WHERE T.LOGIN_AT BETWEEN TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2013-06-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS');SQL1执行计划：
执行计划
----------------------------------------------------------
Plan hash value: 3349134993------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |  1555K|   255M| 19128   (2)| 00:03:50 |       |    |
|   1 |  PARTITION RANGE SINGLE|                   |  1555K|   255M| 19128   (2)| 00:03:50 |     6 |  6 |
|*  2 |   TABLE ACCESS FULL    | TB_RID_TRACE_2013 |  1555K|   255M| 19128   (2)| 00:03:50 |     6 |  6 |
------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - filter("T"."LOGIN_AT"<=TO_DATE('2013-06-15 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
SQL2,执行时间：14.789秒100条数据
SELECT T.* FROM TB_RID_TRACE_2013 T WHERE T.LOGIN_AT BETWEEN TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2013-06-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS') ORDER BY T.LOGIN_AT;SQL2执行计划：
执行计划
----------------------------------------------------------
Plan hash value: 2801453287--------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |  1555K|   255M|       | 78265   (1)| 00:15:40 |    |          |
|   1 |  PARTITION RANGE SINGLE|                   |  1555K|   255M|       | 78265   (1)| 00:15:40 |  6 |        6 |
|   2 |   SORT ORDER BY        |                   |  1555K|   255M|   607M| 78265   (1)| 00:15:40 |    |          |
|*  3 |    TABLE ACCESS FULL   | TB_RID_TRACE_2013 |  1555K|   255M|       | 19128   (2)| 00:03:50 |  6 |        6 |
--------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   3 - filter("T"."LOGIN_AT"<=TO_DATE('2013-06-15 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))SQL3,执行时间：24.492秒100条数据
SELECT * FROM (SELECT ROWNUM RN, T.* FROM TB_RID_TRACE_2013 T WHERE T.LOGIN_AT BETWEEN TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2013-06-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS') ORDER BY T.LOGIN_AT DESC) T2 WHERE T2.RN BETWEEN 0 AND 100;SQL3执行计划：
执行计划
----------------------------------------------------------
Plan hash value: 283752969-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                   |  1555K|   504M|       | 78265   (1)| 00:15:40 |       |       |
|*  1 |  VIEW                     |                   |  1555K|   504M|       | 78265   (1)| 00:15:40 |       |       |
|   2 |   SORT ORDER BY           |                   |  1555K|   255M|   607M| 78265   (1)| 00:15:40 |       |       |
|   3 |    COUNT                  |                   |       |       |       |            |          |       |       |
|   4 |     PARTITION RANGE SINGLE|                   |  1555K|   255M|       | 19128   (2)| 00:03:50 |     6 |     6 |
|*  5 |      TABLE ACCESS FULL    | TB_RID_TRACE_2013 |  1555K|   255M|       | 19128   (2)| 00:03:50 |     6 |     6 |
-----------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - filter("T2"."RN"<=100 AND "T2"."RN">=0)
   5 - filter("T"."LOGIN_AT"<=TO_DATE('2013-06-15 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))Oracle10gOrder by速度慢优化索引

Answer 1

TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2013-06-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
为条件返回多少条记录？从查询计划上看返回1555K
真实有那么多吗？看看是否要收集一些统计信息？

Answer 2

SQL1
SELECT * FROM TB_RID_TRACE_2013 T WHERE T.LOGIN_AT BETWEEN TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2013-06-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS');已选择1618063行。已用时间:  00: 05: 12.15执行计划
----------------------------------------------------------
Plan hash value: 3349134993------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |  1555K|   255M| 19128   (2)| 00:03:50 |       |    |
|   1 |  PARTITION RANGE SINGLE|                   |  1555K|   255M| 19128   (2)| 00:03:50 |     6 |  6 |
|*  2 |   TABLE ACCESS FULL    | TB_RID_TRACE_2013 |  1555K|   255M| 19128   (2)| 00:03:50 |     6 |  6 |
------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - filter("T"."LOGIN_AT"<=TO_DATE('2013-06-15 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
统计信息
----------------------------------------------------------
        284  recursive calls
          0  db block gets
     191262  consistent gets
      59213  physical reads
          0  redo size
  288375280  bytes sent via SQL*Net to client
    1186970  bytes received via SQL*Net from client
     107872  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1618063  rows processed

Answer 3

SQL3
SELECT * FROM (SELECT ROWNUM RN, T.* FROM TB_RID_TRACE_2013 T WHERE T.LOGIN_AT BETWEEN TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2013-06-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS') ORDER BY T.LOGIN_AT DESC) T2 WHERE T2.RN BETWEEN 0 AND 100;已选择100行。已用时间:  00: 00: 18.38执行计划
----------------------------------------------------------
Plan hash value: 283752969-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                   |  1555K|   504M|       | 78265   (1)| 00:15:40 |       |       |
|*  1 |  VIEW                     |                   |  1555K|   504M|       | 78265   (1)| 00:15:40 |       |       |
|   2 |   SORT ORDER BY           |                   |  1555K|   255M|   607M| 78265   (1)| 00:15:40 |       |       |
|   3 |    COUNT                  |                   |       |       |       |            |          |       |       |
|   4 |     PARTITION RANGE SINGLE|                   |  1555K|   255M|       | 19128   (2)| 00:03:50 |     6 |     6 |
|*  5 |      TABLE ACCESS FULL    | TB_RID_TRACE_2013 |  1555K|   255M|       | 19128   (2)| 00:03:50 |     6 |     6 |
-----------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - filter("T2"."RN"<=100 AND "T2"."RN">=0)
   5 - filter("T"."LOGIN_AT"<=TO_DATE('2013-06-15 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
统计信息
----------------------------------------------------------
        317  recursive calls
         14  db block gets
      85985  consistent gets
     104994  physical reads
          0  redo size
      19157  bytes sent via SQL*Net to client
        466  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
        100  rows processed

Answer 4

排序时间比较耗时，PGA的设置是什么情况？

Answer 5

PGA和SGA设置都很大，都是5G

Answer 6

是不是索引没用到？难道索引创建多了？索引是我后来追加的，当时表里已经有这么多数据了，我也REBUILD了啊！

Answer 7

从执行计划大体可以得出：
1）5 - filter("T"."LOGIN_AT"<=TO_DATE('2013-06-15 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
   ==> 存在隐式类型转换！太槽糕了
2）  2 |   TABLE ACCESS FULL    | TB_RID_TRACE_2013 
   ==> CBO选择全表扫、索引在这里不起作用
3）既然分区了、干嘛还选择全局索引？
4）1  sorts (disk)
   ==> 都跑到磁盘上来排序了？pga里面sort_area多大？
5）索引是我后来追加的，当时表里已经有这么多数据了，我也REBUILD了啊！
   ==> 表和索引的统计信息重新收集了没？

Answer 8

pga大小是6G，sort_area是什么东西？怎么查看和设置？表和索引的统计信息怎么收集？

Answer 9

刚刚设置了sort_area_size和hash_area_size，分别是1G。表和索引的统计信息怎么收集？另外是不是分区了，就不用再在LOGIN_AT（分区也是基于这个列）列上加索引了？
斑竹，再指点指点呗O(∩_∩)O~

Answer 10

1）你的LOGIN_AT是DATE类型、你看执行计划"filter("T"."LOGIN_AT"<=TO_DATE('2013-06-15 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))"这里存在隐式转换、这里可以优化
2）表信息收集：dbms_stats.gather_table_stats 
   索引信息收集：dbms_stats.gather_index_stats
3）用了分区就建议用分区索引(即：本地索引)、全局索引一般没啥效果吧

Answer 11

还有 执行计划里面的  Rows   评估怎么没有值？是直方图问题？统计信息问题？还是高水位问题？
另外、根据你4楼贴出的语句 已选择1618063行 1百多万行啊  返回这么多的数量  在sql方面作优化空间非常少了  

Answer 12

我想做的功能是分页的，但是这个SQL作为子查询的时候，确实是慢，有没有其他好的优化方案啊？关于分页的？Rows为什么没有值，我也不知道！“是直方图问题？统计信息问题？还是高水位问题？”不懂O(∩_∩)O~

Answer 13

1）你的LOGIN_AT是DATE类型、你看执行计划"filter("T"."LOGIN_AT"<=TO_DATE('2013-06-15 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))"这里存在隐式转换、这里可以优化
2）表信息收集：dbms_stats.gather_table_stats 
   索引信息收集：dbms_stats.gather_index_stats
3）用了分区就建议用分区索引(即：本地索引)、全局索引一般没啥效果吧
谢谢给的优化建议O(∩_∩)O~另外，表信息收集和索引信息收集，有什么作用？能提高效率还是只是作为开发人员分析的？

Answer 14

1）表信息收集和索引信息收集、这个很重要、可能会让你的sql跑得更快、你收集一下吧！
2）分页用rownum来分吧、Oracle CBO分页优化已经很智能了、