左右SQL执行计划妙招 ⑤ ——执行计划利用设计特性改变【并行度影响】

梁敬彬梁敬弘兄弟出品

往期回顾
左右SQL执行计划妙招 ①——子查询的应用范围
左右SQL执行计划妙招 ②——Hint无效原因
左右SQL执行计划妙招 ③ ——执行计划SQL写法差异改变之【with子句】
左右SQL执行计划妙招 ④ ——执行计划SQL写法差异改变之【insert all】

在Oracle数据库性能优化中，执行计划的变化往往受到多种设计特性的影响。其中，并行度作为一个重要的性能调优手段，对执行计划的结构和性能表现具有显著影响。本文通过实际案例，深入分析并行度设置如何改变执行计划，帮助DBA更好地理解和应用并行处理技术。

实验环境准备

为了清晰展示并行度对执行计划的影响，我们准备一个标准的测试环境：

-- 创建测试表
drop table t;
create table t as select * from dba_objects;

-- 设置显示格式
set linesize 1000
set pagesize 2000
set autotrace off
ALTER SESSION SET statistics_level = all;

串行执行：基准性能表现

首先执行不带任何并行设置的基础查询：

语句1：默认串行执行

select count(*) from t;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));

执行结果显示：

Plan

hash value: 2966233522
--------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.08 |    1047 |    692 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.08 |    1047 |    692 |
|   2 |   TABLE ACCESS FULL| T    |      1 |  80575 |  73118 |00:00:00.07 |    1047 |    692 |
--------------------------------------------------------------------------------------------

关键观察点：

• 执行计划结构简单，采用传统的串行全表扫描
• 单一进程完成整个查询任务

Hint驱动的并行执行

接下来通过parallel hint强制启用并行处理：

语句2：Hint指定并行度

select /*+parallel(t,4)*/ count(*) from t;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));

执行结果显示：

Pla

n hash value: 3126468333
--------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |      1 |        |      1 |00:00:00.09 |       5 |
|   1 |  SORT AGGREGATE        |          |      1 |      1 |      1 |00:00:00.09 |       5 |
|   2 |   PX COORDINATOR       |          |      1 |        |      4 |00:00:00.09 |       5 |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |      0 |      1 |      0 |00:00:00.01 |       0 |
|   4 |     SORT AGGREGATE     |          |      0 |      1 |      0 |00:00:00.01 |       0 |
|   5 |      PX BLOCK ITERATOR |          |      0 |  80575 |      0 |00:00:00.01 |       0 |
|*  6 |       TABLE ACCESS FULL| T        |      0 |  80575 |      0 |00:00:00.01 |       0 |
--------------------------------------------------------------------------------------------

执行计划结构变化分析：

1. PX COORDINATOR：并行协调器，负责管理和协调多个并行进程的执行
2. PX SEND QC (RANDOM)：并行进程向查询协调器发送数据的操作
3. PX BLOCK ITERATOR：并行块迭代器，实现数据块的并行分配

表级并行属性设置及风险

通过ALTER TABLE语句将并行度设置为表的固有属性：

语句3：表属性并行度

alter table t parallel 4;
select count(*) from t;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));

执行结果显示相同的Plan hash value: 3126468333，说明表级并行属性生效，自动采用并行执行。

⚠️ 重要警告：不建议在生产环境中设置表级并行属性

设置表的并行属性存在严重风险：

1. 全局影响：一旦设置，所有访问该表的SQL都会默认启用并行，包括简单的单行查询
2. 资源消耗失控：大量并发的小查询同时启动并行进程，会导致系统资源急剧消耗
3. OLTP性能恶化：在OLTP环境中，大量简单查询启用并行会严重影响响应时间
4. 难以精确控制：无法针对具体业务场景灵活调整并行策略

推荐做法：

• 在表级别保持 NOPARALLEL 设置
• 通过SQL级别的 parallel hint 精确控制需要并行的查询
• 根据具体业务场景和查询特征决定是否使用并行

强制取消并行执行

即使表具有并行属性，也可以通过hint强制使用串行执行：

语句4：no_parallel hint

select /*+no_parallel*/ count(*) from t;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));

执行结果回到Plan hash value: 2966233522，证明：

• no_parallel hint成功覆盖了表的并行属性
• 执行计划回归到串行模式

并行度设置最佳实践

1. 优先使用SQL级hint：通过 /+parallel(table_name, degree)/ 精确控制
2. 避免表级并行设置：防止所有查询无差别启用并行
3. CPU核心数考量：并行度一般不超过CPU核心数的2倍
4. 业务场景区分：OLTP系统谨慎使用，OLAP系统可以积极应用

总结

并行度设置对Oracle执行计划具有根本性影响，不仅改变了计划的结构，更显著影响了查询的性能表现。通过深入理解PX COORDINATOR等并行执行组件的工作机制，以及掌握不同并行度设置方式的优先级关系，DBA可以更精准地进行性能调优。

特别需要强调的是，应该避免在表级别设置并行属性，而是通过SQL级别的hint来精确控制并行执行。这样既能充分发挥并行处理的性能优势，又能避免过度并行导致的资源竞争和系统负载过重。记住，并行处理需要精确控制，合理使用才能真正提升数据库性能。

未完待续…
左右SQL执行计划妙招 ⑥ ——执行计划SQL写法差异改变【rownum分页】

系列回顾

“大白话人工智能” 系列
“数据库拍案惊奇” 系列
“世事洞明皆学问” 系列