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

原创于 2025-08-30 12:38:50 发布 · 1k 阅读

27 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

AI辅助_面试宝典专栏收录该内容

584 篇文章

订阅专栏

在这里插入图片描述

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

1. 视图概述与作用

V$SEGMENT_STATISTICS 是 Oracle 19C 数据库中一个重要的动态性能视图，它提供了数据库段（表、索引、分区等）级别的详细性能统计信息。该视图可以帮助DBA和性能优化专家识别数据库中的热点段、资源竞争问题以及性能瓶颈。

主要作用：

监控数据库段的I/O性能特征
识别高负载的表和索引
诊断段级别的性能问题
提供段访问模式的详细统计
辅助进行存储优化和性能调优

2. 使用场景与应用价值

使用场景：

性能诊断：识别访问频繁或资源消耗高的数据库段
容量规划：基于段访问模式进行存储规划和优化
热点识别：发现数据库中的热点表和索引
资源优化：根据段统计信息调整存储结构和参数设置
问题排查：诊断由特定段引起的性能问题

应用价值：

提供段级别的详细性能指标
帮助识别性能瓶颈的根本原因
为存储优化和索引策略提供数据支持
辅助进行系统资源分配和规划

3. 字段详解与数据类型

以下是 V$SEGMENT_STATISTICS 视图的字段详细说明：

字段名	数据类型	描述
OWNER	VARCHAR2(128)	段的所有者
OBJECT_NAME	VARCHAR2(128)	段的对象名称
SUBOBJECT_NAME	VARCHAR2(128)	段的子对象名称（如分区名）
TABLESPACE_NAME	VARCHAR2(30)	段所在的表空间名称
TS#	NUMBER	表空间编号
OBJ#	NUMBER	对象编号
DATAOBJ#	NUMBER	数据对象编号
OBJECT_TYPE	VARCHAR2(18)	对象类型（TABLE、INDEX等）
STATISTIC_NAME	VARCHAR2(64)	统计信息名称
STATISTIC#	NUMBER	统计信息编号
VALUE	NUMBER	统计值的数值
CON_ID	NUMBER	容器ID（多租户环境）

STATISTIC_NAME字段的常见值及其含义：

STATISTIC_NAME值	描述
logical reads	逻辑读取次数（缓冲区获取）
physical reads	物理读取次数（磁盘读取）
physical reads direct	直接物理读取次数
physical writes	物理写入次数
physical writes direct	直接物理写入次数
segment scans	段扫描次数
row lock waits	行锁等待次数
ITL waits	ITL（事务槽）等待次数
buffer busy waits	缓冲区忙等待次数
global cache cr blocks served	RAC环境中服务的全局缓存CR块数
global cache current blocks served	RAC环境中服务的全局缓存当前块数

4. 相关视图与基表

数据字典视图：

DBA_SEGMENTS：包含所有段的信息
DBA_TABLES：包含所有表的信息
DBA_INDEXES：包含所有索引的信息
DBA_PART_TABLES：包含分区表的信息
DBA_TAB_STATISTICS：包含表统计信息

基表信息：

V$SEGMENT_STATISTICS 的数据主要来自 SGA 中的内存结构。相关的内部表包括：

X$KCFIO：I/O统计信息的内部表
X$BH：缓冲区头部的内部表
SYS.SEG$：段信息的基表
SYS.OBJ$：对象信息的基表
SYS.TS$：表空间信息的基表

5. 底层原理与工作机制

5.1 统计信息收集机制

Oracle 通过以下机制收集段统计信息：

缓冲区缓存跟踪：监控缓冲区缓存中的段访问模式
I/O系统统计：跟踪物理I/O操作到具体段
等待事件统计：关联等待事件到特定段
实时监控：持续收集和更新统计信息

5.2 内存管理

段统计信息存储在 SGA 的相关结构中：

统计信息在内存中累积和更新
定期将重要统计信息持久化到数据字典
实例重启后统计信息重置

5.3 统计类型分类

段统计信息分为几个主要类别：

I/O统计：逻辑读、物理读、物理写等
并发统计：行锁等待、ITL等待、缓冲区忙等待等
RAC统计：全局缓存相关统计
空间统计：段空间使用情况

6. 多租户环境中的考虑

在 CDB 环境中，V$SEGMENT_STATISTICS 视图包含 CON_ID 字段：

CON_ID = 0：表示根容器（CDB$ROOT）
CON_ID > 0：表示可插拔数据库（PDB）
可以连接 V$CONTAINERS 视图获取容器详细信息

7. 常用查询SQL示例

7.1 查看逻辑读最高的段

SELECT owner, object_name, object_type, tablespace_name,
       statistic_name, value
FROM V$SEGMENT_STATISTICS
WHERE statistic_name = 'logical reads'
ORDER BY value DESC
FETCH FIRST 10 ROWS ONLY;

7.2 识别物理I/O最高的表

SELECT owner, object_name, 
       SUM(CASE WHEN statistic_name = 'physical reads' THEN value ELSE 0 END) as physical_reads,
       SUM(CASE WHEN statistic_name = 'physical writes' THEN value ELSE 0 END) as physical_writes
FROM V$SEGMENT_STATISTICS
WHERE object_type = 'TABLE'
GROUP BY owner, object_name
ORDER BY physical_reads DESC
FETCH FIRST 10 ROWS ONLY;

7.3 查找缓冲区忙等待严重的段

SELECT owner, object_name, object_type, subobject_name,
       statistic_name, value
FROM V$SEGMENT_STATISTICS
WHERE statistic_name = 'buffer busy waits'
  AND value > 0
ORDER BY value DESC;

7.4 分析表空间级别的I/O模式

SELECT tablespace_name,
       SUM(CASE WHEN statistic_name = 'logical reads' THEN value ELSE 0 END) as logical_reads,
       SUM(CASE WHEN statistic_name = 'physical reads' THEN value ELSE 0 END) as physical_reads,
       ROUND(SUM(CASE WHEN statistic_name = 'physical reads' THEN value ELSE 0 END) / 
             NULLIF(SUM(CASE WHEN statistic_name = 'logical reads' THEN value ELSE 0 END), 0) * 100, 2) as physical_read_percent
FROM V$SEGMENT_STATISTICS
GROUP BY tablespace_name
ORDER BY logical_reads DESC;

7.5 监控热点索引

SELECT owner, object_name, tablespace_name,
       statistic_name, value
FROM V$SEGMENT_STATISTICS
WHERE object_type = 'INDEX'
  AND statistic_name IN ('logical reads', 'physical reads')
  AND value > 1000
ORDER BY statistic_name, value DESC;

7.6 在多租户环境中分析段统计

SELECT c.name as pdb_name, s.owner, s.object_name, s.object_type,
       s.statistic_name, s.value
FROM V$SEGMENT_STATISTICS s
JOIN V$CONTAINERS c ON s.con_id = c.con_id
WHERE s.value > 10000
  AND s.statistic_name = 'logical reads'
ORDER BY s.value DESC;

7.7 识别需要优化的段

SELECT owner, object_name, object_type,
       SUM(CASE WHEN statistic_name = 'row lock waits' THEN value ELSE 0 END) as row_lock_waits,
       SUM(CASE WHEN statistic_name = 'ITL waits' THEN value ELSE 0 END) as itl_waits,
       SUM(CASE WHEN statistic_name = 'buffer busy waits' THEN value ELSE 0 END) as buffer_busy_waits
FROM V$SEGMENT_STATISTICS
WHERE statistic_name IN ('row lock waits', 'ITL waits', 'buffer busy waits')
GROUP BY owner, object_name, object_type
HAVING SUM(value) > 0
ORDER BY row_lock_waits + itl_waits + buffer_busy_waits DESC;