temp表空间满分析oracle

1.查看当前temp使用率，尤其是执行计划是hash join,一定要关注temp

select df.tablespace_name “Tablespace”,
df.total space “Total(MB)”,
nvl(FS.Used Space, 0) “Used(MB)”,
(df.total space - nvl(FS.Used Space, 0)) “Free(MB)”,
round(100 * (1 - (nvl(fs.Used Space, 0) / df.total space)), 2) “Pct.Free(%)”
FROM (SELECT tablespace_name, round(SUM(bytes) / 1048576) Total Space
FROM dba_TEMP_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name,
ROUND(SUM(bytes_used) / 1024 / 1024) Used Space
FROM v$temp_extent_pool
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)

2.查看过去TEMP使用率

select a.SAMPLE_TIME,
sum(temp_space_allocated) / 1024 / 1024 / 1024,
sqL_id
from dba_hist_active_sess_history a
where a.TEMP_SPACE_ALLOCATED is not null
and sample_time between to_date(‘xxxxxxxxxxx,’ yyyymmdd hh24mi) and
to_date(‘xxxxxxxxxx’, yyyymmdd hh24mi)
group by a.SAMPLE_TIME, sql_id
order by2 desc;

3.查看某个时段占用TEMP空间的SQL

select SQL_ID,
sum(trunc(TEMP_SPACE_ALLOCATED / 1024 / 1024 / 1024)) used GB
from gv$active_session_history
where (SAMPLE_TIME between to_date(‘2021112300’, ‘yyyymmdd hh24mi’) and
to_date(‘202111240059,’ yyyymmdd hh24mi))
and sql_id in (‘xxxxxx’, xxxxxxxxxx ',‘xxxxxxxxxx’)
and TEMP_SPACE_ALLOCATED is not null
and event like ‘%temp%’
group by sql_id;

4.查看SQL文本

select sqL id, to_char(sql_fulltext), plan_hash_value
from v$sql
where sqL_id in (select distinct SQL ID
from DBA_HIST_ACTIVE_SESS_HISTORY
where (SAMPLE_TIME between
to_date(‘xxxxxxxx’, ‘yyyymmdd hh24mi’) and
to_date(‘xxxxxxxx’, ‘yyyymmdd hh24mi’))
and event like ‘%temp%’);

5.查看这批SQL分别占用多少TEMP空间

select *
from (select sample_time,
session_id,
session_serial#,
sql_id,
sum(trunc(TEMP_SPACE_ALLOCATED / 1024 / 1024)) usedMB,
count(*)
from v$active_session_history
where TEMP_SPACE_ALLOCATED is not null
and sample_time between to_date(‘xxxxxxxxxxx’, ‘yyyymmdd hh24mi’) and
to_date(‘xxxxxxxxxxx’, ‘yyyymmdd hh24mi’)
group by sample_time, session_id, session_serial#, sqL_id)
where usedMB > 800
order by 1;

select a.SAMPLE_TIME,
sql_id,
round(sum(temp_space_allocated) / 1024 / 1024, 3) sum_MB
from v$active_session_history a
where a.TEMP_SPACE_ALLOCATED is not null
and sample_time between to_date(‘xxxxxxxxx’, ‘yyyymmdd hh24mi’) and
to_date(’ xxxxxxxxxxx’, ‘yyyymmdd hh24mi’)
group by a.SAMPLE_TIME, sqL_id
order by2 desc;

select a.SAMPLE_TIME,
sql_exec_id,
sql_exec_start,
sum(temp_space_allocated) / 1024 / 1024 / 1024,
sql_id
from dba_hist_active_sess_history a
where a.TEMP_SPACE_ALLOCATED is not null
and sample_time between to_date(‘xxxxxxxxxxx’, ‘yyyymmdd hh24mi’) and
to_date(‘xxxxxxxxxxxx’, ‘yyyymmdd hh24mi’)
and sql_id = ‘xxxxxxxx’
group by a.SAMPLE_TIME, sql_exec_id, sql_exec_start,sql_id
order by 1;

–找到SQL对应的对象

select to_char(wm_concat(distinct ‘’‘’ || current_obj# || ‘’‘’)) currentobj
from dba_hist_active_sess_history
where sample_time between to_date(‘xxxxxxxxx’, ‘yyyymmdd hh24mi’) and
to_date(‘xxxxxxxxx’, ‘yyyymmdd hh24mi ‘)
and sql_id = ‘xxxxxxxx’
group by current_obj#select distinct owner, object_name
from dba_objects
where object_id in (’¤t obj’)

例1：如果查询到一个sql持续占用temp表空间且使用率越来越高，需要关注这个sql，后面故障点sql可能是受害者

例2：并发开启的多个进程，没有集中在一个sql