[ Oracle DBA - Performance Tuning ] - Scripts to get SQL Performance Details

 

• Get SQL Statistics from V$SQLSTATS
  SET LINES 200
COLUMN sql_text_40 FORMAT A42
SELECT
      sql_id,
      SUBSTR(sql_text,1,40) sql_text_40,
      executions,
      TO_CHAR(last_active_time,'DD-MON-YYYY HH24:MI:SS') last_active_time,
      plan_hash_value,
      ROUND((elapsed_time / executions) / 1000000, 3) elapsed_sec_per_exec,
      ROUND((cpu_time / executions) / 1000000, 3) cpu_sec_per_exec
 FROM v$sqlstats
WHERE executions > 0
  AND sql_id = '&sql_id'
ORDER BY ROUND((elapsed_time / executions) / 1000000, 3) DESC;

• Get SQL Statistics from DBA_HIST_SQLSTAT table
  SET LINES 200
col begin_interval_time for A30
col end_interval_time for A30
SELECT
      sq.snap_id,
      sn.begin_interval_time, sn.end_interval_time,
      sql_id,
      executions_delta,
      plan_hash_value,
      ROUND((elapsed_time_delta / executions_delta) / 1000000, 3) elapsed_per_exec,
      ROUND((cpu_time_delta / executions_delta) / 1000000, 3) cpu_per_exec
 FROM DBA_HIST_SQLSTAT sq, dba_hist_snapshot sn
WHERE executions_delta > 0
  AND sql_id = '&sql_id'
  AND sq.snap_id = sn.snap_id
ORDER BY ROUND((elapsed_time_delta / executions_delta) / 1000000, 3) DESC;

Watch Video Format: https://www.youtube.com/shorts/705ujWJMQpg