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

🧠 Oracle 19C V$SHARED_POOL_RESERVED 视图详解

1. 视图概述与作用

V$SHARED_POOL_RESERVED 是 Oracle 数据库中一个重要的动态性能视图，专门用于监控和管理共享池保留区（Shared Pool Reserved Area）的使用情况和性能统计。

核心作用：

共享池保留区是共享池中一块特殊的内存区域，专门用于满足大内存对象（如大包、大SQL语句、大PL/SQL代码块）的分配请求。它的主要目的是：

1. 防止大内存分配失败：避免因共享池碎片化而导致的大内存分配请求失败（如ORA-04031错误）。
2. 减少共享池碎片：将大内存的分配与普通小内存分配隔离开，减少对共享池主要区域的碎片化影响。
3. 提高大对象分配性能：为需要连续大内存块的对象提供专门的分配区域，提高分配效率。

当一个大内存分配请求（大于 _SHARED_POOL_RESERVED_MIN_ALLOC 参数定义的值）发生时，Oracle 会尝试在共享池保留区中为其分配空间，而不是在主共享池中分配。

2. 字段含义详解

下表详细说明了 V$SHARED_POOL_RESERVED 视图中的各个字段：

字段名 (Column Name)	数据类型 (Datatype)	描述 (Description)
FREE_SPACE	NUMBER	保留区中当前空闲的内存大小（单位：字节）。表示尚未被分配的可用于未来大内存请求的空间。
AVG_FREE_SIZE	NUMBER	保留区中空闲内存块的平均大小（单位：字节）。
FREE_COUNT	NUMBER	保留区中当前空闲内存块的数量。
MAX_FREE_SIZE	NUMBER	保留区中最大的空闲内存块大小（单位：字节）。这是一个关键指标，表示当前能满足的最大单次分配请求。
USED_SPACE	NUMBER	保留区中当前已使用的内存大小（单位：字节）。
AVG_USED_SIZE	NUMBER	保留区中已使用内存块的平均大小（单位：字节）。
USED_COUNT	NUMBER	保留区中当前已分配的内存块数量。
MAX_USED_SIZE	NUMBER	保留区中曾经分配过的最大内存块大小（单位：字节）。
REQUEST_FAILURES	NUMBER	由于没有足够连续空间而失败的分配请求次数。如果这个值大于0且持续增长，说明保留区大小可能不足。
LAST_FAILURE_SIZE	NUMBER	最后一次失败的内存分配请求的大小（单位：字节）。
REQUEST_MISSES	NUMBER	在保留区中找不到足够大空闲块的请求次数（即使后来可能在主共享池中找到了空间）。
LAST_MISS_SIZE	NUMBER	最后一次未命中的内存分配请求的大小（单位：字节）。
CON_ID	NUMBER	容器ID。在多租户环境（CDB）中，标识该数据属于哪个容器（PDB）。对于非CDB环境，此值为0。

3. 使用场景

V$SHARED_POOL_RESERVED 在以下场景中非常重要：

1. 诊断和解决ORA-04031错误：当应用程序遇到 ORA-04031: unable to allocate X bytes of shared memory 错误时，此视图可以帮助确定是否是因为共享池保留区配置不当导致的。
2. 优化共享池保留区大小：通过分析 REQUEST_FAILURES、MAX_USED_SIZE 等指标，判断当前 SHARED_POOL_RESERVED_SIZE 参数的设置是否合理。
3. 监控大内存对象的使用模式：了解系统中大内存对象的分配模式、频率和大小分布。
4. 性能调优：当数据库性能问题与共享池竞争相关时，此视图可以提供关于大内存分配行为的 insights。

4. 底层原理与相关知识点

4.1 共享池保留区工作原理

共享池保留区是主共享池的一部分，但其管理是相对独立的：

1. 内存分配策略：当内存分配请求的大小超过 _SHARED_POOL_RESERVED_MIN_ALLOC 参数（默认值为4400字节）时，Oracle 会首先尝试在保留区中分配。
2. LRU管理：保留区有自己的LRU（最近最少使用）链表，用于管理其中内存块的年龄和重用。
3. 回退机制：如果在保留区中分配失败，Oracle 会尝试在主共享池中分配。如果主共享池中也失败，则会产生ORA-04031错误。
4. 内存回收：当保留区中的对象不再被引用时，其内存会被释放回保留区，而不是主共享池。

4.2 相关重要参数

• SHARED_POOL_RESERVED_SIZE：指定保留区的大小（字节）。通常设置为 SHARED_POOL_SIZE 的5%-10%，但不应超过50%。
• _SHARED_POOL_RESERVED_MIN_ALLOC：隐藏参数，定义使用保留区的最小分配阈值（默认4400字节）。大于此值的分配请求才会使用保留区。

4.3 ORA-04031错误处理

当出现ORA-04031错误时，V$SHARED_POOL_RESERVED 可以帮助诊断：

1. 检查 REQUEST_FAILURES 是否大于0
2. 查看 LAST_FAILURE_SIZE 了解需要多大的内存块
3. 比较 LAST_FAILURE_SIZE 和 MAX_FREE_SIZE 判断保留区是否足够大

5. 相关视图

视图名称	主要用途描述
V$SHARED_POOL_ADVICE	提供共享池大小调整建议，预测不同共享池大小下的性能表现。
V$SGASTAT	显示SGA的详细统计信息，包括共享池和保留区的使用情况。
V$LIBRARYCACHE	显示库缓存活动的统计信息，帮助诊断共享池性能问题。
V$SQLAREA	显示共享游标的详细信息，可用于识别占用大量内存的SQL语句。
V$DB_OBJECT_CACHE	显示当前缓存中的数据库对象信息，包括它们占用的内存大小。

6. 基表信息

V$SHARE{D}_{P}OO{L}_{R}ESERVED视图基于底层的\ast \ast X$ 表**构建，这些表是Oracle内部的虚拟内存表。根据内部结构，它很可能基于 **X$KSMRS\ast \ast 或类似的X$表。

重要提示：X$表是Oracle的内部结构，没有官方文档支持，其结构和命名可能随版本变化。强烈建议不要直接查询X$表，而是通过公开的V$视图获取信息。

7. 常用查询 SQL

7.1 保留区使用情况概览

SELECT 
    ROUND(free_space/1024/1024, 2) AS free_space_mb,
    ROUND(avg_free_size/1024, 2) AS avg_free_kb,
    free_count,
    ROUND(max_free_size/1024, 2) AS max_free_kb,
    ROUND(used_space/1024/1024, 2) AS used_space_mb,
    ROUND(avg_used_size/1024, 2) AS avg_used_kb,
    used_count,
    ROUND(max_used_size/1024, 2) AS max_used_kb,
    request_failures,
    ROUND(last_failure_size/1024, 2) AS last_failure_kb,
    request_misses,
    ROUND(last_miss_size/1024, 2) AS last_miss_kb
FROM 
    v$shared_pool_reserved;

7.2 保留区性能指标监控

SELECT 
    request_failures,
    request_misses,
    CASE 
        WHEN request_failures > 0 THEN 'WARNING: Failure detected - consider increasing SHARED_POOL_RESERVED_SIZE'
        WHEN request_misses > 100 THEN 'INFO: Some misses occurred, monitor'
        ELSE 'NORMAL: No significant issues detected'
    END AS status,
    ROUND(last_failure_size/1024, 2) AS last_failure_kb,
    ROUND(last_miss_size/1024, 2) AS last_miss_kb
FROM 
    v$shared_pool_reserved;

7.3 保留区与主共享池比较

SELECT 
    'Reserved Pool' AS pool_type,
    ROUND(used_space/1024/1024, 2) AS used_mb,
    ROUND(free_space/1024/1024, 2) AS free_mb,
    ROUND((used_space + free_space)/1024/1024, 2) AS total_mb
FROM 
    v$shared_pool_reserved
UNION ALL
SELECT 
    'Main Shared Pool',
    ROUND(SUM(bytes)/1024/1024, 2),
    (SELECT ROUND(value/1024/1024, 2) 
     FROM v$parameter 
     WHERE name = 'shared_pool_size') - ROUND(SUM(bytes)/1024/1024, 2),
    (SELECT ROUND(value/1024/1024, 2) 
     FROM v$parameter 
     WHERE name = 'shared_pool_size')
FROM 
    v$sgastat
WHERE 
    pool = 'shared pool'
    AND name != 'free memory'
    AND name != 'reserved pool';

7.4 保留区配置建议查询

SELECT 
    ROUND(used_space/1024/1024, 2) AS used_mb,
    ROUND(max_used_size/1024/1024, 2) AS max_ever_used_mb,
    request_failures,
    CASE 
        WHEN request_failures > 0 THEN
            'Current reserved size: ' || 
            (SELECT ROUND(value/1024/1024, 2) 
             FROM v$parameter 
             WHERE name = 'shared_pool_reserved_size') || 
            'MB. Consider increasing to at least ' || 
            CEIL(MAX(max_used_size * 1.2)/1024/1024) || 'MB'
        ELSE
            'Current reserved size: ' || 
            (SELECT ROUND(value/1024/1024, 2) 
             FROM v$parameter 
             WHERE name = 'shared_pool_reserved_size') || 
            'MB appears sufficient'
    END AS recommendation
FROM 
    v$shared_pool_reserved;

8. 最佳实践与注意事项

1. 合理设置保留区大小：SHARED_POOL_RESERVED_SIZE 通常设置为 SHARED_POOL_SIZE 的5%-10%。监控 MAX_USED_SIZE 和 REQUEST_FAILURES 来调整这个值。
2. 监控请求失败：如果 REQUEST_FAILURES 持续大于0，表明保留区可能太小，需要增加。
3. 分析大对象使用模式：定期检查 MAX_USED_SIZE 和 AVG_USED_SIZE 了解系统中的大内存对象需求。
4. 避免过度分配：保留区过大会浪费内存，导致主共享池可用内存减少。找到平衡点很重要。
5. 结合应用优化：对于持续需要大内存的应用，考虑优化代码（如拆分大PL/SQL包、使用批量处理等）而不仅仅是增加保留区大小。
6. 多租户环境考虑：在CDB环境中，需要为每个PDB单独考虑保留区的需求，因为不同PDB的工作负载特征可能不同。

通过正确使用 V$SHARED_POOL_RESERVED 视图，DBA可以有效地优化共享池性能，预防内存分配错误，并确保数据库稳定运行。

欢迎关注我的公众号《IT小Chen》