oracle sql tuning如何从获取TOP 语句到开始优化，需要的优化工具，优化方法等全流程，做详细讲解 基于 Oracle Database 19c 的官方性能优化指南和 SQL 调优指南，Oracle SQL 调优是一个迭代的过程，旨在让 SQL 语句满足特定的性能目标。以下是从获取 Top SQL 到完成优化的全流程详细讲解： 1. 第一阶段：识别高负载语句 (Identifying High-Load SQL) 优化的第一步是找出系统中消耗资源最多、对性能影响最大的 SQL 语句。 • 利用 ADDM (自动数据库诊断监视器):    ◦ ADDM 是内置的自诊断软件，它通过分析自动工作负载存储库 (AWR) 中的统计信息，自动定位性能问题的根本原因，包括识别高负载 SQL 语句。    ◦ 建议定期（如每晚）运行 ADDM，查看报告中是否建议对消耗大量 CPU 或 I/O 的特定语句运行 SQL Tuning Advisor。 • 查看 AWR 报告中的 "Top SQL" 部分:    ◦ AWR 报告包含 "Top SQL" 章节，根据 CPU 时间、经过时间 (Elapsed Time)、缓冲区获取 (Buffer Gets)、磁盘读取 (Disk Reads) 等指标对 SQL 进行排序。    ◦ 这有助于识别造成短暂性能问题或长期资源占用的语句。 • 使用 V$ 视图:    ◦ 可以通过查询 V$SQL 和 V$SQLSTATS 等动态性能视图来实时监控 SQL 活动，查找执行次数多或资源消耗大的语句。 • Active Session History (ASH):    ◦ ASH 报告（如 "Top SQL" 部分）可以显示在采样会话活动中占比最高的 SQL 语句，有助于诊断瞬时性能抖动。 2. 第二阶段：诊断与数据收集 (Diagnosis and Data Gathering) 在确定了目标 SQL 后，需要收集相关数据以理解为什么优化器选择了当前的执行计划。 • 收集统计信息: 优化器统计信息对于生成最佳计划至关重要。如果统计信息缺失或陈旧，优化器无法做出正确决策。 • 获取执行计划:    ◦ EXPLAIN PLAN: 使用此命令查看优化器在不实际执行语句的情况下会选择什么计划。    ◦ DBMS_XPLAN: 这是一个常用的包，用于格式化和显示执行计划输出。    ◦ V$SQL_PLAN: 查询已执行并缓存在共享池中的实际执行计划。 • 实时 SQL 监控: 使用 Cloud Control 中的 "Monitored SQL Executions" 页面或 DBMS_SQL_MONITOR 包来跟踪长运行查询的执行细节。 3. 第三阶段：使用优化工具 (SQL Tuning Tools) Oracle 提供了两类工具：自动工具和手动工具。建议优先使用自动工具。 A. 自动优化工具 (Automated Tools) 1. SQL Tuning Advisor (SQL 调优顾问):    ◦ 功能: 接收一个或多个 SQL 语句作为输入，并调用自动调优优化器 (Automatic Tuning Optimizer) 进行分析。    ◦ 分析内容: 检查统计信息是否陈旧或缺失、分析是否可以通过创建 SQL Profile 来纠正基数估计错误、评估是否可以通过改变访问路径（如创建索引）来提升性能，以及识别结构性问题。    ◦ 输出: 提供具体的建议（如收集统计信息、创建索引、重写 SQL、接受 SQL Profile）及其预期的性能提升幅度。 2. SQL Access Advisor (SQL 访问顾问):    ◦ 功能: 专注于物理访问结构的优化。它分析实际工作负载，建议创建、删除或保留索引、物化视图及分区，以提升整体工作负载的性能。    ◦ 适用场景: 当你需要优化整个模式或工作负载的吞吐量，而不仅仅是单条语句时。 B. 手动优化工具 (Manual Tools) • 执行计划分析: 通过 EXPLAIN PLAN 和 DBMS_XPLAN 深入理解操作顺序、成本和行数估算。 • SQL Trace 和 TKPROF: 生成详细的跟踪文件，记录每个 SQL 的执行统计信息（如解析、执行、提取次数，以及 CPU 和磁盘等待时间），并通过 TKPROF 格式化输出以便分析。 4. 第四阶段：实施优化方法 (Optimization Methods) 根据诊断结果和工具建议，可以采取以下具体的优化措施： • 优化器统计信息管理:    ◦ 使用 DBMS_STATS 包收集表、列和索引的统计信息。确保统计信息准确反映数据分布。    ◦ 对于数据分布不均的列，直方图（Histograms）可以帮助优化器更准确地估算选择率。 • 创建或调整访问路径:    ◦ 索引: 根据 SQL Access Advisor 的建议创建合适的索引（如 B-tree, Bitmap）以减少全表扫描。    ◦ 分区: 对大表进行分区，以便查询可以利用分区修剪（Partition Pruning）特性。 • SQL 语句重写:    ◦ 使用绑定变量: 将字面量替换为绑定变量，以减少硬解析（Hard Parsing），降低 CPU 消耗并提高并发性。    ◦ 改写低效逻辑: 例如，用 UNION ALL 代替 UNION（如果不需要去重），或者移除 WHERE 子句中导致无法使用索引的函数调用。 • 使用 SQL Profiles:    ◦ SQL Profile 包含辅助统计信息，用于纠正优化器的估算偏差（如基数或选择率错误）。这是 SQL Tuning Advisor 的常见建议结果。 • 使用 SQL Plan Baselines (SQL 计划基线):    ◦ 这是一种预防性机制。通过捕获并验证执行计划，确保数据库仅使用已知性能最佳的计划，防止因环境变更导致的性能回退。 • 使用 Hints (提示):    ◦ 在极少数无法通过统计信息或 SQL Profile 解决问题的情况下，可以使用 Hints（如 /*+ INDEX(t1) */ 或 /*+ USE_NL(t1 t2) */）强制优化器使用特定的访问路径或连接方法。但 Oracle 强烈建议优先使用 SQL Tuning Advisor 等工具而非手动 Hints。 5. 流程总结 1. 识别: 通过 AWR/ADDM 找到 Top SQL。 2. 分析: 运行 SQL Tuning Advisor 获取建议。 3. 实施:    ◦ 如果建议收集统计信息 -> 运行 DBMS_STATS。    ◦ 如果建议创建索引 -> 评估并创建（或运行 SQL Access Advisor 进一步确认）。    ◦ 如果建议 SQL Profile -> 接受 Profile。    ◦ 如果建议重写 SQL -> 修改应用程序代码。 4. 验证: 使用 SQL Performance Analyzer (SPA) 预测更改的影响，确认性能提升。 5. 固化: 使用 SQL Plan Baselines 锁定最佳计划，防止未来性能退化