关系型数据库性能：自动化 SQL 脚本包初版

下面是一个适用于多种主流关系型数据库的 自动化 SQL 脚本包初版（可按模块执行），目标是统一收集性能快照、锁等待、慢 SQL、复制状态等关键信息，便于排障或定期巡检。

---

📦 自动化 SQL 脚本包（跨数据库适配）

✅ 说明

• 每组脚本以 -- [数据库] 模块名 开头，适配不同 RDBMS
• 可封装为 Shell + SQL 执行工具（如 psql, mysql, sqlcmd, sqlplus）或用于采集系统
• 模块清单： 系统状态、会话信息、慢 SQL、锁等待、复制状态、参数配置、执行计划

---

1️⃣ 活跃会话/连接数

-- [MySQL] 活跃会话
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM information_schema.PROCESSLIST;

-- [PostgreSQL]
SELECT pid, usename, datname, client_addr, state, wait_event, query_start, query
FROM pg_stat_activity
WHERE state <> 'idle';

-- [Oracle]
SELECT sid, serial#, username, status, osuser, machine, event, blocking_session, sql_id
FROM v$session
WHERE username IS NOT NULL;

-- [SQL Server]
SELECT session_id, status, login_name, host_name, program_name, cpu_time, memory_usage, command
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

---

2️⃣ 慢 SQL / 热点 SQL

-- [MySQL]
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

-- [PostgreSQL]
SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC LIMIT 10;

-- [Oracle]
SELECT sql_id, sql_text, elapsed_time, buffer_gets, executions
FROM v$sqlarea
WHERE elapsed_time > 1000000
ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

-- [SQL Server]
SELECT TOP 10 qs.total_elapsed_time / qs.execution_count AS avg_time,
             qs.execution_count, st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_time DESC;

---

3️⃣ 锁等待与阻塞

-- [MySQL]
SHOW ENGINE INNODB STATUS;

-- [PostgreSQL]
SELECT pid, blocked_by, wait_event_type, query
FROM pg_blocking_pids(pid);

-- [Oracle]
SELECT a.sid, a.sql_id, a.event, b.sid AS blocker_sid
FROM v$session a JOIN v$session b ON a.blocking_session = b.sid;

-- [SQL Server]
SELECT blocking_session_id, session_id, wait_type, wait_time, wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

---

4️⃣ 复制/主备状态（高可用相关）

-- [MySQL]
SHOW SLAVE STATUS\G;

-- [PostgreSQL]
SELECT client_addr, state, sync_state, sent_lsn, replay_lsn
FROM pg_stat_replication;

-- [Oracle Data Guard]
SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE TARGET = 'STANDBY';

-- [SQL Server Always On]
SELECT ag.name, ar.replica_server_name, ars.role_desc, ars.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;

---

5️⃣ 当前运行 SQL + 执行计划（可选）

-- [MySQL]
EXPLAIN FORMAT=JSON SELECT ...;

-- [PostgreSQL]
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- [Oracle]
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- [SQL Server]
SET SHOWPLAN_ALL ON;
GO
-- your query
GO
SET SHOWPLAN_ALL OFF;

---

6️⃣ 数据库级资源利用率（只示意）

-- [MySQL]
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- [PostgreSQL]
SELECT * FROM pg_stat_bgwriter;
SELECT count(*) FROM pg_stat_activity;

-- [Oracle]
SELECT name, value FROM v$sysstat WHERE name IN ('CPU used by this session');

-- [SQL Server]
SELECT * FROM sys.dm_os_performance_counters WHERE counter_name LIKE '%Buffer%';

---

📁 文件结构建议（Shell 脚本调度）

/db_diag_package/
├── mysql/
│   ├── active_sessions.sql
│   ├── slow_sql.sql
│   └── ha_status.sql
├── postgres/
│   ├── ...
├── oracle/
│   ├── ...
├── sqlserver/
│   ├── ...
└── run_collect.sh

你可以用一个统一的 run_collect.sh 脚本调度这些 SQL 并输出结果到标准日志。

---

✅ 下一步可提供：

• 🛠️ Shell/PowerShell 执行框架
• 📦 自定义参数支持（如采集频率、输出目录）
• 🧠 采集结果自动分析脚本（推荐阈值、高风险识别）

---

🧩 数据库级资源利用率采集清单（详细）

以下是不同关系型数据库系统中 数据库级资源利用率详细采集 SQL 脚本清单，涵盖：

• 会话与连接数
• Buffer Pool / 内存使用
• I/O 与缓存命中率
• 事务 / 死锁情况
• CPU/磁盘利用
• 内部线程/进程状态

这些指标可作为数据库性能基线评估、性能分析与容量规划的关键输入。

---

✅ MySQL

推荐版本：MySQL 5.7/8.0+

-- 活跃连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';

-- 查询缓存命中率（如启用）
SHOW STATUS LIKE 'Qcache%';

-- InnoDB 缓存命中率
SELECT 
  variable_value AS innodb_buffer_pool_reads
FROM performance_schema.global_status 
WHERE variable_name = 'Innodb_buffer_pool_reads';

SELECT 
  variable_value AS total_read_requests
FROM performance_schema.global_status 
WHERE variable_name = 'Innodb_buffer_pool_read_requests';

-- 表/索引缓存命中率
SHOW STATUS LIKE 'Opened_tables';
SHOW STATUS LIKE 'Open_tables';

-- 临时表使用（是否大量磁盘临时表）
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

-- InnoDB 延迟写情况
SHOW ENGINE INNODB STATUS;

-- 文件 I/O 操作
SHOW GLOBAL STATUS LIKE 'Innodb_data%';

---

✅ PostgreSQL

推荐视图：pg_stat_database、pg_stat_bgwriter、pg_stat_io（15+）

-- 当前连接数
SELECT COUNT(*) AS current_connections FROM pg_stat_activity;

-- 每个数据库的连接数上限占比
SELECT datname, numbackends, 
       (numbackends::float / setting::float) * 100 AS usage_pct
FROM pg_stat_database, pg_settings
WHERE name = 'max_connections';

-- 缓存命中率（shared buffers）
SELECT datname,
       blks_hit, blks_read,
       ROUND(blks_hit::numeric / NULLIF((blks_hit + blks_read), 0), 4) AS cache_hit_ratio
FROM pg_stat_database
ORDER BY cache_hit_ratio ASC;

-- Checkpoint 与 Buffer 写入信息
SELECT * FROM pg_stat_bgwriter;

-- 临时文件使用情况
SELECT datname, temp_files, temp_bytes 
FROM pg_stat_database
ORDER BY temp_bytes DESC;

-- 死锁计数
SELECT datname, deadlocks FROM pg_stat_database ORDER BY deadlocks DESC;

---

✅ Oracle

依赖视图：v$sysstat, v$buffer_pool_statistics, v$system_event, v$session, v$sql, v$process

-- 当前会话/连接
SELECT COUNT(*) AS active_sessions FROM v$session WHERE status='ACTIVE';

-- CPU使用（Oracle会话）
SELECT name, value FROM v$sysstat WHERE name = 'CPU used by this session';

-- Buffer Cache 命中率
SELECT 
  ROUND((1 - (physical_reads / (db_block_gets + consistent_gets))) * 100, 2) AS buffer_cache_hit_ratio
FROM v$sysstat
WHERE name IN ('physical reads', 'db block gets', 'consistent gets');

-- Library Cache 命中率
SELECT namespace, gethitratio, pinhitratio FROM v$librarycache;

-- Dictionary Cache 命中率
SELECT (sum(gets - getmisses)) / sum(gets) AS dict_hit_ratio FROM v$rowcache;

-- Redo log I/O
SELECT * FROM v$log;

-- 并发与等待事件
SELECT event, total_waits, time_waited 
FROM v$system_event
ORDER BY time_waited DESC;

-- 并发进程数
SELECT COUNT(*) AS total_processes FROM v$process;

---

✅ SQL Server

适用于 SQL Server 2016+，使用 DMV 和 PerfMon 指标

-- 当前连接数
SELECT COUNT(*) AS current_connections FROM sys.dm_exec_sessions WHERE is_user_process = 1;

-- 缓存命中率（Buffer Cache Hit Ratio）
SELECT 
    cntr_value AS buffer_cache_hit_ratio
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Buffer cache hit ratio';

-- Plan Cache 命中率
SELECT 
    (CAST(usecounts AS FLOAT) / execution_count) AS plan_reuse_rate
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle);

-- 页读写情况（I/O 评估）
SELECT 
    counter_name, cntr_value 
FROM sys.dm_os_performance_counters 
WHERE counter_name IN ('Page reads/sec', 'Page writes/sec', 'Lazy writes/sec');

-- TempDB 使用（空间与争用）
SELECT SUM(user_object_reserved_page_count) * 8 AS user_space_kb,
       SUM(internal_object_reserved_page_count) * 8 AS internal_space_kb
FROM sys.dm_db_file_space_usage;

-- 死锁次数
SELECT 
    object_name, counter_name, cntr_value 
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Number of Deadlocks/sec';

---

🧰 补充建议（自动采集方式）

平台	推荐方法
Linux 系统数据库	Shell + crontab 定时调用 SQL，导出到日志
RDS (如 AWS/Azure)	CloudWatch + Enhanced Monitoring（部分指标如 CPU、IO）
自建平台	使用 Telegraf + InfluxDB + Grafana 或 Prometheus Exporter
日志分析	ELK Stack，结合数据库日志分析资源告警（如慢查询频发）

---

如果你需要：

• 📊 整理为 标准输出报表模板（Excel/CSV）
• 🛠️ 统一封装为 Shell + SQL 工具包
• 🧠 采集结果自动分析（如命中率过低告警）

打包成一个完整的 resource_diag_pack 脚本项目结构？