To show the performance difference between a nonparallel operation and a parallel operation in Oracle, I performed the following tests:
- Started the database with 12 parallel server processes and checked the background processes that were created
- Ran a query without PARALLEL and checked the speed
- Ran a query with PARALLEL that required sorting with a degree of 6
- Checked the output of V$PQ_SYSSTAT and V$PQ_SESSTAT
The next listing shows the ps -ef output (ps -ef is a Unix or Linux OS command) for 12 running parallel servers. I started the database with the parameter PARALLEL_MIN_SERVERS = 12. The name of the database is FDR1.
Next, run the query without using parallel execution servers. A partial result set is shown here. You can time this in a variety of ways, or by just SELECT SYSDATE FROM DUAL.
For this test, the elapsed time was 2 minutes, 30 seconds. Next, run the query using PARALLEL:
For this test, the elapsed time was just over one minute. The query runs more than twice as fast, with a degree of 6.
Increasing the degree of a parallel operation does not always decrease the execution time. It depends on your complete system setup. The degree specifies only the number of parallel execution servers that should be used for the operation. The number of parallel execution servers used depends on the parameter settings and the Database Resource Manager settings.
The following listing shows the V$ view data when executing the preceding query using PARALLEL with a degree of 12: