shared_pool的默认值计算

Inkcup

1. 默认值规则
--------Oracle 9i及以前版本------------

    没有明确的默认值

    通常从 shared_pool_size 参数显式设置

--------Oracle 10g及以后版本----------

-- 默认值规则：
-- 如果使用自动内存管理（AMM）：
--   MEMORY_TARGET 或 MEMORY_MAX_TARGET 已设置
--   则 shared_pool_size 默认为 0（自动管理）

-- 如果使用自动共享内存管理（ASMM）：
--   SGA_TARGET 已设置
--   则 shared_pool_size 默认为 0（自动管理）

-- 如果手动管理SGA：
--   则 shared_pool_size 需要显式设置

2. 查看当前值的方法
-----------查看当前设置------------
sql

-- 查看当前shared_pool_size
SELECT name, value/1024/1024 "Size(MB)", isdefault
FROM v$parameter
WHERE name = 'shared_pool_size';

-- 或者
SHOW PARAMETER shared_pool_size;

-- 查看详细内存分配
SELECT * FROM v$sgainfo WHERE name LIKE '%Shared Pool%';

-- 查看共享池使用情况
SELECT pool, name, bytes/1024/1024 "Size(MB)"
FROM v$sgastat
WHERE pool = 'shared pool'
ORDER BY bytes DESC;

查看是否自动管理
sql

-- 检查内存管理模式
SELECT name, value
FROM v$parameter
WHERE name IN (
  'memory_target',
  'memory_max_target',
  'sga_target',
  'sga_max_target'
);

-- 判断是否自动管理：
-- 如果 memory_target > 0 → AMM启用
-- 如果 sga_target > 0 且 memory_target=0 → ASMM启用
-- 如果都为0 → 手动管理


3.不同版本的具体默认值
Oracle 11g/12c/19c：

-- 安装时的默认计算规则（如果未设置）：
-- 对于64位系统，通常为：
--   SGA_TARGET <= 1GB: shared_pool ≈ SGA的50%
--   SGA_TARGET 1-8GB:  shared_pool ≈ 512MB
--   SGA_TARGET > 8GB:  shared_pool ≈ SGA的10%（最大到2GB）

-- 实际典型默认值：
--   OLTP系统：通常 400MB - 1GB
--   数据仓库：可能更大


Oracle 21c：

-- 默认值可能更大，适应现代硬件
-- 典型范围：1GB - 4GB

4. 操作系统相关默认值

Linux 64位
-- 物理内存 vs SGA vs Shared Pool 的关系
-- 物理内存    SGA默认     Shared Pool默认
--  8GB        2-3GB       500MB-800MB
--  16GB       4-8GB       1GB-1.6GB
--  32GB       8-16GB      2GB-3.2GB
--  64GB       16-32GB     4GB-6.4GB

Windows
-- 通用模板：shared_pool_size = 150MB
-- 数据仓库：shared_pool_size = 300MB
-- OLTP：shared_pool_size = 200MB

-- 实际通过DBCA创建时会根据物理内存动态计算


5. DBCA创建的数据库默认值
不同模板的默认值
sql

-- 通用模板：shared_pool_size = 150MB
-- 数据仓库：shared_pool_size = 300MB
-- OLTP：shared_pool_size = 200MB

-- 实际通过DBCA创建时会根据物理内存动态计算

DBCA计算示例
bash

# DBCA计算公式（近似）
total_memory = 物理内存
shared_pool_size =
  CASE
    WHEN total_memory <= 1GB THEN 80MB
    WHEN total_memory <= 4GB THEN total_memory * 0.1
    WHEN total_memory <= 16GB THEN 400MB
    ELSE total_memory * 0.05
  END

6. 如何确定合适的值
监控共享池使用
sql

-- 检查共享池使用率
SELECT
  ROUND(SUM(bytes)/1024/1024, 2) "Total(MB)",
  ROUND(SUM(bytes - CASE name WHEN 'free memory' THEN bytes ELSE 0 END)/1024/1024, 2) "Used(MB)",
  ROUND(SUM(CASE name WHEN 'free memory' THEN bytes ELSE 0 END)/1024/1024, 2) "Free(MB)",
  ROUND((SUM(bytes - CASE name WHEN 'free memory' THEN bytes ELSE 0 END) / SUM(bytes)) * 100, 2) "Usage(%)"
FROM v$sgastat
WHERE pool = 'shared pool';

-- 检查库缓存命中率
SELECT
  1 - (SUM(reloads) / SUM(pins)) "Library Cache Hit Ratio"
FROM v$librarycache
WHERE namespace IN ('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER');

-- 检查空闲内存是否充足
SELECT * FROM v$sgastat
WHERE pool = 'shared pool'
AND name = 'free memory'
AND bytes < 100*1024*1024;  -- 少于100MB

根据负载类型调整
sql

-- OLTP系统（大量小事务）
-- shared_pool_size = SGA的15-20%

-- 数据仓库（复杂查询）
-- shared_pool_size = SGA的25-35%

-- 混合负载
-- shared_pool_size = SGA的20-25%

7. 调整建议
自动管理（推荐）
sql

-- 启用自动共享内存管理
ALTER SYSTEM SET SGA_TARGET = 4G SCOPE=SPFILE;
ALTER SYSTEM SET SHARED_POOL_SIZE = 0;  -- 设为0让Oracle自动管理

-- 或启用自动内存管理
ALTER SYSTEM SET MEMORY_TARGET = 8G SCOPE=SPFILE;
ALTER SYSTEM SET SHARED_POOL_SIZE = 0;
ALTER SYSTEM SET SGA_TARGET = 0;

手动设置（特殊情况）
sql

-- 如果必须手动设置
-- 计算当前合理值
SELECT
  ROUND(SUM(bytes)/1024/1024) current_mb,
  ROUND(SUM(bytes)*1.2/1024/1024) suggested_mb
FROM v$sgastat
WHERE pool = 'shared pool';

-- 设置新值
ALTER SYSTEM SET SHARED_POOL_SIZE = 1G SCOPE=SPFILE;

8. 最佳实践建议

    使用自动管理：除非有特殊需求

    监控使用率：保持命中率>95%

    避免频繁调整：稳定比最优值更重要

    考虑保留区：
    sql

-- 设置共享池保留区
ALTER SYSTEM SET SHARED_POOL_RESERVED_SIZE = 50M;

-----
总结：

精确的默认值不存在，它取决于：

    ✅ Oracle版本

    ✅ 安装方式（DBCA vs 手动）

    ✅ 内存管理模式（AMM/ASMM/手动）

    ✅ 物理内存大小

    ✅ 数据库模板选择

实际工作中：使用自动管理让Oracle动态调整，定期监控命中率和使用情况即可。