回到主页

FengLeticia

What else can I do ?

← 返回技术笔记
问题解决Mysql · 数据库

数据库资源卡顿查询SQL

FengLeticia 的头像FengLeticia约 33 分钟阅读
18px

Mysql

  1. 查询某个时间段内数据库执行记录之中，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]

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

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

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

    最近一个月的数据库每天资源消耗情况 (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

  1. 标识过去 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

    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

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

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

    目前以下参数是：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)

    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)

  3. 临时查询某一项的排序

    (@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

    (@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;

来点音乐？

Stay Alive
Stay Alive
00:00 / 00:00
© 2026 FengLeticiaGood morning, and in case I don't see you, good afternoon, good evening, and good night!
你好，欢迎来看看。
当前分区暂无公开文章。
↑ ↓ 选择 · Enter 阅读 · Esc 关闭