通过awr报告，获取最近一个快照内，超过10分钟的sql
- SELECT v.SQL_TEXT,m.* FROM (select distinct snap_id,
- sql_id,
- EXECUTIONS_DELTA,
- trunc(max(ELAPSED_TIME_DELTA)
- OVER(PARTITION BY snap_id, sql_id) / 1000000,
- 0) max_elapsed,
- trunc((max(ELAPSED_TIME_DELTA)
- OVER(PARTITION BY snap_id, sql_id)) /
- (SUM(ELAPSED_TIME_DELTA) OVER(PARTITION BY snap_id)),
- 2) * 100 per_total
- from dba_hist_sqlstat t WHERE T.snap_id IN (SELECT MAX(snap_id) FROM dba_hist_sqlstat) ) M,v$sql v
- where m.sql_id=v.sql_id and m.max_elapsed>=300
---查询最近一小时内最慢的SQL:
select inst_id,executions,
cpu_time / 1e6 as cpu_sec,
elapsed_time / 1e6 as elapsed_sec,
round(elapsed_time / round(executions))/1e6 as "执行时间",
v.PARSE_CALLS,v.DISK_READS,v.BUFFER_GETS,v.sql_text,v.sql_fulltext,v.last_active_time
from gv$sql v
where v.executions > 0
and v.last_active_time > sysdate - 1/48
order by "执行时间" desc
oracle sql性能排查 查询sql执行时间
找出执行最慢的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;
根据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;
了解系统性能、分析系统原因最常用的视图/表
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;
--查询执行时间最长的SQL
select *
from (select sa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.EXECUTIONS "执行次数",
round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
sa.COMMAND_TYPE,
sa.PARSING_USER_ID "用户ID",
u.username "用户名",
sa.HASH_VALUE
from v$sqlarea sa
left join all_users u
on sa.PARSING_USER_ID = u.user_id
where sa.EXECUTIONS > 0
order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
where rownum <= 50;
--查询执行次数最多的SQL
select *
from (select s.SQL_TEXT,
s.EXECUTIONS "执行次数",
s.PARSING_USER_ID "用户名",
rank() over(order by EXECUTIONS desc) EXEC_RANK
from v$sql s
left join all_users u
on u.USER_ID = s.PARSING_USER_ID) t
where exec_rank <= 100;
# 根据占用CPU高的PID 查询执行的SQL
SELECT s.sql_text
FROM v$sql s,
v$session t,
v$process v
WHERE s.sql_id = t.SQL_ID
AND t.PADDR = v.ADDR
AND v.SPID = '18510'; --PID
--查询执行时间最长的SQL
select *
from (select sa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.EXECUTIONS "执行次数",
round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
sa.COMMAND_TYPE,
sa.PARSING_USER_ID "用户ID",
u.username "用户名",
sa.HASH_VALUE
from v$sqlarea sa
left join all_users u
on sa.PARSING_USER_ID = u.user_id
where sa.EXECUTIONS > 0
order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
where rownum <= 50;
--查询执行次数最多的SQL
select *
from (select s.SQL_TEXT,
s.EXECUTIONS "执行次数",
s.PARSING_USER_ID "用户名",
rank() over(order by EXECUTIONS desc) EXEC_RANK
from v$sql s
left join all_users u
on u.USER_ID = s.PARSING_USER_ID) t
where exec_rank <= 100;
# 根据占用CPU高的PID 查询执行的SQL
SELECT s.sql_text
FROM v$sql s,
v$session t,
v$process v
WHERE s.sql_id = t.SQL_ID
AND t.PADDR = v.ADDR
AND v.SPID = '18510'; --PID
