Oracle数据库性能查询SQL、优化SQL

1. 查看总消耗时间最长的前10条SQL语句

select *
  from (select v.sql_id,
               v.child_number,
               v.sql_text,
               v.elapsed_time,
               v.cpu_time,
               v.disk_reads,
               rank() over(order by v.elapsed_time desc) elapsed_rank
          from v$sql v) a
 where elapsed_rank <= 10;

2. 查看Oracle当前所有进程执行进度

SELECT A.USERNAME,
       A.TARGET,
       A.SID,
       a.SERIAL#,
       A.OPNAME,
       ROUND(A.SOFAR * 100 / A.TOTALWORK, 0) || '%' AS "执行进度",
       A.TIME_REMAINING "剩余秒数",
       TRUNC(A.TIME_REMAINING / 60, 2) "剩余分钟",
       TRUNC(A.TIME_REMAINING / 60/60, 2) "剩余小时",
       B.SQL_TEXT "执行SQL",
       B.LAST_ACTIVE_TIME "开始时间"
  FROM V$SESSION_LONGOPS A, V$SQLAREA B
 WHERE A.TIME_REMAINING <> 0
   AND A.SQL_ADDRESS = B.ADDRESS
   AND A.SQL_HASH_VALUE = B.HASH_VALUE

3. 查看CPU消耗时间最多的前10条SQL语句

select *
  from (select v.sql_id,
               v.child_number,
               v.sql_text,
               v.elapsed_time,
               v.cpu_time,
               v.disk_reads,
               rank() over(order by v.cpu_time desc) elapsed_rank
          from v$sql v) a
 where elapsed_rank <= 10;

4. 查看消耗磁盘读取最多的前10条SQL语句

select *
  from (