sess_usage_temp
emcs
64次下载
540次浏览
2019-07-24
4.5

脚本内容

--历史会话查看临时表空间使用情况 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) /

评论

贡献排行榜