1、排序进程:
1)如果排序请求使用的memory不大于参数SORT_AREA_SIZE的设置值,则sort操作在内存中进程。反之,如果超过该值:
①数据将被分隔成更小的pieces,被称作sort runs;每个sorted先被分别的sort。
②server进程会将pieces写入临时表空间segment中;这些segments用于存储中间的sort data。
③sorted pieces被合并从而产生最终结果。如果SORT_AREA_SIZE大小不足以一次merge所有sorted runs,sorted runs的子集会经历几次merge过程。
2)sort area的大小是由SORT_AREA_SIZE设置的,它可动态设置(alter session、alter system deferred),其默认值根据OS的不同而不同。其默认值可以满足一般的OLTP需求,对于DSS应用、批量jobs或是较大操作需要适当的条件。
3)另一个相关的参数是SORT_AREA_RETAINED_SIZE。当sorting操作结束,sort area仍保存了部分等待取出的sorted rows,sort area可以shrink到最小为SORT_AREA_RETAINED_SIZE的大小。该部分memory仍然是被释放到UGA中。其默认值等于 SORT_AREA_SIZE。
4)关于位图索引的初始化参数:
CREATE_BITMAP_AREA_SIZE:此参数是静态的,指明了用于创建位图索引可以分配的memory,默认值是8MB(较大的memory设置会加快bitmap的创建速度,如果位图索引的基数比较小,所需的memory也相对小)
BITMAP_MERGE_AREA_SIZE:该参数也是静态的。默认值是1MB。oracle为索引位图段建立一张位图。在进行位图索引扫描时,需要将扫描到的位图索引排序后与位图合并(Merge
),Oracle会在PGA中开辟一片区域用于排序和合并。它就指定了这片区域的大小。
5)sort Area的新参数:
PGA_AGGREGATE_TARGET:指明了连接instance的所有进程的PGA的总区域大小,大小可从10MB到4000GB。设置时,应该先考虑system总的memory,以及分配给SGA的memory大小,将剩余的部分分配给该参数。它指明了自动sort area管理。
WORKAREA_SIZE_POLICY:该参数可设置为(i)AUTO:只有定义了PGA_AGGREGATE_TARGET参数后才可被设置为 AUTO;(ii)MANUAL:对work areas的Sizing是手动的,是基于*_AREA_SIZE参数设置的值来分配的。如果设置为MANUAL,可能会降低PGA内存的利用率。
6)一般SORT_AREA_SIZE和SORT_AREA_RETAINED_SIZE应该设为相同的大小,除非system memory不足或使用的是Oracle shared server模式。
7)内存的需求:
在single server process中:一个执行计划可能会包含多个排序。例如,一个用sort-merge方式join两个tables并使用order 不用子句的sql,包含了三个sort。对此,在运行order by时,一个SORT_AREA_SIZE大小的memory用于当前的sort;两个SORT_AREA_RETAINED_SIZE大小的 memory用于join sorts。
在parallel query 进程中:假设有2个server,则会使用(SORT_AREA_SIZE*2*并发度)大小的memory,此外如果需要,还要使用 (SORT_AREA_RETAINED_SIZE*并发度*事先做的排序次数)的memory。实验表明分配更大的memory不会对性能有更好的提 高。
2、Tuning sorts
需要注意的问题有:
* 如果数据已经使用索引进行了排序,尽量避免再次排序
* 如果sort操作本身并不大,但是设定的sort area过小使得其不得不进行页面的交换。
* 此外使用大的内存chunks用sort也会造成paging核swapping,从而降低系统性能。
* 避免在频繁分配和收回磁盘上的临时表空间操作
所以应该:尽可能的避免排序操作;尽量使sort操作在内存中完成,减少swapping和paging的操作;尽可能的减少对临时空间的请求。
1)临时表空间的优化:
明确指明sort操作使用的临时表空间,可以有效的避免在分配与收回sort空间时的序列化操作。在临时表空间中,不能包含任何永久的object,在 Oracle Parallel Server中,对于每个instance,会有一个单独的临时表空间。临时表空间的datafile在备份时时不需要备份的。
对于临时sort segment:
* 在第一次需要申请临时表空间的sort操作申请时,sort segment才被创建;
* 在DB被关闭时才被drop,可以用命令行对其进行扩容
* 它也是由extents组成的,可以用于不同的sort 操作
* 在SGA中存在一个叫sort extents pool(SEP)的数据结构,用于管理临时表空间。当进程需要申请sort space时,会先在SEP中查找临时表空间中空闲的extents。
2)需要进行排序的命令请求有:
* 索引的创建:在建立b-tree之前,必须先将索引列进行排序操作;
* order by和group by子句:必须先对该子句使用的字段进行排序;
* distinct关键字:必须为消除重复行先进行排序;
* UNION、INTERSECT或MINUS操作符:server需要为了消除重复rows先对表进行sort。
* 两表之间的sort-merge连接:如果没有相应的索引用于两表的连接,对于等值连接,如果使用此方法join,则先需要对两表进行全表扫描,并分别进行排序,再合并两表。
对于server的排序操作的监控,可以从v$sysstat中查看:
select name, value from v$sysstat where name = ’sorts (rows)’;
可以使用analyze或是dbms_utility.analyze_*等方法对tables、indexes或是cluster进程统计,从而更好的指导CBO,从而产生更好的执行计划。
3)避免使用sort:在任何可能的地方尽量避免使用sort
* 使用nosort关键字创建索引:
默认情况下,在表中创建索引的时候,会对表中的记录进行排序,排序成功后再创建索引。但是当记录比较多的是,这个排序作业会占用比较多的时间,这也就增加了索引建立的时间(排序作业是在索引创建作业中完成)。有时候,我们导入数据的时候,如采用insert into 语句插入数据过程中同时采用Order by子句对索引字段进行了排序。此时如果在索引创建过程中再进行排序的话,就有点脱裤子放屁,多此一举了。为此在重新创建索引时,如果表中的数据已经排好序了(按索引字段排序),那么在创建索引时就不需要为此重新排序。此时在创建索引时,数据库管理员就可以使用NOSORT可选项,告诉数据库系统不需要对 表中当记录进行重新排序了。
采用了这个选项之后,如果表中的记录已经按顺序排列,那么在重新创建索引的时候,就不会重新排序,可以提高索引创建的时间,节省内存中的排序缓存空 间。相反,如果表中的记录是不按索引关键字排序的话,那么此时采用NOSORT关键字的话,系统就会提示错误信息,并拒绝创建索引。所以在使用 NOSORT可选项的时候,数据库管理员尽管放心大胆的使用。因为其实在不能够使用这个选项的时候,数据库也会明确的告知。为此其副作用就比较少,数据库管理员只需要把这个可选项去掉然后重新执行一次即可。不过这里需要注意的是,如果表中的记录比较少的话,那么使用NOSORT选项的效果并不是很明显。
* 使用UNION ALL代替UNION:因为UNION ALL不会消除重复的rows,所以也无需进行sort操作。
* 使用Nested loop join代替sort-merge join
* 在经常使用order by子句的列上创建索引。
* 使用analyze时,只统计所需字段的数据:
ANALYZE … FOR COLUMNS或ANALYZE … FOR ALL INDEXED COLUMNS
* 对于ANALYZE COMPUTE,其统计结果更精确,但是需要进行一定的sort,对此,可以使用ESTIMATE子句来替代大表或cluster中的ANALYZE。
4)诊断工具:
* 在视图v$sysstat中显示的信息中有:
sorts (memory):完全在内存中进行的排序的次数
sorts (disk):请求临时segments的I/O进行sorts的次数
sorts (rows):当前已经进行过的sort的rows的数量
* 在statspack输出的report中也存在上述信息,并计算了部分平均值
* 视图v$sort_segment和v$sort_usage显示了当前临时segment中的使用情况,以及那些user占用了这些临时segment。
@@ 在具体诊断时,可计算磁盘sort和memory sort的比率,它应该小于5%。否则,如果此比率显示了较大的磁盘排序,则需要考虑是否可以增大SORT_AREA_SIZE的设置。
@@ 注意的是:如果增大了sort area,则每个需要sort的进程所占用的memory可能都会增加,在一定程度上影响了OS的memory分配及paging和swapping。所 以当增加了sort_area_size后,考虑是否可以适当减小SORT_RESERVED_AREA_SIZE的值,不过此值的减小,在一定程度上减 少了内存的使用,但也可能附加着造成了I/O的可能性。
6) 监控临时表空间:主要是查看V$SORT_SEGMENT视图:
select tablespace_name, current_users, total_extents, used_extents, extent_hits, max_used_blocks, max_sort_blocks from v$sort_segment;
具体字段的意义如下:
CURRENT_USERS:Number of active users
TOTAL_EXTENTS :Total number of extents
USED_EXTENTS :Extents currently allocated to sorts
EXTENT_HITS: Number of times an unused extent was found in the pool
MAX_USED_BLOCKS: Maximum number of used blocks
MAX_SORT_BLOCKS :Maximum number of blocks used by an individual sort
7)临时表空间的设置:
默认临时表空间的存储参数对于sort segment都是适用的,只是它们有无限的extents。
设置临时表空间的参数时,先要考虑sort_area_size的值。temporary tablespace的initial和next参数应该是sort_area_size的整数倍,并要考虑额外的segment header的空间。将PCTINCREASE设置为0 。
可以为users指定不同的temporary tablespace,并且可以将temp tablespace放在不同的磁盘上,对于查看使用情况,可以看v$sort_usage
select username, tablespace, contents, extents, blocks from v$sort_usage;
此外,还可查看V$TEMPFILE和DBA_TEMP_FILES可以获得temporary tablespace的相关信息。