定位SQL哪一步执行的慢

--识别历史执行计划

select sql_id,
plan_hash_value,
sum(elapsed_time) / 1e6 els,
sum(elapsed_time) / greatest(sum(executions), 1) / 1e3 els_per_exec,
sum(buffer_gets) gets,
sum(buffer_gets) / greatest(sum(executions), 1) get_per_exec,
sum(executions) execs,
sum(rows_processed) rowcnt,
sum(elapsed_time) / greatest(sum(rows_processed), 1) / 1e3 els_per_row,
sum(buffer_gets) / greatest(sum(rows_processed), 1) / 1e3 get_per_row
from v$sqlstats
where sql_id ='&sqlid' --07uy29vnzqzf7
group by sql_id,plan_hash_value order by els_per_exec;


select sql_id,
plan_hash_value,
sum(elapsed_time_delta) / 1e6 els,
sum(elapsed_time_delta) / greatest(sum(executions_delta), 1) / 1e3 els_per_exec,
sum(buffer_gets_delta) gets,
sum(buffer_gets_delta) / greatest(sum(executions_delta), 1) get_per_exec,
sum(executions_delta) execs,
sum(rows_processed_delta) rowcnt,
sum(elapsed_time_delta) / greatest(sum(rows_processed_delta), 1) / 1e3 els_per_row,
sum(buffer_gets_delta) / greatest(sum(rows_processed_delta), 1) / 1e3 get_per_row
from dba_hist_sqlstat
where sql_id = '&sqlid'
group by sql_id, plan_hash_value
order by els_per_exec;

--哪一步跑得慢
select SQL_PLAN_LINE_ID, count(*), SQL_PLAN_HASH_VALUE, sql_id
from gv$active_session_history
where sql_id = '&sqlid'
group by SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, sql_id;

select SQL_PLAN_LINE_ID, count(*), SQL_PLAN_HASH_VALUE, st.sql_id
from dba_hist_active_sess_history h, dba_hist_sqlstat st
where st.sql_id = '&sqlid'
and h.sql_id = st.sql_id
and st.snap_id = h.snap_id
and st.dbid = h.dbid
and st.instance_number = h.instance_number
group by SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, st.sql_id;

select SQL_PLAN_LINE_ID, count(*), SQL_PLAN_HASH_VALUE, sql_id
from dba_hist_active_sess_history
where sql_id = '&sqlid'
group by SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, sql_id;

Select sql_plan_line_id,event,count(*) total from v$active_session_history
Where sql_id ='&sqlid' Group by sql_plan_line_id,event order by
sql_plan_line_id,count(*);