ITPUB??ì3
ITPUB论坛 » Oracle专题深入讨论 » 关于排序、sort_area_size、临时表空间

标题: [精华] 关于排序、sort_area_size、临时表空间
离线 biti_rainy
人生就是如此



精华贴数 36
个人空间 0
技术积分 110635 (4)
社区积分 11711 (122)
注册日期 2001-12-12
论坛徽章:41
现任管理团队成员ITPUB长老会主席ITPUB长老会成员ITPUB元老年度论坛发贴之星年度论坛发贴之星
ITPUB北京九华山庄2008年会纪念徽章管理团队2007贡献徽章参与2007年甲骨文全球大会(中国上海)纪念ITPUB北京香山2007年会纪念徽章管理团队2006纪念徽章会员2007贡献徽章

发表于 2002-12-23 11:14 
关于排序、sort_area_size、临时表空间

简单陈述一下:

针对每个session,排序首先会使用sort_area_size ,如果不足则会使用临时表空间。但这里面又到底是怎么一个过程呢?下面阐述一下,也许对大家有用处(如果有什么不清楚或者不恰当的地方欢迎大家探讨)

假设sort_area_size = 100k,正好能盛下100条记录进行排序

当排序记录小于等于100条,ok,所有排序在内存中进行,很快
但若超过100条,则会使用临时表空间(利用磁盘进行)
我们选取一个临界值来说明,假设需要排序的记录有10010条

这个时候我们进行的排序会分为101组进行
每读100条进行一次小组排序,然后写入磁盘,第101组只有10条,排序后也写入磁盘

这是进行第二次排序,这次排序将在前100小组里面各抽取一条进行排序。《按照我个人的猜测,应该是排好后每写入一条入磁盘则将该记录所在小组重新抽取一条出来进行排序(这时是有序记录组里面所以很快)》。当这个过程完成后,这时所需要的磁盘空间大约为  实际记录存储空间的2倍(这也是多数书上提到的排序空间大约是记录空间的2倍的原因)

由于还剩下10条记录,于是这10条记录需要跟前面排序的10000条记录进行排序合并,这个代价也是相当大的!

所以,我们通常推荐,假如你需要排序的记录最大为100万条,则sort_area_size最小要能装下1000条,否则如上面的例子,那多余的10条,仅仅10条将会带来巨大的代价!

如果,设置的极度不合理的情况下,排序记录达到了 sort_area_size所能容纳的三次方以上,比如上面例子中排序需要100万记录
那么同样的,重复这个过程,当每一万条记录如上排序后,再如上从这100小组(每组10000条记录)各抽一条进行排序……

在这个过程中,磁盘的消耗和时间的代价大家都应该有个感性认识了
所以,我们建议:  sprt_area_size 所能容纳记录数至少大于排序记录数的 平方根


__________________
眼界决定边界,态度决定高度
blog:
人生就是如此
只看该作者    顶部
离线 lunar2000
月儿


精华贴数 2
个人空间 0
技术积分 26660 (31)
社区积分 424 (1505)
注册日期 2001-10-8
论坛徽章:3
ITPUB元老会员2006贡献徽章授权会员   
      

发表于 2002-12-23 11:33 
不错,biti的风格就是这样,言简意赅


__________________
MSN: lunar52@hotmail.com------------------------------------------------我的CSDN专栏: http://blog.csdn.net/lunar2000
只看该作者    顶部
离线 超级野人
老会员



精华贴数 2
个人空间 0
技术积分 2000 (783)
社区积分 13 (9044)
注册日期 2002-11-20
论坛徽章:1
会员2006贡献徽章     
      

发表于 2002-12-23 11:40 
biti 到底说得对不对

究竟oracle是不是这样运行的,谁敢肯定


__________________
我是野人我怕谁,我的qq:574674565
只看该作者    顶部
离线 eygle
天下有雪


精华贴数 65
个人空间 0
技术积分 206498 (1)
社区积分 6443 (223)
注册日期 2001-10-8
论坛徽章:57
现任管理团队成员2007年度ITPUB杰出贡献ITPUB长老会成员ITPUB元老ITPUB维基人授权会员
2008北京奥运纪念徽章:射击生肖徽章2007版:鼠2008年新春纪念徽章生肖徽章2007版:龙生肖徽章2007版:猴ITPUB新首页上线纪念徽章

发表于 2002-12-23 11:41 
所以当你需要进行大规模排序时,可以使用
alter session set sort_area_size = 1000000000;

这也就是TOM所说的为什么alter session是个危险权限的原因



__________________
只看该作者    顶部
离线 biti_rainy
人生就是如此



精华贴数 36
个人空间 0
技术积分 110635 (4)
社区积分 11711 (122)
注册日期 2001-12-12
论坛徽章:41
现任管理团队成员ITPUB长老会主席ITPUB长老会成员ITPUB元老年度论坛发贴之星年度论坛发贴之星
ITPUB北京九华山庄2008年会纪念徽章管理团队2007贡献徽章参与2007年甲骨文全球大会(中国上海)纪念ITPUB北京香山2007年会纪念徽章管理团队2006纪念徽章会员2007贡献徽章

发表于 2002-12-23 11:47 
除了下面这点个人猜测外,其他的都是这样的

《按照我个人的猜测,应该是排好后每写入一条入磁盘则将该记录所在小组重新抽取一条出来进行排序(这时是有序记录组里面所以很快)》。

之所以这样猜测是因为个人觉得这样的方式效率能比较高并且算法简单容易控制,资源消耗也少

大家可参考内容
http://www.ixora.com.au/q+a/space.htm#end

If my table occupies 500M of disk space, and I have a 1M sort_area_size, how much temporary tablespace disk space do I need to perform the following sorts?
1. select col1, col2, col3 from tablename order by col1, col2, col3;
2. select col1, col2, col3 from tablename order by col1, col2;

  The sort space requirements are dependent on the size of the row source being sorted, rather than the size of the key. Your two sorts would require the same amount of disk space. However, if you were joining to another table and including one of its columns in the select list, then the size of the row source to be sorted could be larger than the largest base table.
It is also possible for the disk space requirements to be up to twice the size of the row source if the sort area size is too small. If for example you need to sort 1,001,000 rows and your sort area size can only accommodate 1000 rows at a time. Then the first phase of the sort will be to read the row source, and write 1001 sort runs of 1000 rows each to the temporary segment. The next phase is to read one row at a time from each of the first 1000 sort runs and merge them into a single large sort run. Towards the end of this phase, you need space for all the original 1001 sort runs and the big sort run at the same time - which is nearly twice the row source size. The third phase of this sort would be a second merge pass to merge to large sort run with the remaining original 1000 row sort run. To avoid this, and process a sort in a single merge pass, the sort area size must hold at least as many rows as the square root of the number of rows in the row source.


__________________
眼界决定边界,态度决定高度
blog:
人生就是如此
只看该作者    顶部
离线 biti_rainy
人生就是如此



精华贴数 36
个人空间 0
技术积分 110635 (4)
社区积分 11711 (122)
注册日期 2001-12-12
论坛徽章:41
现任管理团队成员ITPUB长老会主席ITPUB长老会成员ITPUB元老年度论坛发贴之星年度论坛发贴之星
ITPUB北京九华山庄2008年会纪念徽章管理团队2007贡献徽章参与2007年甲骨文全球大会(中国上海)纪念ITPUB北京香山2007年会纪念徽章管理团队2006纪念徽章会员2007贡献徽章

发表于 2002-12-23 18:13 
up一下

自己顶一下  


__________________
眼界决定边界,态度决定高度
blog:
人生就是如此
只看该作者    顶部
游客 210.21.108.x
未注册









发表于 2002-12-23 18:36  资料  个人空间  短消息  加为好友 
实际上就是外排序过程,任何译本关于算法分析的书都会讲,把要排序的对象分割为内存可以容纳的小段(sort run),然后每个sort run都放到sort area里面去排序,排序结果作为中间信息放在临时表空间的临时段内,由于分段排序的结果当然不会是最终要求的排序结果,所以还要经过一个merge的过程,才会得到最终排序结果,原理大概是比较每个sort run的第一名,取出领先者,然后该sort run的第二名顶上,继续上述过程直到取完。merge过程也有很多简化算法。


只看该作者    顶部
离线 Calvin
Oracle 9i OCM


精华贴数 0
个人空间 0
技术积分 588 (3206)
社区积分 20 (7326)
注册日期 2002-1-31
论坛徽章:1
授权会员     
      

发表于 2002-12-23 19:06 
So are you gonna 'ALTER SESSION SET SORT_AREA_SIZE=SQRT(SORT_RECORD_SIZE)' for every sort since every sort has different size?


只看该作者    顶部
离线 biti_rainy
人生就是如此



精华贴数 36
个人空间 0
技术积分 110635 (4)
社区积分 11711 (122)
注册日期 2001-12-12
论坛徽章:41
现任管理团队成员ITPUB长老会主席ITPUB长老会成员ITPUB元老年度论坛发贴之星年度论坛发贴之星
ITPUB北京九华山庄2008年会纪念徽章管理团队2007贡献徽章参与2007年甲骨文全球大会(中国上海)纪念ITPUB北京香山2007年会纪念徽章管理团队2006纪念徽章会员2007贡献徽章

发表于 2002-12-23 19:14 
我可没这么说



QUOTE:
最初由 Calvin 发布
So are you gonna 'ALTER SESSION SET SORT_AREA_SIZE=SQRT(SORT_RECORD_SIZE)' for every sort since every sort has different size?


我只是说如果小于平方根的话会有严重问题,事实上如果sort_area_size能有1M--5M则基本能使绝大部分排序在1次merge中完成
你有什么建议呢?  


__________________
眼界决定边界,态度决定高度
blog:
人生就是如此
只看该作者    顶部
游客 210.21.108.x
未注册









发表于 2002-12-23 19:55  资料  个人空间  短消息  加为好友 
biti所说的按记录数开平方来定sort area是有道理的,原因是如果这样设置,可以使在merge过程中刚好每个sort run排序结果的第一名可以放进sort area里面再排序,否则,如果尺寸太小,势必会导致再要使用磁盘作为过渡,代价高昂


只看该作者    顶部
相关内容


CopyRight 1999-2006 itpub.net All Right Reserved.
北京皓辰广域网络信息技术有限公司. 版权所有
E-mail:Webmaster@itpub.net
京ICP证:010037号 联系我们 法律顾问