Database Reference
In-Depth Information
the data is read from the disk) independently of the total throughput the disk I/O subsystem can deliver, the response
time is limited by the bandwidth that a single CPU core can use. This bandwidth is of course limited because of
hardware limitations of the data access path between the CPU core and the disk, and can't be used completely when
the execution is serial: when the server process is on the CPU, by definition it isn't accessing the disk (asynchronous
I/O operations are an exception to this) and thus, isn't taking advantage of the total throughput the disk I/O
subsystem can deliver.
Figure 15-5.
Serially executed SQL statements are processed by a single server process
The following SQL statement with the related execution plan shows an example of the processing illustrated in
Figure
15-5
:
SELECT * FROM t
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| T |
----------------------------------
The aim of parallel processing is to split one large task into several smaller subtasks. If there is a parallel
processed SQL statement, this basically means there are several
parallel query slave processes
(for simplicity, called
slave processes
throughout this topic) that cooperate to execute a single SQL statement. The coordination of the slave
processes is under the control of the server process associated to the session that submits the SQL statement. Because
of this role, it's commonly called the
query coordinator
. The query coordinator is responsible for acquiring the slave
processes, assigning a subtask to each of them, collecting and combining the partial result sets they deliver, and
returning the final result set to the client. For example, in the case of a SQL statement that carries out a scan of a whole
segment, the query coordinator can instruct each of the slave processes to scan part of the segment and to deliver the
necessary data to it. Figure
15-6
illustrates this. Because each of the four slave processes is able to run on a different
CPU core, in this case the response time is no longer limited by the bandwidth that a single CPU core can use.
