Commit 59d057
2025-12-02 12:14:04 feagor: -/-| /dev/null .. oracle/awr и ash/AWR - Top запросов по elapsed time.md | |
| @@ 0,0 1,70 @@ | |
| + | ## AWR - Top запросов по elapsed time |
| + | |
| + | ```oraclesql |
| + | with |
| + | snaps as ( |
| + | select dbid,snap_id |
| + | ,begin_interval_time as beg_time |
| + | ,end_interval_time as end_time |
| + | from dba_hist_snapshot sn |
| + | where dbid=975372630--(select distinct dbid from dba_hist_database_instance i where i.db_name='&_awr_db_name') |
| + | and end_interval_time between to_date('&_awr_beg_time','dd.mm.yyyy hh24:mi:ss') and to_date('&_awr_end_time','dd.mm.yyyy hh24:mi:ss') |
| + | ) |
| + | ,sql_ordered as ( |
| + | select |
| + | dbid as dbid |
| + | ,sql_id as sql_id |
| + | ,max(module) as module |
| + | ,dense_rank()over(order by sum(elapsed_time_delta) desc) as drnk_ela |
| + | ,count(distinct nullif(plan_hash_value,0)) as plans |
| + | ,count(*) as snaps |
| + | ,max(snap_id) as last_snap |
| + | ,count(distinct dbid) as dbids |
| + | ,sum(executions_delta) as execs |
| + | ,sum(disk_reads_delta) as disk_reads |
| + | ,sum(buffer_gets_delta) as buf_gets |
| + | ,sum(rows_processed_delta) as rows_pr |
| + | ,sum(fetches_delta) as fetches |
| + | ,sum(elapsed_time_delta/1e6) as ela_time_secs |
| + | ,sum(cpu_time_delta/1e6) as cpu_time_secs |
| + | ,sum(iowait_delta/1e6) as io_time_secs |
| + | ,sum(apwait_delta/1e6) as app_time_secs |
| + | ,round(sum(elapsed_time_delta/1e6)/nullif(sum(executions_delta),0),4) as elaexe |
| + | ,round(sum(buffer_gets_delta)/nullif(sum(executions_delta),0)) as bufgets_per_exe |
| + | from |
| + | dba_hist_sqlstat st |
| + | natural join snaps |
| + | where module not in ('PL/SQL Developer') |
| + | --and module = 'csc.exe' |
| + | group by dbid,sql_id |
| + | order by ela_time_secs desc |
| + | ) |
| + | ,topsql as ( |
| + | select/*+ no_merge */ so.* |
| + | from sql_ordered so |
| + | where drnk_ela<=&_awr_top_n |
| + | ) |
| + | select |
| + | sql_id |
| + | ,module |
| + | ,execs |
| + | ,fetches |
| + | ,drnk_ela |
| + | ,ela_time_secs |
| + | ,cpu_time_secs |
| + | ,io_time_secs |
| + | ,app_time_secs |
| + | ,elaexe |
| + | ,(select to_char(substr(sql_text,1,800)) from dba_hist_sqltext st where st.sql_id = t.sql_id and st.dbid = t.dbid) as stext200 |
| + | ,(select sql_text from dba_hist_sqltext st where st.sql_id = t.sql_id and st.dbid = t.dbid) textfull |
| + | ,plans |
| + | ,snaps |
| + | ,last_snap |
| + | ,dbids |
| + | ,disk_reads |
| + | ,buf_gets |
| + | ,rows_pr |
| + | ,bufgets_per_exe |
| + | from topsql t |
| + | where rownum>0 |
| + | ``` |