oracle 并行学习 ( 二)

bgktpdyxaw

　　并行查询
　　 
　　下面的操作不管是在查询中还是在子查询中都可以并行的执行：
　　1.全表扫描、全分区扫描以及快速全索引扫描
　　2.索引完全或范围扫描，但只是在索引被分区的情况下
　　3.表连接
　　4.集合操作符
　　5.排序操作
　　6.聚合操作
　　以上操作：全表扫描、全分区扫描和快速全索引扫描在并行执行时是使用直接读(direct read)操作，从而可以避开高速缓存。然而，索引完全和范围扫描还是使用普通的物理读。
　　 
　　并行查询默认是启用的。在绘画级别，可以使用下面的SQL语句启用或禁用并行查询：
　　 
　　ALTER SESSION ENABLE PARALLEL QUERY
　　 
　　ALTER SESSION DISABLE PARALLEL QUERY
　　 
　　另外，还可以通过使用下面的SQL语句来启动并行查询，并同时覆盖表或索引级别定义的并行度：
　　 
　　ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4
　　 
　　需要注意的是：提示可以覆盖会话级别的设置。一方面，即使在绘画级别禁用了并行查询，提示可以强制执行一个并行操作。唯一可以用来关闭并行查询的方法是将初始化参数parallel_max_servers设置为0.另一方面，即使在绘画级别强制设置了一个并行度，提示还是可以改变成另外一个并行度。
　　查询在会话级别启用或禁用并行：
　　select pq_status from v$session where sid=sys_context('userenv','sid')
　　 
　　操作如下：
　　创建测试表：
　　已用时间:  00: 00: 00.43
LIBIN@orac>
LIBIN@orac>CREATE TABLE t1 AS
  2  SELECT rownum AS id, rpad('*',100,'*') AS pad
  3  FROM dual
  4  CONNECT BY level <= 10000;
　　表已创建。
　　已用时间:  00: 00: 00.68
LIBIN@orac>
LIBIN@orac>execute dbms_stats.gather_table_stats(user, 't1')
　　PL/SQL 过程已成功完成。
　　已用时间:  00: 00: 12.20
LIBIN@orac>
LIBIN@orac>DROP TABLE t2;
DROP TABLE t2
           *
第 1 行出现错误:
ORA-00942: table or view does not exist
　　
已用时间:  00: 00: 00.09
LIBIN@orac>
LIBIN@orac>CREATE TABLE t2 AS SELECT * FROM t1;
　　表已创建。
　　已用时间:  00: 00: 01.56
LIBIN@orac>
LIBIN@orac>execute dbms_stats.gather_table_stats(user, 't2')
　　PL/SQL 过程已成功完成。
　　已用时间:  00: 00: 03.45
LIBIN@orac>
　　会话级别显示并行状态
　　LIBIN@orac>ALTER SESSION DISABLE PARALLEL QUERY;
　　会话已更改。
　　已用时间:  00: 00: 00.01
LIBIN@orac>
LIBIN@orac>SELECT pq_status
  2  FROM v$session
  3  WHERE /* sid = sys_context('userenv','sid') */
  4        audsid = sys_context('userenv','sessionid');
PQ_STATUS
----------------
DISABLED
　　已用时间:  00: 00: 00.04
　　LIBIN@orac>ALTER SESSION ENABLE PARALLEL QUERY;
　　会话已更改。
　　已用时间:  00: 00: 00.03
LIBIN@orac>SELECT pq_status
  2  FROM v$session
  3  WHERE /* sid = sys_context('userenv','sid') */
  4        audsid = sys_context('userenv','sessionid');
PQ_STATUS
----------------
ENABLED
　　已用时间:  00: 00: 00.03
　　LIBIN@orac>ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;
　　会话已更改。
　　已用时间:  00: 00: 00.01
LIBIN@orac>
LIBIN@orac>SELECT pq_status
  2  FROM v$session
  3  WHERE /* sid = sys_context('userenv','sid') */
  4        audsid = sys_context('userenv','sessionid');
PQ_STATUS
----------------
FORCED
　　已用时间:  00: 00: 00.03
　　用提示覆盖会话级别的设置
LIBIN@orac>ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;
　　会话已更改。
　　已用时间:  00: 00: 00.01
LIBIN@orac>explain plan for select /*+ noparallel(t1) */ count(*) from t1;
　　已解释。
　　已用时间:  00: 00: 00.04
LIBIN@orac>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 3724264953
　　-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    38   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |    38   (0)| 00:00:01 |
-------------------------------------------------------------------
　　已选择9行。
　　已用时间:  00: 00: 00.40
　　LIBIN@orac>ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;
　　会话已更改。
　　已用时间:  00: 00: 00.01
LIBIN@orac>explain plan for select /*+ parallel(t1 4) */ count(*) from t1;
　　已解释。
　　已用时间:  00: 00: 00.03
LIBIN@orac>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3110199320
　　--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |    11   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          | 10000 |    11   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T1       | 10000 |    11   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------
　　已选择13行。
　　已用时间:  00: 00: 00.11
LIBIN@orac>ALTER SESSION ENABLE PARALLEL QUERY;
　　会话已更改。
　　已用时间:  00: 00: 00.01
　　全表扫描测试
　　LIBIN@orac>EXPLAIN PLAN FOR SELECT count(*) FROM t1;
　　已解释。
　　已用时间:  00: 00: 00.06
LIBIN@orac>SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3724264953
　　-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    38   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |    38   (0)| 00:00:01 |
-------------------------------------------------------------------
　　已选择9行。
　　已用时间:  00: 00: 00.46
　　带并行提示的全表扫描
　　LIBIN@orac>EXPLAIN PLAN FOR SELECT /*+ parallel(t1 2) */ count(*) FROM t1;
　　已解释。
　　已用时间:  00: 00: 00.03
LIBIN@orac>SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3110199320
　　--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |    21   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          | 10000 |    21   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T1       | 10000 |    21   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------
　　已选择13行。
　　已用时间:  00: 00: 00.07
　　并行快速索引扫描测试
　　LIBIN@orac>EXPLAIN PLAN FOR SELECT /*+ index_ffs(t1) parallel_index(t1 4) */ count(id) FROM t1;
　　已解释。
　　已用时间:  00: 00: 00.03
LIBIN@orac>SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2596547647
　　-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |     1 |     4 |     6   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE           |          |     1 |     4 |            |          |        |      |            |
|   2 |   PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000 |     1 |     4 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |          |     1 |     4 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR    |          | 10000 | 40000 |     6   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       INDEX FAST FULL SCAN| I1       | 10000 | 40000 |     6   (0)| 00:00:01 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------
　　已选择13行。
　　已用时间:  00: 00: 00.09
　　并行索引完全扫描
　　LIBIN@orac>EXPLAIN PLAN FOR SELECT /*+ index(t1) parallel_index(t1 4) */ count(id) FROM t1;
　　已解释。
　　已用时间:  00: 00: 00.15
LIBIN@orac>SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2594672137
　　-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     4 |    22   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |      |     1 |     4 |            |          |
|   2 |   INDEX FULL SCAN| I1   | 10000 | 40000 |    22   (0)| 00:00:01 |
-------------------------------------------------------------------------
　　已选择9行。
　　已用时间:  00: 00: 00.07
　　索引范围扫描测试
　　LIBIN@orac>EXPLAIN PLAN FOR SELECT /*+ index(t1) parallel_index(t1 4) */ * FROM t1 WHERE id > 9000;
　　已解释。
　　已用时间:  00: 00: 00.04
LIBIN@orac>SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1704772559
　　------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |  1000 |   101K|    20   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |  1000 |   101K|    20   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I1   |  1000 |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------
　　Predicate Information (identified by operation id):
---------------------------------------------------
　　2 - access("ID">9000)
　　已选择14行。
　　已用时间:  00: 00: 00.09
　　分区索引并行测试
　　LIBIN@orac>CREATE INDEX i1 ON t1 (id) GLOBAL PARTITION BY HASH (id) PARTITIONS 4;
　　索引已创建。
　　已用时间:  00: 00: 00.34
　　索引完全扫描，并行度为4
　　LIBIN@orac>EXPLAIN PLAN FOR SELECT /*+ index(t1) parallel_index(t1 4) */ count(id) FROM t1;
　　已解释。
　　已用时间:  00: 00: 00.03
LIBIN@orac>SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2520697847
　　-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |     1 |     4 |    25   (0)| 00:00:01 |       |       |        |      |            |
|   1 |  SORT AGGREGATE           |          |     1 |     4 |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR          |          |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000 |     1 |     4 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |          |     1 |     4 |            |          |       |       |  Q1,00 | PCWP |            |
|   5 |      PX PARTITION HASH ALL|          | 10000 | 40000 |    25   (0)| 00:00:01 |     1 |     4 |  Q1,00 | PCWC |            |
|   6 |       INDEX FULL SCAN     | I1       | 10000 | 40000 |    25   (0)| 00:00:01 |     1 |     4 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------
　　已选择13行。
　　已用时间:  00: 00: 00.09
　　索引范围扫描，并行度为4
　　LIBIN@orac>EXPLAIN PLAN FOR SELECT /*+ index(t1) parallel_index(t1 4) */ * FROM t1 WHERE id > 9000;
　　已解释。
　　已用时间:  00: 00: 00.01
LIBIN@orac>SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1630224034
　　----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |  1000 |   101K|    20   (0)| 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR                |          |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)          | :TQ10000 |  1000 |   101K|    20   (0)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION HASH ALL       |          |  1000 |   101K|    20   (0)| 00:00:01 |     1 |     4 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1       |  1000 |   101K|    20   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|*  5 |      INDEX RANGE SCAN          | I1       |  1000 |       |     4   (0)| 00:00:01 |     1 |     4 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------
　　Predicate Information (identified by operation id):
---------------------------------------------------
　　5 - access("ID">9000)
　　已选择17行。
　　已用时间:  00: 00: 00.09
　　综合测试
　　LIBIN@orac>EXPLAIN PLAN FOR SELECT /*+ leading(t1) use_hash(t2) index(t1) parallel_index(t1 2) full(t2) parallel(t2 2) pq_distribute(t2 hash,hash) */ *
  2  FROM t1, t2
  3  WHERE t1.id > 9000 AND t1.id = t2.id+1;
　　已解释。
　　已用时间:  00: 00: 00.01
LIBIN@orac>SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3513713489
　　-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |    50 | 10400 |    33   (4)| 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR                   |          |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)             | :TQ10002 |    50 | 10400 |    33   (4)| 00:00:01 |       |       |  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED             |          |    50 | 10400 |    33   (4)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                    |          |  1000 |   101K|    11   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH                 | :TQ10000 |  1000 |   101K|    11   (0)| 00:00:01 |       |       |  Q1,00 | P->P | HASH       |
|   6 |       PX PARTITION HASH ALL       |          |  1000 |   101K|    11   (0)| 00:00:01 |     1 |     4 |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS BY INDEX ROWID| T1       |  1000 |   101K|    11   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|*  8 |         INDEX RANGE SCAN          | I1       |  1000 |       |     2   (0)| 00:00:01 |     1 |     4 |  Q1,00 | PCWP |            |
|   9 |     PX RECEIVE                    |          |   500 | 52000 |    21   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  10 |      PX SEND HASH                 | :TQ10001 |   500 | 52000 |    21   (0)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |
|  11 |       PX BLOCK ITERATOR           |          |   500 | 52000 |    21   (0)| 00:00:01 |       |       |  Q1,01 | PCWC |            |
|* 12 |        TABLE ACCESS FULL          | T2       |   500 | 52000 |    21   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------------
　　Predicate Information (identified by operation id):
---------------------------------------------------
　　3 - access("T1"."ID"="T2"."ID"+1)
   8 - access("T1"."ID">9000)
  12 - filter("T2"."ID"+1>9000)
　　已选择26行。
　　已用时间:  00: 00: 00.14