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:

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

查询TOP SQL:

SELECT 
    slo.sid,
    slo.serial#,
    slo.sql_id,
    slo.sql_plan_hash_value AS plan_id,
    slo.opname,
    slo.target,
    slo.start_time,
    slo.elapsed_seconds,
    slo.time_remaining,
    sql.sql_text
FROM 
    v$session_longops slo
JOIN 
    v$sql sql
ON 
    slo.sql_id = sql.sql_id
ORDER BY 
    slo.elapsed_seconds DESC;

--SQL 2
SELECT 
       s.sid, s.serial#, s.machine, sl.START_TIME,sl.elapsed_seconds, 
       ROUND(sl.sofar/sl.totalwork*100, 2) sql_complete_pcentage, 
       st.piece line#, st.sql_text 
      FROM 
       v$session s, 
       v$session_longops sl, 
       v$sqltext st 
      WHERE 
       s.sid                     = sl.sid 
       AND s.serial#             = sl.serial# 
       AND sl.SQL_ID             = st.SQL_ID 
       AND sl.ELAPSED_SECONDS    > 900 
       AND sl.time_remaining>0 ; 

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