数据库资源卡顿查询SQL

数据库 Mysql

Mysql

查询某个时间段内数据库执行记录之中，SQL性能、内存消耗、定时器等待时间、索引情况、排序情况等信息的SQL

SELECT 
   SCHEMA_NAME AS '模式名',
   DIGEST_TEXT AS '查询文本',
   QUERY_SAMPLE_TEXT AS '简易查询文本',
   SUM_ROWS_AFFECTED AS '总影响的行数',
   SUM_ROWS_SENT AS '返回行数总和',
   SUM_NO_INDEX_USED AS '没用索引查询次数',
   SUM_NO_GOOD_INDEX_USED AS '解析器没有好索引查询次数',
   sys.format_time(SUM_TIMER_WAIT) AS '总等待时间',
   sys.format_time(QUERY_SAMPLE_TIMER_WAIT) AS '粗略等待时间',
   sys.format_time(SUM_CPU_TIME) AS 'CPU时间总和',
   MAX_TOTAL_MEMORY AS '最大占用内存',
   sys.format_time(SUM_LOCK_TIME) AS '等待锁表时间',
   FIRST_SEEN AS '查询首次出现时间',
   LAST_SEEN AS '查询最后出现时间'
FROM PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_SUMMARY_BY_DIGEST
WHERE STR_TO_DATE('2024-08-22 18:01:37', '%Y-%m-%d %H:%i:%s') > FIRST_SEEN  AND STR_TO_DATE('2024-08-22 18:01:37', '%Y-%m-%d %H:%i:%s') < LAST_SEEN 
ORDER BY SUM_TIMER_WAIT DESC, SUM_CPU_TIME DESC, MAX_TOTAL_MEMORY DESC
LIMIT [N]

查在所有SQL之中性能排在95以后的

# 排在执行效率95以后的SQL
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile
ORDER BY avg_latency DESC
LIMIT 10;

整个数据库的资源消耗情况：

最近一个月的数据库每天资源消耗情况 (Mysql暂时并不支持，如果想要实现，只能自己写一个表格 + 存储过程来尝试实现)

// 创建表格 记录每天情况
CREATE TABLE daily_performance_stats (
   date DATE PRIMARY KEY,
   total_count_executions BIGINT,
   total_duration DECIMAL(20,2),
   total_cpu_time DECIMAL(20,2),
   total_logical_io_reads DECIMAL(20,2),
   total_logical_io_writes DECIMAL(20,2),
   total_physical_io_reads DECIMAL(20,2),
   total_wait_time DECIMAL(20,2),
   total_lock_time DECIMAL(20,2),
   total_rows_affected BIGINT,
   total_rows_sent BIGINT,
   total_tmp_tables BIGINT,
   total_tmp_disk_tables BIGINT
);
// 记录用的存储过程
DELIMITER //

CREATE PROCEDURE collect_daily_stats()
BEGIN
   INSERT INTO daily_performance_stats
   SELECT 
       CURDATE(),
       SUM(count_star) AS total_count_executions,
       SUM(sum_timer_wait) / 1000000000 AS total_duration,
       SUM(sum_cpu_time) / 1000000000 AS total_cpu_time,
       SUM(sum_rows_examined) AS total_logical_io_reads,
       SUM(sum_rows_affected) AS total_logical_io_writes,
       SUM(sum_no_index_used) + SUM(sum_no_good_index_used) AS total_physical_io_reads,
       SUM(sum_lock_time) / 1000000000 AS total_wait_time,
       SUM(sum_lock_time) / 1000000000 AS total_lock_time,
       SUM(sum_rows_affected) AS total_rows_affected,
       SUM(sum_rows_sent) AS total_rows_sent,
       SUM(sum_created_tmp_tables) AS total_tmp_tables,
       SUM(sum_created_tmp_disk_tables) AS total_tmp_disk_tables
   FROM performance_schema.events_statements_summary_by_digest
   ON DUPLICATE KEY UPDATE
       total_count_executions = VALUES(total_count_executions),
       total_duration = VALUES(total_duration),
       total_cpu_time = VALUES(total_cpu_time),
       total_logical_io_reads = VALUES(total_logical_io_reads),
       total_logical_io_writes = VALUES(total_logical_io_writes),
       total_physical_io_reads = VALUES(total_physical_io_reads),
       total_wait_time = VALUES(total_wait_time),
       total_lock_time = VALUES(total_lock_time),
       total_rows_affected = VALUES(total_rows_affected),
       total_rows_sent = VALUES(total_rows_sent),
       total_tmp_tables = VALUES(total_tmp_tables),
       total_tmp_disk_tables = VALUES(total_tmp_disk_tables);
END //

DELIMITER ;
// 定义数据库的每天事件
CREATE EVENT daily_stats_collection
ON SCHEDULE EVERY 1 DAY
STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY + INTERVAL 1 HOUR)
DO
   CALL collect_daily_stats();
// 查询过去一个月的数据
SELECT *
FROM daily_performance_stats
WHERE date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
ORDER BY date;

SQLServer

标识过去 x 小时内的前 n 个查询（按执行时间、内存占用等）。

查询id、对象id、对象名称、sql文本、总计持续时间、总计cpu时间、总计逻辑读取次数、总计逻辑写入次数、总计物理读取次数、总计clr时间、总计dop、总计内存占用率、总计记录使用的内存、总计已使用的临时db内存、总计等待时间、执行计数、计划计数

目前以下参数是：2024-08-20 00:00:00 - 2024-08-20 14:00:00 期间 cpu 消耗前40

With wait_stats AS
(
SELECT
   ws.plan_id plan_id,
   ws.wait_category,
   ROUND(CONVERT(float, SUM(ws.total_query_wait_time_ms)/SUM(ws.total_query_wait_time_ms/ws.avg_query_wait_time_ms))*1,2) avg_query_wait_time,
   ROUND(CONVERT(float, SQRT( SUM(ws.stdev_query_wait_time_ms*ws.stdev_query_wait_time_ms*(ws.total_query_wait_time_ms/ws.avg_query_wait_time_ms))/SUM(ws.total_query_wait_time_ms/ws.avg_query_wait_time_ms)))*1,2) stdev_query_wait_time,
   CAST(ROUND(SUM(ws.total_query_wait_time_ms/ws.avg_query_wait_time_ms),0) AS BIGINT) count_executions,
   MAX(itvl.end_time) last_execution_time,
   MIN(itvl.start_time) first_execution_time
FROM sys.query_store_wait_stats ws
   JOIN sys.query_store_runtime_stats_interval itvl ON itvl.runtime_stats_interval_id = ws.runtime_stats_interval_id
WHERE NOT (itvl.start_time > convert(datetime,'2024-08-20 14:00:00') OR itvl.end_time < convert(datetime,'2024-08-20 00:00:00'))
GROUP BY ws.plan_id, ws.runtime_stats_interval_id, ws.wait_category
),
top_wait_stats AS
(
SELECT 
   p.query_id query_id,
   q.object_id object_id,
   ISNULL(OBJECT_NAME(q.object_id),'') object_name,
   qt.query_sql_text query_sql_text,
   ROUND(CONVERT(float, SUM(ws.avg_query_wait_time*ws.count_executions))*1,2) total_query_wait_time,
   MAX(ws.count_executions) count_executions,
   COUNT(distinct p.plan_id) num_plans
FROM wait_stats ws
   JOIN sys.query_store_plan p ON p.plan_id = ws.plan_id
   JOIN sys.query_store_query q ON q.query_id = p.query_id
   JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE NOT (ws.first_execution_time > convert(datetime,'2024-08-20 14:00:00') OR ws.last_execution_time < convert(datetime,'2024-08-20 00:00:00'))
GROUP BY p.query_id, qt.query_sql_text, q.object_id
),
top_other_stats AS
(
SELECT 
   p.query_id query_id,
   q.object_id object_id,
   ISNULL(OBJECT_NAME(q.object_id),'') object_name,
   qt.query_sql_text query_sql_text,
   ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration,
   ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2) total_cpu_time,
   ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads*rs.count_executions))*8,2) total_logical_io_reads,
   ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions))*8,2) total_logical_io_writes,
   ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions))*8,2) total_physical_io_reads,
   ROUND(CONVERT(float, SUM(rs.avg_clr_time*rs.count_executions))*0.001,2) total_clr_time,
   ROUND(CONVERT(float, SUM(rs.avg_dop*rs.count_executions))*1,0) total_dop,
   ROUND(CONVERT(float, SUM(rs.avg_query_max_used_memory*rs.count_executions))*8,2) total_query_max_used_memory,
   ROUND(CONVERT(float, SUM(rs.avg_rowcount*rs.count_executions))*1,0) total_rowcount,
   ROUND(CONVERT(float, SUM(rs.avg_log_bytes_used*rs.count_executions))*0.0009765625,2) total_log_bytes_used,
   ROUND(CONVERT(float, SUM(rs.avg_tempdb_space_used*rs.count_executions))*8,2) total_tempdb_space_used,
   SUM(rs.count_executions) count_executions,
   COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats rs
   JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
   JOIN sys.query_store_query q ON q.query_id = p.query_id
   JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE NOT (rs.first_execution_time > convert(datetime,'2024-08-20 14:00:00') OR rs.last_execution_time < convert(datetime,'2024-08-20 00:00:00'))
GROUP BY p.query_id, qt.query_sql_text, q.object_id
)
SELECT TOP (40)
   A.query_id query_id,
   A.object_id object_id,
   A.object_name object_name,
   A.query_sql_text query_sql_text,
   A.total_duration total_duration,
   A.total_cpu_time total_cpu_time,
   A.total_logical_io_reads total_logical_io_reads,
   A.total_logical_io_writes total_logical_io_writes,
   A.total_physical_io_reads total_physical_io_reads,
   A.total_clr_time total_clr_time,
   A.total_dop total_dop,
   A.total_query_max_used_memory total_query_max_used_memory,
   A.total_rowcount total_rowcount,
   A.total_log_bytes_used total_log_bytes_used,
   A.total_tempdb_space_used total_tempdb_space_used,
   ISNULL(B.total_query_wait_time,0) total_query_wait_time,
   A.count_executions count_executions,
   A.num_plans num_plans
FROM top_other_stats A LEFT JOIN top_wait_stats B on A.query_id = B.query_id and A.query_sql_text = B.query_sql_text and A.object_id = B.object_id
WHERE A.num_plans >= 1
ORDER BY total_cpu_time DESC

```sql
SELECT TOP (40)
   A.query_id "查询id",
   A.object_id "对象id",
   A.object_name "对象名称",
   A.query_sql_text "sql文本",
   A.total_duration "总计持续时间",
   A.total_cpu_time "总计cpu时间",
   A.total_logical_io_reads "总计逻辑读取次数",
   A.total_logical_io_writes "总计逻辑写入次数",
   A.total_physical_io_reads "总计物理读取次数",
   A.total_clr_time "总计clr时间",
   A.total_dop "总计dop",
   A.total_query_max_used_memory "总计内存占用率",
   A.total_rowcount "总计行计数",
   A.total_log_bytes_used "总计记录使用的内存",
   A.total_tempdb_space_used "总计已使用的临时db内存",
   ISNULL(B.total_query_wait_time,0) "总计等待时间",
   A.count_executions "执行计数",
   A.num_plans "计划计数"
FROM top_other_stats A LEFT JOIN top_wait_stats B on A.query_id = B.query_id and A.query_sql_text = B.query_sql_text and A.object_id = B.object_id
WHERE A.num_plans >= 1
ORDER BY total_cpu_time DESC

整个数据库的资源消耗情况：

最近一个月的数据库每天资源消耗情况

目前以下参数是：2024-07-20 00:00:00 - 2024-08-20 00:00:00 消耗情况

WITH DateGenerator AS
(
SELECT CAST(convert(datetime,'2024-07-20 00:00:00') AS DATETIME) DatePlaceHolder
UNION ALL
SELECT  DATEADD(d, 1, DatePlaceHolder)
FROM    DateGenerator
WHERE   DATEADD(d, 1, DatePlaceHolder) < convert(datetime,'2024-08-20 00:00:00')
),
WaitStats AS
(
SELECT
   ROUND(CONVERT(float, SUM(ws.total_query_wait_time_ms))*1,2) total_query_wait_time
FROM sys.query_store_wait_stats ws
   JOIN sys.query_store_runtime_stats_interval itvl ON itvl.runtime_stats_interval_id = ws.runtime_stats_interval_id
WHERE NOT (itvl.start_time > convert(datetime,'2024-08-20 00:00:00') OR itvl.end_time < convert(datetime,'2024-07-20 00:00:00'))
GROUP BY DATEDIFF(d, 0, itvl.end_time)
),
UnionAll AS
(
SELECT
   CONVERT(float, SUM(rs.count_executions)) as total_count_executions,
   ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) as total_duration,
   ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2) as total_cpu_time,
   ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads*rs.count_executions))*8,2) as total_logical_io_reads,
   ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions))*8,2) as total_logical_io_writes,
   ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions))*8,2) as total_physical_io_reads,
   ROUND(CONVERT(float, SUM(rs.avg_clr_time*rs.count_executions))*0.001,2) as total_clr_time,
   ROUND(CONVERT(float, SUM(rs.avg_dop*rs.count_executions))*1,0) as total_dop,
   ROUND(CONVERT(float, SUM(rs.avg_query_max_used_memory*rs.count_executions))*8,2) as total_query_max_used_memory,
   ROUND(CONVERT(float, SUM(rs.avg_rowcount*rs.count_executions))*1,0) as total_rowcount,
   ROUND(CONVERT(float, SUM(rs.avg_log_bytes_used*rs.count_executions))*0.0009765625,2) as total_log_bytes_used,
   ROUND(CONVERT(float, SUM(rs.avg_tempdb_space_used*rs.count_executions))*8,2) as total_tempdb_space_used,
   DATEADD(d, ((DATEDIFF(d, 0, rs.last_execution_time))),0 ) as bucket_start,
   DATEADD(d, (1 + (DATEDIFF(d, 0, rs.last_execution_time))), 0) as bucket_end
FROM sys.query_store_runtime_stats rs
WHERE NOT (rs.first_execution_time > convert(datetime,'2024-08-20 00:00:00') OR rs.last_execution_time < convert(datetime,'2024-07-20 00:00:00'))
GROUP BY DATEDIFF(d, 0, rs.last_execution_time)
)
SELECT 
   total_count_executions,
   total_duration,
   total_cpu_time,
   total_logical_io_reads,
   total_logical_io_writes,
   total_physical_io_reads,
   total_clr_time,
   total_dop,
   total_query_max_used_memory,
   total_rowcount,
   total_log_bytes_used,
   total_tempdb_space_used,
   total_query_wait_time,
   bucket_start,
bucket_end
FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY bucket_start ORDER BY bucket_start, total_duration DESC) AS RowNumber
FROM UnionAll , WaitStats
) as UnionAllResults
WHERE UnionAllResults.RowNumber = 1
OPTION (MAXRECURSION 0)

```sql
SELECT 
   total_count_executions "总计执行计数",
   total_duration "总计持续时间",
   total_cpu_time "总计CPU时间",
   total_logical_io_reads "总计逻辑读取计数",
   total_logical_io_writes "总计逻辑写入计数",
   total_physical_io_reads "总计物理读取次数",
   total_clr_time "总计clr时间",
   total_dop "总计dop",
   total_query_max_used_memory "总计内存占比率",
   total_rowcount "总计行计数",
   total_log_bytes_used "总计记录使用的内存",
   total_tempdb_space_used "总计已使用的临时db内存",
   total_query_wait_time "总计等待时间",
   bucket_start "间隔开始时间",
bucket_end "间隔结束时间"
FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY bucket_start ORDER BY bucket_start, total_duration DESC) AS RowNumber
FROM UnionAll , WaitStats
) as UnionAllResults
WHERE UnionAllResults.RowNumber = 1
OPTION (MAXRECURSION 0)

临时查询某一项的排序

(@interval_end_time datetimeoffset(7),@interval_start_time datetimeoffset(7),@results_row_count int)SELECT TOP (@results_row_count)
   p.query_id query_id,
   q.object_id object_id,
   ISNULL(OBJECT_NAME(q.object_id),'') object_name,
   qt.query_sql_text query_sql_text,
   ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2) total_cpu_time,
   SUM(rs.count_executions) count_executions,
   COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats rs
   JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
   JOIN sys.query_store_query q ON q.query_id = p.query_id
   JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
GROUP BY p.query_id, qt.query_sql_text, q.object_id
HAVING COUNT(distinct p.plan_id) >= 1
ORDER BY total_cpu_time DESC

```sql
(@interval_end_time datetimeoffset(7),@interval_start_time datetimeoffset(7),@results_row_count int)SELECT TOP (@results_row_count)
   p.query_id query_id,
   q.object_id object_id,
   ISNULL(OBJECT_NAME(q.object_id),'') object_name,
   qt.query_sql_text query_sql_text,
   ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration,
   SUM(rs.count_executions) count_executions,
   COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats rs
   JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
   JOIN sys.query_store_query q ON q.query_id = p.query_id
   JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
GROUP BY p.query_id, qt.query_sql_text, q.object_id
HAVING COUNT(distinct p.plan_id) >= 1
ORDER BY total_duration DESC

Oracle

Oracle提供了很多功能来进行分析：

AWR报告，获得性能统计信息的HTML报告，知道CPU使用率最高的SQL

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
  DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    l_dbid        => DBMS_WORKLOAD_REPOSITORY.GET_DBID,
    l_instance_num => DBMS_WORKLOAD_REPOSITORY.GET_INSTANCE_NUMBER,
    l_snap_id_begin => &begin_snap,
    l_snap_id_end   => &end_snap,
    l_report_name   => 'awr_report.html');
END;

V$SQL，直接查询该视图杨能获知SQL语句的性能统计信息

SELECT * FROM
(SELECT sql_id, sql_text, executions, elapsed_time, cpu_time, 
        buffer_gets, disk_reads, rows_processed
 FROM v$sql
 ORDER BY cpu_time DESC)
WHERE ROWNUM <= 10;

DBA_HIST_SQLSTAT视图，可以获知某个时间段内CPU使用率最高SQL

SELECT * FROM
(SELECT ss.sql_id, ss.plan_hash_value, 
        ss.cpu_time_total/1000000 as cpu_time_secs,
        ss.executions_total,
        ss.elapsed_time_total/1000000 as elapsed_time_secs,
        st.sql_text
 FROM dba_hist_sqlstat ss
 JOIN dba_hist_snapshot sn ON ss.snap_id = sn.snap_id
 JOIN dba_hist_sqltext st ON ss.sql_id = st.sql_id
 WHERE sn.begin_interval_time BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD')
                                  AND TO_DATE('2023-12-31', 'YYYY-MM-DD')
 ORDER BY ss.cpu_time_total DESC)
WHERE ROWNUM <= 10;

ASH，具体到某个会话活动的分析

SELECT * FROM
(SELECT ash.sql_id, 
        COUNT(*) as sample_count,
        SUM(ash.time_waited)/1000000 as wait_time_secs,
        st.sql_text
 FROM dba_hist_active_sess_history ash
 JOIN dba_hist_sqltext st ON ash.sql_id = st.sql_id
 WHERE ash.sample_time BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD')
                           AND TO_DATE('2023-12-31', 'YYYY-MM-DD')
 GROUP BY ash.sql_id, st.sql_text
 ORDER BY sample_count DESC)
WHERE ROWNUM <= 10;

如果觉得文章对你有用，请随意赞赏

数据库

数据库资源卡顿查询SQL

https://www.leticiafeng.com/archives/b7a3c03a-0f0c-4808-9359-cdd334341bc2

作者

LeticiaFENG

发布于

2024-08-23

更新于

2024-11-28

许可协议

CC BY 4.0

数据库资源卡顿查询SQL

Mysql

SQLServer

作者

发布于

更新于

许可协议

评论