Oracle中有没有查询近期慢sql的方式？

我来答

匿名用户

oracle

Oracle中有没有查询近期慢sql的方式？

我来答

添加附件

问题补充

4条回答

默认

最新

shunwahⓂ️

一、查询执行最慢的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;

三、Oracle查询SQL语句执行的耗时

select a.sql_text SQL语句,
       b.etime 执行耗时,
       c.user_id 用户ID,
       c.SAMPLE_TIME 执行时间,
       c.INSTANCE_NUMBER 实例数,
       u.username 用户名, a.sql_id SQL编号
  from dba_hist_sqltext a,
       (select sql_id, ELAPSED_TIME_DELTA / 1000000 as etime
          from dba_hist_sqlstat
         where ELAPSED_TIME_DELTA / 1000000 >= 1) b,
       dba_hist_active_sess_history c,
       dba_users u
 where a.sql_id = b.sql_id
   and u.username = 'SYNC_PLUS_1_20190109'
   and c.user_id = u.user_id
   and b.sql_id = c.sql_id
  -- and a.sql_text like '%insert into GK_ZWVCH_HSC_NEW      select  %'
 order by  SAMPLE_TIME desc,
  b.etime desc;

四：定位系统里面哪些SQL脚本存在TABLE ACCESS FULL行为

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

select s.SQL_TEXT
  from v$sqlarea s
 where s.SQL_ID = '4dpd97jh2gzsd'
   and s.HASH_VALUE = '1613233933'
   and s.PLAN_HASH_VALUE = '3592287464';

或者

select s.SQL_TEXT from v$sqlarea s where s.ADDRESS = '00000000A65D2318';

有用 0

打赏 0

徐孝亮

查询指定时间段内的top 10 SQL：

select *
  from (select sql_id,count(*)
          from gv$active_session_history
         where sample_time between
               to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
               to_date('&date2', 'yyyy-mm-dd hh24:mi:ss')
           and inst_id in (select instance_number from v$instance)
         group by sql_id
         order by count(*) desc)
 where rownum <= 10;

有用 0

打赏 0

咖啡哥

---业务高峰期平均执行时间大于5s，执行次数大于10次的sql
select *
  from (select a.parsing_schema_name schema,
               sum(a.executions_delta) "次数",
               sum(a.DISK_READS_delta) "物理读",
               sum(a.DIRECT_WRITES_delta) "直接写",
               round(sum(a.CPU_TIME_delta) / 1000000 / 60, 2) "CPU Time(min)",
               round(sum(a.ELAPSED_TIME_delta) / 1000000 / 60, 2) "TotalTime(Min)",
               round(sum(a.cpu_time_delta) /
                     decode(sum(a.executions_delta),
                            0,
                            1,
                            sum(a.executions_delta)) / 1000000,
                     2) cputime_ps,
               round(sum(a.PHYSICAL_READ_BYTES_delta) / 1024 / 1024 / 1024,
                     2) "物理读G",
               round(sum(a.physical_write_bytes_delta) / 1024 / 1024 / 1024,
                     2) "物理写G",
               (select sql_text
                  from dba_hist_sqltext c
                 where c.sql_id = a.sql_id
                   and rownum = 1) sql,
               a.sql_id
          from DBA_HIST_SQLSTAT a, DBA_HIST_SNAPSHOT b
         where a.SNAP_ID = b.SNAP_ID
           and to_char(b.BEGIN_INTERVAL_TIME, 'hh24') in
               ('16', '17', '18', '19') ---开始时间    
              -- and END_INTERVAL_TIME <= sysdate ---结束时间  
           and a.parsing_schema_name not in ('SYS', 'SYSTEM', 'DEVELOP')
         group by parsing_schema_name, a.sql_id
        having round(sum(a.cpu_time_delta) / decode(sum(a.executions_delta), 0, 1, sum(a.executions_delta)) / 1000000, 2) > 5 and sum(a.executions_delta) > 10
         order by 7 desc);


--系统中最耗费IO 的SQL ：

--1.按照单位时间内(最小时间间隔1小时)，从【磁盘读取的字节数】降序排序，TOP 50的SQL ，
--这个SQL可以查看到 哪类 SQL 消耗了 最多的磁盘IO 读取资源

select *
  from (select a.parsing_schema_name schema,
               a.sql_id,
               (select sql_text
                  from dba_hist_sqltext c
                 where c.sql_id = a.sql_id
                   and rownum = 1) "sql",
               round(sum(a.CPU_TIME_delta) / 1000000 / 60, 2) "CPU Time(Min)",
               round(sum(a.ELAPSED_TIME_delta) / 1000000 / 60, 2) "total Time(Min)",
               sum(a.executions_delta) "次数",
               sum(a.DISK_READS_delta) "物理读",
               sum(a.DIRECT_WRITES_delta) "直接写",               
               round(sum(a.PHYSICAL_READ_BYTES_delta) / 1024 / 1024 / 1024,
                     2) "物理读G",
               round(sum(a.physical_write_bytes_delta) / 1024 / 1024 / 1024,
                     2) "物理写G"        
          from DBA_HIST_SQLSTAT a, DBA_HIST_SNAPSHOT b
         where a.SNAP_ID = b.SNAP_ID
          -- and b.BEGIN_INTERVAL_TIME >= sysdate - 1 / 24 ---开始时间    
          -- and END_INTERVAL_TIME <= sysdate ---结束时间  
         group by parsing_schema_name, a.sql_id
         order by 7 desc)
 where rownum <= 50;

--2.按照单位时间内(最小时间间隔1小时)，读取【磁盘次数】降序排序，TOP 50的SQL ，
--这个SQL同样可以查看到 哪类 SQL 消耗了 最多的磁盘IO 读取资源

select *
  from (select a.parsing_schema_name schema,
               sum(a.executions_delta) "次数",
               sum(a.DISK_READS_delta) "物理读",
               sum(a.DIRECT_WRITES_delta) "直接写",
               round(sum(a.CPU_TIME_delta) / 1000000 / 60, 2) "CPU Time(Min)",
               round(sum(a.ELAPSED_TIME_delta) / 1000000 / 60, 2) "total Time(Min)",
               round(sum(a.PHYSICAL_READ_BYTES_delta) / 1024 / 1024 / 1024,
                     2) "物理读G",
               round(sum(a.physical_write_bytes_delta) / 1024 / 1024 / 1024,
                     2) "物理写G",
               (select sql_text
                  from dba_hist_sqltext c
                 where c.sql_id = a.sql_id
                   and rownum = 1) "sql",
               a.sql_id
          from DBA_HIST_SQLSTAT a, DBA_HIST_SNAPSHOT b
         where a.SNAP_ID = b.SNAP_ID
         --  and b.BEGIN_INTERVAL_TIME >= sysdate - 1 / 24
              --to_date('2012-11-22 16:00:00', 'YYYY-MM-DD HH24:MI:SS') ---开始时间    
          -- and END_INTERVAL_TIME <= sysdate
        -- to_date('2012-11-22 17:00:00', 'YYYY-MM-DD HH24:MI:SS') ---结束时间  
         group by parsing_schema_name, a.sql_id
         order by 3 desc)
 where rownum <= 50;

--系统中最耗费CPU,MEN的SQL
--1. 按照单位时间内(最小时间间隔1小时)，使用CPU最高，TOP 50的SQL  
select *
  from (select a.parsing_schema_name schema,
               sum(a.executions_delta) "次数",
               sum(a.DISK_READS_delta) "物理读",
               sum(a.DIRECT_WRITES_delta) "直接写",
               round(sum(a.CPU_TIME_delta) / 1000000 / 60, 2) "CPU Time(min)",
               round(sum(a.ELAPSED_TIME_delta) / 1000000 / 60, 2) "total Time(Min)",
               round(sum(a.PHYSICAL_READ_BYTES_delta) / 1024 / 1024 / 1024,
                     2) "物理读G",
               round(sum(a.physical_write_bytes_delta) / 1024 / 1024 / 1024,
                     2) "物理写G",
               (select sql_text
                  from dba_hist_sqltext c
                 where c.sql_id = a.sql_id
                   and rownum = 1) sql,
               a.sql_id
          from DBA_HIST_SQLSTAT a, DBA_HIST_SNAPSHOT b
         where a.SNAP_ID = b.SNAP_ID
           --and b.BEGIN_INTERVAL_TIME >= sysdate - 1 / 24
              -- to_date('2012-11-22 16:00:00', 'YYYY-MM-DD HH24:MI:SS') ---开始时间    
          -- and END_INTERVAL_TIME <= sysdate
        -- to_date('2012-11-22 17:00:00', 'YYYY-MM-DD HH24:MI:SS') ---结束时间  
         group by parsing_schema_name, a.sql_id
         order by 5 desc)
 where rownum <= 50;

--2.最消耗MEN 的SQL:

--最消耗MEN 的SQL 分为2类
/*一类是 消耗SHARED POOL 的SQL，消耗SHARED POOL 的SQL 非常好抓，只需改动上面脚本部分条件，但是抓它没什么意义，我们不是OLTP系统。
一类是消耗 PGA 的SQL，消耗PGA的SQL 通过上面的方法无法获得，PGA 是实时分配，它是动态的，需要部署实时监控程序抓取。
系统中，能提供 PGA 消耗的视图 就 只有2个 ，一个是V$SQL_WORKAREA，另外一个是V$SQL_WORKAREA_ACTIVE。
内存一般来说是不会超标的，因为一个SQL 有并行度的限制，也就是说一个SQL不可能开很高的并行度。
另外ORACLE有个硬限制，一个进程不会超过2GB，并且WORK AREA 如果是auto管理方式，一个进程最大的WORK AREA 是1GB
所以我觉得抓取最消耗MEN 的SQL ，可以换种方式，比如抓取 并行度最高的SQL，这样也可以推算出来 哪个SQL消耗MEN多。
这个SQL 暂时不给出系统中单位时间内，执行次数最多的SQL：*/

select *
  from (select a.parsing_schema_name schema,
               sum(a.executions_delta) "次数",
               sum(a.DISK_READS_delta) "物理读",
               sum(a.DIRECT_WRITES_delta) "直接写",
               round(sum(a.CPU_TIME_delta) / 1000000 / 60, 2) "CPUTime(min)",
               round(sum(a.ELAPSED_TIME_delta) / 1000000 / 60, 2) "total Time(Min)",
               round(sum(a.PHYSICAL_READ_BYTES_delta) / 1024 / 1024 / 1024,
                     2) "物理读G",
               round(sum(a.physical_write_bytes_delta) / 1024 / 1024 / 1024,
                     2) "物理写G",
               (select sql_text
                  from dba_hist_sqltext c
                 where c.sql_id = a.sql_id
                   and rownum = 1) sql,
               a.sql_id
          from DBA_HIST_SQLSTAT a, DBA_HIST_SNAPSHOT b
         where a.SNAP_ID = b.SNAP_ID
         --  and b.BEGIN_INTERVAL_TIME >= sysdate - 1 / 24
         --  and END_INTERVAL_TIME <= sysdate
         group by parsing_schema_name, a.sql_id
         order by 2 desc)
 where rownum<=50;

--系统中单位时间内，执行最长的SQL：
select *
  from (select a.parsing_schema_name schema,
               sum(a.executions_delta) "次数",
               sum(a.DISK_READS_delta) "物理读",
               sum(a.DIRECT_WRITES_delta) "直接写",
               round(sum(a.CPU_TIME_delta) / 1000000 / 60, 2) "CPU Time(min)",
               round(sum(a.ELAPSED_TIME_delta) / 1000000 / 60, 2) "TotalTime(Min)",
               round(sum(a.PHYSICAL_READ_BYTES_delta) / 1024 / 1024 / 1024,
                     2) "物理读G",
               round(sum(a.physical_write_bytes_delta) / 1024 / 1024 / 1024,
                     2) "物理写G",
               (select sql_text
                  from dba_hist_sqltext c
                 where c.sql_id = a.sql_id
                   and rownum = 1) sql,
               a.sql_id
          from DBA_HIST_SQLSTAT a, DBA_HIST_SNAPSHOT b
         where a.SNAP_ID = b.SNAP_ID
           and b.BEGIN_INTERVAL_TIME >= sysdate - 1 / 24 ---开始时间    
           and END_INTERVAL_TIME <= sysdate ---结束时间  
         group by parsing_schema_name, a.sql_id
         order by 6 desc)
 where rownum <= 50;

---order by 
select *
  from (select a.parsing_schema_name schema,
               sum(a.executions_delta) "次数",
               sum(a.buffer_gets_delta) "逻辑读",
               round(sum(a.CPU_TIME_delta) / 1000000 / 60, 2) "CPU Time(min)",
               round(sum(a.ELAPSED_TIME_delta) / 1000000 / 60, 2) "TotalTime(Min)",
               round(sum(a.buffer_gets_delta) / 1024 / 1024 / 1024,
                     2) "逻辑读G",
               (select sql_text
                  from dba_hist_sqltext c
                 where c.sql_id = a.sql_id
                   and rownum = 1) sql,
               a.sql_id
          from DBA_HIST_SQLSTAT a, DBA_HIST_SNAPSHOT b
         where a.SNAP_ID = b.SNAP_ID
          -- and b.BEGIN_INTERVAL_TIME >= sysdate - 1 / 24 ---开始时间    
         --  and END_INTERVAL_TIME <= sysdate ---结束时间  
         group by parsing_schema_name, a.sql_id
         order by 6 desc)
 where rownum <= 50;

有用 0

打赏 0

张sir

我一般用这个：

set line 200 pages 1000

col program for a30
col exec_time for a30
select sql_id,sql_exec_start,sql_exec_id,program,sql_plan_hash_value,max(sample_time)-sql_exec_start exec_time
from gv$active_session_history
where
sample_time>to_date('20200911 22:00','yyyymmdd hh24:mi')
and sample_time<to_date('20200911 23:00','yyyymmdd hh24:mi')
and sql_exec_start is not null
group by sql_id,sql_exec_start,sql_exec_id,program,sql_plan_hash_value
order by exec_time;