## 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
```