康康近期的慢SQL（oracle vs 达梦）

whn1977

已于 2024-07-22 18:29:31 修改

阅读量325

点赞数 1

文章标签： sql oracle 数据库

于 2024-07-21 00:00:49 首次发布

本文链接：https://blog.csdn.net/whn1977/article/details/140579866

版权

近期执行的sql，哪些比较慢？
或者健康检查时搂一眼状态

oracle：

--最近3天内的慢sql
set lines 200 pages 100
col txt for a65
col sql_id for a13
select a.sql_id,a.cnt,a.pctload,b.sql_text txt from (select * from (select sql_id,count(0) cnt,round(count(0)/sum(count(0)) over(),4)*100 pctload
from gv$active_session_history A
where A.SAMPLE_TIME>sysdate-3 
and sql_id is not null GROUP BY SQL_ID ORDER BY COUNT(0) DESC) 
where rownum<11) a left join (select distinct sql_text,sql_id from v$sqltext where piece=0) b on a.sql_id=b.sql_id order by 2 desc ,1;

在这里插入图片描述

--实例启动以来的最慢20个sql
set pages 200 lin 180
col SQL_ID for a14
col SQL_EXEC_START for a20
col STATUS for a15
SELECT *
  FROM (SELECT status,
               --username,
               sql_id,
               sql_exec_id,
               TO_CHAR(sql_exec_start, 'yyyy-mm-dd hh24:mi:ss') AS sql_exec_start,
               ROUND(elapsed_time / 1000000) AS "Elapsed (s)",
               ROUND(cpu_time / 1000000) AS "CPU (s)",
               buffer_gets,
               ROUND(physical_read_bytes / (1024 * 1024)) AS "Phys reads (MB)",
               ROUND(physical_write_bytes / (1024 * 1024)) AS "Phys writes (MB)"
          FROM v$sql_monitor
         ORDER BY elapsed_time DESC)
 WHERE rownum <= 20;

在这里插入图片描述

达梦：

--近期sql（1万--具体见SQL_HISTORY_CNT 参数）中最慢的20个sql
SELECT TOP 20 START_TIME,TIME_USED/1000 TIME_USED,case is_over when 'Y' then 'DONE' when 'N' then 'Running' end running ,N_LOGIC_READ buffer_gets,N_PHY_READ disk_gets,AFFECTED_ROWS num_rows,substr(TOP_SQL_TEXT,1,50) SQLTXT,command_type FROM V$SQL_HISTORY ORDER BY TIME_USED DESC;

在这里插入图片描述

--自数据库启动以来执行慢(1秒以上)的 20 条 SQL 信息
SELECT  SESS_ID,SQL_ID,substr(SQL_TEXT,1,50) SQLTXT,EXEC_TIME,FINISH_TIME,N_RUNS  FROM V$SYSTEM_LONG_EXEC_SQLS ORDER BY EXEC_TIME DESC;