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 |
------------------------------------------