--并行查询简介
并行查询允许将一个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也被并行执行。