引言在处理大规模数据时传统的串行SQL执行方式往往成为性能瓶颈——单个服务器进程只能使用一个CPU核心面对百万行甚至亿行级别的表扫描、索引创建、数据装载等操作时响应时间可能长达数小时。Oracle的并行查询Parallel Query简称PQ技术正是为解决这一问题而生它将一个大型任务分解为多个小任务由多个并行进程同时处理充分利用服务器的多核CPU、内存和I/O带宽资源显著缩短处理时间。本文将全面介绍Oracle并行查询的工作原理、体系架构、配置方法、关键参数以及常见问题排查帮助你安全高效地驾驭并行查询这一强大特性。一、并行查询的核心概念与架构1.1 什么是并行查询并行查询是指Oracle将一个SQL操作的执行任务拆分成多个子任务由多个并行的服务器进程Parallel Server Processes简称PX Slaves同时执行最后将结果汇总返回给用户。每个并行进程独立处理一部分数据理想情况下可以将执行时间缩短到原来的1/NN为并行度。用户会话QC协调进程PX Slave 1处理分区1PX Slave 2处理分区2PX Slave 3处理分区3PX Slave 4处理分区4数据汇总QC整合结果返回结果给用户1.2 并行查询的进程架构Oracle并行执行使用一个“生产者-消费者”模型包含两类关键进程| 进程类型 | 角色 | 数量 ||----------|------|------||Query CoordinatorQC| 协调者进程即用户的会话进程。负责解析SQL、生成并行执行计划、分配任务、汇总结果 | 1个 ||PX Slave Processes| 并行执行进程实际执行数据扫描、连接、排序等操作 | Degree of Parallelism × 2通常 |一个典型的并行查询会用到2 × DOPDegree of Parallelism个并行进程——一半作为生产者数据扫描另一半作为消费者数据加工它们之间通过表队列Table Queue传递数据。1.3 并行执行的适用场景并行查询并非万能它最适用于以下场景大规模全表扫描对百万行以上的表进行扫描、聚合、排序大型索引创建/重建加速DDL操作大批量数据装载INSERT INTO ... SELECT、CTAS分区表操作天然支持按分区粒度并行大型表连接Hash Join、Sort-Merge Join等数据仓库和OLAP系统分析型负载为主的环境不适用场景小型表操作数据量不足以分摊并行调度开销高并发的OLTP事务并行会抢夺CPU资源影响并发用户频繁的索引唯一扫描单行查找无法并行化二、并行查询的配置方法2.1 数据库层面的基础配置在使用并行查询之前必须确保数据库有足够的并行进程资源-- 查看当前并行相关参数 SHOW PARAMETER parallel; -- 关键参数说明 -- parallel_max_servers实例允许的最大并行进程总数 -- parallel_min_servers实例启动时预先创建的并行进程数 -- parallel_degree_policy并行度策略MANUAL/LIMITED/AUTO/ADAPTIVE -- parallel_degree_limit单个操作允许的最大并行度基础配置建议-- 设置最大并行进程数根据CPU核数和预期并发并行操作数量 -- 公式参考parallel_max_servers CPU_CORES × 并发并行用户数 × (每个用户的DOP × 2) ALTER SYSTEM SET parallel_max_servers 64 SCOPEBOTH; -- 设置最小并行进程数避免频繁创建销毁进程的开销 ALTER SYSTEM SET parallel_min_servers 16 SCOPEBOTH; -- 设置并行策略为手动控制推荐在OLTP为主的系统中使用MANUAL ALTER SYSTEM SET parallel_degree_policy MANUAL SCOPEBOTH; -- 设置单个操作的并行度上限 ALTER SYSTEM SET parallel_degree_limit 16 SCOPEBOTH;2.2 在对象级别设置并行度方式一创建表时指定并行度CREATE TABLE sales_2024 PARALLEL 8 AS SELECT * FROM sales WHERE sale_year 2024;方式二修改已有表的并行度-- 设置表的并行度 ALTER TABLE sales PARALLEL 8; -- 设置索引的并行度 ALTER INDEX idx_sales_date PARALLEL 4; -- 查看对象的并行度设置 SELECT table_name, degree FROM dba_tables WHERE owner SH; SELECT index_name, degree FROM dba_indexes WHERE owner SH;方式三取消对象的并行设置-- 恢复为默认并行度通常为1即串行 ALTER TABLE sales NOPARALLEL; ALTER INDEX idx_sales_date NOPARALLEL;2.3 在SQL语句中使用Hint指定并行度Hint方式最为灵活可以针对单条SQL临时启用或调整并行度-- 指定全表扫描的并行度 SELECT /* PARALLEL(s 8) */ region, SUM(amount) FROM sales s GROUP BY region; -- 指定多表连接的并行度 SELECT /* PARALLEL(e 4) PARALLEL(d 4) */ e.employee_id, e.last_name, d.department_name FROM employees e, departments d WHERE e.department_id d.department_id; -- 指定整个语句的默认并行度所有对象使用相同DOP SELECT /* PARALLEL(8) */ customer_id, COUNT(*) FROM orders WHERE order_date SYSDATE - 365 GROUP BY customer_id;2.4 使用自动并行度Auto DOP从Oracle 11gR2开始Oracle支持自动计算并行度-- 开启自动并行度 ALTER SYSTEM SET parallel_degree_policy AUTO SCOPEBOTH; -- 此时Oracle会根据SQL的资源消耗估算自动决定是否并行及并行度 -- 无需在对象或SQL中显式指定PARALLEL自动并行的触发条件SQL的估算执行时间超过PARALLEL_MIN_TIME_THRESHOLD默认10秒涉及的对象有足够的空闲并行进程系统整体负载允许三、并行查询的关键参数详解3.1 parallel_max_servers这是最重要的并行限制参数。如果所有并行进程都被占用新的并行请求将降级为串行执行或排队等待。-- 查看当前并行进程使用情况 SELECT * FROM v$px_process_sysstat; -- 查看当前活跃的并行进程 SELECT sid, serial#, degree, req_degree FROM v$px_session WHERE qcsid IS NOT NULL; -- 查看并行进程使用率接近100%说明需要增加parallel_max_servers SELECT (SELECT COUNT(*) FROM v$px_process WHERE status IN USE) / (SELECT value FROM v$parameter WHERE name parallel_max_servers) * 100 AS usage_pct FROM dual;3.2 parallel_degree_policy| 值 | 含义 ||----|------||MANUAL| 手动控制并行度仅当对象设置PARALLEL或SQL使用Hint时才并行 ||LIMITED| 类似MANUAL但某些自动并行特性可能启用 ||AUTO| Oracle自动判断是否并行及计算DOP ||ADAPTIVE| 基于运行时统计信息动态调整并行度12c |3.3 parallel_degree_limit限制单个并行操作的并行度上限防止某条SQL耗尽所有并行进程-- 将单条SQL的DOP限制为CPU核数的2倍 ALTER SYSTEM SET parallel_degree_limit CPU;可选值CPUCPU核数、IOIO带宽相关、整数绝对上限。3.4 parallel_force_local控制并行进程是否跨RAC节点执行-- 限制并行只在本地节点执行避免跨节点数据传输 ALTER SYSTEM SET parallel_force_local TRUE SCOPEBOTH; -- 允许跨RAC节点并行利用全集群资源 ALTER SYSTEM SET parallel_force_local FALSE SCOPEBOTH;四、并行查询的执行计划解读4.1 识别并行执行计划启用并行后执行计划中会出现特殊的并行操作符-- 查看带有并行信息的执行计划 EXPLAIN PLAN FOR SELECT /* PARALLEL(s 4) */ region, SUM(amount) FROM sales s GROUP BY region; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format ALL -PROJECTION));典型的并行执行计划输出---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 36 | 10 (10) | 00:00:01| | | | 1 | PX COORDINATOR | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 4 | 36 | 10 (10) | 00:00:01| Q1,01 | P-S | | 3 | HASH GROUP BY | | 4 | 36 | 10 (10) | 00:00:01| Q1,01 | PCWP | | 4 | PX RECEIVE | | 4 | 36 | 10 (10) | 00:00:01| Q1,01 | PCWP | | 5 | PX SEND HASH | :TQ10000 | 4 | 36 | 10 (10) | 00:00:01| Q1,00 | P-P | | 6 | HASH GROUP BY | | 4 | 36 | 10 (10) | 00:00:01| Q1,00 | PCWP | | 7 | PX BLOCK ITERATOR | | 10000 | 90000 | 9 (0) | 00:00:01| Q1,00 | PCWC | | 8 | TABLE ACCESS FULL| SALES | 10000 | 90000 | 9 (0) | 00:00:01| Q1,00 | PCWP | ----------------------------------------------------------------------------------------------关键并行操作符解释| 操作符 | 含义 ||--------|------||PX COORDINATOR| 查询协调者QC用户的会话进程 ||PX SEND QC| 并行进程将数据发送给QC ||PX RECEIVE| 并行进程接收其他并行进程发送的数据 ||PX SEND HASH/RANGE| 并行进程之间通过Hash或Range方式分发数据 ||PX BLOCK ITERATOR| 并行进程以数据块为粒度迭代处理数据 ||TQ列 | Table Queue编号标识数据在不同进程组之间的流动 ||IN-OUT列 | P-S并行到串行、P-P并行到并行、PCWP并行组合与父操作并行、PCWC并行组合与子操作并行 |4.2 理解Table Queue与数据流结果汇总消费者组DOP4TQ10000TableQueue生产者组DOP4TQ10000TQ10000TQ10000TQ10000TQ10001TQ10001TQ10001TQ10001PX Slave 1全表扫描PX Slave 2全表扫描PX Slave 3全表扫描PX Slave 4全表扫描按HASH分发数据PX Slave 5Hash Group ByPX Slave 6Hash Group ByPX Slave 7Hash Group ByPX Slave 8Hash Group ByQC汇总结果每个TQTable Queue代表一次数据重分发过多的Table Queue意味着大量进程间通信开销。五、并行查询的监控与诊断5.1 实时监控并行查询-- 查看当前正在执行的并行操作 SELECT qcsid, -- QC的会话ID qcserial#, -- QC的序列号 degree, -- 实际使用的并行度 req_degree, -- 请求的并行度 slave_set -- 当前并行进程组 FROM v$px_session WHERE qcsid IS NOT NULL; -- 查看每个并行进程的工作状态 SELECT px.sid, s.username, s.program, px.qcsid, px.server_group, px.server_set, px.server#, s.sql_id, ROUND(s.last_call_et/60, 2) AS active_minutes FROM v$px_session px, v$session s WHERE px.sid s.sid AND px.qcsid IS NOT NULL ORDER BY px.qcsid, px.server_group, px.server#;5.2 查看并行操作的历史统计-- v$pq_sesstat会话级并行统计 SELECT * FROM v$pq_sesstat WHERE statistic LIKE %Queries%; -- v$pq_sysstat系统级并行统计 SELECT * FROM v$pq_sysstat;5.3 并行操作降级的诊断当系统并行进程不足时请求的DOP与实际使用的DOP可能存在差异-- 检查并行降级情况 SELECT name, value FROM v$sysstat WHERE name LIKE %parallel% AND name LIKE %downgraded%; -- 检查并行进程排队等待情况 SELECT name, value FROM v$sysstat WHERE name LIKE Parallel operations%queued;六、并行查询的常见问题与解决方案6.1 并行进程不足现象SQL使用了并行Hint但执行计划显示串行执行或执行时间远超预期。-- 诊断方法 SELECT * FROM v$px_process_sysstat WHERE statistic IN (Servers In Use, Servers Highwater); -- 如果Servers In Use经常接近parallel_max_servers需要增加该参数 ALTER SYSTEM SET parallel_max_servers 128 SCOPEBOTH;6.2 并行度设置过高导致性能下降现象并行度设置过大CPU上下文切换开销超过并行收益甚至导致系统整体变慢。建议初始并行度不超过CPU_CORES × 2对于I/O密集型操作可适当增大DOP对于CPU密集型操作DOP接近CPU核数即可-- 查看CPU核数 SELECT value FROM v$parameter WHERE name cpu_count; -- 合理的默认并行度 ALTER TABLE sales PARALLEL (DEGREE DEFAULT); -- DEFAULT模式下Oracle根据cpu_count和parallel_threads_per_cpu自动计算6.3 并行DDL操作后未关闭并行常见陷阱执行ALTER TABLE sales PARALLEL 8后后续所有对该表的查询都会默认使用并行在高并发OLTP环境下可能耗尽并行进程。-- 最佳实践DDL操作后恢复为串行 ALTER TABLE sales PARALLEL 8; CREATE INDEX idx_sales_date ON sales(sale_date) PARALLEL 8; ALTER TABLE sales NOPARALLEL; ALTER INDEX idx_sales_date NOPARALLEL;6.4 数据倾斜导致并行效率低下当数据分布不均匀时如按销售区域分区某个区域数据量远大于其他区域部分并行进程可能提前完成而闲置最慢的进程决定整体执行时间。解决方案使用HASH分发方式而非RANGE分发在分区设计时关注数据均衡性Oracle 12c的Adaptive Parallel Distribution可自动应对数据倾斜6.5 并行与RAC跨节点通信开销RAC环境下如果并行进程跨节点分布Table Queue的数据传输需要经过私有网络可能成为瓶颈。-- 限制并行在本地节点执行 ALTER SESSION SET parallel_force_local TRUE; -- 或在SQL中指定 SELECT /* PARALLEL(s 8) NO_PARALLEL_PROPAGATION */ ...七、并行查询最佳实践7.1 并行度选择原则| 负载类型 | 建议DOP | 说明 ||----------|---------|------|| 小表1万行 | 1串行 | 并行调度开销大于收益 || 中型表1万-100万行 | 2-4 | 适度并行 || 大型表100万行 | 4-16 | 根据CPU和I/O资源确定 || 超大表1亿行 | 16-64 | 需充分测试并监控资源 || DDL操作索引创建 | CPU_CORES × 2 | 利用全部CPU资源加速DDL |7.2 资源管理策略-- 使用Resource Manager限制并行资源使用 BEGIN DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( plan DAYTIME_PLAN, group_or_subplan OLTP_GROUP, parallel_degree_limit_p1 2, -- 限制OLTP组的并行度 mgmt_p1 80 -- CPU使用上限80% ); END; / -- 为并行查询创建专用的服务 BEGIN DBMS_SERVICE.CREATE_SERVICE( service_name batch_parallel_svc, network_name batch_parallel_svc ); END; / -- 在应用端通过服务名连接隔离OLTP和批处理负载7.3 并行与分区结合分区表天然支持分区级并行Partition-Wise Join可以大幅减少数据分发开销-- 创建分区表 CREATE TABLE sales ( sale_id NUMBER, sale_date DATE, region VARCHAR2(50), amount NUMBER ) PARTITION BY RANGE (sale_date) INTERVAL (NUMTOYMINTERVAL(1, MONTH)) ( PARTITION p202301 VALUES LESS THAN (TO_DATE(2023-02-01, YYYY-MM-DD)) ) PARALLEL 8;八、实战案例优化大表扫描的性能场景某数据仓库需要对一年的销售明细12亿行约500GB按区域汇总串行执行需要2小时。原始SQL串行SELECT region, SUM(amount), COUNT(*) FROM sales_detail WHERE sale_date BETWEEN DATE 2023-01-01 AND DATE 2023-12-31 GROUP BY region;优化方案-- 1. 临时设置表的并行度 ALTER SESSION SET parallel_degree_policy MANUAL; -- 2. 使用并行Hint执行查询 SELECT /* PARALLEL(s 16) */ region, SUM(amount), COUNT(*) FROM sales_detail s WHERE sale_date BETWEEN DATE 2023-01-01 AND DATE 2023-12-31 GROUP BY region; -- 3. 查看执行计划确认并行已启用 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format ALLSTATS LAST));执行计划关键输出| Id | Operation | Name | E-Rows | A-Time | Buffers | | 0 | SELECT STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 6 | | | | 3 | HASH GROUP BY | | 6 |00:08:35 | 152K | | 4 | PX RECEIVE | | 300M| | | | 5 | PX SEND HASH | :TQ10000 | 300M| | | | 6 | PX BLOCK ITERATOR | | 300M|00:07:12 | 12.4M | | 7 | TABLE ACCESS FULL| SALES_DETAIL | 300M|00:06:45 | 12.3M |优化效果并行16个进程执行总耗时从2小时降至8分35秒Buffer Cache使用152K个块逻辑读可控需确保parallel_max_servers 3216×2九、总结与建议并行查询是Oracle处理大规模数据的利器通过将任务分解为多个并行进程同时处理充分利用多核CPU和I/O带宽显著缩短处理时间。配置并行查询需要从三个层面考虑数据库参数parallel_max_servers、parallel_degree_policy、对象设置ALTER TABLE PARALLEL n、SQL Hint/ PARALLEL(t n)/三者灵活组合使用。执行计划中的PX COORDINATOR、PX SEND、PX RECEIVE等操作符是识别并行查询的关键标志TQ列展示了数据在进程组之间的流动路径。并行查询并非银弹小表无需并行、OLTP环境慎用并行、DDL后记得关闭并行、需关注并行进程是否充足。最佳实践从低并行度开始测试逐步增加直至CPU或I/O饱和使用Resource Manager隔离OLTP和批处理负载监控v$px_session和v$px_process_sysstat确保并行执行健康。调优箴言并行查询是用资源换时间的典型策略它的本质不是“让SQL跑得更快”而是“用更多的资源让SQL更快地完成”。在启用并行之前先确保SQL本身已经是串行最优的——并行一个糟糕的SQL只会更快地耗尽系统资源。
