ORACLE 臨時表空間使用率過高分析
在資料庫的日常學習中,發現公司生產資料庫的預設臨時表空間temp使用情況達到了30G,使用率達到了100%;待調整為32G後,使用率還是為100%,導致磁碟空間使用緊張。根據臨時表空間的主要是對臨時資料進行排序和快取臨時資料等特性,待重啟資料庫後,temp會自動釋放。於是想透過重啟資料庫的方式來緩解這種情況,但是重啟資料庫之後,發現臨時表空間temp的使用率還是100%,一點沒變。雖然執行中應用暫時沒有報什麼錯誤,但是這在一定程度上存在一定的隱患,有待解決該問題。由於臨時表空間主要使用在以下幾種情況:
1、order by or group by (disk sort佔主要部分);
2、索引的建立和重建立;
3、distinct操作;
4、union & intersect & minus
5、Analyze 操作;
6、有些異常也會引起TEMP的暴漲。
Oracle臨時表空間暴漲的現象經過分析可能是以下幾個方面的原因造成的:
1. 沒有為臨時表空間設定上限,而是允許無限增長。但是如果設定了一個上限,最後可能還是會面臨因為空間不夠而出錯的問題,臨時表空間設定太小會影響效能,臨時表空間過大同樣會影響效能,至於需要設定為多大需要仔細的測試。
2.查詢的時候連表查詢中使用的表過多造成的。我們知道在連表查詢的時候,根據查詢的欄位和表的個數會生成一個迪斯卡爾積,這個迪斯卡爾積的大小就是一次查詢需要的臨時空間的大小,如果查詢的欄位過多和資料過大,那麼就會消耗非常大的臨時表空間。
3.對查詢的某些欄位沒有建立索引。Oracle中,如果表沒有索引,那麼會將所有的資料都複製到臨時表空間,而如果有索引的話,一般只是將索引的資料複製到臨時表空間中。
透過查詢相關的資料,發現解決方案有如下幾種:
一、重建臨時表空間temp
Temporary tablespace是不能直接drop預設的臨時表空間的,不過我們可以透過以下方法達到。
檢視目前的Temporary Tablespace
SQL> select name from v$tempfile;
NAME
———————————————————————
D:ORACLEORADATAORCLTEMP01.DBF
SQL> select username,temporary_tablespace from dba_users;
USERNAME
------------------------------ ------------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
1.建立中轉臨時表空間
create temporary tablespace
MAXSIZE UNLIMITED;
2.改變預設臨時表空間 為剛剛建立的新臨時表空間temp1
alter database default
3.刪除原來臨時表空間
drop tablespace temp including contents and datafiles;
4.重新建立臨時表空間
create temporary tablespace TEMP TEMPFILE 'D:ORACLEORADATAORCLtemp01.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT
UNLIMITED;
5.重置預設臨時表空間為新建的temp表空間
alter database default
6.刪除中轉用臨時表空間
drop tablespace temp1 including contents and datafiles;
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;
二、修改引數(這個方案緊適用於8i及8i以下的版本)
SQL>alter tablespace temp increase 1;
SQL>alter tablespace temp increase 0;
三、Kill session
1、 使用如下語句檢視一下誰在用臨時段
SELECT se.username, se.SID, se.serial#, se.sql_address, se.machine, se.program,su.TABLESPACE,su.segtype,
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;
四、使用診斷事件,也是相對有效的一種方法
2、 執行清理操作
SQL>alter session set events 'immediate trace name DROP_SEGMENTS level 4';
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25682487/viewspace-1048662/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle 表空間,臨時表空間使用率查詢Oracle
- ORACLE 臨時表空間使用率過高的原因及解決方案Oracle
- ORACLE臨時表空間使用率過高的原因及解決方法Oracle
- 臨時表空間使用率過高的解決辦法
- oracle的臨時表空間使用率99.9%Oracle
- oracle 臨時表空間Oracle
- oracle臨時表空間Oracle
- 臨時表空間和回滾表空間使用率查詢
- Oracle 臨時表空間概念Oracle
- oracle臨時表空間組Oracle
- oracle的臨時表空間Oracle
- Oracle Temp 臨時表空間Oracle
- oracle11g 查詢臨時表空間的使用率和正在使用臨時表空間的使用者Oracle
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法!Oracle
- undo表空間使用率過高解決
- 【實驗】重建臨時表空間解決臨時表空間過大問題
- oracle臨時表空間相關Oracle
- Oracle TEMP臨時表空間概念Oracle
- Oracle 臨時表空間的概念Oracle
- ORACLE臨時表空間總結Oracle
- oracle 重建臨時表空間 tempfileOracle
- 刪掉Oracle臨時表空間Oracle
- ORACLE臨時表空間的清理Oracle
- oracle臨時表空間過大的原因&&處理Oracle
- 轉:Oracle 臨時表空間過大問題解決Oracle
- Oracle Temp臨時表空間處理Oracle
- ORACLE預設的臨時表空間Oracle
- oracle的臨時表空間temporary tablespaceOracle
- oracle清理和重建臨時表空間Oracle
- Oracle修改預設表空間和預設臨時表空間Oracle
- Oracle基礎 02 臨時表空間 tempOracle
- oracle之臨時表空間的收縮Oracle
- 【UNDO】Oracle undo表空間使用率過高,因為一個查詢Oracle
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- 批量處理時臨時增加回滾表空間臨時表空間檔案
- 臨時表空間過大的解決方法
- 臨時表空間的建立、刪除,設定預設臨時表空間
- mysql臨時表,臨時表空間,ibtmp1表空間暴增原因初探MySql