oracle order by 排序优化
order by 排序对性能的影响-***********************************
案例演示
-***********************************
alter system flushshared_pool;
set autotrace traceonly explain stat;
select * from t3 where sid>90;
执行计划
----------------------------------------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
|>
--------------------------------------------------------------------------
| 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>
---------------------------------------------------
1 - filter("SID">90)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
10recursive calls
4db block gets
10consistent gets
0physical reads
496redo>
818bytes sent via SQL*Net to client
519bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
10rows processed
select * from t3 where sid>90 order by sid desc;
执行计划
----------------------------------------------------------
Plan hash value: 1749037557
---------------------------------------------------------------------------
|>
---------------------------------------------------------------------------
| 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>
---------------------------------------------------
2 - filter("SID">90)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
9recursive calls
4db block gets
9consistent gets
1physical reads
540redo>
818bytes sent via SQL*Net to client
519bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
1sorts (memory) --有排序
0sorts (disk)
10rows 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>
--------------------------------------------------
2 - access(SYS_OP_DESCEND("SID")90)
ote
----
- SQL plan baseline "SQL_PLAN_78qgapzz4mwhwd7223dec" used for this statement
统计信息
---------------------------------------------------------
0recursive calls
0db block gets
4consistent gets
0physical reads
0redo>
818bytes sent via SQL*Net to client
519bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory) --无排序
0sorts (disk)
10rows processed
页:
[1]