Parallel query is the capability to take a SQL statement such as a SELECT, CREATE TABLE, CREATE INDEX, UPDATE, and so on and create an execution plan that consists of many execution plans that can be done simultaneously.
The outputs of each of these plans are merged together into one larger result. The goal is to do an operation in a fraction of the time it would take if you did it serially.
For example, say you have a really large table spread across ten different files. You have 16 CPUs at your disposal, and you need to execute an ad hoc query on this table.
It might be advantageous to break the query plan into 32 little pieces and really make use of that machine, as opposed to just using one process to read and process all of that data serially.
When using a parallel query, you will see processes named Pnnn—these are the parallel query execution servers themselves.
During the processing of a parallel statement, your server process will be known as the parallel query coordinator. Its name won’t change at the operating system level, but as you read documentation on parallel query, when you see references to the coordinator process, know that it is simply your original server process.
With the most recent versions of Oracle, your instance will automatically create several parallel server processes.
This occurs because the PARALLEL_MIN_SERVERS parameter is set to a nonzero value (derived from CPU_COUNT * PARALLEL_THREADS_PER_CPU * 2).
For example, on my two CPU box (CPU_COUNT parameter is 2 and PARALLEL_THREADS_PER_CPU parameter is 1), we see the following four parallel execution servers running:
$ ps -ef | grep ora_p00 | grep -v grep
oracle | 24790 | 1 | 0 | 18:55 ? | 00:00:00 ora_p000_CDB | ||
oracle | 24792 | 1 | 0 | 18:55 ? | 00:00:00 ora_p001_CDB | ||
oracle | 24811 | 1 | 0 | 18:55 | ? | 00:00:00 | ora_p002_CDB |
oracle | 24813 | 1 | 0 | 18:55 | ? | 00:00:00 | ora_p003_CDB |
Tip See Chapter 14 for full details on parallel processing.
Summary
We’ve covered the files used by Oracle, from the lowly but important parameter file to datafiles, redo log files, and so on. We’ve taken a look inside the memory structures used by Oracle, both in the server processes and the SGA. We’ve seen how different server configurations, such as shared server vs. dedicated server mode for connections, will have a dramatic impact on how memory is used by the system. Lastly, we looked at the processes (or threads, depending on the operating system) that enable Oracle to do what it does. Now we are ready to look at the implementation of some other features of Oracle, such as locking, concurrency controls, and transactions.