Compaq ProLiant 6500 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 18

Page 16 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Parallel Execution When Oracle is not parallelizing the execution of SQL statements, each SQL statement is done sequentially by a single process. With parallel execution, multiple processes work together simultaneously to execute a single SQL statement. These processes will be referred to as parallel query processes or parallel server processes, but in the Windows NT architecture they are really threads of the Oracle process. The Oracle8 server can use parallel executions for operations such as table scans, joins, group by, order by, union, select distinct, aggregation, PL/SQL functions, create table as select, create/rebuild index, move/split partition, update, delete, insert...select, enable constraint (the table scan is parallelized) and star transformations. Oracle8 parallelizes SQL statements in the following ways. • Parallelize by block ranges for scan operations - Parallel scans by block range break the table or index into pieces delimited by high and low ROWID values. The table or index can be non-partitioned or partitioned. For partitioned objects, the ROWID range cannot span a partition. Oracle sends the partition numbers with the ROWID ranges to avoid a partition map lookup. Predicates on the partitioning columns can be used to restrict the ROWID ranges to relevant partitions only (known as partition pruning). • Parallelize by partitions for operations on partitioned tables and indexes - Partitions are already a logical division of the tables and indexes. These can be used to break up operations into smaller operations executed in parallel. This is done by assigning a different parallel server process to different partitions. Each partition will be accessed by a single parallel server process, but a parallel server process may need to access multiple partitions. • Parallelize by parallel server processes for inserts into nonpartitioned tables only - Oracle8 can parallelize the work of insert operations by dividing the work among the parallel server processes. Since new rows do not have ROWIDs, the rows are distributed among the server processes to insert them into the free space. Degree of Parallelism When Oracle8 utilizes parallel execution, two or more of the instance's parallel server processes may be used to process a SQL statement. The number of parallel server processes associated with a single operation is known as the degree of parallelism. The degree of parallelism is specified at the statement level, using hints or the PARALLEL clause; at the table or index level, in the table or index definition; or by default based on the number of disks or CPUs. The following are factors involved in determining the degree of parallelism. • The query uses the maximum degree of parallelism taken from all of the tables involved in the query and all of the potential indexes that are candidates to satisfy the query. The table or index that has the highest degree of parallelism determines the query's degree of parallelism. Keep in mind, when setting the degree of parallelism, too high of a degree of parallelism on one table may exhaust some other resource of your system like memory or CPU bandwidth for example. • If a table has both a PARALLEL hint specification in the query and a parallel declaration in its table definition, the hint specification takes precedence over the parallel declaration of the table. • If the maximum number of parallel server processes, determined by the PARALLEL_MAX_SERVERS, is already performing parallel tasks, the query must continue the remainder of its operations sequentially. PARALLEL_MAX_SERVERS is the total number of sequential streams accessing data plus the number of parallel engines for sorting, average, sum, count and other aggregate functions. PARALLEL_MAX_SERVERS should be set to two times the maximum degree of parallelism for each table times the number of users. © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398