并行查询，并行DML简介

最新推荐文章于 2025-04-03 18:41:23 发布

最新推荐文章于 2025-04-03 18:41:23 发布 · 536 阅读

文章标签：

#并发

Oracle Basic Principle 专栏收录该内容

57 篇文章

订阅专栏

本文介绍了Oracle数据库中并行查询的基本概念及其实现方式，并通过实例展示了如何使用并行查询来提高SELECT语句的执行效率。此外，还探讨了并行DML的功能及其启用方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--并行查询简介

并行查询允许将一个SELECT 语句划分为多个较小的查询，每个部分的查询并发地执行，然后将每个部分的结果组合起来，提供最终的答案。


EODA@PROD1> explain plan for select count(status) from big_table;

Explained.

EODA@PROD1> select * from table(dbms_xplan.display(null, null, 'TYPICAL -ROWS -BYTES -COST'));  --查看最初的执行计划

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 599409829

---------------------------------------------------
| Id  | Operation	   | Name      | Time	  |
---------------------------------------------------
|   0 | SELECT STATEMENT   |	       | 00:00:05 |
|   1 |  SORT AGGREGATE    |	       |	  |
|   2 |   TABLE ACCESS FULL| BIG_TABLE | 00:00:05 |
---------------------------------------------------

9 rows selected.

EODA@PROD1> alter table big_table parallel;  --启用并行查询

Table altered.

EODA@PROD1> explain plan for select count(status) from big_table;

Explained.
 
EODA@PROD1> select * from table(dbms_xplan.display(null, null, 'TYPICAL -ROWS -BYTES -COST'));  --查看并行执行计划

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2894119656

------------------------------------------------------------------------------------
| Id  | Operation	       | Name	   | Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |	   | 00:00:03 |        |      | 	   |
|   1 |  SORT AGGREGATE        |	   |	      |        |      | 	   |
|   2 |   PX COORDINATOR       |	   |	      |        |      | 	   |
|   3 |    PX SEND QC (RANDOM) | :TQ10000  |	      |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |	   |	      |  Q1,00 | PCWP | 	   |
|   5 |      PX BLOCK ITERATOR |	   | 00:00:03 |  Q1,00 | PCWC | 	   |
|   6 |       TABLE ACCESS FULL| BIG_TABLE | 00:00:03 |  Q1,00 | PCWP | 	   |
------------------------------------------------------------------------------------

13 rows selected.

--对比发现并行查询时间为3s快于不并行状态下的5s
--ID=5将全部扫描分解成多个较小的扫描
--ID=4每个小扫描汇总其count值
--ID=3，2 子结果被传送到并行查询协调器
--ID=1进一步汇总这个结果

--并行DML
Oracle文档将并行DML一词的范围限制为只包括INSERT/UPDATE/DELETE/MERGE。

--显示启用并行DML
EODA@PROD1> alter session enable parallel dml;

Session altered.

--确认当前会话启用并行DML
EODA@PROD1> select pdml_enabled from v$session where sid = sys_context('userenv' ,'sid');

PDM
---
YES

EODA@PROD1> select sys_context('userenv', 'sid') from dual;  --查看当前sid

SYS_CONTEXT('USERENV','SID')
----------------------------------------------------------------------------------------------------
55


--执行UPDATE
EODA@PROD1> update big_table set status = 'done';

--与此同时在另一会话查询PDML操作的事务信息
EODA@PROD1> column program format a30
EODA@PROD1> column trans_id form a20
EODA@PROD1> select a.sid, a.program, b.start_time, b.used_ublk,
  2  b.xidusn ||'.'|| b.xidslot || '.' || b.xidsqn trans_id
  3  from v$session a, v$transaction b
  4  where a.taddr = b.addr
  5  and a.sid in ( select sid
  6  from v$px_session
  7  where qcsid = 55)
  8  order by sid
  9  /

       SID PROGRAM			  START_TIME		USED_UBLK TRANS_ID
---------- ------------------------------ -------------------- ---------- --------------------
	33 oracle@ocm1 (P002)		  12/01/16 01:12:23	      244 11.0.944
	42 oracle@ocm1 (P001)		  12/01/16 01:12:23		1 12.12.947
	48 oracle@ocm1 (P000)		  12/01/16 01:12:23		1 16.29.930
	55 sqlplus@ocm1 (TNS V1-V3)	  12/01/16 01:12:23		1 14.33.957
	56 oracle@ocm1 (P003)		  12/01/16 01:12:23	       19 15.21.1148

--在一个新的会话中查看执行计划
EODA@PROD1> explain plan for update big_table set status = 'done';

Explained.

EODA@PROD1> select * from table(dbms_xplan.display(null,null,'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2685615093

------------------------------------------------------------------------
| Id  | Operation	      | Name	  |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      | 	  |	   |	  |	       |
|   1 |  UPDATE 	      | BIG_TABLE |	   |	  |	       |
|   2 |   PX COORDINATOR      | 	  |	   |	  |	       |
|   3 |    PX SEND QC (RANDOM)| :TQ10000  |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR | 	  |  Q1,00 | PCWC |	       |
|   5 |      TABLE ACCESS FULL| BIG_TABLE |  Q1,00 | PCWP |	       |
------------------------------------------------------------------------

12 rows selected.
--由于新会话此前并未启用并行DML，所以看到的并不是全并行的执行计划，此处的UPDATE是串行执行的。

EODA@PROD1> alter session enable parallel dml;  --启用PDML

Session altered.

EODA@PROD1> explain plan for update big_table set status = 'done';

Explained.

EODA@PROD1> select * from table(dbms_xplan.display(null,null,'BASIC +PARALLEL'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2864480563

---------------------------------------------------------------------------
| Id  | Operation		 | Name      |	  TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------
|   0 | UPDATE STATEMENT	 |	     |	      |      |		  |
|   1 |  PX COORDINATOR 	 |	     |	      |      |		  |
|   2 |   PX SEND QC (RANDOM)	 | :TQ10001  |	Q1,01 | P->S | QC (RAND)  |
|   3 |    INDEX MAINTENANCE	 | BIG_TABLE |	Q1,01 | PCWP |		  |
|   4 |     PX RECEIVE		 |	     |	Q1,01 | PCWP |		  |
|   5 |      PX SEND RANGE	 | :TQ10000  |	Q1,00 | P->P | RANGE	  |
|   6 |       UPDATE		 | BIG_TABLE |	Q1,00 | PCWP |		  |
|   7 |        PX BLOCK ITERATOR |	     |	Q1,00 | PCWC |		  |
|   8 | 		TABLE ACCESS FULL| BIG_TABLE |	Q1,00 | PCWP |		  |
---------------------------------------------------------------------------

15 rows selected.
--启用PDML后，UPDATE也被并行执行。