并行查询，并行DML简介

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