Oracle 并行原理深入解析及案例精粹

一、简单介绍OLTP和OLAP系统的特点小结答：OLTP和OLAP是我们大家在日常生产库中最常用到的2种系统，简单的说OLTP是基于多事务短时

引言：首先说明并行技术属于大数据范畴，适合OLAP系统，在任务分割、数据块分割、资源充裕的场合应用较广，本次分享主要概括并行原理、实际应用、性能对比、并行直接加载、索引属性、特点小结等六个小点去重点阐述。下面的测试是我的笔记，这些笔记也参考了《让Oracle跑得更快2》作者：谭怀远 一书的引导，在此向谭总表示感谢，向帮助过我们的人表示感谢 zhixiang yangqiaojie等好友，下面我们就开始快乐的旅途！

一、简单介绍OLTP和OLAP系统的特点小结

答：OLTP和OLAP是我们大家在日常生产库中最常用到的2种系统，简单的说OLTP是基于多事务短时间片的系统，内存的效率决定了数据库的效率。
OLAP是基于大数据集长时间片的系统，SQL执行效率决定了数据库的效率。因此说“并行parallel”技术属于OLAP系统范畴
二、并行技术实现机制和场合
答：并行是相对于串行而言的，一个大的数据块分割成n个小的数据块，同时启动n个进程分别处理n个数据块，最后由并行协调器coordinater整合结果返回给用户。实际上在一个并行执行的过程中还存在着并行进程之间的通信问题（并行间的交互操作）。上面也说过并行是属于大数据处理的技术适合OLAP，并不适合OLTP，因为OLTP系统中的sql执行效率通常都是非常高的。
三、测试并行技术在实际中的应用和规则
（1)在有索引的表leo_t上使用并行技术，但没有起作用的情况
创建一张表
LS@LEO> create table leo_t as select rownum id ,object_name,object_type from dba_objects;
在表id列上创建索引
LS@LEO> create index leo_t_idx on leo_t(id);
收集表leo_t统计信息
LS@LEO> execute dbms_stats.gather_table_stats(ownname=>'LS',tabname=>'LEO_T',method_opt=>'for all indexed columns size
2',cascade=>TRUE);
为表启动4个并行度
LS@LEO> alter table leo_t parallel 4;
启动执行计划
LS@LEO> set autotrace trace explain stat
LS@LEO> select * from leo_t where id=100;  使用索引检索的数据，并没有启动并行
Execution Plan  执行计划
----------------------------------------------------------
Plan hash value: 2049660393
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    28 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| LEO_T     |     1 |    28 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | LEO_T_IDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=100)
Statistics   统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets   4次一致性读，即处理4个数据块
          0  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
说明：我们在这个表上启动了并行但没有起作用是因为CBO优化器使用了B-tree索引来检索的数据直接就定位到rowid（B-tree索引特点适合重复率比较低的字段），所以才发生了4个一致性读，发现使用索引效率非常高，资源代价比较小没有使用并行的必要了。
（2)读懂一个并行执行计划
LS@LEO> select object_type,count(*) from leo_t group by object_type;  对象类型分组统计
35 rows selected.
Execution Plan   并行执行计划
----------------------------------------------------------
Plan hash value: 852105030
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          | 10337 |   111K|     6  (17)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 | 10337 |   111K|     6  (17)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY         |          | 10337 |   111K|     6  (17)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          | 10337 |   111K|     6  (17)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND HASH        | :TQ10000 | 10337 |   111K|     6  (17)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   6 |       HASH GROUP BY      |          | 10337 |   111K|     6  (17)| 00:00:01 |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          | 10337 |   111K|     5   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| LEO_T    | 10337 |   111K|     5   (0)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------
Statistics   统计信息
----------------------------------------------------------
         44  recursive calls
          0  db block gets
        259  consistent gets  259次一致性读，即处理259个数据块
          0  physical reads
          0  redo size
       1298  bytes sent via SQL*Net to client
        403  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         35  rows processed
ps -ef | grep oracle  从后台进程上看也能发现起了4个并行进程和1个协调进程
oracle   25075     1  0 22:58 ?        00:00:00 ora_p000_LEO
oracle   25077     1  0 22:58 ?        00:00:00 ora_p001_LEO
oracle   25079     1  0 22:58 ?        00:00:00 ora_p002_LEO
oracle   25081     1  0 22:58 ?        00:00:00 ora_p003_LEO
oracle   25083     1  0 22:58 ?        00:00:00 ora_p004_LEO
说明：在进行分组整理的select中，会处理大量的数据集（发生了259次一致性读），这时使用并行来分割数据块处理可以提高效率，因此oracle使用了并行技术，解释一下并行执行计划步骤，并行执行计划应该从下往上读，当看见PX（parallel execution）关键字说明使用了并行技术
1.首先全表扫描
2.并行进程以迭代iterator的方式访问数据块，并将扫描结果提交给父进程做hash group
3.并行父进对子进程传递过来的数据做hash group操作
4.并行子进程（PX SEND HASH）将处理完的数据发送出去，子和父是相对而言的，我们定义发送端为子进程，接收端为父进程
5.并行父进程（PX RECEIVE）将处理完的数据接收
6.按照随机顺序发送给并行协调进程QC（query coordinator）整合结果（对象类型分组统计）
7.完毕后QC将整合结果返回给用户
说明并行执行计划中特有的IN-OUT列的含义（指明了操作中数据流的方向）             
Parallel to Serial（P->S）: 表示一个并行操作向一个串行操作发送数据，通常是将并行结果发送给并行调度进程QC进行汇总
Parallel to Parallel（P->P）：表示一个并行操作向另一个并行操作发送数据，一般是并行父进程与并行子进程之间的数据交流。
Parallel Combined with parent(PCWP): 同一个从属进程执行的并行操作，同时父操作也是并行的。
Parallel Combined with Child(PCWC): 同一个从属进程执行的并行操作，同时子操作也是并行的。
Serial to Parallel（S->P）: 表示一个串行操作向一个并行操作发送数据，如果select部分是串行操作，就会出现这个情况
（3）介绍4个我们常用的并行初始化参数
parallel_min_percent           50%    表示指定SQL并行度最小阀值才能执行，如果没有达到这个阀值，oracle将会报ora-12827错误
parallel_adaptive_multi_user  TRUE    表示按照系统资源情况动态调整SQL并行度，已取得最好的执行性能
parallel_instance_group               表示在几个实例间起并行
parallel_max_servers          100     表示整个数据库实例的并行进程数不能超过这个值
parallel_min_servers          0       表示数据库启动时初始分配的并行进程数，如果我们设置的并行度小于这个值，并行协调进程会按我们的并行度来分配并行进程数，如果我们设置的并行度大于这个值，并行协调进程会额外启动其他的并行进程来满足我们的需求
（4）使用hint方式测试DML并行查询性能
首先说一下什么时候可以使用并行技术
1.对象属性：在创建的时候，就指定了并行关键字，长期有效
2.sql强制执行：在sql中使用hint提示方法使用并行，临时有效，它是约束sql语句的执行方式，本次测试就是使用的hint方式
LS@LEO> select /*+ parallel(leo_t 4) */ count(*) from leo_t where object_name in (select /*+ parallel(leo_t1 4) */ object_name from
leo_t1);
Execution Plan   执行计划
----------------------------------------------------------
Plan hash value: 3814758652
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |     1 |    94 |    16   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE           |          |     1 |    94 |            |          |        |      |            |
|   2 |   PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10002 |     1 |    94 |            |          |  Q1,02 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |          |     1 |    94 |            |          |  Q1,02 | PCWP |            |
|*  5 |      HASH JOIN SEMI       |          | 10337 |   948K|    16   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   6 |       PX RECEIVE          |          | 10337 |   282K|     5   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   7 |        PX SEND HASH       | :TQ10000 | 10337 |   282K|     5   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   8 |         PX BLOCK ITERATOR |          | 10337 |   282K|     5   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   9 |          TABLE ACCESS FULL| LEO_T    | 10337 |   282K|     5   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  10 |       PX RECEIVE          |          | 10700 |   689K|    11   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  11 |        PX SEND HASH       | :TQ10001 | 10700 |   689K|    11   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |
|  12 |         PX BLOCK ITERATOR |          | 10700 |   689K|    11   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  13 |          TABLE ACCESS FULL| LEO_T1   | 10700 |   689K|    11   (0)| 00:00:01 |  Q1,01 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------
并行先扫描子查询leo_t1表，然后对主查询leo_t表进行扫描，，按照随机顺序发送到并行协调进程QC整合结果，最后将结果返回给用户
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("OBJECT_NAME"="OBJECT_NAME")
Note
-----
   - dynamic sampling used for this statement
Statistics   统计信息
----------------------------------------------------------
         28  recursive calls
          0  db block gets
        466  consistent gets   466次一致性读，即处理了446个数据块
          0  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed