Oracle慢SQL定位
- 统计慢查询耗时
select *
from (select sa.SQL_TEXT "执行 SQL",
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
- 查看历史SQL的实际执行计划
-- 1.利用关键常量,模糊查询目标语句的hashCode
select * from v$sql result where result.sql_text like '%ZL0204_03%'
-- 2.根据hashCode查询执行计划
select * from table(dbms_xplan.display_cursor('6d3z1k760s7qp', 0));
学习使我充实,分享给我快乐!