需要报告Oracle 12c数据库中临时表空间的使用情况

Question

有没有办法生成关于Oracle 12c数据库中临时表空间历史使用情况的报告？SQL语句在此数据库上失败，我们已经将临时表空间的大小增加到200GB；SQL语句不再失败，但开发人员希望看到这样的报告。谢谢你的建议。

Answer 1

如果您安装了Diagnostics包并启用了历史AWR收集，您可以从以下几个地方获得此信息：

1. DBA_HIST_TBSPC_SPACE_USAGE显示历史表空间使用情况统计信息
2. 由于11GR2使用dba_hist_active_sess_history.TEMP_SPACE_ALLOCATED来查看会话/SQL对TEMP的使用

1:的示例代码

with temp_blocksize as 
(
SELECT B.ts#, b.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE 1=1
and B.ts#= C.ts# (+)
and b.name like 'TEMP%'
GROUP BY B.ts#, b.name, C.block_size
)
select * from 
(
select tablespace_id
, name Tablespace_name
, (tablespace_maxsize * block_size ) / 1024 / 1024 tablespace_maxsize_mb
, round((tablespace_size * block_size) / 1024 / 1024 / 1024, 2) tablespace_size_gb
, round((tablespace_usedsize * block_size) / 1024 / 1024/ 1024, 2) tablespace_usedsize_gb
, tablespace_size
, tablespace_usedsize
, round((tablespace_usedsize/tablespace_size)*100, 2) used_pct
, rtime 
, awr.snap_id
from dba_hist_tbspc_space_usage where 1=1
and awr.tablespace_id = blk.ts#
)
where used_pct > = nvl(:used_pct, 0)
and (to_number(to_char(to_date(rtime, 'MM/DD/YYYY HH24:MI:SS'), 'HH24')) between nvl(:begin_hour, 0) and  nvl(:end_hour, 24) 
or to_number(to_char(to_date(rtime, 'MM/DD/YYYY HH24:MI:SS'), 'HH24')) between nvl(:begin_hour2, nvl(:begin_hour, 0)) and  nvl(:end_hour2, nvl(:end_hour, 24)))
order by  rtime desc
;

2的示例代码：

with tmp_usage as
(
select sql_id, sql_plan_hash_value, session_id, session_serial#
, min(user_id) user_id
, min(sample_time) min_sample_time, max(sample_time) max_sample_time
, max(sample_time) - min(sample_time) delta_time
, min(snap_id) min_snap_id, max(snap_id) max_snap_id
, round(max(temp_space_allocated) / (1024 * 1024)) Temp_usage_meg
, round(max(pga_allocated) / (1024 * 1024)) PGA_usage_meg
, sum(decode(event, 'direct path read temp', 1, 0) ) cnt_direct_path_read_temp
, sum(decode(event, 'direct path write temp', 1, 0) ) cnt_direct_path_write_temp
, sum(decode(event, null, 1, 0) ) cnt_ON_CPU
, count(*) - sum(decode(event, 'direct path read temp', 1, 0) ) - sum(decode(event, 'direct path write temp', 1, 0) ) - sum(decode(event, null, 1, 0) ) cnt_other
, count(*) cnt_sample
from dba_hist_active_sess_history ash
where temp_space_allocated is not null
and session_id = nvl(:session_id, session_id)
and session_serial# = nvl(:session_serial#, session_serial#)
and trunc(sample_time, 'MI') between to_date(nvl(:sam_tm_str_MM_DD_YYYY_HH24_MI, to_char(sample_time, 'MM_DD_YYYY_HH24_MI')),'MM_DD_YYYY_HH24_MI') 
and to_date(nvl(:sam_tm_end_MM_DD_YYYY_HH24_MI, to_char(sample_time, 'MM_DD_YYYY_HH24_MI')),'MM_DD_YYYY_HH24_MI')
and decode(:today_only_Y_N,'Y', sample_time, trunc(sysdate) ) >= trunc(sysdate)
and nvl(upper(module),'x') like nvl(upper(:module), nvl(upper(module),'x')) 
and nvl(machine,'x') like nvl(:machine, nvl(machine,'x')) 
and nvl(program,'x') like nvl(:program, nvl(program,'x')) 
and nvl(sql_id,'x') = nvl(:sql_id, nvl(sql_id,'x'))
group by sql_id, sql_plan_hash_value, session_id, session_serial#
order by max(temp_space_allocated) desc
)
select tmp.*
, (select username from dba_users du where du.user_id = tmp.user_id) username
, (select max(DBMS_LOB.SUBSTR(sql_text, 3800)) from dba_hist_sqltext st where st.sql_id = tmp.sql_id) sql_text
from tmp_usage tmp
where 1=1
and rownum <= nvl(:top_n, 10)
order by 
case when :ord_temp = 'TEMP' then Temp_usage_meg 
when :ord_temp = 'SNAP' then min_snap_id 
else  Temp_usage_meg 
end 
desc
;

---

如果您没有Diagnostics Pack许可证，您可以从v$sort_usage获取信息，但您必须定期保存信息才能获得历史报告。