ORACLE 暫存資料表空間使用率過高的原因及解決方案

 

      在資料庫的日常學習中，發現公司生產資料庫的預設暫存資料表空間temp使用方式達到了30G，使用率達到了100%； 待調整為32G後，使用率還是為100%，導致磁碟空間使用緊張。根據暫存資料表空間的主要是對臨時資料進行排序和緩衝臨時資料等特性，待重啟資料庫後， temp會自動釋放。於是想通過重啟資料庫的方式來緩解這種情況，但是重啟資料庫之後，發現暫存資料表空間temp的使用率還是100%，一點沒變。雖然運行 中應用暫時沒有報什麼錯誤，但是這在一定程度上存在一定的隱患，有待解決該問題。由於暫存資料表空間主要使用在以下幾種情況：

1、order by or group by (disc sort佔主要部分)；

2、索引的建立和重建立；

3、distinct操作；

4、union & intersect & minus sort-merge joins；

5、Analyze 操作；

6、有些異常也會引起TEMP的暴漲。

Oracle暫存資料表空間暴漲的現象經過分析可能是以下幾個方面的原因造成的：

1. 沒有為暫存資料表空間設定上限，而是允許無限增長。但是如果設定了一個上限，最後可能還是會面臨因為空白間不夠而出錯的問題，暫存資料表空間設定太小會影響效能，暫存資料表空間過大同樣會影響效能，至於需要設定為多大需要仔細的測試。

2.查詢的時候連表查詢中使用的表過多造成的。我們知道在連表查詢的時候，根據查詢的欄位和表的個數會產生一個迪斯卡爾積，這個迪斯卡爾積的大小就是一次查詢需要的臨時空間的大小，如果查詢的欄位過多和資料過大，那麼就會消耗非常大的暫存資料表空間。

3.對查詢的某些欄位沒有建立索引。Oracle中，如果表沒有索引，那麼會將所有的資料都複製到暫存資料表空間，而如果有索引的話，一般只是將索引的資料複製到暫存資料表空間中。

針對以上的分析，對查詢的語句和索引進行了最佳化，情況得到緩解，但是需要進一步測試。

總結：

1.SQL語句是會影響到磁碟的消耗的，不當的語句會造成磁碟暴漲。

2.對查詢語句需要仔細的規劃，不要想當然的去定義一個查詢語句，特別是在可以提供使用者自訂查詢的軟體中。

3.仔細規劃表索引。如果暫存資料表空間是temporary的，空間不會釋放，只是在sort結束後被標記為free的，如果是 permanent的，由SMON負責在sort結束後釋放，都不用去手工釋放的。查看有哪些使用者和SQL導致TEMP增長的兩個重要視圖：v$ sort_usage和v$sort_segment。

通過查詢相關的資料，發現解決方案有如下幾種：

一、重建暫存資料表空間temp

Temporary tablespace是不能直接drop預設的暫存資料表空間的，不過我們可以通過以下方法達到。

查看目前的Temporary Tablespace

SQL> select name from v$tempfile;

NAME

———————————————————————

D:\ORACLE\ORADATA\ORCL\TEMP01.DBF

SQL> select username,temporary_tablespace from dba_users;

USERNAME TEMPORARY_TABLESPACE

------------------------------ ------------------------------

MGMT_VIEW TEMP

SYS TEMP

SYSTEM TEMP

DBSNMP TEMP

SYSMAN TEMP

1.建立中轉暫存資料表空間

create temporary tablespace TEMP1 TEMPFILE 'D:\ORACLE\ORADATA\ORCL\temp02.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 1M

MAXSIZE UNLIMITED;

2.改變預設暫存資料表空間 為剛剛建立的新暫存資料表空間temp1

alter database default temporary tablespace temp1;

3.刪除原來暫存資料表空間

drop tablespace temp including contents and datafiles;

4.重新建立暫存資料表空間

create temporary tablespace TEMP TEMPFILE 'D:\ORACLE\ORADATA\ORCL\temp01.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE

UNLIMITED;

5.重設預設暫存資料表空間為建立的temp資料表空間

alter database default temporary tablespace temp;

6.刪除中轉用暫存資料表空間

drop tablespace temp1 including contents and datafiles;

以上的方法只是暫時釋放了暫存資料表空間的磁碟佔用空間，是治標但不是治本的方法，真正的治本的方法是找出資料庫中消耗資源比較大的sql語句，然後對其進行最佳化處理。下面是查詢在sort排序區使用的執行耗時的SQL：

Select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as Space,tablespace,segtype,sql_text

from v$sort_usage su,v$parameter p,v$session se,v$sql s

where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid;

或是：

Select su.username,su.Extents,tablespace,segtype,sql_text

from v$sort_usage su,v$sql s

Where su.SQL_ID = s.SQL_ID;

註：如果原暫存資料表空間無使用者使用(select tablespace_name,current_users,total_blocks,used_blocks,free_blocks,free_blocks/total_blocks from v$sort_segment;),如果是檔案系統可以看看檔案的時間戳記。

我們可以刪除該資料表空間：如果原暫存資料表空間還有使用者在使用，你是刪除不了這個資料表空間的。在一次生產環境的暫存資料表空間切換中， 原暫存資料表空間始終有使用者在上面，即使我關閉了前景程式，也還是有使用者，新的暫存資料表空間已經沒有使用者在使用了。我估計使用者進程已經死在原暫存資料表空間了，後來 只有重新啟動資料庫才能把原來舊的暫存資料表空間給刪除。

二、修改參數（這個方案緊適用於8i及8i以下的版本）

修改一下TEMP資料表空間的storage參數，讓Smon進程觀注一下臨時段，從而達到清理和TEMP資料表空間的目的。

SQL>alter tablespace temp increase 1;

SQL>alter tablespace temp increase 0;

三、Kill session

1、 使用如下語句a查看一下認誰在用臨時段

SELECT se.username, se.SID, se.serial#, se.sql_address, se.machine, se.program, su.TABLESPACE,su.segtype, su.CONTENTS from

v$session se, v$sort_usage su WHERE se.saddr = su.session_addr

2、kill正在使用臨時段的進程

SQL>Alter system kill session 'sid,serial#';

3、把TEMP資料表空間回縮一下

SQL>Alter tablespace TEMP coalesce;

註：這處方法只能針對字典管理資料表空間(Dictionary Managed Tablespace)。於本地管理資料表空間（LMT:Local Managed Tablespace），不需要整理的。9i以後只能建立本地管理的資料表空間。

CREATE TABLESPACE TEST DATAFILE 'D:\TEST01.dbf' SIZE 5M EXTENT MANAGEMENT DICTIONARY

CREATE TABLESPACE TEST DATAFILE 'D:\TEST01.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL;

四、使用診斷事件，也是相對有效一種方法

1、查詢事件代碼

SQL>select ts#, name from sys.ts$ ;

TS# NAME

---------- ------------------------------

0 SYSTEM

1 UNDOTBS1

2 SYSAUX

3 TEMP

4 USERS

5 UNDOTBS2

2、 執行清理操作

SQL>alter session set events 'immediate trace name DROP_SEGMENTS level 4';

說明：temp資料表空間的TS# 為 3, So TS#+ 1= 4。

oracle暫存資料表空間過大的原因

2009-05-12 11:22

Oracle 暫存資料表空間主要是用來做查詢和存放一些緩衝的資料的，磁碟消耗的一個主要原因是需要對查詢的結果進行排序，如果沒有猜錯的話，在磁碟空間的（記憶體）的分配 上，Oracle使用的是貪心演算法，如果上次磁碟空間消耗達到1GB，那麼暫存資料表空間就是1GB，如果還有增長，那麼依此類推，暫存資料表空間始終保持在一個 最大的上限。Oracle暫存資料表空間暴漲的現象經過分析可能是以下幾個方面的原因造成的。

1. 沒有為暫存資料表空間設定上限，而是允許無限增長。但是如果設定了一個上限，最後可能還是會面臨因為空白間不夠而出錯的問題，暫存資料表空間設定太小會影響效能，暫存資料表空間過大同樣會影響效能，至於需要設定為多大需要仔細的測試。

2.查詢的時候連表查詢中使用的表過多造成的。我們知道在連表查詢的時候，根據查詢的欄位和表的個數會產生一個迪斯卡爾積，這個迪斯卡爾積的大小就是一次 查詢需要的臨時空間的大小，如果查詢的欄位過多和資料過大，那麼就會消耗非常大的暫存資料表空間。

3.對查詢的某些欄位沒有建立索引。Oracle中，如果表沒有索引，那麼會將所有的資料都複製到暫存資料表空間，而如果有索引的話，一般只是將索引的資料複製到暫存資料表空間中。

針對以上的分析，對查詢的語句和索引進行了最佳化，情況得到緩解，但是需要進一步測試。

       總結：

1.SQL語句是會影響到磁碟的消耗的，不當的語句會造成磁碟暴漲。

2.對查詢語句需要仔細的規劃，不要想當然的去定義一個查詢語句，特別是在可以提供使用者自訂查詢的軟體中。

3.仔細規劃表索引。

如果暫存資料表空間是temporary的，空間不會釋放，只是在sort結束後被標記為free的，如果是permanent的，由SMON負責在sort結束後釋放，都不用去手工釋放的。查看有哪些使用者和SQL導致TEMP增長的兩個重要視圖：

v$sort_usage和v$sort_segment

對於非LMT管理方式的TEMP資料表空間，最簡單的方法是Metalink給出的一個方法：

修改一下TEMP資料表空間的storage參數，讓Smon進程觀注一下臨時段，從而達到清理和TEMP資料表空間的目的。

SQL>alter tablespace temp default storage(pctincrease 1);

SQL>alter tablespace temp default storage(pctincrease 0);

而對於LMT管理方式的TEMP資料表空間，需要重建立立一個新的暫存資料表空間，將所有使用者的預設暫存資料表空間指定到新的資料表空間上，然後offline舊的暫存資料表空間，並drop掉。具體步驟如下：

首先查詢使用者的預設暫存資料表空間:

[oracle@jumper oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Apr 12 11:11:43 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning option

JServer Release 9.2.0.4.0 - Production

SQL> select username,temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE

------------------------------ ------------------------------

SYS                                 TEMP2

SYSTEM                         TEMP2

OUTLN                           TEMP2

EYGLE                            TEMP2

CSMIG                            TEMP2

TEST                               TEMP2

REPADMIN                     TEMP2

......

13 rows selected.

SQL> select name from v$tempfile;

NAME

---------------------------------------------------------------------

/opt/oracle/oradata/conner/temp02.dbf

/opt/oracle/oradata/conner/temp03.dbf

重建新的暫存資料表空間並進行切換:

SQL> create temporary tablespace temp tempfile '/opt/oracle/oradata/conner/temp1.dbf' size 10M;

Tablespace created.

SQL> alter tablespace temp add tempfile '/opt/oracle/oradata/conner/temp2.dbf' size 20M;

Tablespace altered.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> select username,temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE

------------------------------ ------------------------------

SYS                                 TEMP

SYSTEM                         TEMP

OUTLN                           TEMP

EYGLE                            TEMP

CSMIG                           TEMP

TEST                               TEMP

REPADMIN                     TEMP

.......

13 rows selected.

如果原暫存資料表空間無使用者使用(select tablespace_name,current_users,total_blocks,used_blocks,free_blocks,free_blocks/total_blocks from v$sort_segment;),如果是檔案系統可以看看檔案的時間戳記。

我們可以刪除該資料表空間:(如 果原暫存資料表空間還有使用者在用，你是刪除不了這個資料表空間的。在一次生產環境的暫存資料表空間切換中，原暫存資料表空間始終有使用者在上面，即使我關閉了前景程式，也還 是有使用者，新的暫存資料表空間已經沒有使用者在使用了。我估計使用者進程已經死在原暫存資料表空間了。後來只有重新啟動資料庫才能把原來舊的暫存資料表空間給刪除。）

SQL> drop tablespace temp2;

Tablespace dropped.

SQL>

SQL> select name from v$tempfile;

NAME

---------------------------------------------------------------

/opt/oracle/oradata/conner/temp1.dbf

/opt/oracle/oradata/conner/temp2.dbf

SQL> select file_name,tablespace_name,bytes/1024/1024 MB,autoextensible

2 from dba_temp_files

3 /

FILE_NAME                              TABLESPACE_NAME              MB AUTOEXTENSIBLE

-------------------------------------- -------------------- ---------- --------------

/opt/oracle/oradata/conner/temp2.dbf   TEMP                         20 NO

/opt/oracle/oradata/conner/temp1.dbf   TEMP                         10 NO

drop tablespace temp including contents and datafiles; --將資料表空間的內容和資料檔案一起刪除。

下面是查詢在sort排序區使用的執行耗時的SQL:

Select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as    Space,tablespace,segtype,sql_text

from v$sort_usage su,v$parameter p,v$session se,v$sql s

where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr

order by se.username,se.sid

或是：

Select su.username,su.Extents,tablespace,segtype,sql_text

from v$sort_usage su,v$sql s

Where su.SQL_ID = s.SQL_ID