Oracle7 Server Tuning

Setting the Degree of Parallelism

The query coordinator process may enlist two or more of the instance's query server processes to process the statement. The number of query server processes associated with a single operation is known as the degree of parallelism. The degree of parallelism is specified at the query level (with hints), at the table level (in the table's definition), or by default in the initialization parameter file. Note that the degree of parallelism applies only to the intra-operation parallelism. If inter-operation parallelism is possible, the total number of query servers can be twice the specified degree of parallelism.

Determining the Degree of Parallelism for Operations

The query coordinator determines the degree of parallelism by considering three specifications. The query coordinator first checks for query hints, then looks at the table's definition, and finally checks initialization parameters for the instance for the default degree of parallelism. Once a degree of parallelism is found in one of these specifications, it becomes the degree of parallelism for the query.

For queries involving more than one table, the query coordinator requests the greatest number specified for any table in the query. For example, on a query joining the EMP and DEPT tables, if EMP's degree of parallelism is specified as 5 and DEPT's degree of parallelism is specified as 6, the query coordinator would request six query servers for each operation in the query.

Keep in mind that no more than two operations can be performed simultaneously. Therefore, the maximum number of query servers requested for any query can be up to twice the degree of parallelism per instance.

Hints, the table definitions, or initialization parameters only determine the number of query servers that the query coordinator requests for a given operation. The actual number of query servers used depends upon how many query servers are available in the query server pool and whether inter-operation parallelism is possible.

When you create a table and populate it with a subquery in parallel, the degree of parallelism for the population is determined by the table's degree of parallelism. If no degree of parallelism is specified in the newly created table, the degree of parallelism is derived from the subquery's parallelism. If the subquery cannot be parallelized, the table is created serially.

Hints

Hints allow you to set the degree of parallelism for a table in a query and the caching behavior of the query. Refer to Chapter 7, "Tuning SQL Statements", for a general discussion on using hints in queries and the specific syntax for the PARALLEL, NOPARALLEL, CACHE, and NOCACHE hints.

Table and Cluster Definition Syntax

You can specify the degree of parallelism within a table definition. Use the CREATE TABLE, ALTER TABLE, CREATE CLUSTER, or ALTER CLUSTER statements to set the degree of parallelism for a table or clustered table. Refer to the Oracle7 Server SQL Reference for the complete syntax of those commands.

Default Degree of Parallelism

The default degree of parallelism is always determined by two initialization parameters. First, Oracle estimates the number of blocks in the table (based on statistics in the data dictionary) and divides that number by the value of the initialization parameter PARALLEL_DEFAULT_SCANSIZE. Next, you can limit the number of query servers to use by default by setting the initialization parameter PARALLEL_DEFAULT_MAX_SCANS. The smaller value of these two methods is used as the default degree of parallelism.

For example, if you have a table with 50,000 blocks and the parameter PARALLEL_DEFAULT_SCANSIZE is set to 1000, the default degree of parallelism is 50 (this also implies that queries on tables with no fewer than 2000 blocks will be parallelized by default). If you have limited resources to devote to parallel query processing, you might want to limit the default degree of parallelism by setting the parameter PARALLEL_DEFAULT_MAX_SCANS. In this example, if PARALLEL_DEFAULT_MAX_SCANS is set to 25, then the default degree of parallelism would be 25.

Limiting the Number of Available Instances

The INSTANCES keyword of the CREATE/ALTER TABLE/CLUSTER commands allows you to specify that a table or cluster is split up among the buffer caches of all available instances of an Oracle Parallel Server. If you do not want tables to be dynamically partitioned among all the available instances, you can specify the number of instances that can participate in scanning or caching with the parameter PARALLEL_DEFAULT_MAX_INSTANCES or the ALTER SYSTEM command.

If you want to specify the number of instances to participate in parallel query processing at startup time, you can specify a value for the initialization parameter PARALLEL_DEFAULT_MAX_INSTANCES. See the Oracle7 Server Reference for more information about this parameter.

If you want to limit the number of instances available for parallel query processing dynamically, use the ALTER SYSTEM command. For example, if you have ten instances running in your Parallel Server, but you want only eight to be involved in parallel query processing, you can specify a value by issuing the following command:

ALTER SYSTEM SET SCAN_INSTANCES = 8;

Therefore, if a table's definition has a value of ten specified in the INSTANCES keyword, the table will be scanned by query servers on eight of the ten instances. Oracle selects the first eight instances in this example. Set the parameter PARALLEL_MAX_SERVERS to zero on the instances that you do not want to participate in parallel query processing.

If you wish to limit the number of instances that cache a table, you can issue the following command:

ALTER SYSTEM SET CACHE_INSTANCES = 8;

Therefore, if a table specifies the CACHE keyword with the INSTANCES keyword specified as 10, it will divide evenly among eight of the ten available instances' buffer caches.