Oracle7 Server Tuning

Parallel Query Processing

Without the parallel query option, the processing of a SQL statement is always performed by a single server process. With the parallel query option, multiple processes can work together simultaneously to process a single SQL statement. This capability is called parallel query processing. By dividing the work necessary to process a statement among multiple server processes, the Oracle Server can process the statement more quickly than if only a single server process processed it.

The parallel query option can dramatically improve performance for data-intensive operations associated with decision support applications or very large database environments. Symmetric multiprocessing (SMP), clustered, or massively parallel systems gain the largest performance benefits from the parallel query option because query processing can be effectively split up among many CPUs on a single system.

It is important to note that the query is parallelized dynamically at execution time. Thus, if the distribution or location of the data changes, Oracle automatically adapts to optimize the parallelization for each execution of a SQL statement.

The parallel query option helps systems scale in performance when adding hardware resources. If your system's CPUs and disk controllers are already heavily loaded, you need to alleviate the system's load before using the parallel query option to improve performance. The section "Tuning for the Parallel Query Option" describes how your system can achieve the best performance with the parallel query option.

The Oracle Server can use parallel query processing for any of these statements:

SELECT statements

subqueries in UPDATE, INSERT, and DELETE statements

CREATE TABLE ... AS SELECT statements

CREATE INDEX

Parallel Query Process Architecture

Without the parallel query option, a server process performs all necessary processing for the execution of a SQL statement. For example, to perform a full table scan (for example, SELECT * FROM EMP), one process performs the entire operation. Figure 6 - 1 illustrates a server process performing a full table scan:

Figure 6 - 1. Full Table Scan without the Parallel Query Option

The parallel query option allows certain operations (for example, full table scans or sorts) to be performed in parallel by multiple query server processes. One process, known as the query coordinator, dispatches the execution of a statement to several query servers and coordinates the results from all of the servers to send the results back to the user. Figure 6 - 2 illustrates several query server processes simultaneously performing a partial scan of the EMP table. The results are then sent back to the query coordinator, which assembles the pieces into the desired full table scan.

Figure 6 - 2. Multiple Query Servers Performing a Full Table Scan in Parallel

The query coordinator process is very similar to the server processes in previous releases of the Oracle Server. The difference is that the query coordinator can break down execution functions into parallel pieces and then integrate the partial results produced by the query servers. Query servers get assigned to each operation in a SQL statement (for example, a table scan or a sort operation), and the number of query servers assigned to a single operation is the degree of parallelism for a query.

The query coordinator calls upon the query servers during the execution of the SQL statement (not during the parsing of the statement). Therefore, when using the parallel query option with the multi-threaded server, the server processing the EXECUTE call of a user's statement becomes the query coordinator for the statement.

CREATE TABLE ... AS SELECT in Parallel

Decision support applications often require large amounts of data to be summarized or "rolled up" into smaller tables for use with ad hoc, decision support queries. Rollup often must occur regularly (such as nightly or weekly) during a short period of system inactivity. Because the summary table is derived from other tables' data, the recoverability from media failure for the smaller table may or may not be important and can be turned off. The parallel query option allows you to parallelize the operation of creating a table as a subquery from another table or set of tables.

Figure 6 - 3 illustrates creating a table from a subquery in parallel.

Figure 6 - 3. Creating a Summary Table in Parallel

If you disable recoverability during parallel table creation, you should take a backup of the tablespace containing the table once the table is created to avoid loss of the table due to media failure. For more information about recoverability of tables created in parallel, see the Oracle7 Server Administrator's Guide.

Clustered tables cannot be created and populated in parallel.

For a discussion of the syntax of the CREATE TABLE command, see the Oracle7 Server SQL Reference.

When creating a table in parallel, each of the query server processes uses the values in the STORAGE clause. Therefore, a table created with an INITIAL of 5M and a PARALLEL DEGREE of 12 consumes at least 60M of storage during table creation because each process starts with an extent of 5M. When the query coordinator process combines the extents, some of the extents may be trimmed, and the resulting table may be smaller than the requested 60M.

For more information on how extents are allocated when using the parallel query option, see Oracle7 Server Concepts.