面试宝典:介绍下Oracle数据库动态性能视图 V$SERVICE_STATS

在这里插入图片描述

📊 Oracle 19C V$SERVICE_STATS 动态性能视图详解

1. ✨ 视图概述与作用

V$SERVICE_STATS 是 Oracle 19C 中一个重要的动态性能视图，它提供了按数据库服务（Service）分组的关键性能统计信息。这个视图将系统级的性能统计信息（类似于 V$SYSSTAT）下钻到服务级别，使得DBA能够监控和分析每个独立服务的性能特征。

核心作用：实现服务级别的性能监控和度量。它帮助回答"哪个服务正在消耗最多的系统资源？"以及"每个服务的性能特征是什么？"这类问题。
重要性：在多服务环境（特别是多租户和RAC环境）中，不同服务通常代表不同的应用程序或业务模块。通过此视图，DBA可以隔离和分析每个应用的性能表现，实现精细化的性能管理。

2. 🧐 主要应用场景

服务级性能监控：监控每个服务的资源消耗情况，如逻辑读、物理读、CPU使用、解析次数等。
多租户资源核算：在CDB环境中，通过服务与PDB的关联关系，统计每个PDB（租户）的资源使用情况，用于成本核算和资源分配决策。
应用性能分析：当特定应用程序出现性能问题时，通过其对应的服务统计信息，快速定位问题是出在数据库层面还是应用层面。
负载特征分析：分析不同服务的负载特征，例如：OLTP服务可能有很多短事务，而报表服务可能执行大量逻辑读。
Resource Manager调优：为Resource Manager配置提供数据支持，基于实际统计信息制定资源分配策略。

3. 📋 V$SERVICE_STATS 字段详解

V$SERVICE_STATS 视图的结构相对简单但信息丰富，每个统计项都有对应的数值。

字段名	数据类型	描述
SERVICE_NAME	VARCHAR2(64)	服务名称。标识这些统计信息所属的服务。值为 `SYS$BACKGROUND` 表示后台进程，`SYS$USERS` 表示未显式指定服务的用户进程。
STAT_ID	NUMBER	统计项目的标识符ID。与 `V$STATNAME.STAT_ID` 关联，用于确定统计项的类型。
VALUE	NUMBER	该统计项目的累计值。从实例启动开始累加，显示该服务在该统计项上的总值。
CON_ID	NUMBER	容器ID。在多租户环境中，标识该统计信息所属的容器。
INST_ID	NUMBER	实例ID。在RAC环境中，标识产生该统计信息的特定实例。

常见STAT_ID对应的统计项（部分）：

0: logons cumulative（累计登录次数）
6: parsed count（hard）（硬解析次数）
7: parsed count（total）（总解析次数）
9: session logical reads（会话逻辑读）
10: session stored procedure space（会话存储过程空间）
11: session uga memory（会话UGA内存）
12: session uga memory max（会话UGA内存最大值）
13: session pga memory（会话PGA内存）
14: session pga memory max（会话PGA内存最大值）
15: physical reads（物理读）
16: physical writes（物理写）
17: physical reads direct（直接物理读）
18: physical writes direct（直接物理写）
40: redo size（redo大小）
41: redo entries（redo条目）
97: execute count（执行次数）
100: user calls（用户调用次数）
104: recursive calls（递归调用次数）
139: DB time（DB时间）
140: DB CPU（DB CPU时间）

4. 🔗 相关视图与基表

4.1 相关性能视图

V$STATNAME：提供统计ID到统计名称的映射，必须与此视图关联才能获得有意义的统计信息。
V$SYSSTAT：系统级的性能统计，是所有服务统计信息的汇总。
V$SERVICES：服务的详细信息（网络名、状态等），可与本视图通过SERVICE_NAME关联。
V$SERVICE_EVENT：服务级别的等待事件统计，与本视图结合可全面了解服务性能。
V$SERVICEMETRIC：服务的度量指标，提供更聚合的性能数据。
DBA_HIST_SERVICE_STAT：AWR历史数据中保存的服务统计信息。

4.2 底层基表 (X$ Tables) 与原理

V$SERVICE_STATS 的数据来源于 Oracle SGA 中的内存数据结构。

底层原理：
1. 统计计数器：Oracle 内核为各种操作维护了大量的原子计数器。这些计数器按多个维度进行组织，其中一个重要维度就是服务。
2. 服务关联：当进程执行操作时，Oracle 会识别该进程所属的服务，并将相应统计计数累加到该服务的计数器上。
3. 内存存储：这些按服务分组的统计计数器存储在内存中的 X$ 表（如 X$KSLSS 或类似结构）中，这些是Oracle内部的未公开结构。
4. 视图暴露：V$SERVICE_STATS 视图通过 SQL 层查询这些 X$ 结构，将二进制数据转换为可读的数值。
数据生命周期：视图中的值是累计值，从实例启动开始不断累加，直到实例重启后被重置。要分析特定时间段的数据，需要采用两次采样求差值的方法（AWR自动完成此过程）。

5. ⚙️ 常用查询SQL

5.1 查询特定服务的所有统计信息（关联统计名称）

这是最常用的查询，显示指定服务的完整统计信息。

SELECT s.service_name,
       n.name AS stat_name,
       s.value,
       n.class,
       n.stat_id
FROM v$service_stats s
JOIN v$statname n ON s.stat_id = n.stat_id
WHERE s.service_name = 'OLTP_APP_SVC'  -- 替换为你的服务名
ORDER BY s.value DESC;

5.2 比较各服务的逻辑读消耗

识别哪个服务正在产生最多的逻辑读（内存压力主要来源）。

SELECT s.service_name,
       s.value AS logical_reads
FROM v$service_stats s
JOIN v$statname n ON s.stat_id = n.stat_id
WHERE n.name = 'session logical reads'
  AND s.value > 0
ORDER BY s.value DESC;

5.3 监控多租户环境下各PDB的服务统计

按容器和服务分析资源消耗，适用于CDB环境。

SELECT s.con_id,
       c.name AS pdb_name,
       s.service_name,
       n.name AS stat_name,
       s.value
FROM v$service_stats s
JOIN v$statname n ON s.stat_id = n.stat_id
JOIN v$containers c ON s.con_id = c.con_id
WHERE n.name IN ('session logical reads', 'physical reads', 'CPU used by this session')
  AND s.value > 0
ORDER BY s.con_id, s.value DESC;

5.4 分析服务的解析情况（软解析 vs 硬解析）

评估服务的SQL效率，高硬解析率可能意味着共享池问题。

SELECT s.service_name,
       hard_parse.value AS hard_parses,
       total_parse.value AS total_parses,
       ROUND((hard_parse.value / NULLIF(total_parse.value, 0)) * 100, 2) AS hard_parse_ratio
FROM v$service_stats hard_parse
JOIN v$service_stats total_parse ON hard_parse.service_name = total_parse.service_name
JOIN v$statname n1 ON hard_parse.stat_id = n1.stat_id
JOIN v$statname n2 ON total_parse.stat_id = n2.stat_id
WHERE n1.name = 'parse count (hard)'
  AND n2.name = 'parse count (total)'
  AND hard_parse.value > 0
ORDER BY hard_parse_ratio DESC;

5.5 查询服务的DB Time和DB CPU时间

了解每个服务消耗的数据库时间，这是性能分析的核心指标。

SELECT s.service_name,
       db_time.value / 1000000 AS db_time_seconds,  -- 转换为秒
       db_cpu.value / 1000000 AS db_cpu_seconds     -- 转换为秒
FROM v$service_stats db_time
JOIN v$service_stats db_cpu ON db_time.service_name = db_cpu.service_name
JOIN v$statname n1 ON db_time.stat_id = n1.stat_id
JOIN v$statname n2 ON db_cpu.stat_id = n2.stat_id
WHERE n1.name = 'DB time'
  AND n2.name = 'DB CPU'
ORDER BY db_time_seconds DESC;

6. 💎 核心知识点与原理

服务级统计的实现：Oracle 通过在内核中为每个服务维护独立的统计计数器来实现服务级统计。当进程执行操作时，会根据其所属服务更新相应的计数器。
统计维度：V$SERVICE_STATS 提供了与 V$SYSSTAT 相同的统计项，只是按服务进行了分组。这使得可以从系统级下钻到服务级进行分析。
性能开销：收集服务级统计信息会带来轻微的性能开销，因为需要根据服务名称进行计数器的选择和更新。但在大多数环境中，这种开销是可以接受的。
与Resource Manager的集成：服务统计信息与Resource Manager紧密相关。服务通常映射到资源消费组，而这些统计信息可以帮助验证资源分配策略的有效性。
AWR集成：AWR 快照会定期捕获 V$SERVICE_STATS 的内容并存储到 DBA_HIST_SERVICE_STAT 中，允许进行历史性能分析。
单位注意：不同统计项有不同的单位。例如，时间相关的统计（如DB time）通常以微秒为单位，而计数相关的统计（如逻辑读）以次数为单位。