脚本内容
--历史会话查看临时表空间使用情况
select * from (
select instance_number as inst_id,
sql_id,
to_char(sql_exec_start,' dd.mm.yyyy hh24:mi:ss') as SQL_EXEC_START,
max(sample_time) - SQL_EXEC_START as duration,
sql_exec_id,
sql_plan_hash_value,
module,
action,
round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
round(max(pga_allocated) / 1024 / 1024 / 1024, 3) as max_pga_gb,
max(px_used) as max_px_used
from (select instance_number, sql_id, sql_exec_start, sql_exec_id, sql_plan_hash_value, module, action, sample_id,
sum(temp_space_allocated) as temp_space_allocated,
sum(pga_allocated) as pga_allocated,
count(distinct session_serial#) - 1 as px_used,
sample_time
from dba_hist_active_sess_history
where sample_time >sysdate-&min/(24*60)
and sql_exec_id > 0
group by instance_number, sql_exec_start, sql_id, sql_exec_id, sql_plan_hash_value, module, action, sample_id, sample_time
having sum(temp_space_allocated) is not null)
group by instance_number, sql_id, SQL_EXEC_START, sql_exec_id, sql_plan_hash_value, module, action
having max(temp_space_allocated) / 1024 / 1024 / 1024 > 2 -- GB
order by 9 desc
) where rownum <= nvl('&2',10)
/
评论
贡献排行榜