mysql、mssql、oracle慢sql查询

Oracle 获取昨天到现在，平均执行时间前十的sql语句：

select *
 from (select sa.SQL_TEXT,
        sa.SQL_FULLTEXT,
        sa.EXECUTIONS "执行次数",
        round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间s",
        round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间s",
        sa.COMMAND_TYPE,
        sa.PARSING_USER_ID "用户ID",
        u.username "用户名",
        sa.SQL_ID "SQL_ID",
        sa.HASH_VALUE,
        to_char(sa.LAST_ACTIVE_TIME, 'yyyy-mm-dd hh24:mi:ss') "上次执行时间"
     from v$sqlarea sa
     left join all_users u
      on sa.PARSING_USER_ID = u.user_id
     where sa.EXECUTIONS > 0
      AND sa.LAST_ACTIVE_TIME >= TRUNC(SYSDATE - 1) 
     order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc) where rownum <= 10;

SQL server ：
 

SELECT TOP 10
  CONVERT(FLOAT, total_elapsed_time * 1.0 / execution_count) / 1000 AS 平均时间ms,
  CONVERT(FLOAT, total_elapsed_time) / 1000 AS 总花费时间ms,
  CONVERT(FLOAT, total_worker_time) / 1000 AS 所用的CPU总时间ms,
  total_physical_reads AS 物理读取总次数,
  CONVERT(FLOAT, total_logical_reads) / execution_count AS 每次逻辑读次数,
  total_logical_reads AS 逻辑读取总次数,
  total_logical_writes AS 逻辑写入总次数,
  execution_count AS 执行次数,
  SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
    ((CASE statement_end_offset
    WHEN -1 THEN DATALENGTH(st.text)
    ELSE qs.statement_end_offset END
    - qs.statement_start_offset) / 2) + 1) AS 执行语句,
    creation_time N'语句编译时间',
    last_execution_time N'上次执行时间'
FROM
  sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
  SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
    ((CASE statement_end_offset
    WHEN -1 THEN DATALENGTH(st.text)
    ELSE qs.statement_end_offset END
    - qs.statement_start_offset) / 2) + 1) NOT LIKE '%fetch%'
ORDER BY
  total_elapsed_time / execution_count DESC;

MySQL：

法一：

1、查看慢SQL是否启用，查看命令：show variables like 'log_slow_queries';

如果结果为ON则是开启了，如果为OFF则表示禁用了。

2、开启慢查询命令：set global log_slow_queries = on;

3、查看是否开启：show variables like 'log_slow_queries';

4、查询最长十条sql：mysqldumpslow -s t -t 10 /usr/local/mysql/data/localhost_slow.log

法二：

SELECT 
    s.SCHEMA_NAME,
    h.SQL_TEXT AS 完整SQL语句,
    s.COUNT_STAR AS 运行次数,
    s.SUM_TIMER_WAIT/1000000000000 AS 总运行时间秒,
    s.AVG_TIMER_WAIT/1000000000000 AS 平均运行时间秒,
    s.MIN_TIMER_WAIT/1000000000000 AS 最小等待时间秒,
    s.MAX_TIMER_WAIT/1000000000000 AS 最大等待时间秒,
    s.FIRST_SEEN,
    s.LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest s
JOIN performance_schema.events_statements_history h ON s.DIGEST = h.DIGEST
-- where  h.SQL_TEXT like "%tpbus_valveinfo_view_som%"
ORDER BY s.AVG_TIMER_WAIT DESC;

或者：将join去掉，直接使用s里面的sql，但是这个并不完整。