Oracle临时段管理

临时段既可以在临时表空间中，也可以在永久表空间中。临时表空间中的临时段主要用于排序，当排序操作无法在内存中完成时，Oracle就会使用到它。不过，在排序时，过多地使用临时表空间会对数据库的性能影响较大。永久表空间中的临时段主要用于在创建对象（如表格、索引）的过程中临时使用，对象创建完成之后临时段就会变成对象所属的段类型，但如果在创建过程中异常退出，则会由SMON进程负责清理永久表空间中的临时段。
1.产生临时段的场景
以下几个场景可能会产生临时段：
创建索引时。Oracle服务进程在创建索引之前需要将索引值进行排序。排序完成之后，先会在索引所在的表空间中创建临时段，然后会将段类型变为索引段。
在SELECT语句中有ORDER BY或者GROUP BY选项的情况下。
在SELECT 语句中有DISTINCT关键字的情况下。
有UNION、INTERSECT或者MINUS操作的情况下。
使用Sort-Merge的表连接方式时。
使用Analyze命令时。
此外CREATE PRIMARY KEY CONSTRAINT、ENABLE CONSTRAINT和CREATE TABLE AS等操作均可能会产生临时段。
2.临时表空间中临时段的使用与释放
从Oracle 7.3开始，推出了新的临时段算法用于管理临时表空间，即使用存储于共享池的SORT EXTENT POOL来管理临时段的使用与释放。使用临时表空间的临时段之前，进程获得SORT EXTENT POOL LATCH之后将会从SORT EXTENT POOL中分配空闲的EXTENT，并标注可用状态。如果找不到可用的EXTENT，则从临时表空间分配新的空间，如果临时表空间空间不足，则出现ORA-01652错误，如下所示：

[ora10205@mcdbatest ~]$ oerr ora 1652
01652, 00000, "unable to extend temp segment by %s in tablespace %s"
// *Cause:  Failed to allocate an extent of the required number of blocks for
//          a temporary segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//          files to the tablespace indicated.

当排序操作完成时，进程需要再次获得SORT EXTENT POOL LATCH，并将使用过的EXTENT标记为FREE状态，然后释放SORT EXTENT POOL LATCH。在RAC系统中，虽然所有的实例均可以使用相同的临时表空间，但每个实例会独立维护自己的SORT EXTENT POOL。当本实例无法在自己的SORT EXTENT POOL中分配到EXTENT且无法从TEMP表空间中分配到空间时，可以从其他节点的SORT EXTENT POOL中分配到FREE状态的EXTENT。这个操作对前台进程是透明的，但在节点的警告日志中会留下ORA-01652错误。
基于性能上的考虑，新的临时段算法在分配临时段时采用“只分配不释放”的原则。即临时段被分配后，即使排序操作完成，也只会在SORT EXTENT POOL中将其标记为FREE状态，并不会从临时表空间中释放。所以Oracle不需要频繁地分配和释放临时段，这大大提高了临时段管理的效率。由于临时段使用之后不释放，所以经常可以看到临时表空间的使用率为100%，这是不足为怪的。临时表空间中的临时段只有在数据库重启或所在的临时表空间被删除后才会释放。可以用以下脚本来观察临时表空间的使用情况：

• For dictionary managed temporary tablespace: select
  (s.tot_used_blocks/f.total_blocks)*100 as “percent used” from
  (select sum(used_blocks) tot_used_blocks from v$sort_segment
  where tablespace_name=‘TEMP’) s, (select sum(blocks)
  total_blocks from dba_data_files where tablespace_name=‘TEMP’) f;

  For locally managed temporary tablespace: select
  (s.tot_used_blocks/f.total_blocks)*100 as "percent used" from
  (select sum(used_blocks) tot_used_blocks from v$sort_segment
  where tablespace_name='TEMP') s,  (select sum(blocks)
  total_blocks from dba_temp_files where tablespace_name='TEMP') f;
  

当数据库或者应用程序出现异常时，可能会导致临时表空间过度扩展，从而导致占用大量的操作系统空间，此时就需要DBA根据V$SORT_USAGE中临时段的使用情况来RESIZE临时文件了。
从Oracle 10g开始，在临时表空间进行排序操作时，表现为DIRECT PATH READ TEMP、DIRECT PATH WRITE TEMP事件。在Oracle 9i主要表现为DIRECT PATH READ、DIRECT PATH WRITE事件。当系统出现较多以上等待事件时可以使用以下脚本来观察使用临时表空间的会话相关信息：

• For 8.1.7 to 9.2: SELECT a.username, a.sid, a.serial#, a.osuser,
  b.tablespace, b.blocks, c.sql_text FROM v$sessiona,v$sort_usage
  b, v$sqlarea c WHERE a.saddr = b.session_addr AND c.address=
  a.sql_address AND c.hash_value = a.sql_hash_value ORDER BY
  b.tablespace, b.blocks;

  For 10.1 and above: SELECT a.username, a.sid, a.serial#,
  a.osuser, b.tablespace, b.blocks, c.sql_text FROM v$session a,
  v$tempseg_usage b, v$sqlarea c WHERE a.saddr = b.session_addr AND
  c.address= a.sql_address AND c.hash_value = a.sql_hash_value
  ORDER BY b.tablespace, b.blocks;