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
oracle order by 排序优化
原创文章标签 oracle order by排序优化 文章分类 Oracle 数据库
©著作权归作者所有:来自51CTO博客作者ocpyang的原创作品,请联系作者获取转载授权,否则将追究法律责任
上一篇:sqlserver 索引视图
下一篇:自动统计信息收集情况
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
MySQL之order by 优化
MySQL之order by
order by