在Debian系统上监控SQL*Plus性能,可通过以下方式实现:
top
、htop
实时查看CPU、内存占用;vmstat
、iostat
监控磁盘I/O和系统负载;free
查看内存使用情况。netstat
或 ss
命令检查网络连接状态,确保SQL*Plus与数据库的网络延迟在合理范围内。SET AUTOTRACE ON
或 SET TIMING ON
记录SQL执行时间、CPU/内存消耗等。sqlplus -s user/password@db <<EOF SET AUTOTRACE ON EXECUTIONS; SELECT * FROM large_table; EOF
。EXPLAIN PLAN
或 DBMS_XPLAN.DISPLAY
查看SQL执行路径,定位低效查询。sqlplus -s user/password@db <<EOF EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id=10; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); EOF
。V$SQL
:查看SQL执行次数、耗时、缓冲区使用等。V$SESSION
:监控会话状态、等待事件。V$SYSTEM_EVENT
:分析系统级等待事件(如I/O、锁等待)。sqlplus -s user/password@db <<EOF SELECT sql_id, executions, elapsed_time/1000000 "Elapsed(s)", cpu_time/1000000 "CPU(s)" FROM V$SQL WHERE executions>100 ORDER BY elapsed_time DESC; EOF
。#!/bin/bash
sqlplus -s user/password@db <<EOF
SET HEADING OFF
SELECT sql_id, elapsed_time/1000000 "Elapsed(s)"
FROM V$SQL
WHERE elapsed_time > 5000000 AND status='EXECUTING'
ORDER BY elapsed_time DESC;
EXIT;
EOF
LINESIZE
、PAGESIZE
避免冗余输出。RLWRAP
提升命令行交互体验。cgroups
限制非关键进程的资源占用。参考来源:[1,2,3,5,6,7,8,9,10,11]