Home A - Z
Menu

Blame

1ff5a4 feagor 2025-12-02 12:15:29
1 
## AWR - Время выполнения запроса в разрезе снапшотов
2
3
```oraclesql
4
/*
5
declare
6
v_num number;
7
begin
8
v_num := dbms_workload_repository.create_snapshot();
9
dbms_output.put_line(v_num);
10
end;
11
*/
12
--call dbms_workload_repository.add_colored_sql('fx9z8jxxvu5a7')
13
select trunc(sn.begin_interval_time,'mi') begin_interval_time,
14
st.plan_hash_value phv,
15
st.OPTIMIZER_MODE opt_m,
16
coalesce(st.sql_profile,sq.SQL_PLAN_BASELINE,sq.SQL_PATCH,sq.SQL_PROFILE) profile,
17
sum(st.executions_delta) as execs,
18
round(avg(st.elapsed_time_delta/decode(st.executions_delta,0,1,st.executions_delta))/1e6,4)as ela_per_exec_sec,
19
round(avg(st.cpu_time_delta/decode(st.executions_delta,0,1,st.executions_delta))/1e6,4)as CPU_per_exec_sec,
20
max(round(st.px_servers_execs_delta/decode(st.executions_delta,0,1,st.executions_delta))) as px_per_exec,
21
round(avg((st.rows_processed_delta/decode(st.executions_delta,0,1,st.executions_delta)))) as rows_per_exec,
22
round(avg((st.fetches_delta/decode(st.executions_delta,0,1,st.executions_delta)))) as fetches_per_exec
23
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
24
where st.sql_id like '66m74s6yq786u%'
25
and st.snap_id = sn.snap_id
26
and sq.SQL_ID(+) = st.SQL_ID and sq.PLAN_HASH_VALUE(+) = st.PLAN_HASH_VALUE
27
and st.instance_number = sn.instance_number
28
and st.executions_delta > 0
29
and sn.begin_interval_time between sysdate - 10 and sysdate
30
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
31
order by 1 desc;
32
```
33
34
Чтобы пометить запрос для отслеживания:
35
```oraclesql
36
call dbms_workload_repository.add_colored_sql('&v_sql_id');
37
```