oracle sql性能排查 查询sql执行时间

1. 找出执行最慢的sql

--根据平均耗时大小排序
SELECT a.SQL_TEXT,
       a.SQL_ID,
       a.EXECUTIONS "总执行次数",
       nvl(a.ELAPSED_TIME, 0) / 1000 / 1000 "总耗时(秒)",
       (nvl(a.ELAPSED_TIME, 0) /
       nvl(decode(a.EXECUTIONS, 0, 1, a.EXECUTIONS), 1)) / 1000 / 1000 "平均耗时（秒）",
       a.PARSE_CALLS "硬解析次数",
       a.DISK_READS "物理读次数",
       a.BUFFER_GETS "读缓存区次数",
       a.FIRST_LOAD_TIME "sql开始执行时间"
  FROM v$SQL a
 WHERE a.first_load_time like '2020-09-24%'
 order by (nvl(a.ELAPSED_TIME, 0) /
          nvl(decode(a.EXECUTIONS, 0, 1, a.EXECUTIONS), 1)) / 1000 / 1000 desc;

2. 根据sql开始执行时间排序

--根据执行时间排序
SELECT a.SQL_TEXT,
       a.SQL_ID,
       a.EXECUTIONS "总执行次数",
       nvl(a.ELAPSED_TIME, 0) / 1000 / 1000 "总耗时(秒)",
       (nvl(a.ELAPSED_TIME, 0) /
       nvl(decode(a.EXECUTIONS, 0, 1, a.EXECUTIONS), 1)) / 1000 / 1000 "平均耗时（秒）",
       a.PARSE_CALLS "硬解析次数",
       a.DISK_READS "物理读次数",
       a.BUFFER_GETS "读缓存区次数",
       a.FIRST_LOAD_TIME "sql开始执行时间"
  FROM v$SQL a
 WHERE a.first_load_time like '2020-09-24%'
 order by FIRST_LOAD_TIME desc;

3. 了解系统性能、分析系统原因最常用的视图/表

select * from v$session t;
select * from v$process t;
select * from user_objects order by last_ddl_time desc;
select t.sql_text, t.first_load_time, t.SQL_ID
  from v$sqlarea t
 where t.first_load_time like '2020-09-24%'
 order by t.first_load_time desc;