ORACLE 临时表空间管理

时间:2021-12-09 05:40:00

 临时表空间和临时段

  临时表空间用于存放排序、临时表等数据,其信息不需要REDO,因此临时表的DML操作往往比普通表产生的REDO少很多。临时表数据变化不产生REDO,UNDO数据变化产生REDO。临时段不仅仅存在于临时表空间中,也可能存在普通表空间。比如通过CTAS创建一张表,新表的数据放在临时段中,这些临时段在CTAS完成的时候会被转换为PERMENT段。ORACLE7.3版本之前,临时段是在需要时分配,使用完毕后被删除.ORACLE7.3 推出的新算法的核心就是SEP(SORT EXTENT POOL),SEP 负责管理临时段中扩展的结构,存储在共享池内,任何需要使用排序空间的操作,都需要从SEP 中分配空闲的扩展,使用完毕后,不需要释放该空间,只需要在SEP 中将该扩展设置为空闲。
  当数据库实例启动后,SMON 将会删除该实例未释放的临时段,并且对临时表空间进行碎片整理。在这个操作完成前,数据库打开的操作不能完成。因此每次数据库重启后,临时段中的垃圾都会被完全清理。当数据库打开后,第一个进行的硬盘排序操作会在相关的临时表空间内创建临时段,这个临时段也是整个实例唯一的临时段(在新的临时段算法下,同一个表空间内,每个实例只有一个临时段)。临时段中扩展的信息会被记录在SEP 中。硬盘排序操作会在SEP 中查找可用的扩展,在查找前,需要获得SORT EXTENT POOL 闩锁。如果能找到可用的扩展,那么SEP 中已被分配的扩展就会被标注为占用状态;如果找不到可用的扩展,那么系统就会试图从表空间中分配新的空间,而如果这个分配工作因为表空间的空闲空间不足而无法完成,那么就会产生一个ORA-1652 错误。当排序操作完成的时候,会再次获取SORT EXTENT POOL 闩锁,并且将使用的扩展标注为空闲,然后释放SORT EXTENT POOL 闩锁。新的临时表空间管理算法不需要频繁地分配和释放临时段,这大大提高了临时段管理的效率。由于这是一种只分配不释放的算法,因此DBA 经常会看到自己的临时表空间总是处于或者接近100%使用的状态。其实对于7.3 以后的版本而言,临时表空间使用率接近100%是十分正常的,DBA 可以通过V$SORT_USAGE 和V$SORT_SEGMENTS 这两个视图来检查临时段的使用情况。
  在RAC/OPS环境下,临时段的管理算法也是类似的。在RAC 中,由于多个实例会共享一个临时表空间,因此这些实例也能够共享相同的扩展。在RAC环境下,每个实例都拥有独立的SEP,各个实例中的排序操作需要在自己的SEP中分配空间。如果SEP 中无法分配到足够的空间,那么首先会在表空间中分配;而如果表空间也已经分配完毕,这时若其他的实例还有空闲的扩展,那么这个扩展就可以分配给需要的实例使用。这些操作都是不可见的,虽然SERVER 进程不会收到ORA-1652 的错误信息,但是在ALTER LOG 中会有一个ORA-1652 的记录。

临时表空间的使用情况:
SELECT TU.TABLESPACE_NAME AS "TABLESPACE_NAME",
TT.TOTAL - TU.USED AS "FREE(G)",
TT.TOTAL AS "TOTAL(G)",
ROUND(NVL(TU.USED, 0) / TT.TOTAL * 100, 3) AS "USED(%)",
ROUND(NVL(TT.TOTAL - TU.USED, 0) * 100 / TT.TOTAL, 3) AS "FREE(%)"
FROM (SELECT TABLESPACE_NAME,
SUM(BYTES_USED) / 1024 / 1024 / 1024 USED
FROM GV_$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) TU ,
(SELECT TABLESPACE_NAME,
SUM(BYTES) / 1024 / 1024 / 1024 AS TOTAL
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) TT
WHERE TU.TABLESPACE_NAME = TT.TABLESPACE_NAME;

查询会话使用临时表空间
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;
或者:

SELECT B.TABLESPACE,
    B.SEGFILE#,
    B.SEGBLK#,
    B.BLOCKS,
    B.BLOCKS * 32 / 1024 / 1024,
    A.SID,
    A.SERIAL#,
    A.USERNAME,
    A.OSUSER,
    A.STATUS,
    C.SQL_TEXT,
    B.CONTENTS
 FROM V$SESSION A, V$SORT_USAGE B, V$SQL C
WHERE A.SADDR = B.SESSION_ADDR
  AND A.SQL_ADDRESS = C.ADDRESS(+)
  and status = 'ACTIVE'
ORDER BY B.BLOCKS DESC

查看临时表空间的空闲情况
SELECT TABLESPACE_NAME,FILE_ID,BYTES_USED/1024/1024,BYTES_FREE/1024/1024 FROM V$TEMP_SPACE_HEADER;

数据库中消耗资源比较大的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;

常见占用临时表空间的几种情况
1:order by or group by (disc sort占主要部分);
2:索引的创建和重创建;
3:distinct操作;
4:union & intersect & minus sort-merge joins;
5:analyze 操作;
6:有些异常也会引起TEMP的暴涨。

临时表空间使用率过高,日常维护
确定数据库schema的默认表空间
select username,temporary_tablespace from dba_users where account_status ='OPEN';

确定数据库的默认临时表空间

select * from database_properties where property_name ='DEFAULT_TEMP_TABLESPACE';

1.创建中转临时表空间
create temporary tablespace temp2 tempfile '/data/app/oracle/oradata/prod/temp02.dbf' size 512M autoextend on next 1M maxsize unlimited;

2.改变缺省临时表空间为中转临时表空间TEMP2
alter database default temporary tablespace TEMP2;
验证用户的临时表空间为TEMP2
select username,temporary_tablespace from dba_users where account_status ='OPEN';

3.删除原临时表空间
drop tablespace temp including contens and datafiles;

4.重建临时表空间
create temporary tablespace temp tempfile '/data/app/oracle/oradata/prod/temp01.dbf' size 512M autoextend on next 1M maxsize unlimited;

5.重置缺省临时表空间为新建的TEMP表空间
alter database default temporary tablespace TEMP1;

验证用户的临时表空间为TEMP
select username,temporary_tablespace from dba_users where account_status ='OPEN';