印刻数据库

外观

Oracle 并行查询优化 ​

Oracle 并行查询概述 ​

并行查询是 Oracle 数据库中提高大型查询性能的重要特性，通过将单个查询分解为多个子任务并在多个 CPU 核心上并行执行，显著减少查询执行时间。并行查询适用于大数据量的扫描、连接、排序等操作，在数据仓库和 OLAP 环境中尤为重要。

并行查询基本原理 ​

并行查询的核心原理是将 SQL 语句的执行过程分解为多个独立的并行执行服务器（PX Server）来完成。主要组件包括：

  • 查询协调器（QC）：负责将查询分解为子任务，并协调所有并行执行服务器的工作
  • 并行执行服务器（PX Server）：执行具体的子任务，如数据扫描、连接、聚合等
  • 并行执行消息（PX Message）：在并行执行服务器之间传递数据和控制信息

并行查询适用场景 ​

  • 大数据量的全表扫描或索引全扫描
  • 大型表之间的连接操作
  • 复杂的排序和聚合操作
  • 数据仓库中的 ETL 处理
  • 大型报表生成

并行度设置与管理 ​

并行度（DOP，Degree of Parallelism）决定了查询执行时使用的并行服务器数量，是并行查询优化的关键参数。

并行度的设置方式 ​

1. 表级并行度 ​

sql
-- 设置表的默认并行度
ALTER TABLE sales PARALLEL 8;

-- 取消表的并行度设置
ALTER TABLE sales NOPARALLEL;

-- 设置表的并行度为自动
ALTER TABLE sales PARALLEL AUTO;

2. 索引级并行度 ​

sql
-- 设置索引的并行度
ALTER INDEX sales_idx PARALLEL 4;

3. SQL 语句级并行度 ​

sql
-- 在 SELECT 语句中指定并行度
SELECT /*+ PARALLEL(8) */ * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

-- 在 INSERT 语句中使用并行度
INSERT /*+ PARALLEL(12) */ INTO sales_backup SELECT * FROM sales;

-- 在 CREATE TABLE AS SELECT 中使用并行度
CREATE TABLE sales_summary PARALLEL 8 AS SELECT * FROM sales;

4. 会话级并行度 ​

sql
-- 设置会话级并行度
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;

-- 重置会话级并行度
ALTER SESSION DISABLE PARALLEL QUERY;

5. 系统级并行度 ​

sql
-- 设置系统默认并行度（Oracle 12c+）
ALTER SYSTEM SET parallel_degree_policy = 'AUTO' SCOPE=BOTH;
ALTER SYSTEM SET parallel_degree_limit = 16 SCOPE=BOTH;

Oracle 19c 和 21c 并行度新特性 ​

Oracle 19c 新特性 ​

  • 自动并行度（Auto DOP）：增强的自动并行度算法，基于工作负载和系统资源自动调整并行度
  • 并行度限制：可以通过 parallel_degree_limit 参数限制最大并行度
  • 并行执行优先级：可以为不同用户或会话设置并行执行优先级

Oracle 21c 新特性 ​

  • 智能并行度：进一步优化的自动并行度算法，考虑更多系统因素
  • 并行度自适应：在查询执行过程中动态调整并行度
  • 并行执行统计增强：提供更详细的并行执行统计信息

并行执行计划分析 ​

理解并行执行计划是优化并行查询的关键。通过分析执行计划，可以识别并行执行的瓶颈和优化机会。

查看并行执行计划 ​

sql
-- 使用 EXPLAIN PLAN 查看并行执行计划
EXPLAIN PLAN FOR
SELECT /*+ PARALLEL(8) */ * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

-- 使用 SQL*Plus 自动跟踪执行计划
SET AUTOTRACE ON EXPLAIN;
SELECT /*+ PARALLEL(8) */ * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

并行执行计划关键操作 ​

  • PX COORDINATOR：查询协调器，负责协调所有并行操作
  • PX SEND QC (RANDOM)：将数据发送给查询协调器
  • PX RECEIVE：从并行执行服务器接收数据
  • PX BLOCK ITERATOR：将表数据块划分为多个范围，分配给不同的并行执行服务器
  • PX JOIN FILTER CREATE：创建连接过滤条件
  • PX HASH JOIN：并行哈希连接
  • PX SORT AGGREGATE：并行排序和聚合

并行执行计划示例 ​

----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |   100K|  100M |  1000   (5)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR       |        |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000|   100K|  100M |  1000   (5)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR  |        |   100K|  100M |  1000   (5)| 00:00:01 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL | SALES  |   100K|  100M |  1000   (5)| 00:00:01 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access(:Z>=:Z AND :Z<=:Z)
       filter("SALE_DATE" BETWEEN '2023-01-01' AND '2023-12-31')

并行查询优化策略 ​

1. 选择合适的并行度 ​

  • 基于工作负载：对于大数据量查询，使用较高并行度；对于小数据量查询，避免使用并行
  • 基于系统资源：考虑系统 CPU 核心数、内存大小和 I/O 能力
  • 自动并行度：在 Oracle 12c+ 中，推荐使用自动并行度设置

2. 优化并行执行的数据分布 ​

  • 避免数据倾斜：确保并行执行服务器之间的数据分布均匀
  • 选择合适的并行分发方式：根据查询类型选择 RANDOM、HASH 或 BROADCAST 分发方式
  • 使用并行提示优化：通过 PQ_DISTRIBUTE 提示指定数据分布策略

3. 优化并行查询的硬件资源 ​

  • 增加 CPU 核心数：并行查询主要依赖 CPU 资源
  • 优化 I/O 子系统：使用 RAID、SSD 或并行文件系统提高 I/O 性能
  • 增加内存：确保有足够的内存支持并行执行

4. 优化并行查询的 SQL 语句 ​

  • 减少数据量：使用 WHERE 子句过滤不必要的数据
  • 优化连接顺序：将最大的表放在最后连接
  • 使用合适的连接方法：根据数据分布选择哈希连接或嵌套循环连接
  • 避免不必要的排序：使用索引避免排序操作

5. 监控和调整并行查询 ​

  • 使用 V$PQ_SYSSTAT 视图：监控并行执行的系统统计信息
  • 使用 V$PQ_SESSTAT 视图：监控当前会话的并行执行统计信息
  • 使用 V$PQ_SLAVE 视图：监控并行执行服务器的状态

并行查询常见问题与解决方案 ​

1. 并行度设置过高导致系统负载过大 ​

问题：设置过高的并行度导致系统 CPU 利用率达到 100%，影响其他业务

解决方案：

  • 降低并行度设置
  • 使用自动并行度策略
  • 限制每个用户或会话的最大并行度

2. 并行查询性能不如预期 ​

问题：并行查询执行时间比串行查询更长

解决方案：

  • 检查是否存在数据倾斜
  • 优化 SQL 语句，减少数据量
  • 检查并行执行服务器的资源使用情况
  • 确保表和索引的统计信息准确

3. 并行查询导致死锁 ​

问题：并行查询在某些情况下可能导致死锁

解决方案：

  • 避免在并行查询中使用行级锁
  • 优化事务设计，减少锁持有时间
  • 考虑使用 SERIALIZABLE 隔离级别

4. 并行查询占用过多内存 ​

问题：并行查询占用过多内存，导致系统内存不足

解决方案：

  • 调整 pga_aggregate_target 和 sga_target 参数
  • 限制并行查询的内存使用
  • 使用自动内存管理

并行查询最佳实践 ​

1. 合理设置并行度 ​

  • 对于 OLTP 系统，通常不建议使用并行查询
  • 对于 OLAP 系统，根据数据量和系统资源设置合适的并行度
  • 使用自动并行度可以简化管理

2. 优化表和索引设计 ​

  • 对大型表设置合适的并行度
  • 确保表和索引的统计信息准确
  • 使用分区表可以提高并行查询的效率

3. 优化 SQL 语句 ​

  • 避免在并行查询中使用复杂的 PL/SQL 函数
  • 减少子查询和复杂表达式
  • 使用绑定变量提高查询性能

4. 监控和管理并行执行 ​

  • 定期监控并行执行的性能和资源使用情况
  • 调整并行执行相关的系统参数
  • 考虑使用资源管理器限制并行查询的资源使用

5. 考虑版本差异 ​

  • Oracle 19c 和 21c 提供了更智能的自动并行度算法
  • Oracle 21c 支持并行度自适应调整
  • 不同版本的并行查询行为可能有所不同，需要根据实际版本进行调整

并行查询与其他优化技术结合 ​

1. 并行查询与分区表 ​

分区表与并行查询结合可以进一步提高性能，每个分区可以由不同的并行执行服务器处理。

sql
-- 创建分区表并设置并行度
CREATE TABLE sales_partitioned (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
)
PARALLEL 8;

2. 并行查询与索引 ​

在某些情况下，并行查询可以与索引结合使用，提高查询性能。

sql
-- 在并行查询中使用索引
SELECT /*+ PARALLEL(8) INDEX(sales sales_idx) */ * FROM sales WHERE sale_id > 1000000;

3. 并行查询与物化视图 ​

物化视图可以预先计算复杂查询的结果，结合并行查询可以进一步提高性能。

sql
-- 使用并行度创建物化视图
CREATE MATERIALIZED VIEW sales_summary_mv
PARALLEL 8
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS SELECT sale_date, SUM(amount) total_amount FROM sales GROUP BY sale_date;

常见问题（FAQ） ​

Q1: 并行查询适用于所有类型的查询吗？ ​

A1: 不是。并行查询主要适用于大数据量的查询，如全表扫描、大型连接和聚合操作。对于小数据量的查询，并行查询可能会因为额外的协调开销而导致性能下降。

Q2: 如何确定最佳并行度？ ​

A2: 最佳并行度取决于多个因素，包括查询的数据量、系统 CPU 核心数、I/O 性能和内存大小。Oracle 12c+ 提供了自动并行度功能，可以根据系统资源和工作负载自动调整并行度。

Q3: 并行查询会影响其他用户的性能吗？ ​

A3: 是的。并行查询会占用大量系统资源，包括 CPU、内存和 I/O，可能会影响其他用户的查询性能。因此，在生产环境中需要合理设置并行度，并考虑使用资源管理器限制并行查询的资源使用。

Q4: 如何监控并行查询的性能？ ​

A4: 可以使用以下视图监控并行查询的性能：

  • V$PQ_SYSSTAT：并行执行的系统统计信息
  • V$PQ_SESSTAT：当前会话的并行执行统计信息
  • V$PQ_SLAVE：并行执行服务器的状态
  • AWR 和 ASH 报告：包含并行执行的详细统计信息

Q5: Oracle 19c 和 21c 的并行查询有什么区别？ ​

A5: Oracle 19c 和 21c 都提供了自动并行度功能，但 Oracle 21c 的自动并行度算法更加智能，支持并行度自适应调整，可以在查询执行过程中动态调整并行度。此外，Oracle 21c 还提供了更详细的并行执行统计信息。

Q6: 如何在 RAC 环境中优化并行查询？ ​

A6: 在 RAC 环境中，并行查询可以利用多个节点的资源。可以通过以下方式优化：

  • 设置合适的并行度，考虑所有节点的 CPU 核心数
  • 确保数据分布均匀，避免数据倾斜
  • 考虑使用服务质量管理（QoS）确保关键查询获得足够的资源

总结 ​

并行查询是 Oracle 数据库中提高大型查询性能的重要工具，特别是在数据仓库和 OLAP 环境中。通过合理设置并行度、优化 SQL 语句和监控并行执行，DBA 可以充分利用系统资源，提高查询性能。在 Oracle 19c 和 21c 中，自动并行度功能得到了进一步增强，可以简化并行查询的管理，提高系统的整体性能。

在实际生产环境中，DBA 需要根据系统资源、工作负载和业务需求，合理配置和优化并行查询，以达到最佳的性能效果。同时，需要定期监控并行查询的性能，及时调整并行度设置，确保系统的稳定运行。