Home A - Z
Menu
Page Index

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
56c725 feagor 2025-12-02 12:16:21 23
from dba_hist_sqlstat st,
24
dba_hist_snapshot sn,
25
(select distinct sq.sql_id,sq.PLAN_HASH_VALUE,sq.SQL_PLAN_BASELINE,sq.SQL_PATCH,sq.SQL_PROFILE from gv$sql sq) sq
1ff5a4 feagor 2025-12-02 12:15:29 26
where st.sql_id like '66m74s6yq786u%'
27
and st.snap_id = sn.snap_id
28
and sq.SQL_ID(+) = st.SQL_ID and sq.PLAN_HASH_VALUE(+) = st.PLAN_HASH_VALUE
29
and st.instance_number = sn.instance_number
30
and st.executions_delta > 0
31
and sn.begin_interval_time between sysdate - 10 and sysdate
56c725 feagor 2025-12-02 12:16:21 32
group by trunc(sn.begin_interval_time,'mi') ,
33
st.plan_hash_value,
34
coalesce(st.sql_profile,sq.SQL_PLAN_BASELINE,sq.SQL_PATCH,sq.SQL_PROFILE),
35
st.OPTIMIZER_MODE
1ff5a4 feagor 2025-12-02 12:15:29 36
order by 1 desc;
37
```
38
39
Чтобы пометить запрос для отслеживания:
40
```oraclesql
41
call dbms_workload_repository.add_colored_sql('&v_sql_id');
42
```