本文详细解释了Oracle数据库中排序操作的三种内存使用策略：完全内存内排序(inmemorysort)，单次通过排序(onepasssort)及多次通过排序(multipasssort)。这三种策略根据可用内存的不同，决定数据是否需要溢出到磁盘以及如何读取已排序的数据块。
在ORACLE 10G官方文档《Performance Tuning Guide》中遇到关于PGA中workarea的几个概念：
Optimal
One-pass
Muli-pass
ASK TOM上的说明：
in memory sort Everything fits in memory, no disk is needed, everything done in ram.
one pass sort We read some amount of data and sort it, the sort workarea fills up, we write that to disk. We do it again (read more, sort it) - this too spills to disk. We do this over and over until we've read the entire set of data and sorted each of the chunks. Now we need read a bit of each of the chunks we've sorted on disk and start returning data from there. In the one pass sort, we can read a bit of each chunk and start returning data. If we cannot read a bit of EVERY chunk then....
we are in the realm of the multi-pass sort. We read and merge the first few chunks - resulting in a new set. We read and merge another set of chunks - resulting in a new set. And so on - until we've processed all of the output from the first pass and created a "second pass" of more merged (more sorted) data. Then we merge those in the sort area and start returning data.
one pass sort We read some amount of data and sort it, the sort workarea fills up, we write that to disk. We do it again (read more, sort it) - this too spills to disk. We do this over and over until we've read the entire set of data and sorted each of the chunks. Now we need read a bit of each of the chunks we've sorted on disk and start returning data from there. In the one pass sort, we can read a bit of each chunk and start returning data. If we cannot read a bit of EVERY chunk then....
we are in the realm of the multi-pass sort. We read and merge the first few chunks - resulting in a new set. We read and merge another set of chunks - resulting in a new set. And so on - until we've processed all of the output from the first pass and created a "second pass" of more merged (more sorted) data. Then we merge those in the sort area and start returning data.
按我理解
in memory sort=Optimal,对于英文理解毕竟不如中文。
以下文档对对理解这几个概念很有帮助：
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/703237/viewspace-677778/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/703237/viewspace-677778/
扫一扫
5235
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
