查看: 70152|回复: 93

[精华] 关于排序、sort_area_size、临时表空间

[复制链接]
论坛徽章:
86
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-01-04 11:49:542012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:20咸鸭蛋
日期:2012-05-08 10:27:19版主8段
日期:2012-05-15 15:24:112013年新春福章
日期:2013-02-25 14:51:24
发表于 2002-12-23 11:14 | 显示全部楼层 |阅读模式
简单陈述一下:

针对每个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 所能容纳记录数至少大于排序记录数的 平方根
论坛徽章:
4
ITPUB元老
日期:2005-02-28 12:57:00授权会员
日期:2005-10-30 17:05:33会员2006贡献徽章
日期:2006-04-17 13:46:34ITPUB技术丛书作者
日期:2010-09-26 15:24:56
发表于 2002-12-23 11:33 | 显示全部楼层
不错,biti的风格就是这样,言简意赅

使用道具 举报

回复
论坛徽章:
1
会员2006贡献徽章
日期:2006-04-17 13:46:34
发表于 2002-12-23 11:40 | 显示全部楼层

biti 到底说得对不对

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

使用道具 举报

回复
论坛徽章:
117
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:20版主7段
日期:2012-05-15 15:24:11ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:42ITPUB 11周年纪念徽章
日期:2012-10-09 18:03:32紫蛋头
日期:2013-03-04 17:00:07优秀写手
日期:2013-12-18 09:29:09
发表于 2002-12-23 11:41 | 显示全部楼层
所以当你需要进行大规模排序时,可以使用
alter session set sort_area_size = 1000000000;

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

使用道具 举报

回复
论坛徽章:
86
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-01-04 11:49:542012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:20咸鸭蛋
日期:2012-05-08 10:27:19版主8段
日期:2012-05-15 15:24:112013年新春福章
日期:2013-02-25 14:51:24
 楼主| 发表于 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.

使用道具 举报

回复
论坛徽章:
86
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-01-04 11:49:542012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:20咸鸭蛋
日期:2012-05-08 10:27:19版主8段
日期:2012-05-15 15:24:112013年新春福章
日期:2013-02-25 14:51:24
 楼主| 发表于 2002-12-23 18:13 | 显示全部楼层

up一下

自己顶一下  

使用道具 举报

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

使用道具

回复
论坛徽章:
2
授权会员
日期:2005-10-30 17:05:332012新春纪念徽章
日期:2012-01-04 11:49:54
发表于 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?

使用道具 举报

回复
论坛徽章:
86
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-01-04 11:49:542012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:20咸鸭蛋
日期:2012-05-08 10:27:19版主8段
日期:2012-05-15 15:24:112013年新春福章
日期:2013-02-25 14:51:24
 楼主| 发表于 2002-12-23 19:14 | 显示全部楼层

我可没这么说

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



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

使用道具 举报

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

使用道具

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表