当前位置:  首页  >  PHP教程  >  PHP 应用  >  CMS建站

Oracleorderby排序优化

orderby排序对性能的影响-***********************************案例演示-***********************************altersyste

order by 排序对性能的影响 -*********************************** 案例演示 -*********************************** alter syste

Linux公社

首页 → 数据库技术

背景:

阅读新闻

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

    吐了个 "CAO" !
    扫码关注 PHP1 官方微信号
    PHP1.CN | 中国最专业的PHP中文社区 | PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | PHP问答
    Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved PHP1.CN 第一PHP社区 版权所有