这篇文章小编给大家分享的是oracle慢查询的内容,下文介绍了oracle慢查询的方法,文中示例介绍的很详细,感兴趣的朋友可以了解看看,下面让我们一起来学习一下吧!
oracle慢查询的方法:
Oracle没有像MySql那样同时设置参数来分析慢查询,然而却提供了慢查询的sql语句,此Sql语句需要通过dba权限的帐号登录进行查询
1.查询执行最慢的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 where u.username='填写同户名' order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc) where rownum <= 50;#查询的数据数目
2.查询次数最多的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;