占用临时表最多的session 临时表空间使用率高

转载

数据小筑 2024-05-26 22:27:30

文章标签 占用临时表最多的session 数据库 python 运维临时表空间 文章分类 架构后端开发

一、Oracle临时表空间作用

在Oracle数据库进行排序、分组汇总、索引等动作时，会产生很多的临时数据，如有一张员工信息表，数据库中是按照记录建立的时间来保存的。如果用户查询是，使用Order BY排序语句指定按员工编号来排序，那么排序后产生的所有记录就是临时数据。通常情况下，Oracle会先将这些临时数据存放到内存的PGA（程序全局区）内。但是这个分区容量是有限的。当这个分区的大小不足以容纳排序后所产生的记录时，数据库系统就会将临时数据存放到临时表空间中。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。临时表空间主要使用在以下几种情况：

1、order by or group by（disc sort占主要部分）；

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

3、distinct操作；

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

5、Analyze操作；

6、有些异常也会引起TEMP暴涨。

二、Oracle临时表空间暴涨原因

三、解决TEMP临时表空间过大

alter database tempfile ‘ 临时文件路径 ’resize 1024M;

此语句会直接修改TEMP表空间的大小，但可能会执行不成功，因为当TEMP使用率为100%或者当前有

会话占用时，TEMP表空间是无法缩小的。使用数据库管理员用户查询当前会话：

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_usagesu where se.saddr = su.session_addr

利用此sql语句查询当前会话，然后kill当前会话：

alter system kill session ‘sid，serial#’

执行此语句后再对TEMP表空间resize空间大小就可以了。

注：此方法只是对TEMP表空间做临时性的缩小，以后还会继续增大。

Temporary tablespace是不能直接drop默认的临时表空间的，不过我们可以通过以下方法达到。

查看目前的Temporary Tablespace

占用临时表最多的session 临时表空间使用率高_运维

占用临时表最多的session 临时表空间使用率高_数据库_02

1、创建中转临时表空间

create temporary tablespace TEMP1 ‘/oradata/qct/temp02.DBF’ size 1000M REUSE AUTOEXTEND ON

NEXT 1M MAXSIZE UNLIMITED；

2、改变缺省临时表空间为刚刚创建的新临时表空间temp1

3、删除原来临时表空间

drop tablespace temp including contents and datafiles;

4、重新创建临时表空间

create temporary tablespace TEMP TEMPFILE’/oradata/qct/temp01.DBF’ SIZE 1000M REUSE

AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

5、重置缺省临时表空间为新建的temp表空间

alter database default temporary tablespace temp;

6、删除中转用临时表空间

drop tablespace temp1 including contents and datafiles;

以上的方法只是暂时释放了临时表空间的磁盘占用空间，是治标但不是治本的方法，真正治本的方法是找出

数据库中消耗资源较大的sql语句，然后对其进行优化处理。

四、监控临时表空间使用情况

Oracle用户查看哪些用户和SQL导致TEMP增长有两个重要视图：v$sort_usagev和$sort_segment。

通过下面语句可查询在sort排序区使用的执行耗时的用户SQL：

select * from (select sess.sid,segtype,blocks,sql_text from v$sort_usagesu,v$sessionsess,v$sqlsql where su.session_addr = sess.saddr and sql.address = sess.sql_address order by blocks desc) where rownum <= 5

占用临时表最多的session 临时表空间使用率高_python_03

或者select su.username,su.extents,tablespace,segtype,sql_text from v$sort_usage,v$sql s where su.sql_id = s.sql_id

占用临时表最多的session 临时表空间使用率高_数据库_04

注：如果原临时表空间无用户使用（ select tablespace_name,current_users,total_blocks,used_blocks,free_blocks,round((free_blocks/total_blocks), 4) * 100 || '%' as freeprc from v$sort_segment）

占用临时表最多的session 临时表空间使用率高_临时表空间_05

通过下面的SQL可以查看哪些用户在使用临时段

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_usagesu where se.saddr = su.session_addr;

占用临时表最多的session 临时表空间使用率高_运维_06

通过下面的SQL可以监控临时表空间使用率

select round((f.BYTES_FREE + f.BYTES_USED) / 1024 / 1024, 2) "total MB",round(((f.BYTES_FREE + f.BYTES_USED) - nvl(p.BYTES_USED, 0)) / 1024 / 1024,2) "Free MB",round(nvl(p.BYTES_USED, 0) / 1024 / 1024, 2) "Used MB",round(nvl(p.BYTES_USED, 0) / 1024 / 1024, 2) /round((f.BYTES_FREE + f.BYTES_USED) / 1024 / 1024, 2) * 100 || '%' "UPRC",d.file_name "fileName" from sys.v_$temp_space_headerf,Dba_Temp_Filesd,SYS.v_$temp_Extent_Pool p where f.TABLESPACE_NAME(+) = d.tablespace_name and f.FILE_ID(+) = d.file_id and p.FILE_ID(+) = d.file_id and d.tablespace_name = 'TEMP';

占用临时表最多的session 临时表空间使用率高_python_07

或者

select A.tablespace_name, total "total MB", used "Used MB", (used / total * 100 || '%') uprc from (select tablespace_name, sum(bytes) / 1024 / 1024 total from dba_temp_files group by tablespace_name) A left outer join (select tablespace_name,sum(BYTES_USED) / 1024 / 1024 used from v_$temp_Extent_Pool group by tablespace_name) B on b.tablespace_name = a.tablespace_name where A.tablespace_name = 'TEMP‘;

占用临时表最多的session 临时表空间使用率高_占用临时表最多的session_08