Oracle查看执行最慢与查询次数、高消耗资源最多的sql语句

深藏阁楼爱情的钟 2022-11-12 09:48 311阅读 0赞

在ORACLE数据库应用调优中，一个SQL的执行次数/频率也是常常需要关注的，因为某个SQL执行太频繁，要么是由于应用设计有缺陷，需要在业务逻辑上做出优化处理，要么是业务特殊性所导致。如果执行频繁的SQL，往往容易遭遇一些并发性的问题:  
查询执行最慢的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 "用户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;

根据用户ID查询用户名：

select USERNAME from dba_users where USER_ID = '89';

读硬盘多或占用内存可能多的SQL

select sql_text, disk_reads, buffer_gets, parsing_schema_name, executions
    from v$sqlarea
    order by disk_reads desc;

说明：单纯从V$sqlarea中是无法查出每个SQL消耗的内存量的，但我们可以借助磁盘读次数间接反映可能的消耗内存量较大的SQL语句，然后再借助执行计划（如v$sql\_plan视图）具体查看。  
利用系统视图v$sqlarea，其中disk\_reads是磁盘读次数，也是主要字段，剩余字段均为参考字段。其中，buffer\_gets是内存读次数，parsing\_schema\_name是首次编译者模式名（一般与user名相同），executions是语句执行次数。  
需要注意的是，v$sqlarea中sql\_text可能不完整，若需要完整的则需要借助hash\_value或sql\_id结合v$sqltext来查看分析。  
查看排序次数最多的SQL

select sql_text, sorts, parsing_schema_name
    from v$sqlarea
    order by sorts desc;

转载地址：[https://www.unixso.com/Oracle/sql-slow-long.html][https_www.unixso.com_Oracle_sql-slow-long.html]

[https_www.unixso.com_Oracle_sql-slow-long.html]: https://www.unixso.com/Oracle/sql-slow-long.html