Oracle TOP SQL查询

查询执行时间最长的10条SQL:

--SQL 1
select rownum as rank, a.* 
from (select PARSING_SCHEMA_NAME RUN_USER, --执行用户 
SQL_FULLTEXT, --SQL文本,全，带格式 
sql_text, --SQL文本前面部分 
elapsed_Time , --语句执行总消耗时间（包含CPU 时间和等待时间） ，单位是μs
cpu_time , --CPU 时间（语句运行时间 ）
trunc((elapsed_Time - cpu_time)*100/elapsed_Time,2) "wait_time_per%",--等待时间所占百分比 
executions, --执行次数 
elapsed_Time/(executions+1) Per_Time, --每次执行平均时间, 
buffer_gets, -- 从内存中读取的数据量
disk_reads, --磁盘读 
hash_value, --SQL的hash_value，以备获取完整SQL 
USER_IO_WAIT_TIME, --用户IO等待时间
SORTS --排序次数 
from v$sqlarea t 
where elapsed_time > 20000　and PARSING_SCHEMA_NAME<>'SYS' 
order by elapsed_time desc) a 
where rownum <= 10 
order by elapsed_Time/(executions+1)/1000 desc

--SQL 2
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 <= 10;

--SQL 3
select *
 from (select * from v$sqlarea t where t.PARSING_SCHEMA_NAME in ('用户名') order by t.ELAPSED_TIME desc)  --用户名要大写
where rownum <= 10;

查询执行次数最多的10条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 <= 10;

参考：https://www.cnblogs.com/singlecodeworld/p/9946840.html