# 系统资源监控
# 1. CPU监控
$ top
# 输出
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1234 dmdba 20 0 8192m 4096m 1024m R 50.0 50.0 1:30.00 dmserver
# 2. 内存监控
$ free -h
# 输出
total used free shared buff/cache available
Mem: 16G 8.0G 4.0G 1.0G 4.0G 7.0G
Swap: 4.0G 0B 4.0G
# 3. 磁盘IO监控
$ iostat -x
# 输出
device r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 10 20 0.1 0.2 32.0 0.1 5.0 2.0 6.0 1.0 30.0
# 4. 网络监控
$ netstat -tuln
# 输出
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 0.0.0.0:5236 0.0.0.0:* LISTEN

# 数据库资源监控
# 1. 会话监控
$ cd /dm/app/bin
$ ./disql SYSDBA/SYSDBA@fgedu.localhost:5236
SQL> select count(*) from v$session;
COUNT(*)
———- 更多视频教程www.fgedu.net.cn
100
SQL> select username, status, sql_text from v$session where status=’ACTIVE’;
# 2. SQL执行监控
SQL> select * from v$long_exec_sql;
# 3. 缓冲区监控
SQL> select * from v$bufpool;
# 4. 锁监控
SQL> select * from v$lock;
# 5. 系统状态监控
SQL> select * from v$sysstat;

# 监控脚本实施
# 1. 性能监控脚本
#!/bin/bash
# performance_monitor.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
date=$(date +%Y%m%d_%H%M%S)
log_file=”/var/log/performance_monitor_$date.log”
echo “Starting performance monitoring…” >> $log_file
echo “Date: $(date)” >> $log_file
# 系统资源监控
echo “System resource usage:” >> $log_file
top -n 1 >> $log_file
echo “Memory usage:” >> $log_file
free -h >> $log_file
echo “Disk IO:” >> $log_file
iostat -x >> $log_file
echo “Network connections:” >> $log_file
netstat -tuln >> $log_file
# 数据库资源监控
echo “Database sessions:” >> $log_file
/dm/app/bin/disql SYSDBA/SYSDBA@fgedu.localhost:5236 << EOF >> $log_file
select count(*) from v$session;
EOF
echo “Long running SQL:” >> $log_file
/dm/app/bin/disql SYSDBA/SYSDBA@fgedu.localhost:5236 << EOF >> $log_file
select * from v$long_exec_sql; 更多学习教程公众号风哥教程itpux_com
EOF
echo “Buffer pool status:” >> $log_file
/dm/app/bin/disql SYSDBA/SYSDBA@fgedu.localhost:5236 << EOF >> $log_file
select * from v$bufpool;
EOF
echo “Performance monitoring completed.” >> $log_file
# 2. 定期执行监控脚本
# 添加到crontab
# */5 * * * * /path/to/performance_monitor.sh
# 3. 告警脚本
#!/bin/bash
# performance_alert.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
# 检查CPU使用率
cpu_usage=$(top -n 1 | grep “Cpu(s)” | awk ‘{print $2}’ | sed ‘s/%//’)
if [ $(echo “$cpu_usage > 80” | bc) -eq 1 ]; then
echo “CPU usage is high: $cpu_usage%” | mail -s “Performance Alert: High CPU Usage” admin@fgedu.net.cn
fi
# 检查内存使用率
mem_usage=$(free -h | grep Mem | awk ‘{print $3}’ | sed ‘s/G//’)
mem_total=$(free -h | grep Mem | awk ‘{print $2}’ | sed ‘s/G//’)
mem_percent=$(echo “$mem_usage / $mem_total * 100” | bc)
if [ $(echo “$mem_percent > 80” | bc) -eq 1 ]; then
echo “Memory usage is high: $mem_percent%” | mail -s “Performance Alert: High Memory Usage” admin@fgedu.net.cn
fi
# 检查磁盘使用率
disk_usage=$(df -h / | tail -1 | awk ‘{print $5}’ | sed ‘s/%//’)
if [ $disk_usage -gt 80 ]; then
echo “Disk usage is high: $disk_usage%” | mail -s “Performance Alert: High Disk Usage” admin@fgedu.net.cn from DB视频:www.itpux.com
fi
# 检查数据库会话数
session_count=$(/dm/app/bin/disql SYSDBA/SYSDBA@fgedu.localhost:5236 << EOF select count(*) from v$session; EOF | grep -E '^[0-9]+$') if [ $session_count -gt 500 ]; then echo "Session count is high: $session_count" | mail -s "Performance Alert: High Session Count" admin@fgedu.net.cn fi

# 数据库参数调优
# 1. 内存参数调优
$ cd /dm/app/bin
$ ./disql SYSDBA/SYSDBA@fgedu.localhost:5236
SQL> alter system set MEMORY_POOL=200 scope=spfile;
SQL> alter system set BUFFER=8192 scope=spfile;
SQL> alter system set SORT_BUF_SIZE=32 scope=spfile;
SQL> alter system set HASH_BUF_SIZE=32 scope=spfile;
# 2. 日志参数调优
SQL> alter system set LOG_BUFFER_SIZE=16 scope=spfile;
SQL> alter system set REDO_BUFFER_SIZE=16 scope=spfile;
# 3. 并发参数调优
SQL> alter system set MAX_SESSIONS=500 scope=spfile;
SQL> alter system set SESSION_MEM_SIZE=1024 scope=spfile;
# 4. IO参数调优
SQL> alter system set IO_THR_GROUPS=4 scope=spfile;
SQL> alter system set IO_THR_PER_GROUP=4 scope=spfile;
# 5. 重启数据库
SQL> shutdown immediate;
SQL> startup;

# 数据库结构调优
# 1. 表结构优化
$ cd /dm/app/bin
$ ./disql SYSDBA/SYSDBA@fgedu.localhost:5236
# 分区表
SQL> create table fgedu.orders_partitioned (
order_id int,
order_date date,
status varchar(20)
) partition by range (order_date) (
partition p1 values less than (to_date(‘2026-01-01’, ‘YYYY-MM-DD’)),
partition p2 values less than (to_date(‘2026-04-01’, ‘YYYY-MM-DD’)),
partition p3 values less than (to_date(‘2026-07-01’, ‘YYYY-MM-DD’)),
partition p4 values less than (to_date(‘2026-10-01’, ‘YYYY-MM-DD’)),
partition p5 values less than (maxvalue)
);
# 2. 索引优化
SQL> create index idx_orders_order_date on fgedu.orders(order_date);
SQL> create index idx_orders_customer_id on fgedu.orders(customer_id);
SQL> create index idx_customers_name on fgedu.customers(customer_name);
# 3. 统计信息收集
SQL> analyze table fgedu.orders compute statistics;
SQL> analyze table fgedu.customers compute statistics;
# 4. 索引重建
SQL> alter index idx_orders_order_date rebuild;
SQL> alter index idx_orders_customer_id rebuild;
SQL> alter index idx_customers_name rebuild;

# SQL语句调优
# 1. 分析执行计划
$ cd /dm/app/bin
$ ./disql SYSDBA/SYSDBA@fgedu.localhost:5236
SQL> explain select * from fgedu.orders where order_date > ‘2026-01-01’;
PLAN
—————————-
1 #NSET2: [1000000, 1000000]
2 #PRJT2: [1000000, 1000000]
3 #CSCN2: [1000000, 1000000]
4 #SEGMENT: [1000000, 1000000] TSNAME: FGEDUTBS TABNAME: ORDERS
# 2. 优化SQL语句
SQL> select order_id, order_date, status from fgedu.orders where order_date > ‘2026-01-01’;
# 3. 创建索引
SQL> create index idx_orders_order_date on fgedu.orders(order_date);
# 4. 分析执行计划
SQL> explain select order_id, order_date, status from fgedu.orders where order_date > ‘2026-01-01’;
PLAN
—————————-
1 #NSET2: [1000000, 1000000]
2 #PRJT2: [1000000, 1000000]
3 #SSEK2: [1000000, 1000000]
4 #INDEX: [1000000, 1000000] IDX_ORDERS_ORDER_DATE
# 5. 测试执行时间
SQL> set timing on;
SQL> select order_id, order_date, status from fgedu.orders where order_date > ‘2026-01-01’;
# 执行时间：0.5秒

# 性能监控工具
# 1. DM管理工具
# 图形化管理工具，用于监控数据库性能
# 功能：实时监控、历史分析、告警管理等
# 2. Zabbix
# 第三方监控工具，用于监控数据库和服务器状态
# 配置步骤：
# 1. 安装Zabbix server和agent
# 2. 配置Zabbix agent
# 3. 创建监控模板
# 4. 添加监控项
# 5. 配置告警规则
# 3. Prometheus
# 第三方监控工具，用于监控系统性能
# 配置步骤：
# 1. 安装Prometheus
# 2. 配置Prometheus
# 3. 安装exporter
# 4. 配置Grafana
# 5. 创建监控面板
# 4. Shell脚本
# 自定义监控脚本，用于监控数据库性能
# 功能：收集性能数据、生成监控报告、发送告警等
# 5. 系统命令
# 系统自带的监控命令，用于监控系统资源
# 常用命令：top、iostat、vmstat、free等