Oracle Performance Comparisons and Monitoring Parallel Operations

By: Richard Niemiec


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.

 

0719_001 0720_001

 

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.

 

0720_002

 

For this test, the elapsed time was 2 minutes, 30 seconds. Next, run the query using PARALLEL:

 

0720_003

 

For this test, the elapsed time was just over one minute. The query runs more than twice as fast, with a degree of 6.

TIP

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:

 

0721_001

 

 

Leave a Reply