Oracle: top 10 queries in a week.

1. get the sql_id, elapsed_time etc from this script.
with viewd as (

select /*+ materialize */ t.*,round(t.BUFFER_GETS /decode(t.exec,0,1,t.exec)) gets_per_exec,round(t.Ela/decode(t.exec,0,1,t.exec)) Ela_sec_per_exec

from (

select sql_id,sum(BUFFER_GETS_DELTA) BUFFER_GETS,

sum(st.DISK_READS_DELTA) DISK_READS,

sum(st.EXECUTIONS_DELTA) exec,

round(sum(st.ELAPSED_TIME_DELTA)/1000000)  Ela

from dba_hist_sqlstat st,dba_hist_snapshot sn

where st.snap_id=sn.snap_id

and st.snap_id=sn.snap_id

and  trunc(BEGIN_INTERVAL_TIME)>=trunc(sysdate-7)

and  trunc(BEGIN_INTERVAL_TIME)<=trunc(sysdate)

and EXTRACT(HOUR FROM Sn.END_INTERVAL_TIME) between 9 and 16

and to_NUMBER(TO_CHAR(END_INTERVAL_TIME,'D')) NOT IN (1,7)

group by sql_id

order by sum(st.ELAPSED_TIME_DELTA) desc

  ) t

)

select * from

(select viewd.* ,substr(sqlt.sql_text,1,90) sql_text

from viewd left join dba_hist_sqltext sqlt

on viewd.sql_id=sqlt.sql_id

where upper(sqlt.sql_text) not like 'BEGIN%' and upper(sqlt.sql_text) not like 'DECLARE%'

order by viewd.ela desc)

where rownum <=10;

2.
After you get the sql_id for the top 10 queries, then you can use the following script to get the full text of the queries.

set long 10000
select sql_id,LOADS,FIRST_LOAD_TIME,DISK_READS,BUFFER_GETS,EXECUTIONS,
round(buffer_gets/decode(executions,0,1,executions)) "gets/exec",SQL_FULLTEXT
from v$sql
where sql_id='&sql_id'
;

undefine sql_id