oracle排查慢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;

视图列介绍：
EXECUTIONS :执行次数

ELAPSED_TIME ：SQL解析和执行共花费的时间

ELASED_TIME / EXECUTIONS = 每条SQL平均耗时


查询次数最多的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;

v$sql字段解析

executions: 被载入缓存后，SQL执行次数

 

具体参考：https://blog.csdn.net/weixin_39673947/article/details/110318457

SQL中存在全表扫描

select *
  from v$sql_plan v
 where v.operation = 'TABLE ACCESS'
   and v.OPTIONS = 'FULL'
   and v.OBJECT_OWNER='SYNC_PLUS_1_20190109';

v$sql_plan视图字段解析

operation: 在各步骤执行内部操作的名称，例如：TABLE ACCESS

options: operation操作的变种，例如FULL

object_owner: 表或索引的所有者名称