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

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

1 视图概述与核心作用

V$SYSTEM_PARAMETER 是 Oracle 数据库中一个重要的动态性能视图，它显示了当前数据库实例中所有系统参数的详细信息，包括参数的当前值、默认值、是否可修改等属性。这个视图提供了数据库运行时参数的实时状态，是数据库管理和性能调优的重要工具。

与 V$PARAMETER 视图不同，V$SYSTEM_PARAMETER 专门显示系统级别的参数设置，而不包含会话级别的参数覆盖信息。

2 字段详解

以下是 V$SYSTEM_PARAMETER 视图包含的字段及其详细含义：

字段名称	数据类型	描述
NUM	NUMBER	参数的内部编号
NAME	VARCHAR2(80)	参数的名称
TYPE	NUMBER	参数的数据类型（1=布尔型，2=字符串型，3=整数型，4=参数文件，5=保留）
VALUE	VARCHAR2(4000)	参数的当前值
DISPLAY_VALUE	VARCHAR2(4000)	参数的显示值（格式化后的值）
ISDEFAULT	VARCHAR2(9)	指示当前值是否为默认值（TRUE/FALSE）
ISSES_MODIFIABLE	VARCHAR2(5)	指示参数是否可以在会话级别修改（TRUE/FALSE）
ISSYS_MODIFIABLE	VARCHAR2(9)	指示参数是否可以在系统级别修改（IMMEDIATE/DEFERRED/FALSE）
ISINSTANCE_MODIFIABLE	VARCHAR2(5)	指示参数是否可以在实例级别修改（TRUE/FALSE）
ISMODIFIED	VARCHAR2(10)	指示参数是否已被修改（MODIFIED/TRUE/FALSE）
ISADJUSTED	VARCHAR2(5)	指示参数是否已被Oracle自动调整（TRUE/FALSE）
ISDEPRECATED	VARCHAR2(5)	指示参数是否已弃用（TRUE/FALSE）
DESCRIPTION	VARCHAR2(255)	参数的描述信息
UPDATE_COMMENT	VARCHAR2(255)	参数更新的注释信息
HASH	NUMBER	参数的哈希值
CON_ID	NUMBER	容器ID（在多租户环境中）

3 底层原理与相关对象

3.1 底层原理

V$SYSTEM_PARAMETER 视图的数据来源于 Oracle 实例的内存结构：

1. 参数存储机制：

   • 参数信息存储在 SGA 的固定内存区域中
   • 实例启动时从参数文件（spfile 或 pfile）读取初始值
   • 运行时可以通过 ALTER SYSTEM 命令动态修改某些参数

2. 参数管理：

   • 参数分为静态参数和动态参数
   • 静态参数需要重启实例才能生效
   • 动态参数可以立即生效或在下一次登录时生效

3. 数据持久化：

   • 对参数的修改可以通过 SCOPE 选项指定是否持久化到 spfile
   • 使用 ALTER SYSTEM SET parameter=value SCOPE=SPFILE 可以将修改保存到服务器参数文件

3.2 相关视图

视图名称	描述
V$PARAMETER	显示参数信息，包括会话级别的覆盖
V$SPPARAMETER	显示服务器参数文件（spfile）中的参数设置
GV$SYSTEM_PARAMETER	在 RAC 环境中显示所有实例的参数信息
DBA_HIST_PARAMETER	AWR 历史数据中的参数信息

3.3 基表

V$SYSTEM_PARAMETER 基于 Oracle 内部的 X$ 表构建，主要是：

• X$KSPPI：参数信息表
• X$KSPPCV：参数当前值表

这些 X$ 表是 Oracle 内部的内存结构，不建议直接查询。

4 主要使用场景

1. 参数查询与验证：

   • 查看当前实例的参数设置
   • 验证参数修改是否生效

2. 性能调优：

   • 分析和调整数据库性能参数
   • 确认优化建议的参数设置

3. 故障排查：

   • 检查参数设置是否导致性能问题
   • 确认参数配置是否符合最佳实践

4. 数据库管理：

   • 监控参数变化
   • 准备数据库迁移或升级时的参数审查

5 常用SQL查询示例

5.1 查看所有参数的基本信息

SELECT name, value, display_value, isdefault, issys_modifiable
FROM v$system_parameter
ORDER BY name;

5.2 查找特定参数的值和属性

SELECT name, value, display_value, isdefault, 
       isses_modifiable, issys_modifiable, ismodified,
       description
FROM v$system_parameter
WHERE name = 'sga_max_size';

5.3 检查可以被动态修改的参数

SELECT name, value, issys_modifiable, description
FROM v$system_parameter
WHERE issys_modifiable != 'FALSE'
ORDER BY name;

5.4 查找已被修改的参数

SELECT name, value, isdefault, ismodified, description
FROM v$system_parameter
WHERE isdefault = 'FALSE' OR ismodified != 'FALSE'
ORDER BY name;

5.5 查看与内存相关的参数设置

SELECT name, value, display_value, description
FROM v$system_parameter
WHERE name LIKE '%memory%' 
   OR name LIKE '%sga%' 
   OR name LIKE '%pga%'
ORDER BY name;

5.6 检查已弃用的参数

SELECT name, value, description
FROM v$system_parameter
WHERE isdeprecated = 'TRUE'
ORDER BY name;

6 重要参数类别解读

6.1 内存相关参数

参数名称	描述	建议
sga_max_size	SGA 最大大小	设置为物理内存的 40-60%
sga_target	SGA 目标大小	自动管理 SGA 组件
pga_aggregate_target	PGA 目标大小	设置为物理内存的 20-25%
memory_target	总内存目标	自动管理 SGA 和 PGA

6.2 性能相关参数

参数名称	描述	建议
db_cache_size	数据库缓存大小	根据工作负载调整
shared_pool_size	共享池大小	足够大以避免ORA-04031错误
processes	最大进程数	根据并发连接数设置
sessions	最大会话数	通常设置为 processes * 1.1

6.3 安全相关参数

参数名称	描述	建议
sec_case_sensitive_logon	密码大小写敏感	设置为 TRUE 增强安全性
sec_max_failed_login_attempts	最大失败登录尝试次数	设置为 3-5 增强安全性
audit_trail	审计跟踪	根据需要设置为 DB 或 OS

7 参数管理最佳实践

7.1 参数修改方法

1. 动态修改：

   ALTER SYSTEM SET parameter_name = value SCOPE = MEMORY;
   -- 仅当前实例有效，重启后失效
   

2. 持久化修改：

   ALTER SYSTEM SET parameter_name = value SCOPE = SPFILE;
   -- 修改spfile，重启后生效
   

3. 立即生效并持久化：

   ALTER SYSTEM SET parameter_name = value SCOPE = BOTH;
   -- 立即生效并修改spfile
   

7.2 参数监控策略

1. 定期检查参数设置：

   -- 创建参数快照
   CREATE TABLE parameter_snapshot AS
   SELECT name, value, sysdate AS snapshot_time
   FROM v$system_parameter;
   

2. 比较参数变化：

   SELECT a.name, a.value AS old_value, b.value AS new_value
   FROM parameter_snapshot a
   JOIN v$system_parameter b ON a.name = b.name
   WHERE a.value != b.value;
   

8 多租户环境考虑

在 CDB 环境中，V$SYSTEM_PARAMETER 显示整个 CDB 的参数设置。如果需要查看特定 PDB 的参数，可以：

1. 切换到目标 PDB 后查询 V$SYSTEM_PARAMETER
2. 使用 CON_ID 字段过滤特定容器的数据

-- 查看所有容器的参数设置
SELECT con_id, name, value, isdefault
FROM v$system_parameter
WHERE name = 'sga_target'
ORDER BY con_id;

9 总结

V$SYSTEM_PARAMETER 是 Oracle 数据库管理和性能调优的重要工具，提供了系统参数的详细信息。通过这个视图，DBA 可以：

1. 查看和验证参数的当前设置
2. 识别可以被动态修改的参数
3. 检查参数是否已被修改或调整
4. 了解参数的属性和限制

有效的参数管理需要结合对参数含义的深入理解以及对应用程序需求的全面分析。定期审查参数设置是维护数据库高性能和稳定运行的关键实践。

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