1.抓取ash中TEMP_SPACE_ALLOCATED消耗最多的SQL_ID
select sample_time, sql_id, sum(TEMP_SPACE_ALLOCATED/ 1024 / 1024 / 1024) GB
from v$active_session_history a
where sample_time between
to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
to_date('&date2', 'yyyy-mm-dd hh24:mi:ss')
and a.TEMP_SPACE_ALLOCATED is not null
group by sample_time, sql_id
order by 3 desc
2.抓取ash中的pga_allocated消耗的SQL_ID
select a.sample_time,
a.sql_id,
sum(a.pga_allocated / 1024 / 1024 / 1024) GB1
from v$active_session_history a
where pga_allocated is not null
and sample_time between
to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
to_date('&date2', 'yyyy-mm-dd hh24:mi:ss')
group by a.sample_time, a.sql_id
order by 3 desc
3.ASH中剖析各种操作类型的次数，这里以SQL_OPNAME、SQL_OPCODE字段来区分
select SQL_OPCODE,SQL_OPNAME,count(*) from v$active_session_history a
where sample_time between
to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
to_date('&date2', 'yyyy-mm-dd hh24:mi:ss')
group by SQL_OPCODE,SQL_OPNAME;
