oracle数据库centos下的调优技巧

Oracle Database在CentOS下的性能调优技巧

一、操作系统级优化

1. 内核参数调优

调整CentOS内核参数以匹配Oracle数据库的资源需求，关键参数包括：

• 共享内存：kernel.shmmax（单进程最大共享内存，建议设为物理内存的85%）、kernel.shmall（共享内存总页数，shmmax/4096取整）、kernel.shmmni（共享内存段最大数量，默认4096，无需修改）；
• 信号量：kernel.sem（信号量集合，格式为SEMMSL（每集合信号量数，250）、SEMMNS（总信号量数，32000）、SEMOPM（每进程操作数，100）、SEMMNI（信号量集合数，128））；
• 文件描述符：fs.file-max（系统最大文件描述符，建议65536以上）、oracle soft nofile/oracle hard nofile（Oracle用户软/硬限制，分别设为1024/65536）；
• 网络参数：net.ipv4.ip_local_port_range（客户端端口范围，9000-65500）、net.core.rmem_default/net.core.wmem_default（读/写缓冲区默认大小，262144）、net.core.rmem_max/net.core.wmem_max（读/写缓冲区最大值，4194304/1048576）；
• 异步I/O：fs.aio-max-nr（异步I/O请求数，1048576）。
  修改后执行sysctl -p使参数生效。

2. 关闭不必要的服务

禁用防火墙（systemctl stop firewalld && systemctl disable firewalld）和SELinux（setenforce 0并修改/etc/selinux/config中SELINUX=disabled），减少系统资源消耗。

二、内存优化

1. SGA（系统全局区）调整

SGA是Oracle共享内存区域，需根据服务器内存大小合理分配：

• SGA Target：建议设为物理内存的70%-80%（如16GB内存设为12GB），使用ALTER SYSTEM SET sga_target=12G SCOPE=BOTH；
• PGA（进程全局区）调整：PGA用于存储进程私有数据，建议设为物理内存的10%-20%（如16GB内存设为2GB），使用ALTER SYSTEM SET pga_aggregate_target=2G SCOPE=BOTH；
• 内存管理：优先启用自动内存管理（MEMORY_TARGET），简化内存分配（如ALTER SYSTEM SET memory_target=14G SCOPE=BOTH）。

2. 大内存页（HugePages）配置

减少内存页表项数量，提升内存访问效率：

• 计算HugePages数量：内存大小（MB）× 0.8 × 0.8 / 2（如16GB内存设为5120）；
• 修改/etc/sysctl.conf：vm.nr_hugepages=5120，执行sysctl -p生效；
• 配置Oracle用户限制：在/etc/security/limits.conf中添加oracle hard memlock 8G（根据HugePages大小调整）。

三、数据库参数优化

1. 连接数设置

根据应用负载调整最大连接数，避免过多连接导致资源竞争：

• ALTER SYSTEM SET processes=200 SCOPE=SPFILE（进程数）；
• ALTER SYSTEM SET sessions=220 SCOPE=SPFILE（会话数，通常比processes多20%）；
• 重启数据库使参数生效。

2. 日志缓冲区优化

调整日志缓冲区大小，提升事务提交性能：

• 建议值：ALTER SYSTEM SET log_buffer=64M SCOPE=BOTH（默认1MB，适用于高并发事务场景）。

四、I/O优化

1. 磁盘布局优化

• 分离关键文件：将数据文件、redo log、归档日志、临时表空间存储在不同物理磁盘，减少磁盘竞争；
• 条带化（Striping）：使用LVM或硬件RAID将数据分散到多个磁盘，提升并行I/O能力；
• 避免动态扩展：创建表空间时预留足够空间（如初始大小设为预计容量的1.5倍），减少段动态扩展的开销。

2. I/O相关参数调整

• db_file_multiblock_read_count：控制全表扫描时每次I/O读取的块数，建议设为DB_BLOCK_SIZE×8（如8KB块大小设为64）；
• disk_asynch_io：启用异步I/O（ALTER SYSTEM SET disk_asynch_io=true SCOPE=BOTH），提升写进程（DBWn）效率；
• log_archive_max_processes：归档模式下增加ARCH进程数（如ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH），提升归档速度。

五、SQL与索引优化

1. SQL语句优化

• **避免SELECT ***：明确列出所需列，减少数据传输量；
• 使用绑定变量：如SELECT * FROM employees WHERE department_id=:dept_id，减少硬解析（降低CPU开销）；
• 优化查询逻辑：添加合适的WHERE条件过滤数据，避免全表扫描；
• 使用EXPLAIN PLAN：分析查询计划（如EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id=10），识别全表扫描、索引缺失等问题。

2. 索引优化

• 创建索引：为高频查询的WHERE、JOIN、ORDER BY列创建索引（如CREATE INDEX idx_emp_dept ON employees(department_id)）；
• 重建索引：定期重建碎片化索引（如ALTER INDEX idx_emp_dept REBUILD），提升索引访问效率；
• 删除无用索引：通过DBA_INDEXES视图查看未使用的索引（SELECT * FROM dba_indexes WHERE used='NO'），删除冗余索引。

3. 分区技术

• 表分区：将大表按时间、范围、列表等方式分区（如CREATE TABLE sales (sale_id NUMBER, sale_date DATE) PARTITION BY RANGE (sale_date) (...)），提升查询性能（仅扫描相关分区）；
• 分区索引：为分区表创建本地分区索引（CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL），减少索引维护开销。

六、监控与维护

1. 使用AWR/ADDM报告

• AWR报告：生成数据库性能快照（@?/rdbms/admin/awrrpt.sql），分析top SQL、等待事件等；
• ADDM报告：基于AWR数据生成诊断建议（@?/rdbms/admin/addmrpt.sql），针对性解决性能瓶颈。

2. 定期维护任务

• 更新统计信息：使用DBMS_STATS包收集表、索引的统计信息（EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME')），帮助优化器生成最佳执行计划；
• 重建碎片化对象：定期整理表碎片（ALTER TABLE table_name COALESCE）、回收未使用空间（ALTER TABLE table_name DEALLOCATE UNUSED）。