centos oracle性能优化技巧

一、操作系统级优化

1. 内核参数调优：调整/etc/sysctl.conf文件，优化系统对Oracle的支持。关键参数包括：fs.aio-max-nr = 1048576（异步I/O最大请求数）、fs.file-max = 6815744（系统最大文件描述符数）、kernel.sem = 250 32000 100 128（信号量参数）、net.ipv4.ip_local_port_range = 9000 65500（本地端口范围）、net.core.rmem_default/wmem_default = 262144（接收/发送缓冲区默认大小）、net.core.rmem_max/wmem_max = 4194304/1048586（接收/发送缓冲区最大大小）、vm.swappiness = 10（减少内存交换，优先使用物理内存）、kernel.shmmax = $(free|grep Mem|awk '{print int($2*1024*0.85)}')（共享内存最大值，取物理内存85%）、kernel.shmall = $(free|grep Mem|awk '{print int(($2*1024*0.85)/4096)}')（共享内存总页数）、vm.nr_hugepages = $(free -m|grep Mem|awk '{print int(($2*0.8*0.8)/2)}')（大页数量，提升SGA访问效率）。修改后执行sysctl -p使配置生效。
2. 文件系统优化：选择高性能文件系统（如XFS），挂载时添加noatime,nodiratime选项（减少文件访问时间更新），例如：mount -o noatime,nodiratime /dev/sda1 /oracle。
3. 关闭不必要服务：停止防火墙（systemctl stop firewalld）和SELinux（setenforce 0），若无需远程访问可禁用SSH反向隧道等服务，减少系统资源消耗。
4. 用户资源限制：编辑/etc/security/limits.conf，为Oracle用户设置上限：oracle soft nproc 2047（软进程数）、oracle hard nproc 16384（硬进程数）、oracle soft nofile 1024（软文件描述符数）、oracle hard nofile 65536（硬文件描述符数）、oracle soft memlock $(free|grep Mem|awk '{print int(2*0.90*1024)}')（软内存锁定限制）、oracle hard memlock $(free|grep Mem|awk '{print int(2*0.90*1024)}')（硬内存锁定限制）。编辑/etc/profile，为Oracle用户添加环境变量：if [ $USER = "oracle" ]; then ulimit -u 16384; ulimit -n 65536; fi，执行source /etc/profile生效。

二、数据库参数优化

1. SGA/PGA内存调整：根据服务器内存大小合理分配SGA（共享内存区）和PGA（进程全局区）。例如，对于16GB内存服务器，可设置ALTER SYSTEM SET sga_target = 8G SCOPE=BOTH;（SGA目标大小）、ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=BOTH;（PGA聚合目标大小）。启用自动内存管理（AMM）可简化配置：ALTER SYSTEM SET memory_target = 10G SCOPE=BOTH; ALTER SYSTEM SET memory_max_target = 12G SCOPE=BOTH;。
2. 日志缓冲区优化：调整日志缓冲区大小（默认1MB~6MB），适用于高并发事务场景。例如：ALTER SYSTEM SET log_buffer = 64M SCOPE=SPFILE;，重启数据库生效。
3. 连接数优化：根据应用负载设置最大连接数，避免过多连接导致资源竞争。例如：ALTER SYSTEM SET processes = 300 SCOPE=SPFILE; ALTER SYSTEM SET sessions = 335 SCOPE=SPFILE;（sessions通常为processes的1.1倍加5）。

三、索引优化

1. 合理创建索引：为查询频繁的列（如WHERE、JOIN、ORDER BY子句中的列）、外键列创建索引。例如：CREATE INDEX idx_employee_name ON employees(last_name);。根据查询模式选择索引类型：B-tree索引（默认，适用于等值/范围查询）、位图索引（适用于低基数列，如性别、状态）。
2. 定期重建索引：索引碎片会影响查询效率，定期重建可恢复索引性能。例如：ALTER INDEX idx_employee_name REBUILD ONLINE;（ONLINE选项允许重建时继续使用索引）。
3. 删除无用索引：通过DBA_INDEXES视图分析未使用的索引（SELECT * FROM dba_indexes WHERE last_analyzed IS NULL;），删除无用索引以减少插入、更新、删除操作的开销。

四、SQL语句优化

1. 使用EXPLAIN PLAN分析：通过EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 30;查看查询执行计划，识别全表扫描、索引未使用等问题。结合SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);查看详细分析结果。
2. 避免SELECT * 和冗余字段：明确列出需要的列，减少数据传输量。例如：SELECT employee_id, last_name, salary FROM employees WHERE department_id = 30;。
3. 使用绑定变量：避免硬解析（每次执行SQL都需解析），提高SQL执行效率。例如：SELECT * FROM employees WHERE department_id = :dept_id;，通过应用程序传递:dept_id参数。
4. 优化连接操作：选择合适的连接类型（如哈希连接适用于大表连接、嵌套循环连接适用于小表连接）。使用查询提示引导优化器，例如：SELECT /*+ HASH_JOIN(e, d) */ * FROM employees e JOIN departments d ON e.department_id = d.department_id;。
5. 避免全表扫描：通过WHERE子句过滤数据，创建合适的索引。例如：SELECT * FROM employees WHERE department_id = 30 AND salary > 5000;（若department_id和salary有复合索引，可避免全表扫描）。

五、分区技术优化

1. 分区表创建：对大表按时间、范围、列表等方式分区，减少查询扫描的数据量。例如，按时间分区：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')));。
2. 分区索引：对分区表创建本地分区索引（每个分区独立索引），提高分区查询效率。例如：CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;。定期维护分区索引（如重建、合并），保持索引有效性。
3. 分区维护：根据业务需求添加、删除分区。例如，添加2025年分区：ALTER TABLE sales ADD PARTITION p2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'));；删除2023年分区：ALTER TABLE sales DROP PARTITION p2023;。

六、并发与事务管理

1. 合理设置并行度：对大表查询、DML操作设置并行度，利用多核CPU提高处理效率。例如，设置表并行度：ALTER TABLE sales PARALLEL (DEGREE 4);；使用并行提示：SELECT /*+ PARALLEL(sales, 4) */ * FROM sales WHERE sale_date BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-12-31', 'YYYY-MM-DD');。
2. 事务设计优化：控制事务大小（避免长事务），减少锁争用。例如，将大事务拆分为多个小事务，及时提交（COMMIT）或回滚（ROLLBACK）。使用SELECT FOR UPDATE NOWAIT避免等待锁，提高并发性能。

七、监控与维护

1. AWR/ADDM报告分析：定期生成AWR（自动工作负载存储库）和ADDM（自动数据库诊断监视器）报告，识别性能瓶颈。生成AWR报告：@?/rdbms/admin/awrrpt.sql（选择两个快照时间点）；生成ADDM报告：@?/rdbms/admin/addmrpt.sql（基于AWR报告）。
2. 定期维护任务：更新统计信息（优化器依赖统计信息生成执行计划）：EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME');；重建碎片化严重的索引；备份数据库（如使用RMAN）；清理归档日志（避免占用过多磁盘空间）。