It is beyond the scope of these samples to describe the details of Oracle’s parallel query feature. Suffice it to say that when certain environment conditions are met (especially a hardware environment that supports multiple concurrently executing processes making concurrent disk accesses, and a user environment close to single-user) and when the objects referenced in a query have appropriate parallel attributes, then the elapsed time for long-running queries can be cut in direct proportion to the number of available CPUs. This is especially significant in decision support systems (a.k.a. DSS) both at query time and in the extraction, transformation and load (a.k.a. ETL) operations to populate them.
Oracle9i introduces table function features to allow their execution to be parallelized. These features require (with one small exception) that the table function has exactly one strongly typed ref cursor input parameter.
Oracle Corp compared pre-Oracle9i and Oracle9i performance using a Sun Ultra-Enterprise 4500 machine with 3 GB RAM and 10 CPUs at 168 MHz. A 1,000,000 row source table was used for a 1 row in to 7 rows out pivot transform. The pre-Oracle9i approach was a PL/SQL cursor loop with 7 INSERTs. The Oracle9i approach was a table function with 7 PIPE ROWs. The experiment is described in Performance and Scalability in DSS Environment with Oracle9i, by Neil Thombre, on /deploy/performance/.
The pre-Oracle9i approach took 87 minutes. The Oracle9i approach with no parallelization took 37 minutes (ie improvement factor 2.4x). The Oracle9i approach with parallelization degree 20 took 12 minutes (ie improvement factor 7.5x over the pre-Oracle9i baseline).