Oracle 性能最佳实践

性能最佳实践概述

Oracle 性能最佳实践是确保数据库系统高效运行的关键指南，涵盖了从设计、开发到运维的各个阶段。良好的性能最佳实践可以显著提高系统的响应速度、吞吐量和可扩展性，减少资源消耗，降低运营成本。

性能最佳实践的目标

提高系统的响应速度和吞吐量
降低 CPU、内存、I/O 等资源消耗
提高系统的可扩展性和可靠性
减少系统维护成本
确保系统在高负载下的稳定运行

性能最佳实践的范围

数据库设计和架构
SQL 和 PL/SQL 开发
索引和分区设计
内存和存储管理
并发控制和锁定
备份和恢复策略
监控和调优

数据库设计最佳实践

1. 数据模型设计

规范化与反规范化

遵循适当的规范化原则（通常到第三范式），减少数据冗余
根据查询需求适当反规范化，提高查询性能
使用视图封装复杂查询，保持数据一致性

sql

-- 示例：反规范化设计，添加冗余列提高查询性能
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    customer_name VARCHAR2(100), -- 冗余列，避免 JOIN 操作
    order_date DATE,
    total_amount NUMBER(10,2)
);

数据类型选择

选择合适的数据类型，避免不必要的存储空间浪费
优先使用 VARCHAR2 而非 CHAR，除非长度固定
使用 NUMBER 而非 VARCHAR2 存储数值类型
对于日期和时间，使用 DATE 或 TIMESTAMP 类型

sql

-- 示例：合适的数据类型选择
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(20), -- 变长字符串，节省空间
    last_name VARCHAR2(25),
    hire_date DATE, -- 日期类型，便于日期运算
    salary NUMBER(8,2), -- 数值类型，便于数值运算
    active_flag CHAR(1) -- 固定长度，只有 Y/N 两种值
);

2. 表设计

分区表设计

对于大表，考虑使用分区表，提高查询和维护性能
根据业务需求选择合适的分区类型（范围、列表、哈希、复合等）
合理设计分区键，确保数据均匀分布

sql

-- 示例：范围分区表设计
CREATE TABLE sales (
    sale_id NUMBER PRIMARY KEY,
    sale_date DATE,
    product_id NUMBER,
    quantity NUMBER,
    amount NUMBER(10,2)
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_q1_2023 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
    PARTITION sales_q2_2023 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
    PARTITION sales_q3_2023 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
    PARTITION sales_q4_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);

索引组织表（IOT）

对于频繁通过主键访问的表，考虑使用索引组织表
减少 I/O 操作，提高查询性能
适合字典表、配置表等

sql

-- 示例：索引组织表设计
CREATE TABLE product_categories (
    category_id NUMBER PRIMARY KEY,
    category_name VARCHAR2(50),
    description VARCHAR2(200)
) ORGANIZATION INDEX;

SQL 开发最佳实践

1. 查询优化

避免 SELECT *

明确指定所需的列名，避免返回不必要的数据
减少网络传输和 I/O 开销
提高缓存效率

sql

-- 示例：避免 SELECT *
-- 不好的做法
SELECT * FROM employees;

-- 好的做法
SELECT first_name, last_name, email, hire_date FROM employees;

使用绑定变量

使用绑定变量，提高 SQL 重用率
减少硬解析，降低 CPU 消耗
防止 SQL 注入

sql

-- 示例：使用绑定变量
DECLARE
    v_dept_id NUMBER := 60;
BEGIN
    EXECUTE IMMEDIATE 'SELECT * FROM employees WHERE department_id = :dept_id'
    USING v_dept_id;
END;
/

优化 WHERE 子句

避免在 WHERE 子句中对列使用函数或表达式
使用 BETWEEN 替代 >= 和 <=
使用 IN 替代多个 OR 条件
避免使用 NOT IN，考虑使用 NOT EXISTS 或 LEFT JOIN ... IS NULL

sql

-- 示例：优化 WHERE 子句
-- 不好的做法
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

-- 好的做法
SELECT * FROM employees WHERE last_name = 'SMITH'; -- 假设数据已经是大写

-- 或者创建函数索引
CREATE INDEX idx_emp_last_name_upper ON employees(UPPER(last_name));

2. JOIN 优化

合理选择 JOIN 顺序

将小表放在前面，减少后续 JOIN 的数据量
使用 INNER JOIN 替代 WHERE 子句中的连接条件
避免复杂的嵌套 JOIN

sql

-- 示例：合理的 JOIN 顺序
SELECT e.first_name, e.last_name, d.department_name
FROM employees e -- 小表在前
JOIN departments d ON e.department_id = d.department_id;

使用合适的 JOIN 类型

根据业务需求选择合适的 JOIN 类型
INNER JOIN：只返回匹配的行
LEFT JOIN：返回左表所有行和右表匹配的行
RIGHT JOIN：返回右表所有行和左表匹配的行
FULL JOIN：返回左右表所有行

sql

-- 示例：使用合适的 JOIN 类型
-- 查询所有员工及其部门信息，包括没有部门的员工
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

3. 子查询优化

避免相关子查询

相关子查询会逐行执行，性能较差
考虑使用 JOIN 替代相关子查询

sql

-- 示例：避免相关子查询
-- 不好的做法
SELECT e.first_name, e.last_name
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary) FROM employees WHERE department_id = e.department_id
);

-- 好的做法
SELECT e.first_name, e.last_name
FROM employees e
JOIN (
    SELECT department_id, AVG(salary) avg_salary
    FROM employees
    GROUP BY department_id
) d ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

使用 WITH 子句

使用 WITH 子句（CTE）提高复杂查询的可读性和性能
Oracle 会缓存 WITH 子查询的结果，减少重复计算

sql

-- 示例：使用 WITH 子句
WITH high_salary_emp AS (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE salary > 10000
)
SELECT *
FROM high_salary_emp
JOIN departments d ON high_salary_emp.department_id = d.department_id;

索引最佳实践

1. 索引设计

选择合适的索引类型

B-tree 索引：适合等值查询和范围查询
位图索引：适合低基数列和复杂查询
函数索引：适合经常使用函数的列
分区索引：适合分区表

sql

-- 示例：选择合适的索引类型
-- B-tree 索引，适合等值查询
CREATE INDEX idx_emp_department_id ON employees(department_id);

-- 位图索引，适合低基数列
CREATE BITMAP INDEX bidx_emp_active_flag ON employees(active_flag);

-- 函数索引，适合经常使用函数的列
CREATE INDEX idx_emp_hire_year ON employees(EXTRACT(YEAR FROM hire_date));

复合索引设计

将选择性高的列放在前面
考虑查询中 WHERE、ORDER BY 和 GROUP BY 子句的组合
避免创建过多的复合索引

sql

-- 示例：复合索引设计
-- 选择性高的列放在前面
CREATE INDEX idx_emp_dept_job ON employees(department_id, job_id);

-- 考虑 ORDER BY 子句
CREATE INDEX idx_emp_hire_date_salary ON employees(hire_date DESC, salary DESC);

2. 索引维护

定期收集统计信息

定期收集索引统计信息，确保优化器生成准确的执行计划
使用 DBMS_STATS 包收集统计信息
考虑使用自动统计信息收集

sql

-- 示例：收集索引统计信息
EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'IDX_EMP_DEPARTMENT_ID');

-- 示例：收集表和索引统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', CASCADE => TRUE);

定期重建或重组索引

索引在频繁的 DML 操作后会产生碎片
定期重建或重组索引，提高索引性能
对于大索引，考虑使用在线重建

sql

-- 示例：重建索引
ALTER INDEX idx_emp_department_id REBUILD;

-- 示例：在线重建索引
ALTER INDEX idx_emp_department_id REBUILD ONLINE;

-- 示例：重组索引
ALTER INDEX idx_emp_department_id COALESCE;

内存管理最佳实践

1. SGA 配置

合理设置 SGA 大小

根据系统内存大小和数据库负载，合理设置 SGA 大小
通常 SGA 大小为系统内存的 50-70%
调整 SGA 组件大小，如共享池、缓冲区缓存、重做日志缓冲区等

sql

-- 示例：设置 SGA 大小
ALTER SYSTEM SET sga_target = 4G SCOPE = SPFILE;
ALTER SYSTEM SET sga_max_size = 4G SCOPE = SPFILE;

共享池优化

设置合适的共享池大小，避免过度解析
启用共享池保留区，保护大对象
监控共享池碎片和空闲空间

sql

-- 示例：设置共享池大小
ALTER SYSTEM SET shared_pool_size = 1G SCOPE = SPFILE;

-- 示例：启用共享池保留区
ALTER SYSTEM SET shared_pool_reserved_size = 100M SCOPE = SPFILE;

2. PGA 配置

合理设置 PGA 大小

设置合适的 PGA 聚合目标，避免过度使用 PGA
对于 DSS 系统，PGA 可以设置较大
对于 OLTP 系统，PGA 可以设置较小

sql

-- 示例：设置 PGA 聚合目标
ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE = SPFILE;

优化排序和哈希操作

确保排序和哈希操作在 PGA 中完成，避免写入临时表空间
监控临时表空间使用情况
考虑增加临时表空间大小或添加多个临时表空间

sql

-- 示例：监控临时表空间使用情况
SELECT tablespace_name, bytes_used/1024/1024 AS used_mb, bytes_free/1024/1024 AS free_mb
FROM v$temp_space_header;

存储管理最佳实践

1. 表空间设计

分离不同类型的数据

将系统表空间与用户表空间分离
将数据文件与重做日志文件分离
将索引与表分离，放在不同的表空间
将临时表空间与其他表空间分离

sql

-- 示例：创建不同类型的表空间
-- 数据文件表空间
CREATE TABLESPACE data_ts DATAFILE 'data_ts01.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G;

-- 索引表空间
CREATE TABLESPACE index_ts DATAFILE 'index_ts01.dbf' SIZE 5G AUTOEXTEND ON NEXT 500M;

-- 临时表空间
CREATE TEMPORARY TABLESPACE temp_ts TEMPFILE 'temp_ts01.dbf' SIZE 2G AUTOEXTEND ON NEXT 1G;

使用自动扩展和大文件表空间

启用数据文件自动扩展，避免空间不足
对于大数据库，考虑使用大文件表空间（Bigfile Tablespace）
合理设置自动扩展增量，避免频繁扩展

sql

-- 示例：创建大文件表空间
CREATE BIGFILE TABLESPACE big_data_ts DATAFILE 'big_data_ts01.dbf' SIZE 100G AUTOEXTEND ON NEXT 10G;

2. I/O 优化

使用异步 I/O

启用异步 I/O，提高 I/O 吞吐量
减少 CPU 等待时间
对于 Linux 系统，确保安装了异步 I/O 库

sql

-- 示例：启用异步 I/O
ALTER SYSTEM SET disk_asynch_io = TRUE SCOPE = SPFILE;

使用 RAID 技术

根据性能和可靠性需求，选择合适的 RAID 级别
RAID 10：适合高性能和高可靠性需求
RAID 5：适合读密集型应用，成本较低
RAID 0：适合临时表空间等，性能高但可靠性低

并发控制最佳实践

1. 锁管理

减少锁持有时间

尽量缩短事务长度，减少锁持有时间
避免在事务中执行长时间运行的操作
考虑使用自治事务处理独立操作

sql

-- 示例：减少锁持有时间
-- 不好的做法
BEGIN
    -- 开始事务
    UPDATE employees SET salary = salary * 1.1 WHERE department_id = 60;
    -- 执行长时间运行的操作
    DBMS_LOCK.SLEEP(60); -- 模拟长时间操作
    COMMIT;
END;
/

-- 好的做法
BEGIN
    -- 先准备数据
    -- 然后快速执行事务
    UPDATE employees SET salary = salary * 1.1 WHERE department_id = 60;
    COMMIT;
    -- 执行长时间运行的操作
    DBMS_LOCK.SLEEP(60);
END;
/

使用合适的隔离级别

根据业务需求选择合适的隔离级别
大多数应用使用 READ COMMITTED 隔离级别
避免使用 SERIALIZABLE 隔离级别，除非必要

sql

-- 示例：设置隔离级别
ALTER SESSION SET isolation_level = READ COMMITTED;

2. 避免死锁

合理设计事务顺序

确保所有事务按照相同的顺序访问资源
避免循环依赖
监控锁等待和死锁情况

sql

-- 示例：监控锁等待情况
SELECT 
  s.sid,
  s.serial#,
  s.username,
  s.machine,
  s.program,
  l.type,
  l.id1,
  l.id2,
  l.lmode,
  l.request
FROM v$session s
JOIN v$lock l ON s.sid = l.sid
WHERE s.status = 'ACTIVE';

监控与调优最佳实践

1. 性能监控

使用 AWR 和 ASH 报告

定期生成 AWR（Automatic Workload Repository）报告，分析系统性能
使用 ASH（Active Session History）报告，分析会话级别的性能问题
识别性能瓶颈和热点 SQL

sql

-- 示例：生成 AWR 报告
@?/rdbms/admin/awrrpt.sql

-- 示例：生成 ASH 报告
@?/rdbms/admin/ashrpt.sql

使用 SQL 监控

启用 SQL 监控，监控长时间运行的 SQL
分析 SQL 执行计划和资源消耗
识别并优化慢查询

sql

-- 示例：启用 SQL 监控
ALTER SESSION SET sql_monitor = TRUE;

-- 示例：查看 SQL 监控报告
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR() FROM dual;

2. 自动优化

使用 Oracle 自动优化功能

启用自动共享内存管理（ASMM）或自动内存管理（AMM）
启用自动统计信息收集
考虑使用自动索引（Oracle 18c+）

sql

-- 示例：启用自动内存管理
ALTER SYSTEM SET memory_target = 6G SCOPE = SPFILE;
ALTER SYSTEM SET memory_max_target = 6G SCOPE = SPFILE;

-- 示例：启用自动统计信息收集
EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL
);

备份与恢复最佳实践

1. 备份策略

制定合适的备份策略

采用 3-2-1 备份原则：3 份备份，2 种不同媒介，1 份异地存储
结合使用 RMAN 和数据泵备份
定期测试备份的可恢复性

sql

-- 示例：使用 RMAN 进行全备
RMAN>
RUN {
  ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;
  BACKUP DATABASE PLUS ARCHIVELOG;
  RELEASE CHANNEL ch1;
}

启用归档日志模式

对于生产数据库，启用归档日志模式
确保归档日志路径有足够的空间
定期备份和清理归档日志

sql

-- 示例：启用归档日志模式
-- 1. 关闭数据库
SHUTDOWN IMMEDIATE;
-- 2. 启动到 mount 状态
STARTUP MOUNT;
-- 3. 启用归档日志模式
ALTER DATABASE ARCHIVELOG;
-- 4. 打开数据库
ALTER DATABASE OPEN;

版本差异与新特性

Oracle 10g 性能特性

引入 AWR 和 ASH 报告
支持自动共享内存管理（ASMM）
支持结果集缓存
引入 SQL 调整顾问

Oracle 11g 性能特性

引入自动内存管理（AMM）
支持 SQL 计划管理
引入实时 SQL 监控
支持虚拟列
增强了并行查询功能

Oracle 12c 性能特性

支持多租户架构（CDB/PDB）
引入不可见索引
支持临时 undo
增强了 JSON 支持
引入自适应执行计划

Oracle 18c/19c 性能特性

支持自动索引
增强了 SQL 模式匹配
支持区块链表
引入机器学习集成
增强了云原生特性

Oracle 21c 性能特性

支持向量索引
增强了 JSON 支持
支持原生 JavaScript 存储过程
支持低代码开发
增强了分布式数据库支持

常见问题（FAQ）

Q1: 如何识别性能瓶颈？

A1: 可以采取以下措施：

生成 AWR 和 ASH 报告，分析系统级性能问题
使用 SQL 监控，识别慢查询
监控系统资源使用情况（CPU、内存、I/O）
查看等待事件，识别瓶颈类型

Q2: 如何优化慢查询？

A2: 可以采取以下措施：

分析执行计划，优化索引和 SQL 语句
考虑使用分区表和分区索引
优化 JOIN 操作，调整连接顺序
考虑使用物化视图

Q3: 如何提高并发性能？

A3: 可以采取以下措施：

减少锁持有时间，缩短事务长度
使用合适的隔离级别
优化锁设计，避免死锁
考虑使用乐观锁或无锁设计

Q4: 如何优化 I/O 性能？

A4: 可以采取以下措施：

使用合适的 RAID 级别
启用异步 I/O
优化表空间设计，分离不同类型的数据
使用 SSD 存储提高 I/O 性能

Q5: 如何优化内存使用？

A5: 可以采取以下措施：

合理设置 SGA 和 PGA 大小
启用自动内存管理
优化共享池和缓冲区缓存
监控内存使用情况，避免内存泄漏

Q6: 如何选择合适的 Oracle 版本？

A6: 考虑以下因素：

业务需求和性能要求
现有系统的兼容性
支持周期和维护成本
新特性需求
预算限制

最佳实践总结

设计优先：在设计阶段就考虑性能优化，避免后期改造
遵循规范化：适当规范化数据模型，减少冗余
合理使用索引：创建合适的索引，避免过度索引
优化 SQL 语句：编写高效的 SQL 语句，使用绑定变量
合理配置资源：优化内存、存储和 I/O 配置
监控与调优：定期监控系统性能，识别并解决瓶颈
自动化管理：利用 Oracle 自动管理功能，减少手动干预
备份与恢复：制定合适的备份策略，确保数据安全
持续优化：性能优化是一个持续过程，需要定期评估和调整
关注新版本：关注 Oracle 新版本的性能特性，及时升级

结论

Oracle 性能最佳实践是确保数据库系统高效运行的关键指南，涵盖了从设计、开发到运维的各个阶段。通过遵循这些最佳实践，可以显著提高系统的性能、可靠性和可扩展性，降低运营成本。

在实际生产环境中，应根据具体业务需求和系统特点，灵活应用这些最佳实践。同时，应关注 Oracle 新版本的性能特性，持续学习和改进，不断提高数据库系统的性能和管理水平。

Oracle 性能最佳实践 ​

性能最佳实践概述 ​

性能最佳实践的目标 ​

性能最佳实践的范围 ​

数据库设计最佳实践 ​

1. 数据模型设计 ​

规范化与反规范化 ​

数据类型选择 ​

2. 表设计 ​

分区表设计 ​

索引组织表（IOT） ​

SQL 开发最佳实践 ​

1. 查询优化 ​

避免 SELECT * ​

使用绑定变量 ​

优化 WHERE 子句 ​

2. JOIN 优化 ​

合理选择 JOIN 顺序 ​

使用合适的 JOIN 类型 ​

3. 子查询优化 ​

避免相关子查询 ​

使用 WITH 子句 ​

索引最佳实践 ​

1. 索引设计 ​

选择合适的索引类型 ​

复合索引设计 ​

2. 索引维护 ​

定期收集统计信息 ​

定期重建或重组索引 ​

内存管理最佳实践 ​

1. SGA 配置 ​

合理设置 SGA 大小 ​

共享池优化 ​

2. PGA 配置 ​

合理设置 PGA 大小 ​

优化排序和哈希操作 ​

存储管理最佳实践 ​

1. 表空间设计 ​

分离不同类型的数据 ​

使用自动扩展和大文件表空间 ​

2. I/O 优化 ​

使用异步 I/O ​

使用 RAID 技术 ​

并发控制最佳实践 ​

1. 锁管理 ​

减少锁持有时间 ​

使用合适的隔离级别 ​

2. 避免死锁 ​

合理设计事务顺序 ​

监控与调优最佳实践 ​

1. 性能监控 ​

使用 AWR 和 ASH 报告 ​

使用 SQL 监控 ​

2. 自动优化 ​

使用 Oracle 自动优化功能 ​

备份与恢复最佳实践 ​

1. 备份策略 ​

制定合适的备份策略 ​

启用归档日志模式 ​

版本差异与新特性 ​

Oracle 10g 性能特性 ​

Oracle 11g 性能特性 ​

Oracle 12c 性能特性 ​

Oracle 18c/19c 性能特性 ​

Oracle 21c 性能特性 ​

常见问题（FAQ） ​

Q1: 如何识别性能瓶颈？ ​

Q2: 如何优化慢查询？ ​

Q3: 如何提高并发性能？ ​

Q4: 如何优化 I/O 性能？ ​

Q5: 如何优化内存使用？ ​

Q6: 如何选择合适的 Oracle 版本？ ​

最佳实践总结 ​

结论 ​