如何在Oracle中优化SQL并行执行？提升多核性能的步骤

星夢妙者

发布时间：2025-09-02 13:06:01

477人浏览过

来源于php中文网

原创

优化Oracle SQL并行执行需综合配置系统参数、SQL语句、对象属性与监控策略，核心是合理利用多核资源。首先识别适合并行的场景，如DSS查询、大表扫描、ETL任务等，避免对短时OLTP查询启用并行。关键参数包括PARALLEL_MAX_SERVERS（控制最大并行进程数）、PARALLEL_THREADS_PER_CPU（设定每核线程数）、PARALLEL_DEGREE_LIMIT（限制并行度上限）和PARALLEL_DEGREE_POLICY（推荐AUTO或LIMITED以实现自动管理）。通过SQL Hint（如/+ PARALLEL /）、对象级PARALLEL属性设置、会话级ALTER SESSION命令实现细粒度控制，优先级顺序为：SQL Hint > 会话设置 > 对象属性 > 系统参数。同时需优化I/O子系统，避免成为瓶颈，并利用分区表分散负载。监控方面，结合EXPLAIN PLAN查看执行计划中的PX操作，使用V$PQ_SLAVE、V$PX_PROCESS、V$SESSION_LONGOPS等视图跟踪并行进程状态与进度，分析AWR/ASH报告识别等待事件（如I/O等待、数据倾斜），防止过度并行化导致资源争用。最终目标是在提升查询吞吐量与控制资源消耗间取得平衡，确保并行执行高效稳定。

如何在oracle中优化sql并行执行？提升多核性能的步骤

在Oracle数据库中，优化SQL并行执行的核心在于智慧地利用多核处理器资源，将原本耗时的单一任务分解为多个子任务并行处理，从而显著缩短执行时间。这并非简单地“开启并行”，而是需要深入理解其工作原理，并根据具体负载和系统资源进行精细化配置和调优。

解决方案

要有效优化Oracle中的SQL并行执行，提升多核性能，我们需要从系统参数、SQL语句、对象属性和监控诊断等多个维度入手，形成一套综合性的策略。首先，明确哪些类型的查询适合并行执行至关重要——通常是那些涉及大量数据扫描、复杂连接或聚合的决策支持系统（DSS）或批处理操作。对于这类任务，并行执行能将工作负载分散到多个并行进程（Parallel Slaves）上，每个进程处理数据的一个子集，最终由查询协调器（Query Coordinator）汇总结果。

实际操作中，我们应关注以下几个关键点：

系统级参数配置： 调整
```
PARALLEL_MAX_SERVERS
```
（数据库允许的最大并行进程数）、
```
PARALLEL_THREADS_PER_CPU
```
（每个CPU可运行的并行进程数）、
```
PARALLEL_DEGREE_LIMIT
```
（并行度的上限）和
```
PARALLEL_DEGREE_POLICY
```
（并行度自动管理策略，推荐
```
AUTO
```
或
```
LIMITED
```
）等参数，确保数据库有足够的资源来支持并行操作，同时避免资源过度消耗。
SQL语句优化： 通过SQL Hint
```
/*+ PARALLEL(table_alias, degree) */
```
或
```
/*+ ENABLE_PARALLEL_DML */
```
精确控制特定查询或DML操作的并行度。例如，在一个复杂的报表查询中，为最大的事实表指定一个合适的并行度，能有效加速数据扫描和连接。
对象级并行设置： 在表或索引的DDL中指定
```
PARALLEL (DEGREE N)
```
，使得对这些对象的操作默认以指定的并行度执行。这对于那些经常被并行查询访问的大型表尤其有用。
会话级控制： 使用
```
ALTER SESSION FORCE PARALLEL QUERY PARALLEL N;
```
或
```
ALTER SESSION ENABLE PARALLEL DML;
```
来为当前会话强制指定并行度或启用并行DML，适用于临时性的、需要加速的批处理会话。
I/O子系统优化： 并行执行对I/O能力要求很高。如果存储系统是瓶颈，再高的并行度也无济于事。确保存储阵列能够提供足够的吞吐量，并考虑使用分区表来分散I/O负载。
监控与诊断： 利用
```
V$PQ_SLAVE
```
、
```
V$PX_PROCESS
```
、
```
V$SESSION_LONGOPS
```
等视图以及
```
EXPLAIN PLAN
```
的输出，监控并行进程的活动、进度和执行计划，及时发现并解决并行执行中的瓶颈或效率低下问题。

这些步骤共同构成了一个全面的优化策略，旨在平衡并行执行带来的性能提升与系统资源消耗，最终实现SQL查询在多核环境下的高效运行。

何时以及为何需要考虑SQL并行执行？

我个人经验来看，对于那些跑个把小时甚至更久的报表查询、数据仓库ETL加载或复杂的批处理任务，并行执行简直是救命稻草。你不可能指望一个涉及数十亿行数据扫描、多表关联和复杂聚合的查询在几秒钟内完成，尤其是在OLTP数据库上。这时，并行执行就显得尤为重要了。

考虑并行执行的场景主要有：

决策支持系统（DSS）或数据仓库（DW）查询： 这些系统通常处理海量数据，查询复杂，响应时间要求相对宽松但总执行时间可能很长。
大型批处理操作： 例如，月末或年末的数据统计、数据清洗、数据迁移等，这些操作往往需要处理整个数据集。
大规模数据加载或DML操作： 使用
```
INSERT /*+ APPEND PARALLEL */ INTO ... SELECT ...
```
或
```
CREATE TABLE ... AS SELECT ...
```
时，并行DML或并行CTAS能显著加速。
索引创建或重建： 对于非常大的表，并行创建或重建索引可以大大缩短维护窗口。

为何要用它？简单说，就是“人多力量大”。一个复杂的SQL操作，如果能被分解成多个独立的、可在不同CPU核上同时执行的小任务，那么总的完成时间就会大大缩短。它通过将数据和计算任务分散到多个并行进程，充分利用了现代服务器的多核CPU架构，从而提升了吞吐量和降低了延迟。但请记住，并行执行不是万能药，对于返回少量数据、执行时间短的OLTP查询，开启并行反而会引入额外的协调开销，适得其反。

配置Oracle并行执行的关键参数有哪些？

在Oracle中，并行执行的效能很大程度上取决于几个关键的系统参数设置。这些参数共同决定了数据库如何管理和分配并行资源。我通常会根据服务器的实际物理资源（CPU核数、内存、I/O能力）和数据库的负载特性来调整它们。

PARALLEL_MAX_SERVERS
：这个参数定义了实例中可以启动的最大并行进程（Parallel Slaves）数量。这是个硬上限。如果你的服务器有64个CPU核，你可能不会把它设成2000，因为那会耗尽资源。我通常会将其设置为CPU核数的2到4倍，具体取决于并发的并行任务数量和每个任务的期望并行度。例如，
```
ALTER SYSTEM SET PARALLEL_MAX_SERVERS = 128 SCOPE=BOTH;
```
PARALLEL_THREADS_PER_CPU
：这个参数用于计算默认的并行度（Degree of Parallelism, DOP）。它告诉Oracle每个CPU核可以处理多少个并行执行线程。默认值通常是2。如果你有超线程（Hyper-Threading）的CPU，这个值可能更合适，但如果你的CPU是物理核，有时设为1反而更稳定，避免过度调度。
PARALLEL_DEGREE_LIMIT
：这是并行度的上限。它限制了任何一个并行操作所能使用的最大并行度。例如，如果你设置为
```
CPU
```
，则并行度不会超过CPU核数；如果设置为
```
IO
```
，则不会超过I/O吞吐量所能支持的有效并行度。我个人偏向于将其设置为
```
CPU_COUNT * PARALLEL_THREADS_PER_CPU
```
，或者一个明确的数字，以防止某个查询意外地消耗过多资源。
PARALLEL_DEGREE_POLICY
：这是Oracle并行执行策略的核心。
- ```
MANUAL
```
  ：完全手动控制，Oracle不会自动决定并行度，必须通过Hint或对象属性明确指定。
- ```
AUTO
```
  ：Oracle会根据系统负载和SQL语句的资源消耗估算来自动确定并行度。这在某些场景下很方便，但有时可能不够精确。
- ```
LIMITED
```
  ：这是一个折衷方案，它允许Oracle在一定程度上自动调整并行度，但会尊重Hint或对象属性中指定的并行度上限。我发现
```
AUTO
```
  或
```
LIMITED
```
  在现代Oracle版本中表现越来越好，可以减少手动干预，但需要配合良好的监控。

这些参数的调整并非一劳永逸，它们需要与实际的SQL工作负载和硬件配置相匹配。调整后务必进行充分的测试，观察系统性能和资源利用率，避免引入新的瓶颈。

如何通过SQL语句和对象属性精确控制并行度？

虽然系统级参数提供了全局的控制，但在实际应用中，我们往往需要对特定的SQL语句或数据库对象进行更细粒度的并行度控制。这就像给不同的车辆分配不同的车道数量，以确保交通流畅。

SQL语句中的并行Hint： 这是最直接、最常用的控制方式。通过在
```
SELECT
```
、
```
INSERT
```
、
```
UPDATE
```
、
```
DELETE
```
语句中加入Hint，我们可以强制指定某个操作的并行度。
- /*+ PARALLEL(table_alias, degree) */
  ：这是最常见的，用于指定表或视图的并行度。
```
table_alias
```
  是SQL语句中表的别名，
```
degree
```
  是并行度。如果
```
degree
```
  设为
```
DEFAULT
```
  ，Oracle会根据系统参数自动计算。
```
SELECT /*+ PARALLEL(e, 8) */ e.employee_id, e.first_name, d.department_name
FROM employees e JOIN departments d ON e.department_id = d.department_id
WHERE e.hire_date < SYSDATE - INTERVAL '5' YEAR;
```
  这个例子中，
```
employees
```
  表会被8个并行进程扫描。
- /*+ PARALLEL_INDEX(table_alias, index_name, degree) */
  ：用于并行扫描索引。
- /*+ ENABLE_PARALLEL_DML */
  ：这是一个语句级的Hint，用于允许DML操作并行执行。通常需要先在会话级别
```
ALTER SESSION ENABLE PARALLEL DML;
```
  。
我通常倾向于在SQL层面通过Hint来精细控制，因为它最具针对性，也最容易回溯。当某个报表突然变慢时，我可以直接检查其SQL，看看并行Hint是否被移除或设置不当。

企奶奶
一款专注于企业信息查询的智能大模型，企奶奶查企业，像聊天一样简单。

下载
数据库对象（表/索引）的并行属性： 你可以在创建表或索引时就指定其并行属性，或者在之后通过
```
ALTER
```
语句修改。
- 创建表时：
```
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
) PARALLEL (DEGREE 4);
```
  这意味着对
```
sales
```
  表的扫描操作默认会尝试以4个并行进程执行。
- 修改表属性：
```
ALTER TABLE sales PARALLEL (DEGREE 8);
ALTER TABLE sales NOPARALLEL; -- 禁用并行
ALTER TABLE sales PARALLEL (DEGREE DEFAULT); -- 恢复到默认计算并行度
```
- 创建索引时：
```
CREATE INDEX idx_sales_date ON sales (sale_date) PARALLEL (DEGREE 2);
```
  这种方式的好处是“一劳永逸”，对于那些始终需要并行访问的大型表，设置对象属性可以省去每次在SQL中添加Hint的麻烦。然而，它也可能导致一些小型查询意外地触发并行，反而增加开销。所以，使用时需要权衡。
会话级别的并行控制： 我们可以通过
```
ALTER SESSION
```
命令来影响当前会话的所有后续SQL操作。
- ALTER SESSION FORCE PARALLEL QUERY PARALLEL N;
  ：强制当前会话的所有查询都以指定的并行度
```
N
```
  执行。
- ALTER SESSION FORCE PARALLEL DML PARALLEL N;
  ：强制当前会话的所有DML操作都以指定的并行度
```
N
```
  执行。
- ALTER SESSION DISABLE PARALLEL DML;
  /
  ALTER SESSION DISABLE PARALLEL QUERY;
  ：禁用当前会话的并行DML或查询。
会话级控制适用于那些需要临时性地调整并行策略的场景，比如在执行一个批处理脚本之前，统一设置并行度。它的优先级低于SQL Hint，高于对象属性。

这些控制方式形成了一个优先级体系：SQL Hint > 会话设置 > 对象属性 > 系统参数。这意味着SQL Hint的优先级最高，它可以覆盖其他层面的设置，这为我们提供了极大的灵活性和精确性。

并行执行的常见陷阱与性能监控策略

并行执行虽然强大，但并非没有副作用。我遇到过不少情况，DBA以为开足马力就能快，结果反而拖慢了整个系统，甚至引发稳定性问题。因此，理解其陷阱并辅以有效的监控策略至关重要。

常见陷阱：

过度并行化（Over-parallelization）： 这是最常见的错误。如果并行度设置过高，超过了CPU或I/O的实际处理能力，反而会导致大量的上下文切换、调度开销，甚至并行进程之间争抢资源，最终性能不升反降。想象一下，一条本来只需要两个人抬的木头，你非要找八个人来抬，结果大家互相挤占空间，谁也使不上劲。
I/O瓶颈： 并行执行的本质是加速数据处理，但如果数据从磁盘读取的速度跟不上，CPU再多、并行度再高也无济于事。存储系统的吞吐量（IOPS和带宽）是决定并行执行上限的关键因素。如果
```
AWR
```
报告显示
```
db file sequential read
```
或
```
db file scattered read
```
等待事件占比很高，那很可能I/O是瓶颈。
数据倾斜（Data Skew）： 如果并行操作的数据分布不均匀，例如一个分区的数据量远大于其他分区，或者一个连接键的值出现次数特别多，那么负责处理这部分数据的并行进程就会成为瓶颈，其他进程早早完成等待，导致整体效率低下。
资源争用： 并行操作可能导致对共享资源（如锁、闩锁、Buffer Cache）的激烈争用。例如，并行DML在更新同一块数据时，可能会出现
```
enqueue
```
等待。
小表并行： 对小表或返回少量数据的查询开启并行，其启动和协调并行进程的开销可能远大于串行执行的收益。Oracle通常有优化器规则避免这种情况，但如果强制使用Hint，就可能出现反效果。
并行DML的限制： 并行DML有其自身的限制，例如不能在有外键约束、触发器或位图索引的表上直接并行DML，需要特殊处理或禁用这些特性。

性能监控策略：

有效的监控是诊断和优化并行执行问题的关键。

EXPLAIN PLAN
分析：在执行SQL之前，务必查看其执行计划。关注计划中是否包含
```
PX
```
（Parallel Execution）操作，如
```
PX Coordinator
```
、
```
PX Send
```
、
```
PX Receive
```
。检查
```
P-S
```
（Producer-Slave）和
```
P-QC
```
（Producer-Query Coordinator）操作，确保数据流向和并行度符合预期。如果期望并行但计划显示串行，或者并行度不正确，就需要调整Hint或参数。
V$PQ_SLAVE
和
V$PX_PROCESS
：这些视图提供了并行进程的实时状态。
- ```
V$PQ_SLAVE
```
  ：显示每个并行进程的当前活动、等待事件、SQL ID等。可以帮助你识别哪个进程在等待，或者哪个进程在做大量工作。
- ```
V$PX_PROCESS
```
  ：显示并行执行的进程信息，包括其角色（Coordinator或Slave）和关联的会话。
V$SESSION_LONGOPS
：对于长时间运行的并行操作，这个视图能提供进度信息，例如已完成的百分比、剩余时间估计等。如果一个并行操作长时间停滞在某个阶段，可以结合其他视图进一步诊断。
AWR
和
ASH
报告：定期生成
```
AWR
```
报告，分析
```
CPU Usage
```
、
```
I/O Activity
```
、
```
Wait Events
```
等指标。在
```
Top SQL
```
部分，关注那些
```
PX
```
相关的SQL语句的性能指标。
```
ASH
```
报告则能提供更细粒度的会话活动历史，帮助你深入分析某个时间段内并行执行的瓶颈。
操作系统级监控： 使用
```
top
```
、
```
vmstat
```
、
```
iostat
```
等工具监控服务器的CPU利用率、内存使用、磁盘I/O。如果Oracle报告并行进程大量运行，但CPU利用率不高，可能意味着I/O瓶颈；如果CPU利用率饱和，但SQL执行时间仍然很长，可能需要优化SQL本身或调整并行度。