Oracle 临时表空间总结(转载)

孟5016

　　转载：http://www.itpub.net/thread-1481844-1-1.html
　　
All operations that use sorts, including joins, index builds, ordering, computing aggregates (GROUP BY), and collecting optimizer statistics, benefit from temporary tablespaces. The performance gains are significant with Real Application Clusters.
　　
　　
首先从数据字典视图说起
v$dba_temp_files
v$sort_segment
v$tempseg_usage
dba_temp_free_space

上面这几个视图是我总结的在遇到Oracle临时空间问题时能用到的数据字典信息。
先看v$dba_temp_files
这个视图显示了数据库中临时文件的具体信息。要注意的有4列

SQL> desc dba_temp_files;
Name            Type          Nullable Default Comments                                            
--------------- ------------- -------- ------- ---------------------------------------------------
FILE_NAME       VARCHAR2(513) Y                Name of the database temp file                     
FILE_ID         NUMBER        Y                ID of the database temp file                        
TABLESPACE_NAME VARCHAR2(30)                   Name of the tablespace to which the file belongs   
BYTES           NUMBER        Y                Size of the file in bytes                           
BLOCKS          NUMBER        Y                Size of the file in ORACLE blocks                   
STATUS          VARCHAR2(7)   Y                File status: "AVAILABLE"                           
RELATIVE_FNO    NUMBER        Y                Tablespace-relative file number                     
AUTOEXTENSIBLE  VARCHAR2(3)   Y                Autoextensible indicator:  "YES" or "NO"            
MAXBYTES        NUMBER        Y                Maximum size of the file in bytes                  
MAXBLOCKS       NUMBER        Y                Maximum size of the file in ORACLE blocks           
INCREMENT_BY    NUMBER        Y                Default increment for autoextension                 
USER_BYTES      NUMBER        Y                Size of the useful portion of file in bytes         
USER_BLOCKS     NUMBER        Y                Size of the useful portion of file in ORACLE blocks

这四列中， BYTES , BLOCKS 显示的是临时文件有多少BYTE大小，包含多少个数据块。而USER_BYTES,USER_BLOCKS是可用的BYTE和数据块个数。因此，我们可以知道临时文件中有一部分是被系统占用的，大概可以理解成文件头信息，在我的db中，这一部分大小是128个block，如下图所示，22784-22656=128.
SQL> select file_name, blocks , user_blocks from dba_temp_files;

FILE_NAME                                                                            BLOCKS          USER_BLOCKS
-------------------------------------------------------------------------------- -------------------- -----------
/u01/app/oracle/oradata/sales/temp01.dbf                                    22784                22656
/u01/app/oracle/oradata/sales/temp02.dbf                                    6400                  6272


再看v$sort_segment视图
这个视图从字面翻译虽然叫做排序段，但实际上不仅仅是排序，只要是消耗了临时表空间的操作，比如创建临时表等，都会在这个视图中显示。但如果一个操作没有占用临时表空间（比如开销很小的排序，在内存中完成，或者一个临时表在创建时通过tablespace子句指定到了一个永久表空间中),那么这个视图就不会被更新。
但有一个问题是，我们指定正常的segment是一个segment用来承载一个对象如表或者index，再或者一个对象的分区，如分区表的分区，但我们的sort_segment似乎不是这样的，一个sort_segment可以供多个对象或者说操作使用。
如图，一个sort_segment, 有两个extent被占用，同时有两个user在使用。因为这个测试是我自己进行的，所以我清楚的知道，两个user 一个在创建临时表，另一个在创建一个大表的索引。这根本就是两件不相干的事，但用的是同一个sort_segment.
不过这个问题不重要，因为我们通过这个视图主要知道多少个用户在使用临时表空间，已经使用了多少这就够了。
SQL> select tablespace_name ,TOTAL_BLOCKS,USED_EXTENTS,USED_BLOCKS ，CURRENT_USERS from v$sort_segment;

TABLESPACE_NAME    TOTAL_BLOCKS    USED_EXTENTS    USED_BLOCKS  CURRENT_USERS
-------------------------------  -----------------------      ----------------------     -----------                 ---------------------
TEMP                             22656                         2                               256                        2


再看v$tempseg_usage
这个视图可以详细的显示那些用户在使用临时空间，使用了多少，其session的id，SQL的ID等。但有一个很困惑的问题是其中的SQL_ID列显示的信息不准确。根据我的试验，它显示的应该是目标会话的当前SQL的SQL_ID，而非消耗临时表空间的SQL_ID。比如说你在一个会话中发出了一条SQL消耗了很大的临时空间，然后你又发出了一条不相干的SQL，这时如果我select * from v￥tempseg_usage，查到的SQL_ID对应的SQL是第二条，也就是最新的那条。
另外一条要注意的是，这个视图与 v$sort_usage是一样的，只不过后者改了个名字而已。大概oracle是这么考虑的，如果叫sort_usage会引起歧义，会让人以为这个视图针对的是排序的开销，但实际上不仅仅是排序，只要是对临时表空间的消耗都会体现在这个视图中。


dba_temp_free_space
SQL> desc dba_temp_free_space
Name                                          Null?            Type
----------------------------------------- --------            ----------------------------
TABLESPACE_NAME                NOT NULL  VARCHAR2(30)
TABLESPACE_SIZE                                        NUMBER
ALLOCATED_SPACE                                     NUMBER
FREE_SPACE                                                  NUMBER

该视图要注意的是ALLOCATED_SPACE 和 FREE_SPACE。前面两个比较简单，tablespace_name不用说了，而      tablespace_size  体现的是整个表空间的大小。如果把前面dba_temp_files 中BYTES列的值加起来，就是这个tablespace_size的值。 至于ALLOCATED_SPACE则要注意了，他体现的是分配出去的临时空间，包括分配出去但仍然可用的以及分配出去但不可用的。我们通过下面四个语句来理解这个视图。
SQL> select * from dba_temp_free_space;

TABLESPACE_NAME  TABLESPACE_SIZE             ALLOCATED_SPACE FREE_SPACE
------------------------------ -------------------------------------- ---------------------- ----------
TEMP                             239075328                            187695104             234881024

SQL> select file_name, blocks , user_blocks from dba_temp_files;

FILE_NAME                                                                            BLOCKS          USER_BLOCKS
-------------------------------------------------------------------------------- -------------------- -----------
/u01/app/oracle/oradata/sales/temp01.dbf                                    22784                22656
/u01/app/oracle/oradata/sales/temp02.dbf                                    6400                  6272

SQL> select TOTAL_BLOCKS,USED_EXTENTS,USED_BLOCKS ，CURRENT_USERS from v$sort_segment;

TOTAL_BLOCKS       USED_EXTENTS      USED_BLOCKS       CURRENT_USERS
-----------------------        ----------------------        -----------                     ---------------------
22656                             2                                256                              2

对于tablespace_name没什么好说的。

对于tablespace_size，我们理解为是临时表空间的总大小。验证一下。
通过dba_temp_files视图，我们知道了每个temp文件的数据块个数，22784和6400. 那么总的大小是数据块个数之和乘以数据块的大小（本系统中是8192）。 （22784 +   6400） * 8192 = 239075328。 验证成功。

对于ALLOCATED_SPACE我们理解为分配出去的空间。
验证一下。
v$sort_segment中查到total_block 为22656. 而我们知道temp文件每个文件都被系统占用了128个block（从dba_temp_file 视图的 block ， user_block对比可以看出）。那么被占用的总的数据块个数应该是128 *2 （两个临时文件所以乘以2） 加上sort_segment中的22656.   128*2 + 22656=22912。 这是数据块的个数，如果要得到BYTE为单位的大小还要乘以8192（数据块尺寸）， 结果正好为ALLOCATED_SPACE。验证成功。不过要注意的很重要的一点是，这里虽然是allocate的空间但并非是不可用的。其中包含了一部分仍然可用的。比如sort_segment这个视图中没分配的extent。

对于FREE_SPACE，也许你会认为直接用 total size 减去 allocate的space就可以了。但如果这样，那么这列存在的意义就不大了。实际上这一列显示的是 临时空间中可用的空间。可用的意思是如果有排序操作或者说临时表操作需要，我们仍然可以提供这么大的空间。这一部分不是 total 与 allocate的差，实际上它应该是 total 减去 allocate 再加上allocate中可用的那部分。
验证一下 ，total 减去 allocate 为239075328 - 187695104 = 51380224。而 allocate中可用部分的大小我们可以从v$sort_segment视图中看出。可以看到仅仅使用了256个block。那么剩余的空间应该是v$sort_segment的总大小减去256 ，为22656-256 = 22400.  这里只是数据块的个数，要得到大小还要乘以8192 得 183500800。用这个数字加上之前的51380224正好为  234881024。