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,但是这个并不完整。