Parallel Execution (Parallel Hint)

 Parallel Execution (Parallel Hint):-

Parallel execution is a commonly used method of speeding up operations by splitting a task in smaller sub tasks.

Imagine that your task is to count the number of cars in a street. There are two ways to do this, one, you can go through the street by yourself and count the number of cars or you can enlist a friend and then the two of you can start on opposite ends of the street, count cars until you meet each other and add the results of both counts to complete the task.

Assuming your friend counts equally fast as you do, you expect to complete the task of counting all cars in a street in roughly half the time compared to when you perform the job all by yourself. If this is the case then your operations scales linearly; 2x the number of resources halves the total processing time.

The database is not very different from the counting cars example. If you allocate twice the number of resources and achieve a processing time that is half of what it was with the original amount of resources, then the operation scales linearly. Scaling linearly is the ultimate goal of parallel processing, both in counting cars as well as in delivering answers from a database query.

Processing Parallel SQL Statement:-

0. When you execute a SQL statement in the Oracle Database it is decomposed into individual steps or row-sources, which are identified as separate lines in an execution plan. Below is an example of a simple SQL statement that touches just one table and its execution plan. The statement returns the total number of customers in the CUSTOMERS table: 
   
   SELECT count(*) FROM customers c;
   
   
   
   
   
   
   logistical processing steps due to the parallel processing that we did not have before.
   following section discusses these concepts that help you understand the parallel execution setup in your database and read the basics of parallel SQL execution plans. 
   
   Query Coordinator(QC) and Parallel Server (PX):-
   
   called the Query Coordinator – QC for short) and parallel execution (PX) server processes. The QC is the session that initiates the parallel SQL statement and the PX servers are the individual sessions that perform work in parallel. The QC distributes the work to the PX servers and may have to perform a minimal
   QC is the session that initiates the parallel SQL statement and the PX servers are the individual
   sessions that perform work in parallel. The QC distributes the work to the PX servers and may
   have to perform a minimal – mostly logistical – portion of the work that cannot be executed in parallel. For example a parallel query with a SUM() operation requires a final adding up of all individual sub-totals calculated by each PX server.
   parallel. For example a parallel query with a SUM() operation requires a final adding up of all
   individual sub-totals calculated by each PX server.
   given operation . All the work shown below the QC
   entry in our sample parallel plans is done by the PX servers.
   
   
   
   
   
   0. A more complex serial execution plan would be one that includes a join between multiple tables. In the example below, information about purchases made by customers is requested. This requires a join between the CUSTOMERS and SALES tables. 
      
      SELECT c.name, s.purchase_date, s.amount
      FROM customers c, sales s
      WHERE s.customer_id = c.id ;
      
      
      
   
   
   
   
   
   If you execute a statement in parallel, the Oracle Database will parallelize as many of the individual steps as possible and reflects this in the execution plan. If we were to re-execute the two statements above in parallel we could get the following execution plans1.
   
   

   

   
   

   
   
   
   These plans look quite a bit different than before, mainly because we are having additional
   
   
   
   
   SQL parallel execution in the Oracle database is based on a few fundamental concepts. The
   
   
   
   
   
   
   
   
   
   
   SQL parallel execution in the Oracle Database is based on the principles of a coordinator (often The QC is easily identified in the parallel execution plans above as 'PX COORDINATOR'  The process acting as the QC of a parallel SQL operation is the actual user session process itself.
   
   
   
   
        The PX servers are taken from a pool of globally available PX server processes and assigned to a
   given operation (the setup is discussed in a later section). All the work shown below the QC
   entry in our sample parallel plans  is done by the PX servers.