Ubuntu Oracle性能调优参数有哪些

Ubuntu环境下Oracle数据库性能调优的核心参数及优化方向

一、内存管理参数（核心调优项）

内存是Oracle性能的关键瓶颈，合理配置SGA（共享内存区）与PGA（进程全局区）直接影响数据库缓存效率与并发处理能力。

SGA_TARGET：设置SGA的总大小（包含共享池、数据库缓冲区高速缓存、重做日志缓冲区等），启用自动共享内存管理（ASMM）。例如：ALTER SYSTEM SET SGA_TARGET=2G SCOPE=SPFILE;
PGA_AGGREGATE_TARGET：设置PGA的总大小（用于排序、哈希连接等操作），启用自动PGA管理（APMM）。例如：ALTER SYSTEM SET PGA_AGGREGATE_TARGET=1G SCOPE=SPFILE;
MEMORY_TARGET/MEMORY_MAX_TARGET：启用自动内存管理（AMM），让Oracle自动分配SGA与PGA的比例（需Oracle 11g及以上版本）。例如：ALTER SYSTEM SET MEMORY_TARGET=3G SCOPE=SPFILE; ALTER SYSTEM SET MEMORY_MAX_TARGET=3G SCOPE=SPFILE;
SHARED_POOL_SIZE：单独调整共享池大小（缓存SQL、PL/SQL代码、数据字典等），避免共享池碎片。例如：ALTER SYSTEM SET SHARED_POOL_SIZE=500M SCOPE=SPFILE;
DB_CACHE_SIZE：设置数据库缓冲区高速缓存大小（缓存数据块，提升查询性能），通常占SGA的50%-70%。例如：ALTER SYSTEM SET DB_CACHE_SIZE=1G SCOPE=SPFILE;
LOG_BUFFER：调整重做日志缓冲区大小（缓存重做日志条目，减少磁盘I/O），适用于高并发事务场景。例如：ALTER SYSTEM SET LOG_BUFFER=10M SCOPE=SPFILE;

二、SQL与索引优化参数

SQL执行效率是性能瓶颈的主要来源，通过索引与查询优化减少资源消耗。

SHARED_POOL_RESERVED_SIZE：保留共享池空间给常用对象（如高频SQL、大PL/SQL包），避免共享池溢出。例如：ALTER SYSTEM SET SHARED_POOL_RESERVED_SIZE=100M SCOPE=SPFILE;
CURSOR_SHARING：设置为FORCE或SIMILAR，减少硬解析（将SQL中的常量替换为绑定变量）。例如：ALTER SYSTEM SET CURSOR_SHARING=FORCE SCOPE=SPFILE;
OPTIMIZER_MODE：设置优化器模式（ALL_ROWS适合批处理，FIRST_ROWS_n适合交互式查询）。例如：ALTER SYSTEM SET OPTIMIZER_MODE=ALL_ROWS SCOPE=SPFILE;
STATISTICS_LEVEL：设置为TYPICAL或ALL，收集统计信息供优化器生成高效执行计划。例如：ALTER SYSTEM SET STATISTICS_LEVEL=TYPICAL SCOPE=SPFILE;

三、I/O与存储优化参数

I/O性能直接影响数据库响应速度，合理配置文件系统与存储参数提升吞吐量。

FILESYSTEMIO_OPTIONS：启用异步I/O（SETALL），提升文件读写效率（适用于Linux ext4/xfs文件系统）。例如：ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;
DISK_ASYNCH_IO：启用异步磁盘I/O（TRUE），减少I/O等待时间。例如：ALTER SYSTEM SET DISK_ASYNCH_IO=TRUE SCOPE=SPFILE;
DB_FILE_MULTIBLOCK_READ_COUNT：设置多块读取的块数（适用于全表扫描或索引快速全扫描），提升批量数据读取效率。例如：ALTER SYSTEM SET DB_FILE_MULTIBLOCK_READ_COUNT=16 SCOPE=SPFILE;
LOG_FILE_SIZE：增大重做日志文件大小（如每个日志文件100M-200M），减少日志切换频率。例如：ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/oradata/ORCL/redo04.log') SIZE 200M;
FAST_START_MTTR_TARGET：设置实例恢复的目标时间（单位：秒），平衡恢复速度与日志写入量。例如：ALTER SYSTEM SET FAST_START_MTTR_TARGET=30 SCOPE=SPFILE;

四、并行处理参数

利用多核CPU提升大数据量操作的效率（如全表扫描、排序、聚合）。

PARALLEL_MAX_SERVERS：设置并行服务器进程的最大数量（根据CPU核心数调整，如8核可设为16-32）。例如：ALTER SYSTEM SET PARALLEL_MAX_SERVERS=32 SCOPE=SPFILE;
PARALLEL_MIN_SERVERS：设置并行服务器进程的最小数量（保持空闲进程，避免频繁创建/销毁）。例如：ALTER SYSTEM SET PARALLEL_MIN_SERVERS=8 SCOPE=SPFILE;
PARALLEL_DEGREE_POLICY：设置为AUTO，让Oracle自动决定并行度（基于系统负载与资源使用情况）。例如：ALTER SYSTEM SET PARALLEL_DEGREE_POLICY=AUTO SCOPE=SPFILE;
表/索引并行度设置：通过ALTER TABLE或ALTER INDEX命令为特定对象设置并行度。例如：ALTER TABLE large_table PARALLEL (DEGREE 8); 或 ALTER INDEX idx_large_table PARALLEL (DEGREE 8);

五、分区技术参数

通过分区将大表拆分为小块，提升查询与管理效率（如分区裁剪、并行处理）。

分区策略选择：根据业务需求选择合适的分区键（如时间、地区、ID范围），常见分区类型包括范围分区（RANGE）、列表分区（LIST）、哈希分区（HASH）。例如：

CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
) PARTITION BY RANGE (sale_date) (
    PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

分区维护操作：定期添加/删除分区（如按月添加新分区、删除过期分区），减少全表扫描范围。例如：

ALTER TABLE sales ADD PARTITION p2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'));
ALTER TABLE sales DROP PARTITION p2023;

六、监控与诊断参数

通过内置工具收集性能数据，定位瓶颈（如SQL执行慢、资源争用）。

AWR（自动工作负载存储库）：定期生成AWR报告（默认每60分钟收集一次），分析SQL执行计划、等待事件、资源消耗。例如：@?/rdbms/admin/awrrpt.sql（生成HTML格式报告）。
ADDM（自动数据库诊断监视器）：基于AWR数据生成诊断建议（如调整内存、优化SQL），直接指导性能优化。例如：@?/rdbms/admin/addmrpt.sql。
ASH（活动会话历史）：收集活跃会话的实时数据，分析当前性能问题（如锁等待、I/O瓶颈）。例如：@?/rdbms/admin/ashrpt.sql。

七、操作系统级关联参数

Oracle性能受操作系统环境影响，需配合调整Ubuntu内核与文件系统参数。

内核参数调优：调整shmmax（共享内存最大值，需大于SGA大小）、shmall（共享内存总页数）、semmsl（信号量集最大数量）等参数。例如：

echo "kernel.shmmax = 4294967296" >> /etc/sysctl.conf  # 设置为4GB（大于SGA大小）
echo "kernel.shmall = 1048576" >> /etc/sysctl.conf    # 设置为1M页（4GB/4KB）
sysctl -p  # 生效配置

文件系统优化：使用ext4或xfs文件系统（支持大文件、高并发），挂载时添加noatime（不更新访问时间）、nodiratime（不更新目录访问时间）选项。例如：
```
mount -o remount,noatime,nodiratime /u01/oradata
```
关闭不必要的服务：通过systemctl disable命令关闭不用的服务（如蓝牙、打印服务），释放系统资源。