oracle11g 查詢臨時表空間的使用率和正在使用臨時表空間的使用者

記錄每一次錯誤發表於2018-12-21
Oracle

今天開發反映說臨時表空間不夠要求新增臨時表空間，新增完成（新增了30G的臨時表空間）又報臨時表空間的不足，開發又要求是新增，有新增了10G，監控臨時表空間的使用情況，又撐滿了，又新增了30G,最後臨時表空間又撐滿了，最後批處理還是沒有執行，SQL語句的執行是一次性全部執行完成，最後的解決辦法是開發使用遊標，一萬條資料一提交或者定量提交，下午監控臨時表空間的使用情況發現使用率 74.91%。不禁有些疑問到底是那些使用者在使用臨時表空間，使用臨時表空間的大小有多大，

在做一些什麼操作，SQL語句是什麼。

    首先要說明一點的是表空間的使用惰性，如果你一下使用幾十個G的臨時表空間，如果SQL語句執行失敗，臨時表空間的釋放需要時間，不會一下全部釋放，這個時候我們檢查臨時表空間的使用率發現使用率已經是99%，如果你擴容臨時表空間，擴容30G，開發又開始執行sql，你會發現臨時表空間使用率蹭蹭的網上增最後又達到了99%，開發的sql又沒有執行過去，還是報臨時表空間不足，而臨時表空間釋放有需要時間，如果開發的非常著急需要執行sql。所以建議dba一次擴容臨時表，擴容大點，

    說一下今天處理問題的過程，

    1.查詢臨時表空間的使用率：

select c.tablespace_name,

to_char(c.bytes/1024/1024/1024,'99,999.999') total_gb,

to_char( (c.bytes-d.bytes_used)/1024/1024/1024,'99,999.999') free_gb,

to_char(d.bytes_used/1024/1024/1024,'99,999.999') use_gb,

to_char(d.bytes_used*100/c.bytes,'99.99') || '%'use

from  (select tablespace_name,sum(bytes) bytes

from dba_temp_files GROUP by tablespace_name) c,

(select tablespace_name,sum(bytes_cached) bytes_used

from v$temp_extent_pool GROUP by tablespace_name) d

where c.tablespace_name = d.tablespace_name;

    2.查詢那些使用者在使用

select a.username,

       a.sql_id,

       a.SEGTYPE,

       b.BYTES_USED/1024/1024/1024||‘G’,

       b.BYTES_FREE/1024/1024/1024  from   V$TEMPSEG_USAGE  a  join  V$TEMP_SPACE_HEADER b on   a.TABLESPACE=b.tablespace_name; 

        解釋username  正在執行sql的使用者名稱

            sql_id    正在執行的sql的的sql_id

            segtype   正在執行的SQL語句做的是什麼操作

            BYTES_USED 正在執行sql語句使用的臨時表空間的大小

            BYTES_FREE  剩餘多少臨時表空間

   大家可以看到這個臨時表空間的有6個資料檔案，查詢的結果顯示按照每個臨時的資料檔案使用了多少，還剩餘多少，又又有一個問題是第一使用者DBSNMP使用者已經把臨時表空間佔滿了那麼第二個使用者還能使用這個六個資料檔案的臨時表空間麼，個人理解是六個資料檔案中有第一個使用者在使用，也有第二個使用者在使用。

   查詢例項中時候是否有大欄位在使用臨時表空間：

    select *  from V$TEMPORARY_LOBS;

關於資料字典V$TEMP_SPACE_HEADER官方文件的解釋：

V$TEMP_SPACE_HEADER 顯示每個LOCALLY MANAGED臨時表空間的每個檔案的聚合資訊，包括當前正在使用的空間量以及空間頭中標識的空閒量。    

    

Column Datatype Description
TABLESPACE_NAME VARCHAR2(30) Name of the temporary tablespace
FILE_ID NUMBER Absolute file number
BYTES_USED NUMBER How many bytes are in use
BLOCKS_USED NUMBER How many blocks are in use
BYTES_FREE NUMBER How many bytes are free
BLOCKS_FREE NUMBER How many blocks are free
RELATIVE_FNO NUMBER The relative file number for the file

關於V$TEMPSEG_USAGE的官方文件的解釋：

V$TEMPSEG_USAGE 描述臨時段使用情況。 

柱 資料型別 描述
USERNAME VARCHAR2(30) 請求臨時空間的使用者
USER VARCHAR2(30) 此列已過時並維護以便向後相容。 此列的值始終等於中的值 USERNAME 。
SESSION_ADDR RAW(4 | 8) 會話地址
SESSION_NUM NUMBER 會話序列號
SQLADDR RAW(4 | 8) SQL語句的地址
SQLHASH NUMBER SQL語句的雜湊值
SQL_ID VARCHAR2(13) SQL語句的SQL識別符號
TABLESPACE VARCHAR2(31) 分配空間的表空間
CONTENTS VARCHAR2(9) 指示表是否 TEMPORARY 或 PERMANENT
SEGTYPE VARCHAR2(9) 排序型別的型別：

  • SORT

  • HASH

  • DATA

  • INDEX

  • LOB_DATA

  • LOB_INDEX

SEGFILE# NUMBER 初始範圍的檔案號
SEGBLK# NUMBER 初始範圍的塊號
EXTENTS NUMBER 分配給排序的範圍
BLOCKS NUMBER 分配給排序的塊中的範圍
SEGRFNO# NUMBER 初始範圍的相對檔案號


關於V$TEMP_EXTENT_POOL的官方文件中的解釋

V$TEMP_EXTENT_POOL顯示快取並用於例項的臨時空間的狀態。請注意，臨時空間快取的載入是惰性的，並且例項可以處於休眠狀態。

柱 資料型別 描述
TABLESPACE_NAME VARCHAR2(30) 表空間的名稱
FILE_ID NUMBER 絕對檔案號
EXTENTS_CACHED NUMBER 已快取的範圍數
EXTENTS_USED NUMBER 實際使用的範圍數
BLOCKS_CACHED NUMBER 快取的塊數
BLOCKS_USED NUMBER 使用的塊數
BYTES_CACHED NUMBER 快取的位元組數
BYTES_USED NUMBER 使用的位元組數
RELATIVE_FNO NUMBER 相對檔案號

關於V$TEMPORARY_LOBS官方文件解釋

V$TEMPORARY_LOBS 顯示臨時LOB。

柱 資料型別 描述
SID NUMBER 會話ID
CACHE_LOBS NUMBER 快取臨時LOB的數量
NOCACHE_LOBS NUMBER nocache臨時LOB的數量
ABSTRACT_LOBS NUMBER 抽象LOB的數量

如果書寫過程或者對官方文件理解有什麼錯誤歡迎大家留言。

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

相關文章