如何在Oracle中优化SQL执行计划？分析与调整的详细步骤-SQL-PHP中文网

答案是通过系统性步骤优化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在某个特定时间点导致了性能瓶颈，以及它们在等待什么资源。

美图设计室

5分钟在线高效完成平面设计，AI帮你做设计

查看详情

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执行计划时，初看可能会觉得信息量大得有点眼花缭乱。但其实，我通常会把注意力集中在几个核心指标上，它们能很快帮我判断问题所在。

Operation

登录后复制

和
Object Name
登录后复制
：这是最基础也是最重要的。

Operation

登录后复制

告诉我们Oracle在做什么（比如

TABLE ACCESS FULL

登录后复制

、

INDEX UNIQUE SCAN

登录后复制

、

HASH JOIN

登录后复制

），

Object Name

登录后复制

则指明了操作作用于哪个表或索引。

关注点： 如果一个大表出现了
```
TABLE ACCESS FULL
```
登录后复制
，那几乎肯定是个问题。或者，如果本应走索引的查询却走了全表扫描，这就要警惕了。
```
NESTED LOOPS
```
登录后复制
和
```
HASH JOIN
```
登录后复制
的选择也很关键，前者适合小数据集关联，后者适合大数据集。

Rows

登录后复制

(Estimated Rows)： 这是优化器在执行这个步骤时，预估会返回的行数。

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

Cost

登录后复制

(Estimated Cost)： 优化器为这个操作估算的相对成本。它是一个抽象的数值，用于比较不同执行路径的优劣，成本越低越好。

关注点：
```
Cost
```
登录后复制
本身并不直接代表时间，但它是优化器决策的基础。如果一个操作的
```
Cost
```
登录后复制
异常高，那它就是重点优化对象。同时，要结合
```
Rows
```
登录后复制
来看，如果
```
Cost
```
登录后复制
高但
```
Rows
```
登录后复制
估算不准，那么优化器可能被误导了。

Predicate Information

登录后复制

：这部分会详细列出每个操作的过滤条件（

filter

登录后复制

）和连接条件（

access

登录后复制

）。

关注点： 它可以帮助我们理解数据是如何被筛选和关联的。比如，
```
filter
```
登录后复制
中出现的列是否都有合适的索引？
```
access
```
登录后复制
条件是否能有效利用索引？如果
```
filter
```
登录后复制
中有很多函数操作在索引列上，那索引可能就失效了。

Bytes

登录后复制

(Estimated Bytes)： 这个步骤预估处理的字节数。

关注点： 结合
```
Rows
```
登录后复制
来看，可以估算单行的平均大小。如果
```
Bytes
```
登录后复制
很高，说明处理的数据量非常大，可能存在不必要的全表扫描或数据传输。

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执行计划？分析与调整的详细步骤的详细内容，更多请关注php中文网其它相关文章！

大家都在看：

如何在mysql数据库中创建一张表 postgresql如何创建表 mysql建表时出现错误1005的原因 postgresql如何新建数据库 mysql导入sql文件乱码怎么解决