Also assume that the DOP for the query is set to 4, which means that four parallel execution servers can be active for any given operation.If parallel execution is chosen, then the following steps occur.The parallel execution servers performs each operation in parallel if possible.
For example, a parallel query with a SUM() operation requires adding the individual subtotals calculated by each parallel server. Parallels With Oracle 11G Full Table ScanFor example, the parallel execution method might be to perform a parallel full table scan by block range or a parallel index range scan by partition. The coordinator must decide whether an operation can be performed in parallel and, if so, how many parallel execution servers to enlist. The number of parallel execution servers in one set is the degree of parallelism (DOP). As an example of parallel query with intra- and inter-operation parallelism, consider the query in Example 8-1. In other words, the consumers in SS2 and the producers in SS1 work concurrently: one in scanning customers in parallel, the other is consuming rows and building the hash table to enable the hash join in parallel. It sends its rows to servers in SS2, which then perform the probes to finish the hash-join in parallel. After SS1 has scanned the sales table in parallel and sent the rows to SS2, it switches to performing the GROUP BY operation in parallel. This is how two server sets run concurrently to achieve inter-operation parallelism across various operators in the query tree. For the query plan in Example 8-2, after a server process in SS1 scans a row from the customers table, which server process in SS2 should it send it to The operator into which the rows are flowing decides the redistribution. In this case, the redistribution of rows flowing up from SS1 performing the parallel scan of customers into SS2 performing the parallel hash-join is done by hash partitioning on the join column. That is, a server process scanning customers computes a hash function of the value of the column customers.custid to decide the number of the server process in SS2 to send it to. ![]() In Figure 8-1, the GROUP BY SORT operation is the consumer of the HASH JOIN operation because GROUP BY SORT requires the HASH JOIN output. In Example 8-2, while the parallel execution servers are producing rows in the FULL SCAN of the sales table, another set of parallel execution servers can begin to perform the HASH JOIN operation to consume the rows. Therefore, both query operations and the data flow tree itself have parallelism. The parallelism of an individual operation is called intra-operation parallelism and the parallelism between operations in a data flow tree is called inter-operation parallelism. Due to the producer-consumer nature of the Oracle Database operations, only two operations in a given tree must be performed simultaneously to minimize execution time. To illustrate intra- and inter-operation parallelism, consider the following statement. This operation is followed by a sorting of the retrieved rows, based on the value of the lastname column. For the sake of this example, assume the lastname column is not indexed.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2020
Categories |