暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

临时表空间原理

eygle 2019-10-17
1589

用户执行的很多操作都可能使用临时表空间中的临时段,但是通常最常见的操作是排序。


当用户执行排序操作时,Oracle将优先将这个操作在内存中完成(内存使用在Oracle9i之前主要受sort_area_size参数设置影响;从Oracle9i开始,使用自动PGA管理则主要受pga_aggregate_target参数影响),如果内存中无法容纳排序操作,则Oracle将使用临时表空间作为临时空间进行排序,使用临时表空间的排序在Oracle中被称为磁盘排序(Sort Disk),磁盘排序的性能远远低于内存排序(Sort Memory),所以如果一个数据库系统的磁盘排序过多,则需要考虑优化SQL,看能否减少不适当的排序操作。

 

数据库的排序信息可以通过动态性能视图v$sysstat查询得到:

SQL> select name,value from v$sysstat
  2  where name like 'sort%';
NAME                                VALUE
------------------------------ ----------
sorts (memory)                   72613963
sorts (disk)                           19
sorts (rows)                   7268452226


排序使用临时表空间的方式和永久表空间不同,当第一个使用临时表空间的排序操作开始后,临时段被创建,区间(Extent)被分配到这个临时段中供排序操作使用;当排序完成后,这个临时段并不会被删除,Oracle会将这个临时段中的区间标记为Free,其他排序操作可以继续使用这个临时段;也就是说,临时段在数据库中是一次分配,循回使用的。


在一个数据库实例中,同一个临时表空间中只存在一个临时段,Oracle根据排序的空间需求,逐渐分配区间加入到这个临时段中(增加的区间通过v$sort_segment. ADDED_EXTENTS记录)。


在SGA中存在一块内存区域用于记录临时段中区间的使用情况,这个内存组件被称为排序区池-Sort Extent Pool(SEP)。不同的排序操作可以通过这块内存区域来确定哪些区间是可以被使用的;多个并发进程可以共享同一个临时段,但是不能共享一个正在使用的区间。

SEP的访问通过一个Latch来控制,这个Latch是Sort Extent Pool Latch:
SQL> select * from v$version where rownum <2;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
SQL> select * from v$latchname
  2  where name like 'sort%';
    LATCH# NAME
---------- ------------------------------
       138 sort extent pool


我们可以通过v$latch等视图来查看这个Latch的分配和使用情况:

SQL> select addr,latch#,name,gets,misses,sleeps
  2  from v$latch where name='sort extent pool';
ADDR                LATCH# NAME                                 GETS     MISSES     SLEEPS
---------------- ---------- ------------------------------ ---------- ---------- ----------
0000000380018760       138 sort extent pool                   174897          1          0


SEP的信息还可以通过视图v$temp_extent_pool来查询(对于V$SORT_SEGMENT 和 V$TEMP_EXTENT_POOL的查询和变更都需要获取sort extent pool的Latch):

SQL> select tablespace_name,extents_cached,extents_used,blocks_cached,blocks_used,
     bytes_cached,bytes_used
  2  from v$temp_extent_pool;
TABLESPACE EXTENTS_CACHED EXTENTS_USED BLOCKS_CACHED BLOCKS_USED BYTES_CACHED BYTES_USED
---------- -------------- ------------ ------------- ----------- ------------ ----------
TEMP                    5            0           640           0      5242880          0


以上记录的是一个Windows平台上Oracle10gR2测试数据库的临时区间信息。


在这个测试环境中执行一个排序操作,注意以下操作是为了测试的需要,请不要在生产环境上执行任何测试操作:

SQL> select * from dba_tab_columns a,dba_tab_columns b
  2  order by a.owner,b.table_name;


记录一下此时临时区间的使用:

SQL> select tablespace_name,extents_cached,extents_used,blocks_cached,blocks_used,
  2       bytes_cached,bytes_used
  3  from v$temp_extent_pool;
TABLESPACE EXTENTS_CACHED EXTENTS_USED BLOCKS_CACHED BLOCKS_USED BYTES_CACHED BYTES_USED
---------- -------------- ------------ ------------- ----------- ------------ ----------
TEMP                  547          547         70016       70016    573571072  573571072


我们注意到Extent Pool中的区间在不断增加,增加的区间同时被分配给排序使用。当前正在进行排序的用户信息等,则可以通过v$sort_usage视图来得到:

SQL> select * from v$sort_usage;
USERNAME     USER           SESSION_ SESSION_NUM SQLADDR     SQLHASH SQL_ID
------------ ------------- -------- ----------- -------- ---------- -------------
TABLESPACE    CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#    EXTENTS     BLOCKS   SEGRFNO#
------------- --------- --------- ---------- ---------- ---------- ---------- ----------
SYS          SYS            2132A09C           9 20DB9074 3933222116 dyk4dprp70d74
TEMP          TEMPORARY SORT             201        521        565      72320       1


这里显示一个排序操作正在进行,同时SQLHASH等信息被记录。


排序段的使用情况还可以从v$sort_segment视图中查询得到,注意这里的ADDED_EXTENTS就是指为了完成这个排序已经向这个临时段中增加的区间数量:

SQL> select TABLESPACE_NAME,TOTAL_EXTENTS,TOTAL_BLOCKS,USED_EXTENTS,ADDED_EXTENTS
   2 from v$sort_segment;  
 
TABLESPACE  TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTS ADDED_EXTENTS
------------ ----------- ------------- ----------- -------------
TEMP                 608        77824          608           603


如果结合v$session视图,排序的用户等更为详细的信息都可以查询得到:

SQL> SELECT   b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
  2            a.username, a.osuser, a.status
  3  FROM     v$session a,v$sort_usage b
  4  WHERE    a.saddr = b.session_addr
  5  ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
TABLESPACE  SEGFILE#  SEGBLK#    BLOCKS        SID    SERIAL# USERNAME   OSUSER     STATUS
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----
TEMP          201        521      85888        141          9 SYS        eygle      ACTIVE


更进一步的,根据session信息,排序的SQL操作也可以被即时捕获:

SQL> SELECT   sql_text
  2      FROM v$sqltext a
  3     WHERE a.hash_value = (SELECT sql_hash_value
  4                             FROM v$session b
  5                            WHERE b.SID = '&sid')
  6  ORDER BY piece ASC
  7  /
输入 sid 的值:  141
SQL_TEXT
----------------------------------------------------------------------------------
select * from dba_tab_columns a,dba_tab_columns b order by a.owner,b.table_name

十分不幸,这个排序操作最后没能完成,由于临时表空间的不停扩展,最后耗尽了我的磁盘空间,SQL操作得到以下错误:

SQL> select * from dba_tab_columns a,dba_tab_columns b
  2  order by a.owner,b.table_name;
ERROR:
ORA-01114: 将块写入文件 %s 时出现 IO 错误 (块 # %s)


然后数据库Crash崩溃,从警告日志中,我们可以找到更详细的错误信息:

Mon Apr 09 21:16:43 2007
Errors in file c:\oracle\admin\eygle\udump\eygle_ora_3112.trc:
ORA-00206: 写入控制文件时出错 (块 17, # 块 1)
ORA-00202: 控制文件: ''D:\ORADATA\EYGLE\CONTROLFILE\O1_MF_2G8OGNNL_.CTL''
ORA-27072: 文件 I/O 错误
OSD-04008: WriteFile() 失败, 无法写入文件
O/S-Error: (OS 112) 磁盘空间不足。
 
Mon Apr 09 21:16:45 2007
Errors in file c:\oracle\admin\eygle\bdump\eygle_ckpt_1020.trc:
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: 'D:\ORADATA\EYGLE\CONTROLFILE\O1_MF_2G8OGNNL_.CTL'
ORA-27072: File I/O error
OSD-04008: WriteFile() 失败, 无法写入文件
O/S-Error: (OS 112) 磁盘空间不足。
 
Mon Apr 09 21:16:45 2007
CKPT: terminating instance due to error 221


由于这个环境中,控制文件、数据文件存放在同一个磁盘上,最终的错误是由于控制文件无法写入,CKPT进程终止了实例。由此我们也可以看到过度空间耗用可能带来的严重后果。


在数据库重新启动之后,SMON会释放临时临时段中的区间,准备为新的排序服务:

SQL> select TABLESPACE_NAME,TOTAL_EXTENTS,TOTAL_BLOCKS,USED_EXTENTS,ADDED_EXTENTS
  2       from v$sort_segment;
TABLESPACE_NAME      TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTS ADDED_EXTENTS
-------------------- ------------- ------------ ------------ -------------
TEMP                           810       103680            0             0


在刚才的排序测试中,Oracle已经将临时表空间扩展到了800M左右:

SQL> select file#,bytes/1024/1024 from v$tempfile;
     FILE# BYTES/1024/1024
---------- ---------------
         1             812


此时我们可以将临时文件resize,以缩减其空间耗用:

SQL> alter database tempfile
  2  'D:\ORADATA\EYGLE\DATAFILE\O1_MF_TEMP_2G8OJPDX_.TMP' resize 100M;


数据库已更改。


更多的时候,在生产环境中,我们可能会遇到如下错误(错误信息来自警告日志):

Mon Dec  6 16:51:44 2004
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP2
Mon Dec  6 16:51:55 2004
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP2


这个提示表明临时表空间不足,空间不足可能是由于空间耗尽,也可能是由于临时表空间不能扩展,通过如下SQL我们能够获得当前正在使用临时段排序的用户:

SELECT          /*+ rule */
       DISTINCT a.SID, a.process, a.serial#,
                TO_CHAR (a.logon_time, 'YYYYMMDD HH24:MI:SS') LOGON, a.osuser,
                TABLESPACE, b.sql_text
           FROM v$session a, v$sql b, v$sort_usage c
          WHERE a.sql_address = b.address AND a.saddr = c.session_addr
/


在这个案例中,我们捕获了以下主要排序语句:

SQL> @getsort
       SID PROCESS         SERIAL# LOGON             OSUSER                         TABLESPACE
---------- ------------ ---------- ----------------- ------------------------------
SQL_TEXT
-----------------------------------------------------------------------------------
        15                   24965 20041207 16:38:01 oracle                         TEMP2
select count(1) from HW_User4Love u where u.numIntention<>99 and u.numGender=:1
 
        21                   49757 20041207 16:33:28 oracle                         TEMP2
select * from (select t.*, rownum i from (select u.numUserId,u.vc2UserName,u.numUserType,u.numRank,u.numGender,u.numAge,
u.numDistrict,u.vc2District,u.numLooking,u.numPersonality,u.numAbility,u.numZodiac,u.numExperience from HW_User4Love u where u.numIntention<>99 order by u.numUserType desc, u.numRank desc, u.numUserId desc) t where rownum<=260) where i>240
 
        30                   65414 20041207 16:34:04 oracle                         TEMP2
select * from (select t.*, rownum i from (select u.numUserId,u.vc2UserName,u.numUserType,u.numRank,u.numGender,u.numAge,
u.numDistrict,u.vc2District,u.numLooking,u.numPersonality,u.numAbility,u.numZodiac,u.numExperience from HW_User4Love u where u.numIntention<>99 order by u.numUserType desc, u.numRank desc, u.numUserId desc) t where rownum<=40) where i>20


这里大量类似的SQL在占用大量的排序空间,最终确定是这些SQL引起的临时表空间过量使用,找到这些SQL之后,我们就可以通过研究其执行计划,优化调整以解决过度排序问题。


除了临时表空间中的临时段之外,在特定操作的执行中,也可能在常规的数据表空间中产生临时段,比如创建索引中间过程产生的临时段、执行数据导入或加载中间过程产生的临时段。以下是在一个Oracle9iR2的数据库环境中,执行包含大对象的数据表导入操作,在漫长的导入过程中,可以看到数据库中创建了大量的临时段,这些临时段按照file_id.block_id的方式命名,在对象导入完成之后,段名称会被修改,如果导入失败,这些大量的临时段会被SMON进程维护清理:

SQL> select segment_name,segment_type,tablespace_name,blocks 
   2 from user_segments where segment_type='TEMPORARY';
 SEGMENT_NAME      SEGMENT_TYPE      TABLESPACE_NAME                BLOCKS
 ------------------ ------------------ ------------------------------ ----------
 85.64060           TEMPORARY          GLOB09                        12800
 85.76860           TEMPORARY          GLOB09                        12800
 85.89660           TEMPORARY          GLOB09                        12800
 85.102460          TEMPORARY          GLOB09                        12800
 85.115260          TEMPORARY          GLOB09                        12800
 85.128060          TEMPORARY          GLOB09                        12800
 87.192060          TEMPORARY          GLOB09                        12800
 87.204860          TEMPORARY          GLOB09                        12800
 87.217660          TEMPORARY          GLOB09                        12800
 87.230460          TEMPORARY          GLOB09                        12800
 87.243260          TEMPORARY          GLOB09                        12800
 87.256060          TEMPORARY          GLOB09                        12800
 87.268860          TEMPORARY          GLOB09                        12800
 87.281660          TEMPORARY          GLOB09                        12800
 87.294460          TEMPORARY          GLOB09                        12800
 87.307260          TEMPORARY          GLOB09                        12800
 87.320060          TEMPORARY          GLOB09                        12800
 87.332860          TEMPORARY          GLOB09                        12800
 87.345660          TEMPORARY          GLOB09                        12800
 87.358460          TEMPORARY          GLOB09                        12800


了解了临时表空间的使用原理之后,让我们来看一下在不同版本中,Oracle关于临时表空间的技术变迁。

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论