第九章：性能优化实战

文档信息

适用版本：Oracle 12c / 18c / 19c / 21c
阅读时间：约 60 分钟
前置知识：第八章事务与并发控制

9.1 SQL 执行计划

9.1.1 执行计划基础

-- 1. EXPLAIN PLAN
EXPLAIN PLAN FOR
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000
ORDER BY e.salary DESC;

-- 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 2. AUTOTRACE
SET AUTOTRACE ON
SELECT * FROM employees WHERE department_id = 50;
SET AUTOTRACE OFF

-- AUTOTRACE 选项
SET AUTOTRACE ON;              -- 显示执行计划和统计
SET AUTOTRACE ON EXPLAIN;     -- 只显示执行计划
SET AUTOTRACE ON STATISTICS;  -- 只显示统计信息
SET AUTOTRACE TRACEONLY;      -- 不输出查询结果
SET AUTOTRACE TRACEONLY EXPLAIN;  -- 不执行，只看计划

-- 3. DBMS_XPLAN.DISPLAY_CURSOR
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));

-- 4. SQL Developer 执行计划
-- F10 或 工具菜单 → 监视执行计划

9.1.2 执行计划解读

┌─────────────────────────────────────────────────────────────────┐
│                      执行计划输出示例                              │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  Plan hash value: 1234567890                                    │
│                                                                 │
│  ┌──────┬────────────────┬──────┬──────┬───────────┬──────────┐│
│  │ Id   │ Operation      │ Name │ Rows │ Cost (%CPU)│ Time     ││
│  ├──────┼────────────────┼──────┼──────┼───────────┼──────────┤│
│  │   0  │ SELECT STATEMENT│      │  100 │ 5 (20)    │ 00:00:01 ││
│  │   1  │  SORT ORDER BY │      │  100 │ 5 (20)    │ 00:00:01 ││
│  │   2  │   NESTED LOOPS │      │  100 │ 4 (0)     │ 00:00:01 ││
│  │   3  │    TABLE ACCESS│ EMP  │  100 │ 2 (0)     │ 00:00:01 ││
│  │*  4  │     INDEX RANGE│ IDX  │   50 │ 1 (0)     │ 00:00:01 ││
│  │   5  │    TABLE ACCESS│ DEPT │    1 │ 2 (0)     │ 00:00:01 ││
│  └──────┴────────────────┴──────┴──────┴───────────┴──────────┘│
│                                                                 │
│  Predicate Information (identified by operation id):            │
│  ──────────────────────────────────────────────────────────── │
│     4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")        │
│     4 - filter("E"."SALARY">5000)                              │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

关键列说明：
• Id: 操作步骤编号
• Operation: 操作类型
• Name: 对象名称
• Rows: 估算返回行数
• Cost: 估算成本
• Time: 估算时间
• Bytes: 估算字节数

9.1.3 常见访问路径

操作	说明	性能
TABLE ACCESS FULL	全表扫描	慢
TABLE ACCESS BY ROWID	通过 ROWID 访问	快
INDEX UNIQUE SCAN	唯一索引扫描	快
INDEX RANGE SCAN	索引范围扫描	中
INDEX FULL SCAN	索引全扫描	中
INDEX FAST FULL SCAN	索引快速全扫描	快
INDEX SKIP SCAN	跳跃索引扫描	中

9.2 SQL 调优技巧

9.2.1 避免全表扫描

-- ❌ 错误：全表扫描
SELECT * FROM employees WHERE TO_CHAR(hire_date, 'YYYY') = '2024';

-- ✅ 正确：使用函数索引
CREATE INDEX idx_hire_year ON employees(EXTRACT(YEAR FROM hire_date));
SELECT * FROM employees WHERE EXTRACT(YEAR FROM hire_date) = 2024;

-- ❌ 错误：隐式类型转换
SELECT * FROM employees WHERE employee_id = '100';  -- NUMBER 列用字符串

-- ✅ 正确：类型匹配
SELECT * FROM employees WHERE employee_id = 100;

-- ❌ 错误：使用函数
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

-- ✅ 正确：函数索引
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

-- ❌ 错误：NOT IN
SELECT * FROM employees WHERE department_id NOT IN (10, 20);

-- ✅ 正确：NOT EXISTS 或外部连接
SELECT * FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.dept_id = e.department_id);

9.2.2 优化 JOIN

-- 小表驱动大表（NL 循环时）
-- Oracle 自动优化，无需手动指定

-- 避免不必要的 JOIN
-- ❌ 过度 JOIN
SELECT e.employee_id, e.first_name, 
       d.department_name, l.city, c.country_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN locations l ON d.loc_id = l.loc_id
JOIN countries c ON l.country_id = c.country_id  -- 可能不需要
WHERE e.employee_id = 100;

-- ✅ 按需 JOIN
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.employee_id = 100;

-- WHERE 条件位置
-- ❌ WHERE 在 JOIN 之后
SELECT * FROM employees e, departments d
WHERE e.dept_id = d.dept_id AND e.salary > 5000;

-- ✅ Oracle 自动优化，但保持清晰
SELECT * FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 5000;

9.2.3 分页查询优化

-- ❌ 低效分页
SELECT * FROM (
    SELECT a.*, ROWNUM rn FROM (
        SELECT * FROM large_table ORDER BY create_time DESC
    ) a
) WHERE rn BETWEEN 1001 AND 1020;

-- ✅ 优化：使用 ROWID 分页
SELECT * FROM large_table
WHERE rowid IN (
    SELECT rowid FROM large_table 
    WHERE create_time > '2024-01-01'
    ORDER BY create_time DESC
)
AND ROWNUM <= 20;

-- ✅ 使用 Keyset 分页（游标分页，更高效）
SELECT * FROM large_table
WHERE create_time < :last_time  -- 使用上一页最后一条的时间
ORDER BY create_time DESC
FETCH FIRST 20 ROWS ONLY;

-- ✅ Oracle 12c+ 优化
SELECT * FROM large_table
ORDER BY create_time DESC
OFFSET 1000 ROWS FETCH NEXT 20 ROWS ONLY;

9.2.4 批量操作优化

-- FORALL 批量插入
DECLARE
    TYPE emp_tab IS TABLE OF employees%ROWTYPE;
    v_emps emp_tab;
BEGIN
    SELECT * BULK COLLECT INTO v_emps FROM employees WHERE department_id = 50;
    
    FORALL i IN 1..v_emps.COUNT
        INSERT INTO employees_backup VALUES v_emps(i);
    
    COMMIT;
END;
/

-- BULK COLLECT 批量查询
DECLARE
    TYPE id_tab IS TABLE OF NUMBER;
    TYPE name_tab IS TABLE OF VARCHAR2(100);
    v_ids id_tab;
    v_names name_tab;
BEGIN
    SELECT employee_id, first_name
    BULK COLLECT INTO v_ids, v_names
    FROM employees
    WHERE department_id = 50;
    
    FOR i IN 1..v_ids.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v_ids(i) || ': ' || v_names(i));
    END LOOP;
END;
/

-- FORALL with SAVE EXCEPTIONS
DECLARE
    TYPE num_tab IS TABLE OF NUMBER;
    v_ids num_tab := num_tab(1, 2, 3, 4, 5);
    v_errors NUMBER;
BEGIN
    FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
        UPDATE employees SET salary = salary * 1.1 WHERE employee_id = v_ids(i);
EXCEPTION
    WHEN OTHERS THEN
        v_errors := SQL%BULK_EXCEPTIONS.COUNT;
        FOR i IN 1..v_errors LOOP
            DBMS_OUTPUT.PUT_LINE('Error ' || i || ': Row ' || 
                SQL%BULK_EXCEPTIONS(i).ERROR_INDEX || 
                ' - ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
        END LOOP;
END;
/

9.3 提示词 (Hints)

9.3.1 常用提示词

提示词	说明
`/+ FULL(t) /`	全表扫描
`/+ INDEX(t idx) /`	使用索引
`/+ INDEX_FFS(t idx) /`	索引快速全扫描
`/+ USE_NL(t1 t2) /`	使用嵌套循环
`/+ USE_HASH(t1 t2) /`	使用 Hash 连接
`/+ USE_MERGE(t1 t2) /`	使用排序合并
`/+ PARALLEL(t, 4) /`	并行执行
`/+ LEADING(t1) /`	指定驱动表
`/+ ORDERED /`	按 FROM 顺序连接

9.3.2 提示词使用

-- 强制使用索引
SELECT /*+ INDEX(e idx_emp_dept) */ *
FROM employees e
WHERE department_id = 50;

-- 强制嵌套循环
SELECT /*+ USE_NL(e d) */ *
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- 强制 Hash 连接
SELECT /*+ USE_HASH(e d) */ *
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- 并行查询
SELECT /*+ PARALLEL(employees, 4) */ COUNT(*)
FROM employees;

-- 指定驱动表
SELECT /*+ LEADING(d e) */ *
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id;

-- 优化器参数提示
SELECT /*+ OPT_PARAM('optimizer_index_caching', 80) */ *
FROM employees WHERE department_id = 50;

9.4 AWR 与性能诊断

9.4.1 AWR 基础

┌─────────────────────────────────────────────────────────────────┐
│                      AWR 工作原理                                │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  MMON 进程每 60 分钟自动采集快照                                  │
│                                                                 │
│  ┌─────────┐    ┌─────────┐    ┌─────────┐                     │
│  │ 快照 1  │ →  │ 快照 2  │ →  │ 快照 3  │  →  ...             │
│  └─────────┘    └─────────┘    └─────────┘                     │
│      ↓              ↓              ↓                            │
│    10:00          11:00          12:00                           │
│                                                                 │
│  AWR 存储：                                                      │
│  • 默认保留 8 天（可调整）                                        │
│  • SYSAUX 表空间                                                 │
│  • 快照数据可通过 DBA_HIST_* 视图查询                             │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

9.4.2 AWR 操作

-- 查看 AWR 配置
SELECT 
    snap_interval,
    retention,
    topnsql
FROM dba_hist_wr_control;

-- 修改 AWR 设置
BEGIN
    DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
        retention => 43200,    -- 30 天（分钟）
        interval  => 3600,      -- 1 小时
        topnsql   => 100        -- Top SQL 数量
    );
END;
/

-- 手动创建快照
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

-- 查看快照
SELECT 
    snap_id,
    to_char(begin_interval_time, 'YYYY-MM-DD HH24:MI') as begin_time,
    to_char(end_interval_time, 'YYYY-MM-DD HH24:MI') as end_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC;

-- 生成 AWR 报告（SQL*Plus）
@?/rdbms/admin/awrrpt.sql

-- 生成 AWR 比较报告
@?/rdbms/admin/awrddrpt.sql

-- 生成单实例 AWR HTML 报告
SELECT OUTPUT FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    DBID => 1234567890,
    INST_NUM => 1,
    START_SNAP_ID => 100,
    END_SNAP_ID => 110
));

9.4.3 AWR 报告解读

-- 生成指定时间段的 AWR
SELECT DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    DBID      => (SELECT DBID FROM V$DATABASE),
    INST_NUM  => 1,
    START_SNAP_ID => 150,
    END_SNAP_ID   => 160
) FROM DUAL;

-- 关键指标：
-- Load Profile
-- ┌─────────────────┬──────────┬──────────┐
-- │ Per Second      │ Per Trans│ Redo Size│
-- ├─────────────────┼──────────┼──────────┤
-- │ DB Time(s): 1.5 │ 15.2     │ 125KB    │
-- │ DB CPU(s): 1.0  │ 10.0     │          │
-- │ Buffer Gets:150 │ 1500     │          │
-- └─────────────────┴──────────┴──────────┘

-- Top 5 Timed Events
-- ┌─────────────────────────┬──────────┬───────────┬───────────┐
-- │ Event                   │ Waits    │ Time(s)   │ %DB Time  │
-- ├─────────────────────────┼──────────┼───────────┼───────────┤
-- │ db file sequential read │ 1,500    │ 50        │ 55%       │
-- │ log file sync           │ 200      │ 20        │ 22%       │
-- │ CPU time                │ -        │ 15        │ 17%       │
-- └─────────────────────────┴──────────┴───────────┴───────────┘

-- SQL Statistics
-- Top SQL by Elapsed Time
-- Top SQL by CPU Time
-- Top SQL by Buffer Gets
-- Top SQL by Row Processed

9.4.4 ADDM 分析

-- 运行 ADDM 分析
@?/rdbms/admin/addmrpt.sql

-- 查看 ADDM 建议
SELECT 
    task_name,
    TO_CHAR(execution_start, 'YYYY-MM-DD HH24:MI') as start_time,
    STATUS
FROM dba_advisor_tasks
WHERE task_type = 'ADDM'
ORDER BY execution_start DESC;

-- 查看 ADDM 发现
SELECT 
    finding_id,
    message
FROM dba_advisor_finding_names
WHERE task_name = 'MY_ADDM_TASK';

-- 查看 ADDM 推荐
SELECT 
    r.rank,
    r.type,
    r.message,
    r.benefit_type,
    r.benefit
FROM dba_advisor_recommendations r
WHERE r.task_name = 'MY_ADDM_TASK'
ORDER BY r.rank;

9.4.5 ASH 实时分析

-- 查看当前活动的会话
SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.status,
    s.event,
    s.seconds_in_wait,
    sql.sql_text
FROM v$session s
LEFT JOIN v$sql sql ON s.sql_id = sql.sql_id
WHERE s.status = 'ACTIVE'
AND s.type = 'USER';

-- 查看等待事件
SELECT 
    event,
    COUNT(*) as wait_count,
    SUM(seconds_in_wait) as total_wait_sec
FROM v$session
WHERE status = 'ACTIVE'
AND wait_class != 'Idle'
GROUP BY event
ORDER BY SUM(seconds_in_wait) DESC;

-- ASH 样本数据
SELECT 
    session_id,
    session_serial#,
    event,
    wait_class,
    sql_id,
    program
FROM v$active_session_history
WHERE sample_time >= SYSDATE - INTERVAL '5' MINUTE
ORDER BY sample_time DESC;

-- Top SQL 正在执行
SELECT 
    sql_id,
    COUNT(*) as sample_count,
    SUM(CASE WHEN session_state = 'ON CPU' THEN 1 ELSE 0 END) as on_cpu,
    SUM(CASE WHEN session_state = 'WAITING' THEN 1 ELSE 0 END) as waiting
FROM v$active_session_history
WHERE sample_time >= SYSDATE - INTERVAL '5' MINUTE
GROUP BY sql_id
ORDER BY COUNT(*) DESC;

9.5 统计信息管理

9.5.1 表与索引统计

-- 收集表统计
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname          => 'HR',
        tabname          => 'EMPLOYEES',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
        degree           => DBMS_STATS.AUTO_DEGREE,
        cascade          => TRUE,  -- 同时收集索引统计
        no_invalidate    => FALSE
    );
END;
/

-- 收集模式统计
BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS(
        ownname          => 'HR',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
        degree           => DBMS_STATS.AUTO_DEGREE,
        cascade          => TRUE,
        options          => 'GATHER AUTO'  -- 只收集脏数据
    );
END;
/

-- 收集系统统计
BEGIN
    DBMS_STATS.GATHER_SYSTEM_STATS(
        stattab     => 'SYS_STATS',
        statid      => 'PROD_STATS',
        statown     => 'SYS'
    );
END;
/

-- 查看统计信息
SELECT 
    table_name,
    num_rows,
    blocks,
    sample_size,
    last_analyzed
FROM user_tab_statistics
WHERE table_name = 'EMPLOYEES';

SELECT 
    index_name,
    num_rows,
    distinct_keys,
    leaf_blocks,
    clustering_factor,
    last_analyzed
FROM user_ind_statistics
WHERE index_name = 'IDX_EMP_DEPT';

9.5.2 统计信息锁定

-- 锁定统计信息（生产环境常用）
BEGIN
    DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES');
END;
/

-- 锁定模式统计
BEGIN
    DBMS_STATS.LOCK_SCHEMA_STATS('HR');
END;
/

-- 解锁
BEGIN
    DBMS_STATS.UNLOCK_TABLE_STATS('HR', 'EMPLOYEES');
END;
/

-- 导出/导入统计信息
-- 导出
BEGIN
    DBMS_STATS.EXPORT_TABLE_STATS(
        ownname  => 'HR',
        tabname  => 'EMPLOYEES',
        statown  => 'HR',
        stattab  => 'STATS_TABLE'
    );
END;
/

-- 导入
BEGIN
    DBMS_STATS.IMPORT_TABLE_STATS(
        ownname  => 'HR',
        tabname  => 'EMPLOYEES',
        statown  => 'HR',
        stattab  => 'STATS_TABLE'
    );
END;
/

9.6 本章小结

✅ 执行计划分析：TABLE ACCESS FULL、INDEX SCAN、JOIN 类型
✅ SQL 调优技巧：避免全表扫描、优化 JOIN、分页优化
✅ 提示词：强制执行计划、提示优化器行为
✅ AWR：自动工作负载仓库，快照管理，性能报告
✅ ADDM：自动诊断推荐，分析性能问题
✅ ASH：活动会话历史，实时性能分析
✅ 统计信息：收集、锁定、导出导入

📖 下章预告：日志管理

下一步：学习第十章：日志管理