Useful Scripts to finding Query timings from AWR report and Memory

Below query collecting data from AWR report.

set lines 155

col execs for 999,999,999

col avg_etime for 999,999.999

col avg_lio for 999,999,999.9

col begin_interval_time for a30

col node for 99999

break on plan_hash_value on startup_time skip 1

select ss.snap_id, ss.instance_number node, begin_interval_time,

sql_id, plan_hash_value,

nvl(executions_delta,0) execs,

(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000

avg_etime,

(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))

avg_lio

from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

where

ss.snap_id = S.snap_id

and ss.instance_number = S.instance_number

and sql_id='<SQL_ID>'

and executions_delta > 0

order by 1, 2, 3

/

 Below query use to (.sql) file

set echo off

set verify off

set feedback off

set heading on

set timing off

undef sql_id

prompt "SQL_ID PLAN FROM CURSOR MEMORY"

SELECT inst_id,sql_id, plan_hash_value, (SUM(elapsed_time)/power(10,6))/SUM(executions) avg_response_seconds FROM gv$sql  WHERE sql_id ='&&sql_id' and executions > 0 group by sql_id, plan_hash_value,inst_id  order by 4;

prompt "SQL_ID PLAN FROM AWR HISTORY"

SELECT plan_hash_value, (SUM(elapsed_time_total)/power(10,6))/SUM(executions_total) avg_response_seconds

FROM dba_hist_sqlstat WHERE sql_id = '&sql_id' AND executions_total > 0 GROUP BY plan_hash_value order by 2;

prompt "SQL_ID PLAN_HASH MONTHWISE

SELECT distinct to_char(end_interval_time,'YYYY-MM'),plan_hash_value FROM dba_hist_sqlstat A,  dba_hist_snapshot B

WHERE sql_id ='&sql_id'

and A.snap_id=B.snap_id

order by 1 desc;

 

Below query collecting data from from Memory

select inst_id,

       sql_id,

     PLAN_HASH_VALUE,

   executions,

       round(elapsed_time/1000000, 6) elapsed_sec,

       ((elapsed_time/1000000)/executions) elapsed_per_exec,

       to_char(last_active_time, 'yyyy-mm-dd hh24:mi:ss')

from gv$sqlstats

where sql_id = '<SQL_ID>'

/