oracle 并行原理

先来看看oralce官方文档的解释吧

Parallel execution enables the application of multiple CPU and I/O resources to the execution of a single database operation. It dramatically reduces response time for data-intensive operations on large databases typically associated with a decision support system (DSS) and data warehouses. You can also implement parallel execution on an online transaction processing (OLTP) system for batch processing or schema maintenance operations such as index creation. Parallel execution is sometimes called parallelism. Parallelism is the idea of breaking down a task so that, instead of one process doing all of the work in a query, many processes do part of the work at the same time.An example of this is when four processes combine to calculate the total sales for a year, each process handles one quarter of the year instead of a single process handling all four quarters by itself. The improvement in performance can be quite significant. Parallel execution improves processing for:

    Queries requiring large table scans, joins, or partitioned index scans       

    Creation of large indexes        

    Creation of large tables (including materialized views)        

    Bulk insertions, updates, merges, and deletions       

解释一下吧，并行执行就是使用多个cpu和I/O资源去完成一个数据库操作，看打标记的那句话，并行是将一个任务打碎，让很多进程去执行原来应该有一个进程完成的动作。使用并行操作可以减少响应时间，但是这个和你的系统资源息息相关，如果系统资源缺乏，是用并行效果会跟差，并且增加资源的消耗。

oracle并行执行的机制

  当cbo判断一个会话的使用了并行，oracle会将server process 转换为一个并行协调进程，Oracle启动时候，oracle使用默认参数parallel_min_servers来确定预先创建的slave process数，如果需要的slave process超出了oracle刚开始的创建的process，则并行协调进程将创建额外的slave process。然后并行协调进程将要处理的对象打碎，分给slave process处理，处理完成之后再汇总给server process，由server process将数据进行处理并返回给客户。

  我们来看一个图：如图1

  

    Oracle 使用了并行度为2来执行图中的sql，那么oracle使用了两个slave process  p1，p2  来扫描customer这张表，扫描完成后，Oracle又启动了两个进程p3，p4，然后p1，p2 进程将扫描的数据分别传到对应的p3，p4进程中，由p3，p4进程执行group by操作。执行完成以后p3，p4进程，将数据送到p1，p2进程（因为扫描完数据后，p1，p2进程已经空闲，所以oracle没有启动新的进程），然后进行order by操作，最后将数据送到协调进程返回给用户。

前面就是一个并行执行的典型例子，但是并行进程之间的交互是怎么进行的了，oracle官方文档中是如下描述的：

 To execute a query in parallel, Oracle Database generally creates a set of producer parallel execution servers and a set of consumer parallel execution servers. The producer server retrieves rows from tables and the consumer server performs operations such as join, sort, DML, and DDL on these rows. Each server in the producer set has a connection to each server in the consumer set. The number of virtual connections between parallel execution servers increases as the square of the degree of parallelism.

Each communication channel has at least one, and sometimes up to four memory buffers, which are allocated from the shared pool. Multiple memory buffers facilitateasynchronous communication among the parallel execution servers.

A single-instance environment uses at most three buffers for each communication channel. An Oracle Real Application Clusters environment uses at most four buffers for each channel.Figure 8-3illustrates message buffers and how producer parallel execution servers connect to consumer parallel execution servers.

 

Figure 8-3 Parallel Execution Server Connections and Buffers

When a connection is between two processes on the same instance, the servers communicate by passing the buffers back and forth in memory (in the shared pool). When the connection is between processes in different instances, the messages are sent using external high-speed network protocols over the interconnect. InFigure 8-3, the DOP equals the number of parallel execution servers, which in this case is n.Figure 8-3does not show the parallel execution coordinator. Each parallel execution server actually has an additional connection to the parallel execution coordinator. It is important to size the shared pool adequately when using parallel execution. If there is not enough free space in the shared pool to allocate the necessary memory buffers for a parallel server, it fails to start.

读懂并行执行计划

   先来看operation这一行，可以看到出现了PX，表示Oracle使用了并行了。根据执行计划的读法以及上面了解的并行的执行过程，可以对执行计划做如下解读：

       1.