印刻数据库

外观

Oracle 会话级指标 ​

常用会话监控视图 ​

核心视图 ​

视图名称用途关键字段
V$SESSION会话基本信息和状态SID, SERIAL#, STATUS, USERNAME, PROGRAM
V$SESSION_WAIT会话等待事件EVENT, WAIT_TIME, SECONDS_IN_WAIT
V$SESSION_LONGOPS长时间运行操作OPNAME, SOFAR, TOTALWORK, TIME_REMAINING
V$SESSION_STAT会话统计信息STATISTIC#, NAME, VALUE
V$SESSION_EVENT会话事件历史EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED
V$SESSTAT会话级统计数据SID, STATISTIC#, VALUE
V$SQL_MONITORSQL执行监控SID, SQL_ID, STATUS, ELAPSED_TIME

辅助视图 ​

视图名称用途关键字段
V$LOCK会话锁信息SID, TYPE, ID1, ID2, LMODE, REQUEST
V$TRANSACTION事务信息SES_ADDR, STATUS, START_TIME, TOTAL_USED_UBLK
V$SESSION_CURSOR_CACHE游标缓存信息SID, CURSORS_OPEN, CURSORS_PINNED
V$PROCESS进程信息SPID, SID, PROGRAM, PGA_USED_MEM

关键会话级指标 ​

会话状态指标 ​

1. 会话状态 ​

sql
SELECT sid, serial#, username, status, program, machine
FROM v$session
WHERE status = 'ACTIVE';
  • ACTIVE：会话正在执行SQL语句
  • INACTIVE：会话处于空闲状态
  • KILLED：会话被标记为终止
  • CACHED：共享服务器模式下的缓存会话
  • SNIPED：长时间空闲的会话

2. 会话等待事件 ​

sql
SELECT sid, event, wait_time, seconds_in_wait, state
FROM v$session_wait
WHERE state != 'WAITING';

资源使用指标 ​

1. CPU使用 ​

sql
SELECT s.sid, s.username, ss.value AS cpu_usage
FROM v$session s,
     v$sesstat ss,
     v$statname sn
WHERE s.sid = ss.sid
  AND ss.statistic# = sn.statistic#
  AND sn.name = 'CPU used by this session'
  AND ss.value > 0
ORDER BY ss.value DESC;

2. 内存使用 ​

sql
SELECT s.sid, s.username, p.pga_used_mem, p.pga_alloc_mem
FROM v$session s,
     v$process p
WHERE s.paddr = p.addr
ORDER BY p.pga_used_mem DESC;

3. I/O操作 ​

sql
SELECT s.sid, s.username,
       ss1.value AS physical_reads,
       ss2.value AS physical_writes
FROM v$session s,
     v$sesstat ss1,
     v$sesstat ss2,
     v$statname sn1,
     v$statname sn2
WHERE s.sid = ss1.sid
  AND s.sid = ss2.sid
  AND ss1.statistic# = sn1.statistic#
  AND ss2.statistic# = sn2.statistic#
  AND sn1.name = 'physical reads'
  AND sn2.name = 'physical writes'
ORDER BY ss1.value + ss2.value DESC;

SQL执行指标 ​

1. 执行时间 ​

sql
SELECT s.sid, s.username, sm.sql_id, sm.elapsed_time,
       sm.cpu_time, sm.buffer_gets
FROM v$session s,
     v$sql_monitor sm
WHERE s.sid = sm.sid
  AND sm.status = 'EXECUTING'
ORDER BY sm.elapsed_time DESC;

2. 逻辑读 ​

sql
SELECT s.sid, s.username, ss.value AS logical_reads
FROM v$session s,
     v$sesstat ss,
     v$statname sn
WHERE s.sid = ss.sid
  AND ss.statistic# = sn.statistic#
  AND sn.name = 'session logical reads'
ORDER BY ss.value DESC;

3. 解析次数 ​

sql
SELECT s.sid, s.username, ss.value AS parse_calls
FROM v$session s,
     v$sesstat ss,
     v$statname sn
WHERE s.sid = ss.sid
  AND ss.statistic# = sn.statistic#
  AND sn.name = 'parse calls'
ORDER BY ss.value DESC;

会话级性能问题诊断 ​

1. 长时间运行的会话 ​

sql
SELECT s.sid, s.username, s.program, 
       ROUND(sl.elapsed_seconds/60, 2) AS minutes_running,
       sl.opname, ROUND(sl.sofar/sl.totalwork*100, 2) AS progress_pct
FROM v$session s,
     v$session_longops sl
WHERE s.sid = sl.sid
  AND sl.totalwork > 0
  AND sl.sofar < sl.totalwork
ORDER BY sl.elapsed_seconds DESC;

2. 阻塞会话 ​

sql
SELECT blocker.sid AS blocker_sid,
       blocker.username AS blocker_user,
       blocked.sid AS blocked_sid,
       blocked.username AS blocked_user,
       blocked.program AS blocked_program
FROM v$session blocker,
     v$session blocked
WHERE blocker.sid IN (
    SELECT blocking_session
    FROM v$session
    WHERE blocking_session IS NOT NULL
)
AND blocked.blocking_session = blocker.sid;

3. 死锁检测 ​

sql
SELECT * FROM v$lock
WHERE block = 1;

4. 游标使用异常 ​

sql
SELECT s.sid, s.username, 
       s.open_cursors,
       p.value AS max_cursors
FROM v$session s,
     v$parameter p
WHERE p.name = 'open_cursors'
ORDER BY s.open_cursors DESC;

会话级指标监控策略 ​

1. 实时监控 ​

  • 监控频率：根据系统负载和重要性，设置5-15分钟的监控间隔
  • 监控工具：
    • Oracle Enterprise Manager (OEM)
    • 自定义脚本
    • 第三方监控工具

2. 历史趋势分析 ​

  • 数据收集：使用AWR报告收集会话级历史数据
  • 分析周期：每日、每周、每月分析
  • 趋势识别：识别会话数量、资源使用的变化趋势

3. 告警设置 ​

指标告警阈值告警级别
会话数超过最大会话数的80%警告
单个会话CPU持续超过50%严重
会话等待时间单个会话等待超过30分钟严重
阻塞会话阻塞时间超过5分钟严重
游标使用率超过open_cursors的80%警告

会话级性能优化 ​

1. 会话参数优化 ​

优化PGA内存 ​

sql
ALTER SYSTEM SET workarea_size_policy = 'AUTO';
ALTER SYSTEM SET pga_aggregate_target = '2G';

优化游标设置 ​

sql
ALTER SYSTEM SET open_cursors = 300;
ALTER SYSTEM SET session_cached_cursors = 50;

2. 会话管理 ​

终止异常会话 ​

sql
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

限制会话资源 ​

sql
ALTER PROFILE default LIMIT
  SESSIONS_PER_USER 5
  CPU_PER_SESSION 10000
  LOGICAL_READS_PER_SESSION 100000;

3. SQL优化 ​

识别问题SQL ​

sql
SELECT s.sid, s.username, s.sql_id,
       t.sql_text,
       ss.value AS executions
FROM v$session s,
     v$sql t,
     v$sesstat ss,
     v$statname sn
WHERE s.sql_id = t.sql_id
  AND s.sid = ss.sid
  AND ss.statistic# = sn.statistic#
  AND sn.name = 'execute count'
ORDER BY ss.value DESC;

绑定变量使用 ​

sql
-- 检查硬解析比例
SELECT s.sid, s.username,
       (ss1.value / NULLIF(ss2.value, 0)) * 100 AS hard_parse_ratio
FROM v$session s,
     v$sesstat ss1,
     v$sesstat ss2,
     v$statname sn1,
     v$statname sn2
WHERE s.sid = ss1.sid
  AND s.sid = ss2.sid
  AND ss1.statistic# = sn1.statistic#
  AND ss2.statistic# = sn2.statistic#
  AND sn1.name = 'hard parse count'
  AND sn2.name = 'parse count'
  AND ss2.value > 0
ORDER BY hard_parse_ratio DESC;

监控脚本示例 ​

1. 会话状态监控脚本 ​

sql
-- session_monitor.sql
SET LINESIZE 200
SET PAGESIZE 100

COLUMN username FORMAT A15
COLUMN program FORMAT A25
COLUMN machine FORMAT A20
COLUMN status FORMAT A10

SELECT COUNT(*) AS total_sessions,
       COUNT(CASE WHEN status = 'ACTIVE' THEN 1 END) AS active_sessions,
       COUNT(CASE WHEN status = 'INACTIVE' THEN 1 END) AS inactive_sessions
FROM v$session
WHERE username IS NOT NULL;

SELECT sid, serial#, username, status, program, machine
FROM v$session
WHERE username IS NOT NULL
ORDER BY status, username;

2. 会话性能监控脚本 ​

sql
-- session_performance.sql
SET LINESIZE 200
SET PAGESIZE 100

COLUMN username FORMAT A15
COLUMN event FORMAT A30
COLUMN wait_time FORMAT 99999
COLUMN seconds_in_wait FORMAT 99999

SELECT s.sid, s.username, s.program,
       sw.event, sw.wait_time, sw.seconds_in_wait
FROM v$session s,
     v$session_wait sw
WHERE s.sid = sw.sid
  AND s.username IS NOT NULL
  AND sw.event NOT LIKE '%SQL*Net%'
ORDER BY sw.seconds_in_wait DESC;

常见问题（FAQ） ​

Q1: 如何快速识别消耗资源最多的会话？ ​

A1: 使用以下SQL语句识别资源消耗最多的会话：

sql
SELECT s.sid, s.username, s.program,
       ss1.value AS cpu_usage,
       ss2.value AS logical_reads,
       ss3.value AS physical_reads
FROM v$session s,
     v$sesstat ss1,
     v$sesstat ss2,
     v$sesstat ss3,
     v$statname sn1,
     v$statname sn2,
     v$statname sn3
WHERE s.sid = ss1.sid
  AND s.sid = ss2.sid
  AND s.sid = ss3.sid
  AND ss1.statistic# = sn1.statistic#
  AND ss2.statistic# = sn2.statistic#
  AND ss3.statistic# = sn3.statistic#
  AND sn1.name = 'CPU used by this session'
  AND sn2.name = 'session logical reads'
  AND sn3.name = 'physical reads'
  AND s.username IS NOT NULL
ORDER BY ss1.value + ss2.value DESC;

Q2: 如何处理长时间运行的会话？ ​

A2: 处理长时间运行的会话的步骤：

  1. 分析会话：确定会话正在执行的操作

    sql
    SELECT s.sid, s.username, sl.opname, sl.sofar, sl.totalwork,
       ROUND(sl.sofar/sl.totalwork*100, 2) AS progress
FROM v$session s,
     v$session_longops sl
WHERE s.sid = sl.sid;

  2. 评估影响：分析会话对系统的影响

  3. 采取措施：

    • 如果是正常的批处理任务，监控其进度
    • 如果是异常会话，终止并分析原因
    sql
    ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Q3: 如何避免会话阻塞问题？ ​

A3: 避免会话阻塞的方法：

  • 优化SQL：减少锁持有时间
  • 使用合理的事务隔离级别：根据业务需求选择合适的隔离级别
  • 实现超时机制：设置合理的语句超时
  • 定期检查：使用监控脚本定期检查阻塞情况
  • 应用设计：优化应用程序逻辑，减少长时间事务

Q4: 会话数过多怎么办？ ​

A4: 处理会话数过多的方法：

  1. 增加最大会话数：

    sql
    ALTER SYSTEM SET processes = 300 SCOPE=SPFILE;

  2. 清理空闲会话：

    • 设置idle_time参数
    sql
    ALTER PROFILE default LIMIT idle_time 30;
    • 使用脚本定期清理长时间空闲会话

  3. 优化连接池：

    • 配置应用连接池参数
    • 减少不必要的连接

  4. 分析会话来源：

    • 识别产生大量会话的应用或用户
    • 优化应用连接策略

Q5: 如何监控会话级别的SQL执行情况？ ​

A5: 监控会话级SQL执行的方法：

  1. 使用V$SQL_MONITOR：

    sql
    SELECT s.sid, s.username, sm.sql_id, sm.sql_text,
       sm.elapsed_time, sm.cpu_time, sm.buffer_gets
FROM v$session s,
     v$sql_monitor sm
WHERE s.sid = sm.sid
ORDER BY sm.elapsed_time DESC;

  2. 启用SQL_TRACE：

    sql
    ALTER SESSION SET sql_trace = TRUE;
-- 执行操作
ALTER SESSION SET sql_trace = FALSE;

  3. 使用DBMS_MONITOR：

    sql
    EXEC DBMS_MONITOR.session_trace_enable(session_id => sid);

Q6: 会话游标使用过高是什么原因？ ​

A6: 会话游标使用过高的常见原因：

  • 应用程序未关闭游标：代码中未正确关闭打开的游标
  • 复杂查询：复杂SQL语句会打开多个游标
  • 大量PL/SQL执行：PL/SQL过程会打开多个游标
  • 游标泄漏：应用程序中的游标泄漏问题

解决方案：

  • 检查应用程序代码，确保正确关闭游标
  • 增加open_cursors参数值
  • 优化SQL语句，减少游标使用
  • 使用绑定变量，减少硬解析

Q7: 如何区分正常会话和异常会话？ ​

A7: 区分正常会话和异常会话的方法：

特征正常会话异常会话
资源使用稳定在合理范围持续高资源使用
执行时间操作完成迅速长时间运行不结束
等待事件短暂等待长时间等待同一事件
状态变化状态正常切换长时间保持同一状态
会话行为符合业务逻辑不符合预期行为

Q8: 会话级指标与系统级指标有什么关系？ ​

A8: 会话级指标与系统级指标的关系：

  • 组成关系：系统级指标是所有会话级指标的汇总
  • 因果关系：会话级异常会导致系统级性能问题
  • 诊断流程：从系统级指标发现问题，到会话级指标定位具体原因
  • 监控策略：两者结合使用，系统级监控整体状况，会话级监控具体问题

Q9: 如何设置会话级别的参数？ ​

A9: 设置会话级别参数的方法：

  • 会话级别：仅影响当前会话

    sql
    ALTER SESSION SET parameter = 'value';

  • 系统级别：影响所有新会话

    sql
    ALTER SYSTEM SET parameter = 'value';

  • 用户级别：影响特定用户的所有会话

    sql
    ALTER USER username DEFAULT TABLESPACE users;

  • ** profile级别**：影响profile下所有用户的会话

    sql
    ALTER PROFILE profile_name LIMIT parameter value;

Q10: 会话监控对系统性能有影响吗？ ​

A10: 会话监控对系统性能的影响：

  • 轻微影响：监控本身会产生一定的系统开销

  • 影响因素：

    • 监控频率：频率越高，开销越大
    • 监控范围：监控的指标越多，开销越大
    • 监控工具：不同工具的开销不同

  • 优化建议：

    • 根据系统重要性设置合理的监控频率
    • 只监控必要的指标
    • 使用轻量级监控脚本
    • 在低峰期进行详细监控

通过合理的监控策略，可以在最小化性能影响的同时，获得有效的会话级指标数据。