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 = ''
    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;

    

Below query collecting data from from Memory

SELECT 
    inst_id,
    sql_id,
    plan_hash_value,
    executions,
    ROUND(elapsed_time / 1000000, 6) AS elapsed_sec,
    (elapsed_time / 1000000) / executions AS elapsed_per_exec,
    TO_CHAR(last_active_time, 'yyyy-mm-dd hh24:mi:ss') AS last_active_time
FROM 
    gv$sqlstats
WHERE 
    sql_id = 'SQL_ID'
/