By glennf on May 29, 2008
ResultsI created a 20GB table on a T5240 server to serve as the source for the "Create as Select" (CAS) operations. The parallelism of the CAS operation was increased until the IO subsystem was maxed out. This resulted in a drop from 25 minutes with no parallelism to 2 minutes 40 seconds with 8 threads...thats nearly a 10x speedup by simply using parallelism built into Oracle!
This server was configured with just two HBAs, one for each the source and destination tables. This limited throughput of CAS operations to 127MB/sec, or one HBA. With this IO configuration, it took only 8 threads to reach maximum throughput. You should experiment to achieve maximum throughput of your IO configuration. If you suspect your IO configuration is not performing up to speed, look into doing some IO micro benchmarking to find the maximum throughput outside of Oracle. A topic for a later discussion
SQL syntaxThe following shows how to use parallel DML and parallel query.
## Create as Select ## ## SQL> alter session enable parallel dml; SQL> create table abc parallel (degree 32) as select /\*+ parallel(gtest, 32) \*/ \* from gtest; ## Insert as Select ## ## SQL> alter session enable parallel dml; SQL> insert /\*+ parallel(abc,32) \*/ into abc select /\*+ parallel(gtest,32) \*/ \* from gtest;