order by 排序对性能的影响
-***********************************
案例演示
-***********************************
alter system flush  shared_pool;
set autotrace traceonly explain stat;
select * from t3 where sid>90  ;
执行计划
----------------------------------------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |   330 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T3   |    10 |   330 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("SID">90)
Note
-----
   - dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
         10  recursive calls
          4  db block gets
         10  consistent gets
          0  physical reads
        496  redo size
        818  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed
select * from t3 where sid>90   order by sid desc;
执行计划
----------------------------------------------------------
Plan hash value: 1749037557
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |   330 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    10 |   330 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T3   |    10 |   330 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("SID">90)
Note
-----
   - dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
          9  recursive calls
          4  db block gets
          9  consistent gets
          1  physical reads
        540  redo size
        818  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)   --有排序
          0  sorts (disk)
         10  rows processed
可以看出CPU发生变化,如果排序语句很多的情况下,性能影响更大.
-***********************************
解决办法
-***********************************
create index index_sid on t3(sid desc);
exec dbms_stats.gather_table_stats('SYS','T3',cascade=>TRUE);
select * from t3 where sid>90   order by sid desc;
执行计划
---------------------------------------------------------
lan hash value: 243714934
----------------------------------------------------------------------------------------
 Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
   0 | SELECT STATEMENT            |           |    10 |   140 |     2   (0)| 00:00:01 |
   1 |  TABLE ACCESS BY INDEX ROWID| T3        |    10 |   140 |     2   (0)| 00:00:01 |
*  2 |   INDEX RANGE SCAN          | INDEX_SID |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
redicate Information (identified by operation id):
--------------------------------------------------
  2 - access(SYS_OP_DESCEND("SID")<HEXTORAW('3EA4FF') )
      filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SID"))>90)
ote
----
  - SQL plan baseline "SQL_PLAN_78qgapzz4mwhwd7223dec" used for this statement
统计信息
---------------------------------------------------------
         0  recursive calls
         0  db block gets
         4  consistent gets
         0  physical reads
         0  redo size
       818  bytes sent via SQL*Net to client
       519  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)   --无排序
         0  sorts (disk)
        10  rows processed