This content has been marked as final. Show 6 replies
or you can use
SET TIMING ON; your Query SET TIMING OFF;
Can you please provide me any script via which i can find out queries based on elapsed time from last 3 Months using dictionary tables.
Open the link there's query inside it .
Use this query :
select * from (select module,sql_id,round(sum(CPU_TIME_DELTA)/1000000) "CPUTIME(S)",round(sum(ELAPSED_TIME_DELTA)/1000000) "E
LPSED(S)",round(sum(EXECUTIONS_DELTA)) "NUMOFEXEC",round(round(sum(ELAPSED_TIME_DELTA))/1000000)/(round(sum(EXECUTIONS_DELTA))) "Per Execution",sum(ROWS_PROCESSED_DELTA) "NUMOFROW",sum(DISK_READS_DELTA)/1024/1024 "DISK
READ(MB)",sum(BUFFER_GETS_DELTA)/1024/1024 "BUFFERREAD(MB)" from dba_hist_sqlstat where (snap_id between (select min(snap_id)
from dba_hist_snapshot where trunc(begin_interval_time)=trunc(sysdate-92 )) and (select max(snap_id) from dba_hist_snapshot
where trunc(begin_interval_time)=trunc(sysdate ))) and EXECUTIONS_DELTA>10 group by module,sql_id order by "NUMOFEXEC" desc
) where rownum < 51;
select sql_id,sql_text from dba_hist_sqltext where sql_id='45qc5x8gsjf5q';