オラクルで Slow Query を調べる

oracle-slowquery.sql

-- sqlplus output format
SET PAGES 1000
SET LINES 8192
SET LONG  100000

-- @see: http://www.shift-the-oracle.com/view/dynamic-performance-view/sqlstats.html
-- each time value is as msec
SELECT * FROM (
  SELECT
    ROUND(elapsed_time/executions/1000, 3) AS elapsed_time_av,
    ROUND(cpu_time/executions/1000, 3) AS cpu_time_av,
    executions,
    elapsed_time,
    cpu_time,
    sql_fulltext
  FROM v$sqlstats 
  WHERE executions > 0
  ORDER BY cpu_time_av DESC
)
WHERE rownum <= 50;