Commit 1ff5a4
2025-12-02 12:15:29 feagor: -/-| /dev/null .. oracle/awr и ash/AWR - Время выполнения запроса в разрезе снапшотов.md | |
| @@ 0,0 1,37 @@ | |
| + | ## AWR - Время выполнения запроса в разрезе снапшотов |
| + | |
| + | ```oraclesql |
| + | /* |
| + | declare |
| + | v_num number; |
| + | begin |
| + | v_num := dbms_workload_repository.create_snapshot(); |
| + | dbms_output.put_line(v_num); |
| + | end; |
| + | */ |
| + | --call dbms_workload_repository.add_colored_sql('fx9z8jxxvu5a7') |
| + | select trunc(sn.begin_interval_time,'mi') begin_interval_time, |
| + | st.plan_hash_value phv, |
| + | st.OPTIMIZER_MODE opt_m, |
| + | coalesce(st.sql_profile,sq.SQL_PLAN_BASELINE,sq.SQL_PATCH,sq.SQL_PROFILE) profile, |
| + | sum(st.executions_delta) as execs, |
| + | round(avg(st.elapsed_time_delta/decode(st.executions_delta,0,1,st.executions_delta))/1e6,4)as ela_per_exec_sec, |
| + | round(avg(st.cpu_time_delta/decode(st.executions_delta,0,1,st.executions_delta))/1e6,4)as CPU_per_exec_sec, |
| + | 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 |
| + | 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 |
| + | order by 1 desc; |
| + | ``` |
| + | |
| + | Чтобы пометить запрос для отслеживания: |
| + | ```oraclesql |
| + | call dbms_workload_repository.add_colored_sql('&v_sql_id'); |
| + | ``` |