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

```