Back to Papers
Single-threaded Full Table Scan
First, letís look at the default Single-threaded architecture.
Figure 1 - Standard Single-threaded Architecture using Dedicated Server Process
This should be very familiar. The User Process (on the client or server) submits a SELECT statement that requires a full table scan of the EMP table and the Dedicated Server Process is responsible for retrieving the results and returning them to the User Process.
Parallel Full Table Scan - Degree 2
Letís look at how things change when we enable Parallel Execution.
Figure 2 Ė Parallel Full Table Scan Ė Degree 2
This time, the server is going to process the query in parallel as a result of the optimizer hint. When the server sees that the requested Degree of Parallelism (DOP) for the emp table is two the dedicated server process becomes the Query Coordinator. It makes a request for two PX slaves and, if itís able to acquire them, it will divide all of the blocks that it would have had to scan in the emp table into two equal ranges. Then it will send a SQL statement similar to the following to each of the slave processes.
SELECT /*+ Q1000 NO_EXPAND ROWID(A1) */ A1."EMPNO",A1."ENAME",A1."JOB", A1."MGR",A1."HIREDATE",A1."SAL",A1."COMM",A1."DEPTNO" FROM "EMP" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A1
Note that Oracle 10g changes this approach slightly, so that the SQL statement associated with the slave processes will be the same as the Query Co-ordinator, i.e.
SELECT /*+ parallel (emp,2) */ * FROM EMP
Although this makes things a little easier to follow, itís more difficult to get at the detail of what various px slaves are doing because you canít use the SQL statement to differentiate them or find the cost for a given slaveís SQL statement.
As the data is retrieved from the emp table, it will be returned to the query co-ordinator which will, in turn, return the data to the user process. The way that all of the data is moved between the processes is using areas of memory called parallel execution message buffers or table queues. These can be stored in either the Shared Pool or Large Pool.
Parallel Full Table Scan with Sort
Figure 3 ĖParallel Full Table Scan with Sort Ė Degree 2
The first thing to note is that there is no PARALLEL hint in the query and yet Oracle chooses to use Parallel Execution to process it. The reason is that the emp table itself also has a parallel DEGREE setting which allows us to specify whether Oracle should attempt to parallelise operations against that table and in this case, itís been set to 2 (Note that the use of PX implies the use of the cost-based optimiser, so you should make sure that you have appropriate statistics on the object too) :-
ALTER TABLE emp PARALLEL (DEGREE 2); SELECT table_name, degree FROM user_tables WHERE table_name = 'EMP'; TABLE_NAME DEGREE ------------------------------ ---------- EMP 2
Hold on a minute! We requested a DOP of two and yet there are four PX slaves being used to process our request. This is because Oracle will often use two sets of PX slaves for a specific action. The first set produces rows (and are known as producers) and the second set (called consumers) consumes the rows produced by the producers. So, in this case, Oracle can see that we are going to have to perform a sort because the NAME column isnít indexed, so it requests 4 PX slaves Ė two sets of two. The first set are responsible for scanning the EMP table and the second set for sorting the data as itís delivered by the producers. As you can see, though, the type of operation defines the way the workload is distributed between the slaves in each set. For the full table scan, itís based on block ranges. For the sort, the QC process acts as a Ranger and divides up the sort activity by calculating the correct range of values for each slave to sort so that theyíll process a reasonably equal number of rows. The sort slaves will receive statements similar to the following.
SELECT A1.C0 C0,A1.C1 C1,A1.C2 C2,A1.C3 C3,A1.C4 C4,A1.C5 C5,A1.C6 C6,A1.C7 C7 FROM :Q1000 A1 ORDER BY A1.C0
There are a couple of very important things to note :-