当前位置：首页 > news >正文

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

news 2025/8/26 19:06:55

近期执行的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_monitorORDER 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;