首页 → 数据库技术
背景：
阅读新闻
oracle order by 排序优化
[日期：2013-06-26]来源：linux社区 作者：ocpyang[字体：]
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) 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
0
初始化oracle用户以及表空间的bash shell脚本
imp/exp数据迁移（二）
相关资讯 oracle排序 oracle order by