关于表的并行处理

并行执行计划中IN-OUT含义：
Parallel to Serial(P->S):
表示一个并行操作发送数据给一个串行操作，通常是并行进程将数据发给并行调度进程
Parallel to Parallel(P->P):
表示一个并行进程操作向另一个并行操作发送数据。通常是两个从属进程之间的数据交流。
Parallel Combined With Parent(PCWP):
同一个从属进程执行的并行操作，同时父操作也是并行的。
Parallel Combined With Child(PCWC):
同一个从属进程执行的并行操作，子操作也是并行的。
Serial to Parallel(S->P):
一个串行操作发送数据给并行操作


关于并行相关的初始化参数：
parallel_max_servers
如果并行度的值大于parallel_min_servers或者当前可用的并行服务器进程
不能满足Sql的并行执行要求，ORACLE将创建新的并行服务器进程，当前实例
总共启动的并行服务进程不超过这个参数的设定。
parallel_adaptive_multi_user= [true|false]
默认开启，ORACLE会根据sql执行时的负载情况，动态的调整sql的并行度。以
取得最好的性能。
parallel_min_percent
是一个百分比数值。11gR2默认为0。如果当前的并行服务进程不足，按照百分比申请所需的
并行服务器进程。
例如：当前参数值为50。假设一个sql需申请20个并行服务进程时，当前的并行服务进程不足，
按这个参数要求，sql必须申请到20*50%=10个并行服务器进程，如果申请不到这个数量，
会报出 ora-12827错误
12827, 00000, "insufficient parallel query slaves (requested %s, available %s, parallel_min_percent %s)"
// *Cause: PARALLEL_MIN_PERCENT parameter was specified and fewer than
//         the minimum number of slaves were acquired
// *Action: Either reexecute the query with a lower PARALLEL_MIN_PERCENT or
//          wait until some running queries are completed, thus freeing
//          up slaves
以下的这个parallel_min_percent官方说明。
PARALLEL_MIN_PERCENT lets you specify the minimum percentage of the requested number of parallel 
execution processes required for parallel execution. Setting this parameter ensures that parallel operations will not execute unless adequate resources are available. 
The default value of 0 means that no minimum percentage of processes has been set.

Consider the following settings:

PARALLEL_MIN_PERCENT = 50
PARALLEL_MIN_SERVERS = 5
PARALLEL_MAX_SERVERS = 10
If 8 of the 10 parallel execution processes are busy, only 2 processes are available. If you then request a query with a degree of parallelism of 8, the minimum 50% will not be met.

You can use this parameter in conjunction with PARALLEL_ADAPTIVE_MULTI_USER. In a multi-user environment, an individual user or application can set PARALLEL_MIN_PERCENT to a minimum value until sufficient resources are available on the system and an acceptable degree of parallelism is returned.

--简单测试 dml parallel
--session

--默认degree=1
SQL> select distinct sid from v$mystat;

       SID
----------
        85
        
--session没有 enable parallel dml
SQL> select pdml_enabled from v$session where sid =85;

PDM
---
NO

SQL> show parameter PARALLEL_ADAPTIVE_MULTI_USER;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_adaptive_multi_user         boolean     TRUE

SQL> show parameter parallel_min;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_min_time_threshold          string      AUTO
--创建测试表t,没有更改表属性parallel
SQL> create table t as select * from dba_objects;

Table created.

SQL> select degree from user_tables where table_name ='T';

DEGREE
--------------------
         1

SQL> set autotrace trace;

SQL> set linesize 1000;
--hint 开启parallel会启动并行处理
SQL> delete /*+parallel(2)*/ from t;

75381 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 1038566389

-------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |          |     1 |   166   (0)| 00:00:01 |        |      |            |
|   1 |  DELETE               | T        |       |            |          |        |      |            |
|   2 |   PX COORDINATOR      |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 |     1 |   166   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |          |     1 |   166   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| T        |     1 |   166   (0)| 00:00:01 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
   - Degree of Parallelism is 2 because of hint


Statistics
----------------------------------------------------------
        136  recursive calls
      84329  db block gets
       1267  consistent gets
          0  physical reads
   28475984  redo size
        840  bytes sent via SQL*Net to client
        782  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      75381  rows processed

SQL> rollback;

Rollback complete.

--不加hint 因为表degree为1，没有默认启用parallel
SQL> delete from t;

75381 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 2034385699

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |     1 |   300   (1)| 00:00:04 |
|   1 |  DELETE            | T    |       |            |          |
|   2 |   TABLE ACCESS FULL| T    |     1 |   300   (1)| 00:00:04 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
        157  recursive calls
      84351  db block gets
       1218  consistent gets
          0  physical reads
   28476808  redo size
        842  bytes sent via SQL*Net to client
        765  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      75381  rows processed

SQL> rollback;

Rollback complete.
--更改parallel值，再测试一次，执行计划并没有更改并行度
SQL> delete /*+parallel(8)*/ from t;

75381 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 1038566389

-------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |          |     1 |    42   (0)| 00:00:01 |        |      |            |
|   1 |  DELETE               | T        |       |            |          |        |      |            |
|   2 |   PX COORDINATOR      |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 |     1 |    42   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |          |     1 |    42   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| T        |     1 |    42   (0)| 00:00:01 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
   - Degree of Parallelism is 8 because of hint


Statistics
----------------------------------------------------------
        202  recursive calls
      84363  db block gets
       1712  consistent gets
          0  physical reads
   28477364  redo size
        842  bytes sent via SQL*Net to client
        782  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      75381  rows processed

SQL> drop table t purge;

Table dropped.

--创建 degree为4 table
SQL> create table t parallel 4 as select * from dba_objects;

Table created.

SQL> set autotrace off; 

SQL> select degree from user_tables where table_name ='T';

DEGREE
--------------------
         4
SQL> set autotrace trace;
--默认会启动并行，无需手工hint
SQL> delete from t;

75381 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 1038566389

-------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |          |     1 |    87   (0)| 00:00:02 |        |      |            |
|   1 |  DELETE               | T        |       |            |          |        |      |            |
|   2 |   PX COORDINATOR      |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 |     1 |    87   (0)| 00:00:02 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |          |     1 |    87   (0)| 00:00:02 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| T        |     1 |    87   (0)| 00:00:02 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         30  recursive calls
      84222  db block gets
       1625  consistent gets
          0  physical reads
   28474736  redo size
        845  bytes sent via SQL*Net to client
        765  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      75381  rows processed