6.4 Oracle and parallel processing

---

Oracle also supports parallel processing. Oracle's basic architecture (the background and foreground processes) demonstrates a parallel processing architecture if implemented on a hardware that supports multiple processors. While Oracle supports most of the operating systems on most hardware platforms, its architecture may not scale on the smaller platforms that have only one processor but may cause high wait times because one processor has to be shared by the many background and foreground processes.

With platforms that support multiple processors, user requests could be implemented to take advantage of the multiple processors by executing user requests in parallel and returning the results much more quickly. Parallel processing is implemented on either a single stand-alone database configuration or on multi-instance database configurations, such as RAC.

When Oracle executes SQL statements in parallel, multiple processes work together simultaneously to execute a single SQL statement. By dividing the work necessary to execute a statement among multiple processes, Oracle can execute the statement more quickly than if a single process executed it.

Parallel execution dramatically reduces response time for data-intensive operations on large databases such as those typically associated with decision support systems (DSS), data warehouses, certain types of online transaction processing (OLTP), and hybrid systems. Systems that have multiple processors can provide the greatest performance benefits to parallel execution because statement processing can be split up among many CPUs on a single Oracle system.

Parallel execution is useful for many types of operations that access significant amounts of data. Parallel execution improves performance for:

• Queries that are doing a full table scan

• Fast full index scans

• The creation of large indexes

• DML operations doing bulk inserts, updates, and deletes

• Aggregations and copying

• Partitioned index scans

• Database recovery

Parallel execution benefits systems that have the following characteristics:

• Sufficient I/O bandwidth

• Underutilization (in the sense that the CPUs are mostly idle)

• Sufficient memory to handle multiple requests including sorting, hashing, and additional I/O buffers

Basically, parallel processing requires additional resources to accom modate the additional processing requests. Lack of these additional resources to accommodate these requests could potentially cause a negative effect on the system.

There are certain areas of the application where parallel execution will not be beneficial, for example:

• Applications that have small singleton transactions with a short duration, normally retrieving data from one table.

• Systems that have fewer resources available to take on this additional load of parallel operation. Basically, systems that are already heavy on CPU usage or are I/O bound are not suitable for parallel executions.

---