关于排序、sort_area_size、临时表空间

[biti_rainy]

有兴趣大家可以探讨探讨

简单陈述一下：

针对每个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 所能容纳记录数至少大于排序记录数的 平方根

[cyberflying]

学习ing

[yuxuan]

就是说对于大数据量的排序
sort_area_size设置很有讲究了，是吗？

[yuxuan]

请问biti_rainy (biti_rainy) ：

大数据量触发排序会消耗很多CPU，内存和磁盘。比如：Distinct，Union，Order by

问：sort_area_size 和sort_area_retained_size有什么区别？

[sz_news]

我的oracle的临时表空间已到了2个多G了，可能就是这个缘故了！！

[Anajian]

up

[penitent]

但往往sort_area_size是不可能设置那么大的
假定你的oracle是专用进程服务器，那么sort_area_size是在PGA中分配的。
假定你有300个并发用户，每个人的sort_area_size设置为1M，如果完全使用的话，你的内存将被消耗300M，所以说，当并发用户很多的时候，建议使用MTS（当然，MTS不仅仅是这个原因，主要是每个user process得需要一个server process，很浪费的）。
再假定，你的每个表的平均行长度不等，从几个字节到几百K，而且在这些表中都存在排序，那怎么分配sort_area_size，这个从biti_rainy (biti_rainy) 的角度来分析，就很麻烦了，可能需要大量的统计与调优。
不仅仅sort_area_size,设置临时段也有技巧，临时段的区间大小要求相等，而且extent size= n*sort_area_size+db_block_size是最佳大小。

[biti_rainy]

原文及讨论的地方：

http://www.itpub.net/showthread.php?s=&threadid=84823


sort_area_size 和sort_area_retained_size

retained: 从字面意思你就应该明白了，保留的意思！
有这类问题最好查oracle的帮助文档
sort_area_size是最大使用限额，但不能老占这么大，于是有个保留值
大致先这么理解了，至于仔细的有些细处内容慢慢的深入了

[glmcglmc]

select disk.value "Disk", mem.value "Mem",
2 (disk.value/mem.value)*100 "Ratio"
3 from v$sysstat mem, v$sysstat disk
4 where mem.name = ’sorts (memory)’
5 and disk.name = ’sorts (disk)’;
Disk Mem Ratio
--------- --------- ---------
23 206 11.165049
(1)The ratio of disk sorts to memory sorts should be
less than 5%.
(2)Increase the size of SORT_AREA_SIZE if the ratio
is greater than 5%.

请问biti_rainy或有认识的朋友:
应该如何理解(1)、（2）两句话呢？为什么要定5%呢（10%不行吗）？

[biti_rainy]

请明白一个问题：
这个所谓的 5% / 10% 不过是他所认为的一个经验值！！！

不要恪守教条

假如你的查询涉及的数据都比较小，那自然这个值因该很低
但假如你的查询都是 数百万的数据的排序的大查询并且比较多的话
你认为这个经验值还适用么？

关键在于你的系统到底是否让客户满意！
可以适当的通过调整参数来缓解/优化问题

[glmcglmc]

引自penitent:"再假定，你的每个表的平均行长度不等，从几个字节到几百K，而且在这些表中都存在排序，那怎么分配sort_area_size，这个从biti_rainy (biti_rainy) 的角度来分析，就很麻烦了"

引自"ORACLE高性能SQL调整":"设计者必须在为较大的排序任务分配足够的排序空间以避免在磁盘上进行较大的排序任务和倾覆服务器RAM内存之间保持平衡"

这句话应该可以解释的penitent疑问吧。

[glmcglmc]

CREATE TEMPORARY TABLESPACE temp
TEMPFILE ’/DISK2/temp_01.dbf’
SIZE 500M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 10M;

疑问：（1）UNIFORM SIZE= SORT_AREA_SIZE * n
还是= SORT_AREA_SIZE * n+BLOCK SIZE？
（2）如何设计临时数据文件的大小（如：500M）？

[biti_rainy]

UNIFORM SIZE : SORT_AREA_SIZE * n+BLOCK SIZE 比较合适一些

因为头部需要一个block作为header来记录信息

但事实上，这个不是特别的重要
不同的session可能大小不一致，那么你浪费一丁点磁盘问题不是很大

关键在于理解，而不是教条！
我还要强调这个问题

但至于临时数据文件大小？
你觉得你一次排序最大可能需要多少空间？
允许自动扩展么？
峰值需求是多大？
这个问题，别人无法替你回答
关键在于你理解了
结合自己的系统来确定

[glmcglmc]

关键在于理解，而不是教条！
说得好！！！