适用于:
Oracle Database - Enterprise Edition - 版本 11.2.0.1 和更高版本
本文档所含信息适用于所有平台
目标
提供一些诊断脚本,用于从 ASH 中找到最消耗 PGA 或者临时段的 sql_id。
这有助于诊断 ORA-1652 错误。
解决方案
最消耗 PGA 的 sql_id:
select *
from (select instance_number, sql_id, max(pga_sum_mb) pga_max
from (select instance_number, sample_time, sql_id, round(sum(nvl(pga_allocated, 0))/1024/1024) pga_sum_mb
from dba_hist_active_sess_history
where sample_time between to_timestamp('&begin_timestamp', 'yyyy/mm/dd hh24:mi') and to_timestamp('&end_timestamp', 'yyyy/mm/dd hh24:mi')
group by instance_number, sample_time, sql_id)
group by instance_number, sql_id
order by pga_max desc)
where rownum <= 10;
最消耗临时段的 sql_id:
select *
from (select instance_number, sql_id, max(temp_sum_mb) temp_max
from (select instance_number, sample_time, sql_id, round(sum(nvl(temp_space_allocated, 0))/1024/1024) temp_sum_mb
from dba_hist_active_sess_history
where sample_time between to_timestamp('&begin_timestamp', 'yyyy/mm/dd hh24:mi') and to_timestamp('&end_timestamp', 'yyyy/mm/dd hh24:mi')
group by instance_number, sample_time, sql_id)
group by instance_number, sql_id
order by temp_max desc)
where rownum <= 10;