临时段：当排序操作时发生了什么？

介绍

大部分SQL操作不要求排序。然而，确实有一些操作是要求排序的。排序可能会对性能产生很大的影响。

–创建索引
CREATE INDEX语句会引起一个或多个（若果创建索引时启用并行）服务进程对索引值进行排序，这些排序操作发生在创建平衡树之前。完成排序之后，一个索引以临时段的形式创建在INDEX表空间；一旦索引创建完成，段类型会转变成index.

–带有ORDER BY 或 GROUP BY 的SELECT 语句
当运行含有ORDER BY 或GROUP BY的SELECT语句时，服务器进程一定会对返回值进行排序。

–含有DISTINCT的SELECT语句
对于关键词DISTINCT，为了排除重复值，排序是必须的。

–UNION, INTERSECT 或MINUS操作
服务器进程需要对表排序以排除重复值。

–执行分析命令
分析命令对数据排序以提供汇总信息。

除了以上操作，还有一些SQL也需要临时段。例如：CREATE PRIMARY KEY CONSTRAINT, ENABLE CONSTRAINT, 和CREATE TABLE。当使用语句CREATE TABLE AS SELECT，或者MINEXTENTS的值大于1创建新表时，该表会以临时段的形式开始创建。

内存分配

排序需要内存空间。这些内存区域被称为排序区（sort areas）。每一个用户会话拥有自己的排序区大小。文件“init.ora”中参数SORT_AREA_SIZE定义了被单个排序操作使用真正内存空间的最大值（单位是bytes）。对于大型系统来说，参数SORT_AREA_SIZE通常设置为64K到256K之间。

排序区在内存中的哪个结构中分区取决于服务器（Server）的配置。
–专用服务器
排序区被分配在程序全局区（PGA）中除了排序区以外，PGA还包含会话信息（例如用户权限），游标状态和堆空间（stack-space）。
–共享服务器
排序区被分配在用户全局区（UGA）中。UGA位于系统全局区（SGA）的共享池（shared pool）中。

对于每个会话，都会有第一次发生排序操作的时刻。这一刻发生时，内存被分配。排序区的大小会增大，直到足够进行排序操作或者达到SORT_AREA_SIZE所指定的最大值。
当一个排序完成，内存会为下一次排序操作保留下来，但是大小会降到参数SORT_AREA_RETAINED_SIZE所指定的值。当先前的排序完成之后，内存区域会为下次排序操作而保留下来，参数SORT_AREA_RETAINED_SIZE定义了这个内存的最大值。
SORT_AREA_SIZE和SORT_AREA_RETAINED_SIZE之间的区别是排序之后被释放的内存。默认情况下，两个参数的值是相等的，这样别上一次排序使用的所有内存对于下一次排序来说都是可用的。当一个进程作为一个整体被中断是，内存将被返还给操作系统（专用连接），或者返还给共享池（共享连接）。

参数SORT_AREA_SIZE和SORT_AREA_RETAINED_SIZ都能够在session级别修改。例如设置当前会话的排序内存为100k：

ALTER SESSIONSET SORT_AREA_SIZE=102400SORT_AREA_RETAINED_SIZE=102400;

如果想要为所有新的会话改变这两个参数的值，可以使用ALTER SYSTEM语句。例如将所有新的会话的排序区设置为64k：

ALTER SYSTEM SET SORT_AREA_SIZE=65536 DEFERREDSORT_AREA_RETAINED_SIZE=65536 DEFERRED;

用户定义：
如果排序操作需要额外的内存（大于由参数SORT_AREA_SIZE指定的值），那么已经排好序的行被写到磁盘来释放排序区，这样排序区就就能够被剩下的排序重新使用。这意味着临时段被创建。临时写入磁盘的排好序的行被存入临时段。临时段被创建的位置，取决于用户的设置。为了找出能够被用户使用的永久段和临时段，可以使用如下查询：

SELECT TABLESPACE_NAME, STATUS, CONTENTSFROM dba_tablespaces;TABLESPACE_NAME STATUS CONTENTS----------------- --------- ---------SYSTEM ONLINE PERMANENTRBS ONLINE PERMANENTUSERS ONLINE PERMANENTTEMP ONLINE TEMPORARYINDX ONLINE PERMANENTDRSYS ONLINE PERMANENTPERM01 ONLINE PERMANENTTEMP01 ONLINE TEMPORARY8 rows selected.

当一个创建新用户时，TEMPORARY TABLESPACE子句指定了用户的临时段所在的表空间，例如：

CREATE USER hugoIDENTIFIED by welcomeDEFAULT TABLESPACE usersTEMPORARY TABLESPACE perm01QUOTA 10M on users;

如果遗漏了TEMPORARY TABLESPACE子句，那么临时段默认存放在SYSTEM表空间（没有默认临时表空间）。对于每个已经存在的用用户，临时表空间能够被改变。例如：

ALTER USER hugoTEMPORARY TABLESPACE temp01;

表空间中的空间管理。

表空间在区（extents）中分配空间。表空间使用两种不同的方法来追踪空闲和已使用的空间。

–字典管理的表空间（数据字典管理区）
对于使用字典管理区的表空间，当区被分配或者被释放时，Oracle更新相应的数据字典表。默认情况下，字典管理的表空间的区大小是递增的。区的大小由存储参数INITIAL,NEXT和PCTINCREASE。当一个对象被创建，第一个区的大小由INITIAL的值指定。
当需要额外的空间时，参数NEXT和PCTINCREASE决定了新段的大小。因为数据字典是数据库的一部分，所以他们自己占用的空间的表里方法和其他的数据一致。这是默认的表空间管理方法，在Oracle8及之前版本，这是唯一的可用方法。

–本地管理的表空间（Oracle8.1及更高版本）
管理自己区的表空间，在每个数据文件中维护着一个位图，这个位图跟踪空间和已使用块的状态。位图中的每个bit对应这一个或者一组数据块。当一个区被分配或者释放，Oracle更新响应位图的值以反应块的状态。本地管理的表空间要么使用同一大小的区要么使用可变大小的区，可变大小的区的尺寸由系统自动控制。当创建表空间时，UNIFORM或者AUTOALLOCATE选项指定了分配去的方式。对于本地管理的分区，存储参数NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, 和 DEFAULT STORAGE不是有效的参数。

本地管理的表空间相比数据字典管理的表空间有如下优点：
-本地管理的表空间避免了分配和释放空间时的递归操作。
-本地区管理自动跟踪相邻的空闲空间，消除了合并空闲区的需求。

当创建表空间是，需要选择一个空间管理的方法，并且创建完成之后是无法修改的。

永久表空间中的临时段

临时段能够在任何表空间中存储。然而，当存储在永久表空间中会有一些缺点。

–任意一个实例在这个永久表空间中可能拥有一个或多个临时段，因为在事务级别，任何时候需要的时候临时段就会被创建。
–这个永久表空间碎片化会很严重，因为临时段会重复的分配和回收。
–当一个语句执行完成，后台进程SMON会清理临时段，如果创建了大量的排序段，SMON进程会花费很多的时间来清理他们；这意味着会有数据库性能损失。
SMON进程释放临时段之后，释放的空间能够被其他对象使用。

一般的，使用如下语句创建一个用来存放临时段的，拥有一个数据文件的永久表空间。（在oracle8.1+中，默认为字典管理方式）

CREATE TABLESPACE perm01DATAFILE '\oradata\test815\perm01.dbf' SIZE 10M REUSEAUTOEXTEND ON NEXT 10M MAXSIZE 100MDEFAULT STORAGE (INITIAL 72K NEXT 72K PCTINCREASE 0);

在Oracle8.1+中，也可以创建本地管理的永久表空间来存放临时段（需要使用统一区大小）

CREATE TABLESPACE perm02DATAFILE '\oradata\test815\perm02.dbf' SIZE 10M REUSEAUTOEXTEND ON NEXT 10M MAXSIZE 100MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 72K;

如果假设数据库块大小为8k，并且在位图中一个bit代表一个区（72K），那么一个bit对应9（72k/8k）个Oracle数据块。

一个字典管理的永久表空间能够转变成一个临时表空间，并且这个表空间中不能有永久对象：

ALTER TABLESPACE perm01TEMPORARY;

临时表空间中的临时段

指定一个临时表空间专门用做排序操作是高效的（Oracle7.3+)，这样避免了连续不断的分配和和回收排序空空间。所有使用排序的操作（包括：表连接，创建索引，排序，聚合计算等）都能够从临时表空间中受益。在oracle并行环境中性能会有可观的受益。

多个需要在磁盘中排序的事务能够共享相同的排序段，但是不能共享同一个区。通过分配新的区来扩展排序段。实例运行是不会回收排序区，但是会被标记为空闲并且能够被再利用。因此，排序段会增长到一个稳定的状态。Oracle将排序段的信息存储在排序区池（Sort Extent Pool，SEP），属于SGA的一部分。需要在临时表空间中排序的语句会查看这个区域以确定是否有空闲的区。因为每个操作结束后，这些区不会分配和回收，数据库将会得到一个综合的性能提升。

在实例启动和数据库被打开之后，后台进程SMON确实会回收排序段。因此，数据库打开之后，SMON可能会消耗很大的CPU，因为它在回收排序段中的区。这种行为会被放大，如果临时表空间的默认NEXT存储参数不合适（小）。

一般的，使用如下语句创一个带有一个数据文件的临时表空间。（Oracle8.1+中，这个语句创建的表空间是字典管理的）

CREATE TABLESPACE temp01DATAFILE '\oradata\test815\temp01.dbf' SIZE 10M REUSEAUTOEXTEND ON NEXT 10M MAXSIZE 100MDEFAULT STORAGE (INITIAL 72K NEXT 72K PCTINCREASE 0)TEMPORARY;

在Oracle8.1中，也可以创建一个本地管理的临时表空间（使用如下语句意味这表空间是本地管理的，并且统一区大小）

CREATE TEMPORARY TABLESPACE temp02TEMPFILE '\oradata\test815\temp02.dbf' SIZE 10M REUSEAUTOEXTEND ON NEXT 10M MAXSIZE 100MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 72K;

如果假设数据库块大小为8k，并且在位图中一个bit代表一个区（72K），那么一个bit对应9（72k/8k）个Oracle数据块。
临时表空中只能存放临时段，无能存放永久读段。字段管理的临时表空间可以转变成永久表空间。
ALTER TABLESPACE temp01
PERMANENT;

指导方针

下面的指导方针可用于临时段：
-使用临时表空间来减少频繁的分配和回收临时段。
-使用一个或多个临时表空间来最小化数据访问冲突。
-将临时表空间在多个磁盘上条带话。
-当指定默认的存储参数时，设置INITIAL=NEXT，因为一个进程写到临时段的数据量总是等于参数SORT_AREA_SIZE的大小。
-使用下来的公式来计算NEXT值（适用于字典个管理的临时表空间）：（n*s+b)
n=正整数
s=参数SORT_AREA_SIZE的值
b=参数DB_BLOCK_SIZE的值

临时段信息
查询视图v$sort_segment来检查临时表空间。

SELECT tablespace_name, extent_size, total_extents, used_extents,free_extents, max_used_sizeFROM v$sort_segment;

SELECT s.username, u.tablespace, u.contents, u.extents, u.blocksFROM v$session s, v$sort_usage uWHERE s.saddr=u.session_addr;