oracle/awr и ash/AWR - Время выполнения запроса в разрезе снапшотов.md ..
@@ 20,14 20,19 @@
max(round(st.px_servers_execs_delta/decode(st.executions_delta,0,1,st.executions_delta))) as px_per_exec,
round(avg((st.rows_processed_delta/decode(st.executions_delta,0,1,st.executions_delta)))) as rows_per_exec,
round(avg((st.fetches_delta/decode(st.executions_delta,0,1,st.executions_delta)))) as fetches_per_exec
-
from dba_hist_sqlstat st, dba_hist_snapshot sn, (select distinct sq.sql_id,sq.PLAN_HASH_VALUE,sq.SQL_PLAN_BASELINE,sq.SQL_PATCH,sq.SQL_PROFILE from gv$sql sq) sq
+
from dba_hist_sqlstat st,
+
dba_hist_snapshot sn,
+
(select distinct sq.sql_id,sq.PLAN_HASH_VALUE,sq.SQL_PLAN_BASELINE,sq.SQL_PATCH,sq.SQL_PROFILE from gv$sql sq) sq
where st.sql_id like '66m74s6yq786u%'
and st.snap_id = sn.snap_id
and sq.SQL_ID(+) = st.SQL_ID and sq.PLAN_HASH_VALUE(+) = st.PLAN_HASH_VALUE
and st.instance_number = sn.instance_number
and st.executions_delta > 0
and sn.begin_interval_time between sysdate - 10 and sysdate
-
group by trunc(sn.begin_interval_time,'mi') , st.plan_hash_value, coalesce(st.sql_profile,sq.SQL_PLAN_BASELINE,sq.SQL_PATCH,sq.SQL_PROFILE),st.OPTIMIZER_MODE