臨時表空間的空間使用情況查詢

bfc99發表於2014-03-17
以下轉自：http://www.itpub.net/thread-1481844-1-1.html 作者：kramer2009
這兩天把臨時表空間的知識點總結了一下。記下來分享一下。


首先從資料字典檢視說起
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。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22207394/viewspace-1123506/，如需轉載，請註明出處，否則將追究法律責任。

相關文章