如何在Oracle中优化SQL执行计划？分析与调整的详细步骤（步骤.优化.执行.调整.计划...）

wufei123 发布于 2025-09-02 阅读（4）
答案是通过系统性步骤优化Oracle SQL执行计划，包括定位慢SQL、分析执行计划关键指标、实施索引与SQL重写等策略，并结合高级技术如SQL Baseline、分区表、物化视图等提升性能。

如何在oracle中优化sql执行计划？分析与调整的详细步骤

在Oracle数据库中，优化SQL执行计划的核心在于理解数据库如何处理你的查询，并在此基础上进行干预和调整，以减少资源消耗并缩短响应时间。这通常涉及一系列从诊断到实施再到验证的步骤，旨在确保查询能够以最高效的方式访问和处理数据。

解决方案

优化Oracle SQL执行计划是一个系统性的工程，我通常会从以下几个关键环节入手：

1. 定位问题SQL 首先，你得知道哪些SQL是性能瓶颈。这就像医生看病，得先找到病灶。我最常用的方法是查阅AWR报告（如果数据库有诊断包许可），或者直接查询

V$SQL
、
V$SQLAREA
等动态性能视图。我会特别关注那些
ELAPSED_TIME
高、
EXECUTIONS
多、
BUFFER_GETS
或
DISK_READS
异常的语句。有时候，一个单次执行很快但调用频率极高的SQL，累积起来的总耗时可能更惊人。

2. 获取并理解执行计划 找到问题SQL后，下一步就是获取它的执行计划。我一般会用

EXPLAIN PLAN FOR
来预估一个查询的计划，但更准确的是通过
DBMS_XPLAN.DISPLAY_CURSOR
结合
SQL_ID
和
CHILD_NUMBER
来查看实际运行过的计划。这个计划是Oracle优化器告诉我们它打算如何执行SQL的“路线图”。

理解执行计划的关键在于：

  • 操作（Operation）：比如
    TABLE ACCESS FULL
    （全表扫描）、
    INDEX SCAN
    （索引扫描）、
    HASH JOIN
    、
    NESTED LOOPS
    等。
  • 对象名（Object Name）：操作涉及的具体表或索引。
  • 成本（Cost）：优化器估算的执行成本，越低越好。
  • 行数（Rows）：优化器估算的该步骤返回的行数。这是个关键指标，如果估算值与实际值偏差巨大，往往意味着统计信息不准确。
  • 字节（Bytes）：该步骤处理的字节数。
  • 谓词信息（Predicate Information）：显示了过滤条件和连接条件，帮助我们理解数据是如何被筛选和关联的。

3. 分析与诊断瓶颈 拿到执行计划后，我会像侦探一样去分析。

  • 高成本操作：是不是有某个操作的成本特别高？比如一个本应走索引的查询却走了全表扫描。
  • 行数估算偏差：如果
    Rows
    估算值与实际数据量相差悬殊，那优化器很可能做出了错误的决策。这通常是统计信息过时或缺失的信号。
  • 连接方式：
    NESTED LOOPS
    通常适合小结果集关联，而
    HASH JOIN
    适合大结果集。如果选择不当，性能会大打折扣。
  • 全表扫描：对于大表，除非是获取大部分数据，否则全表扫描几乎总是性能杀手。

4. 实施优化策略 诊断出问题后，就可以对症下药了。

  • 索引优化：这是最常见也最有效的手段。
    • 创建新索引：根据查询的
      WHERE
      子句、
      JOIN
      条件和
      ORDER BY
      子句来创建单列或复合索引。
    • 调整现有索引：有时候索引建了，但优化器没用上，可能是因为统计信息问题，也可能是索引列顺序不合理。
    • 删除无用索引：过多的索引会增加DML操作的开销，并且占用存储空间。
  • SQL重写：
    • 简化复杂查询：避免不必要的子查询、视图嵌套。
    • 使用
      UNION ALL
      代替
      OR
      ：在某些情况下，
      OR
      条件可能会导致全表扫描，拆分成
      UNION ALL
      并利用索引效果更好。
    • 调整
      JOIN
      顺序：优化器通常会选择最优的
      JOIN
      顺序，但有时人工干预（通过
      ORDERED
      Hint）会有奇效。
    • 避免函数在索引列上：
      WHERE TO_CHAR(date_col, 'YYYY') = '2023'
      会导致索引失效。
  • 统计信息管理：
    • 定期收集统计信息：
      DBMS_STATS.GATHER_SCHEMA_STATS
      或
      DBMS_STATS.GATHER_TABLE_STATS
      。确保统计信息能够准确反映数据的分布情况。
    • 直方图：对于数据分布不均匀的列，直方图能帮助优化器做出更明智的决策。
  • 使用Hint（提示）：当优化器选择的执行计划不理想时，可以通过Hint来强制优化器走我们认为最优的路径。比如
    /*+ USE_NL(a b) */
    强制使用嵌套循环连接，
    /*+ FULL(t) */
    强制全表扫描，
    /*+ INDEX(t idx_name) */
    强制使用某个索引。但我个人建议，Hints是最后的手段，因为它们可能会在数据和结构变化后变得适得其反。
  • 绑定变量：对于重复执行的SQL，使用绑定变量可以减少硬解析的次数，从而降低CPU消耗。
  • 分区表：对于超大表，通过分区可以实现分区裁剪，显著减少数据扫描量。
  • 物化视图：对于复杂的聚合查询或报表查询，物化视图可以预先计算结果，提高查询速度。

5. 验证与监控 优化不是一劳永逸的。每次调整后，都必须重新获取执行计划，对比性能指标（如

ELAPSED_TIME
、
CPU_TIME
、
BUFFER_GETS
），确认优化效果。同时，也要持续监控数据库性能，因为数据量、业务模式的变化都可能再次影响SQL性能。 如何快速定位Oracle数据库中的慢SQL？

定位Oracle中的慢SQL，我通常会从几个维度入手。最直接的，也是我个人最喜欢的方式，就是深入Oracle的动态性能视图。

首先，

V$SQLAREA
和
V$SQL
是两个宝藏视图。它们存储了共享池中SQL语句的性能统计信息。我通常会这样查询：
SELECT
    s.SQL_ID,
    s.SQL_FULLTEXT,
    s.EXECUTIONS,
    s.ELAPSED_TIME / 1000000 AS TOTAL_ELAPSED_SECONDS,
    s.CPU_TIME / 1000000 AS TOTAL_CPU_SECONDS,
    s.BUFFER_GETS,
    s.DISK_READS,
    s.ROWS_PROCESSED,
    s.OPTIMIZER_MODE,
    s.PARSING_SCHEMA_NAME
FROM
    V$SQLAREA s
WHERE
    s.ELAPSED_TIME > 0 -- 排除未执行或耗时为0的SQL
ORDER BY
    s.ELAPSED_TIME DESC -- 按总耗时降序排列，找出最慢的
FETCH FIRST 10 ROWS ONLY; -- 只看前10个

这个查询能帮我快速识别出那些“总耗时”最高的SQL。但这里有个坑，有些SQL可能单次执行很快，但由于执行频率极高，累积起来的总耗时却非常可观。所以，我还会关注

ELAPSED_TIME / EXECUTIONS
（平均单次执行时间）以及
BUFFER_GETS / EXECUTIONS
（平均单次逻辑读）等指标。
SELECT
    s.SQL_ID,
    s.SQL_FULLTEXT,
    s.EXECUTIONS,
    s.ELAPSED_TIME / DECODE(s.EXECUTIONS, 0, 1, s.EXECUTIONS) / 1000000 AS AVG_ELAPSED_SECONDS,
    s.BUFFER_GETS / DECODE(s.EXECUTIONS, 0, 1, s.EXECUTIONS) AS AVG_BUFFER_GETS,
    s.OPTIMIZER_MODE
FROM
    V$SQLAREA s
WHERE
    s.EXECUTIONS > 0
ORDER BY
    AVG_ELAPSED_SECONDS DESC
FETCH FIRST 10 ROWS ONLY;

此外，AWR（Automatic Workload Repository）报告也是一个非常强大的工具，特别是当你需要分析某个时间段内的整体性能趋势时。AWR报告会详细列出Top SQL by Elapsed Time、CPU Time、Buffer Gets等，并提供执行计划、等待事件等丰富的信息。如果数据库有诊断包许可，我会毫不犹豫地生成一份AWR报告来做深度分析。

对于实时或近期活动的分析，

V$ACTIVE_SESSION_HISTORY
(ASH) 视图则非常有用。它记录了过去一小时内（默认）数据库活动会话的采样数据。通过ASH，我可以观察到哪些SQL在某个特定时间点导致了性能瓶颈，以及它们在等待什么资源。
SELECT
    s.SQL_ID,
    s.EVENT,
    COUNT(*) AS SAMPLE_COUNT
FROM
    V$ACTIVE_SESSION_HISTORY s
WHERE
    s.SAMPLE_TIME BETWEEN SYSDATE - INTERVAL '10' MINUTE AND SYSDATE -- 过去10分钟内的活动
GROUP BY
    s.SQL_ID, s.EVENT
ORDER BY
    SAMPLE_COUNT DESC
FETCH FIRST 10 ROWS ONLY;

通过这些方法，结合我的经验，通常都能很快锁定那些“捣乱”的SQL语句。

分析SQL执行计划时，哪些关键指标需要重点关注？

当我们拿到一份SQL执行计划时，初看可能会觉得信息量大得有点眼花缭乱。但其实，我通常会把注意力集中在几个核心指标上，它们能很快帮我判断问题所在。

1. 

Operation
和 
Object Name
： 这是最基础也是最重要的。
Operation
告诉我们Oracle在做什么（比如
TABLE ACCESS FULL
、
INDEX UNIQUE SCAN
、
HASH JOIN
），
Object Name
则指明了操作作用于哪个表或索引。
  • 关注点： 如果一个大表出现了
    TABLE ACCESS FULL
    ，那几乎肯定是个问题。或者，如果本应走索引的查询却走了全表扫描，这就要警惕了。
    NESTED LOOPS
    和
    HASH JOIN
    的选择也很关键，前者适合小数据集关联，后者适合大数据集。

2. 

Rows
(Estimated Rows)： 这是优化器在执行这个步骤时，预估会返回的行数。
  • 关注点： 
    Rows
    的准确性至关重要。如果优化器预估的行数与实际情况相差巨大（比如预估10行，实际返回10万行），那么它很可能基于错误的假设选择了次优的执行路径。这种巨大的偏差往往是统计信息过时或缺失的直接体现。

3. 

Cost
(Estimated Cost)： 优化器为这个操作估算的相对成本。它是一个抽象的数值，用于比较不同执行路径的优劣，成本越低越好。
  • 关注点： 
    Cost
    本身并不直接代表时间，但它是优化器决策的基础。如果一个操作的
    Cost
    异常高，那它就是重点优化对象。同时，要结合
    Rows
    来看，如果
    Cost
    高但
    Rows
    估算不准，那么优化器可能被误导了。

4. 

Predicate Information
： 这部分会详细列出每个操作的过滤条件（
filter
）和连接条件（
access
）。
  • 关注点： 它可以帮助我们理解数据是如何被筛选和关联的。比如，
    filter
    中出现的列是否都有合适的索引？
    access
    条件是否能有效利用索引？如果
    filter
    中有很多函数操作在索引列上，那索引可能就失效了。

5. 

Bytes
(Estimated Bytes)： 这个步骤预估处理的字节数。
  • 关注点： 结合
    Rows
    来看，可以估算单行的平均大小。如果
    Bytes
    很高，说明处理的数据量非常大，可能存在不必要的全表扫描或数据传输。

6. 

Id
和 
Parent Id
：
Id
是操作的唯一标识，
Parent Id
指向其父操作。通过它们可以构建执行计划的树形结构，理解操作的执行顺序。
  • 关注点： 从最内层（最深
    Id
    ）开始向上分析，可以更好地理解数据流和处理过程。

总结来说，我分析执行计划就像读一份地图，

Operation
和
Object Name
是地标，
Rows
和
Cost
是路况和耗时估算，
Predicate Information
是具体路线指示。抓住这些核心点，就能高效地找出SQL的性能瓶颈。 除了索引和SQL重写，还有哪些高级技巧能显著提升Oracle SQL性能？

确实，索引和SQL重写是优化SQL性能的基石，但Oracle提供了许多更高级的特性，能在特定场景下带来显著的性能提升。这些方法往往需要更深入的数据库知识，但一旦掌握，效果立竿见影。

1. SQL Profile 和 SQL Baseline：

  • SQL Profile (SQL概要文件)： 这是Oracle 10g引入的一项强大功能。它允许数据库在不改变SQL文本的情况下，为特定的SQL语句存储并应用额外的优化器统计信息和校正。这意味着即使优化器最初选择了次优计划，SQL Profile也能像一个“智能补丁”一样，引导优化器生成更好的执行计划。我经常在无法修改应用程序代码，但又需要优化慢SQL时使用它。
    • 示例： 
      DBMS_SQLTUNE.ACCEPT_SQL_PROFILE
      可以从SQL Tuning Advisor建议中创建Profile。
  • SQL Baseline (SQL计划基线)： 旨在稳定SQL执行计划，防止因统计信息变化、数据库升级等原因导致计划退化。你可以将一个已知的好计划作为基线，优化器会优先尝试使用这个基线计划。如果优化器找到了更好的计划，它可以被“进化”到基线中。这就像给SQL计划上了一把锁，确保性能的稳定性。
    • 示例： 
      DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
      可以将当前缓存中的计划加载为基线。

2. 分区表策略： 对于数据量巨大的表，分区是提升性能的利器。通过将一张大表逻辑或物理地分割成更小的、更易管理的部分，可以实现：

  • 分区裁剪 (Partition Pruning)： 当查询条件包含分区键时，优化器只会扫描相关的分区，大大减少了数据扫描量。
  • 分区连接 (Partition Join)： 在某些情况下，当两个表都按相同的键分区时，可以实现更高效的连接。
  • 示例： 
    CREATE TABLE sales ( ... ) PARTITION BY RANGE (sale_date) ( PARTITION p1 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')), ... )

3. 物化视图 (Materialized Views)： 物化视图是预先计算并存储查询结果的数据库对象。对于复杂的聚合查询、多表连接查询，或者报表类查询，物化视图能显著提升查询速度，因为它避免了每次执行都重新计算。

  • 刷新策略： 可以选择
    FAST REFRESH
    （增量刷新）或
    COMPLETE REFRESH
    （完全刷新），以及
    ON COMMIT
    （提交时刷新）或
    ON DEMAND
    （按需刷新）。
  • 查询重写： 优化器能够自动识别并使用物化视图来回答查询，即使查询本身没有直接引用物化视图。
  • 示例： 
    CREATE MATERIALIZED VIEW mv_daily_sales BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT TRUNC(sale_date), SUM(amount) FROM sales GROUP BY TRUNC(sale_date);

4. 并行执行 (Parallel Execution)： Oracle可以利用多CPU和多I/O通道并行执行单个SQL语句的多个部分，从而显著缩短大查询的响应时间。这对于数据仓库、批处理或大规模分析型查询尤其有效。

  • 使用场景： 适用于扫描大量数据、执行复杂计算（如聚合、排序、连接）的查询。
  • 控制方式： 可以通过表、索引的并行度设置，或者在SQL语句中使用
    /*+ PARALLEL(table_alias, degree) */
    Hint来控制。
  • 注意事项： 并行执行会消耗更多资源，不当使用可能导致系统资源耗尽。

5. Result Cache (结果缓存)： Oracle的Result Cache可以缓存查询的结果集，当相同的查询再次执行时，可以直接从缓存中获取结果，避免重复执行查询。这对于那些执行频率高、数据变化不慢的查询非常有效。

  • 类型： 可以是SQL Query Result Cache（缓存整个查询结果）或PL/SQL Function Result Cache（缓存PL/SQL函数的结果）。
  • 示例： 
    SELECT /*+ RESULT_CACHE */ emp_name FROM employees WHERE emp_id = 100;

这些高级技巧在处理特定性能瓶颈时，往往能提供超越常规优化的解决方案。但使用它们需要对业务场景和数据库内部机制有深刻的理解，避免“过度优化”或引入新的问题。

以上就是如何在Oracle中优化SQL执行计划？分析与调整的详细步骤的详细内容，更多请关注知识资源分享宝库其它相关文章！

标签:  步骤 优化 执行 

相关阅读

发表评论:

◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。

最近发表

标签列表