oracle查看性能的sql整理

最新推荐文章于 2025-06-28 08:02:52 发布

转载最新推荐文章于 2025-06-28 08:02:52 发布 · 4.5k 阅读

ORACLE 专栏收录该内容

51 篇文章

订阅专栏

在ORACLE数据库应用调优中，SQL执行频率很重要。本文介绍查看ORACLE数据库中执行最慢、查询次数最多的SQL语句的方法，还说明了通过V$ACTIVE_SESSION_HISTORY视图查找最近一分钟内最消耗CPU、I/O、资源的SQL语句及会话信息的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

oracle查看执行最慢与查询次数最多的sql语句

前言

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

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

有很多种方法可以用来找出哪些SQL语句需要优化，但是很久以来，最简单的方法都是分析保存在V $S Q L 视图中的缓存的 S Q L 信息。通过 V$ SQL视图，可以确定具有高消耗时间、CUP和IO读取的SQL语句。

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

select *
from (select v.sql_id,
v.child_number,
v.sql_text,
last_load_time,
v.PARSING_USER_ID,
ROUND(v.ELAPSED_TIME / 1000000 / (CASE
               WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN
                1
               ELSE
                EXECUTIONS
             END),
             2) "执行时间'S'",
 v.SQL_FULLTEXT,
v.cpu_time,
v.disk_reads,
rank() over(order by v.elapsed_time desc) elapsed_rank
from v$sql v  ) a
where elapsed_rank <= 100  and   last_load_time > to_char(sysdate - 1/24, 'YYYY-MM-DD/HH:MI:SS')    order by "执行时间'S'" desc

查询最近一小时内最慢的SQL:

select executions, cpu_time/1e6 as cpu_sec, elapsed_time/1e6 as elapsed_sec, round(elapsed_time/sqrt(executions)) as important, v.* 
from v$sql v 
where executions > 10 and last_load_time > to_char(sysdate - 1/24, 'YYYY-MM-DD/HH:MI:SS')  
order by important desc

2.查看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;

3.查看消耗磁盘读取最多的前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.disk_reads desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

一、查询执行最慢的sql

select *
 from(selectsa.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
     fromv$sqlarea sa
     leftjoinall_users u
      onsa.PARSING_USER_ID = u.user_id
     wheresa.EXECUTIONS > 0
     orderby(sa.ELAPSED_TIME / sa.EXECUTIONS)desc)
 whererownum <= 50;

二、查询次数最多的 sql

select *
 from(selects.SQL_TEXT,
        s.EXECUTIONS"执行次数",
        s.PARSING_USER_ID"用户名",
        rank() over(orderbyEXECUTIONS desc) EXEC_RANK
     fromv$sql s
     leftjoinall_users u
      onu.USER_ID = s.PARSING_USER_ID) t
 whereexec_rank <= 100;

Oracle之查看最近最消耗CPU的SQL语句及会话信息的方法

导读：
1、查找最近一分钟内，最消耗CPU的SQL语句；
2、查找最近一分钟内，最消耗CPU的会话；
3、查找最近一分钟内，最消耗I/O的SQL语句；
4、查找最近一分钟内，最消耗资源的SQL语句；
5、查找最近一分钟内，最消耗资源的会话。

在Oracle中，查找最近一段时间，最消耗CPU的SQL语句及会话信息：

可以根据 V$ACTIVE_SESSION_HISTORY 视图来获取。

1、查找最近一分钟内，最消耗CPU的SQL语句：

SELECT ASH.INST_ID,
        ASH.SQL_ID,
        (SELECT VS.SQL_TEXT
           FROM GV$SQLAREA VS
          WHERE VS.SQL_ID = ASH.SQL_ID
            AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,
        ASH.SQL_CHILD_NUMBER,
        ASH.SQL_OPNAME,
        ASH.SESSION_INFO,
        COUNTS,
        PCTLOAD * 100 || '%' PCTLOAD
   FROM (SELECT ASH.INST_ID,
                ASH.SQL_ID,
                ASH.SQL_CHILD_NUMBER,
                ASH.SQL_OPNAME,
                (ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
                ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
                ASH.SESSION_TYPE) SESSION_INFO,
                COUNT(*) COUNTS,
                ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), 2) PCTLOAD,
                DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER
           FROM GV$ACTIVE_SESSION_HISTORY ASH
          WHERE  ASH.SESSION_TYPE <> 'BACKGROUND'
           AND ASH.SESSION_STATE = 'ON CPU'
AND SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
          GROUP BY ASH.INST_ID,
                   ASH.SQL_ID,
                   ASH.SQL_CHILD_NUMBER,
                   ASH.SQL_OPNAME,
                   (ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
                   ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
                   ASH.SESSION_TYPE)) ASH
  WHERE RANK_ORDER <= 10
  ORDER BY COUNTS DESC;

2、查找最近一分钟内，最消耗CPU的会话：

SELECT SESSION_ID,
       COUNT(*)
FROM   V$ACTIVE_SESSION_HISTORY V
WHERE  V.SESSION_STATE = 'ON CPU'
AND    V.SAMPLE_TIME > SYSDATE - 10/ (24 * 60)
GROUP  BY SESSION_ID
ORDER  BY COUNT(*) DESC;

3、查找最近一分钟内，最消耗I/O的SQL语句：

SELECT ASH.INST_ID,
        ASH.SQL_ID,
        (SELECT VS.SQL_TEXT
           FROM GV$SQLAREA VS
          WHERE VS.SQL_ID = ASH.SQL_ID
            AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,
        ASH.SQL_CHILD_NUMBER,
        ASH.SQL_OPNAME,
        ASH.SESSION_INFO,
        COUNTS,
        PCTLOAD * 100 || '%' PCTLOAD
   FROM (SELECT ASH.INST_ID,
                ASH.SQL_ID,
                ASH.SQL_CHILD_NUMBER,
                ASH.SQL_OPNAME,
                (ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
                ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
                ASH.SESSION_TYPE) SESSION_INFO,
                COUNT(*) COUNTS,
                ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), 2) PCTLOAD,
                DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER
           FROM GV$ACTIVE_SESSION_HISTORY ASH
          WHERE ASH.SESSION_TYPE <> 'BACKGROUND'
            AND ASH.SESSION_STATE = 'WAITING'
            AND ASH.SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
            AND ASH.WAIT_CLASS = 'USER I/O'
          GROUP BY ASH.INST_ID,
                   ASH.SQL_ID,
                   ASH.SQL_CHILD_NUMBER,
                   ASH.SQL_OPNAME,
                   (ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
                   ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
                   ASH.SESSION_TYPE)) ASH
  WHERE RANK_ORDER <= 10
  ORDER BY COUNTS DESC;

4、查找最近一分钟内，最消耗资源的SQL语句：

SELECT ASH.INST_ID,
        ASH.SQL_ID,
        (SELECT VS.SQL_TEXT
           FROM GV$SQLAREA VS
          WHERE VS.SQL_ID = ASH.SQL_ID
            AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,
        ASH.SQL_CHILD_NUMBER,
        ASH.SQL_OPNAME,
        ASH.SESSION_INFO,
        COUNTS,
        PCTLOAD * 100 || '%' PCTLOAD
   FROM (SELECT ASH.INST_ID,
                ASH.SQL_ID,
                ASH.SQL_CHILD_NUMBER,
                ASH.SQL_OPNAME,
                (ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
                ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
                ASH.SESSION_TYPE) SESSION_INFO,
                COUNT(*) COUNTS,
                ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), 2) PCTLOAD,
                DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER
           FROM GV$ACTIVE_SESSION_HISTORY ASH
          WHERE ASH.SESSION_TYPE <> 'BACKGROUND'
            AND ASH.SESSION_STATE = 'WAITING'
            AND ASH.SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
            AND ASH.WAIT_CLASS = 'USER I/O'
          GROUP BY ASH.INST_ID,
                   ASH.SQL_ID,
                   ASH.SQL_CHILD_NUMBER,
                   ASH.SQL_OPNAME,
                   (ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
                   ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
                   ASH.SESSION_TYPE)) ASH
  WHERE RANK_ORDER <= 10
  ORDER BY COUNTS DESC;

5、查找最近一分钟内，最消耗资源的会话：

SELECT ASH.SESSION_ID,
       ASH.SESSION_SERIAL#,
       ASH.USER_ID,
       ASH.PROGRAM,
       SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', 1, 0)) "CPU",
       SUM(DECODE(ASH.SESSION_STATE, 'WAITING', 1, 0)) -
       SUM(DECODE(ASH.SESSION_STATE,
                  'WAITING',
                  DECODE(ASH.WAIT_CLASS, 'USER I/O', 1, 0),
                  0)) "WAITING",
       SUM(DECODE(ASH.SESSION_STATE,
                  'WAITING',
                  DECODE(ASH.WAIT_CLASS, 'USER I/O', 1, 0),
                  0)) "IO",
       SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', 1, 1)) "TOTAL"
  FROM V$ACTIVE_SESSION_HISTORY ASH
 WHERE ASH.SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
 GROUP BY ASH.SESSION_ID, ASH.USER_ID, ASH.SESSION_SERIAL#, ASH.PROGRAM
 ORDER BY SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', 1, 1));