Parallel Operation in Oracle Server- Does it improve Performance?

February 12, 2011 | By

database-servicesParallel Operation in Oracle Server- Does it improve Performance?

Article explains detail characteristic of Oracle Parallel operation execution. There are lots of benefits of Parallel execution but with some drawbacks in Oracle Database server.

Any operation doesn’t consume or utilize whole server resource at any time. Reason behind this whole work is performing by single server process in database. Means if we have more resources like more CPU then little bit resource of CPU is consumed by server process during whole operation. In short we can say that we are not utilizing full server resource at any time. There are two types of operations available in Oracle database server. First one is called Serial operation which is default behavior of Oracle server. Single server process fetches data or performs operation serially. This kind of operation doesn’t consume more resource of Oracle server. Serial operation can be not effective when large table objects or large operations. Performance of operation will be degrading. During this serial operation Oracle server doesn’t consume full server resources. Second operation called as parallel operation. During parallel operation more than single threads are performing operation simultaneously. Due to this reason full server resource utilized and providing result very quick. Parallel process is more effective for large Oracle databases. Performance of Oracle server will be upgrading whenever using parallel operation. One server process is controlling parallel threads and it will verify how the tasks required for the procedure are to be assigned.

Once processing of data finished then Oracle server process is gathering data serially and returning output. Hence we can say that in parallel process both operations are involving parallel and serial.

This is the same concept of our mathematics one man finishes work within 2 hours then more than 1 man will finish same work 2 hours/number of man. 2 men can finish same work within 1 hour and 4 men can finish same work within 30 min. When we are talking about 1 man finish it is serial operation and more than 1 man, it parallel operation.

We can’t say that parallel process is always faster than serial operation in Oracle Database server. Sometimes parallel operation is not more effective and not able to provide result as per requirement. We have seen that every server process has some child processes in parallel execution. Sometimes lack of memory child processes are not performing good task. Allocation of process are may be fully utilized by other processes. It also raises a related point that the finest point for one parallel operation may not actually be the best level for numerous operations on a Oracle server system in which all the processes are trying to run in parallel. In short we can say, the resources consumed by adding additional parallel processes up to certain point where the operation finishes the quickest may be comparatively greater than the supplementary performance improvement. Therefore, reducing the number of child processes to a level below where the operation would finish fastest method essentially offer the most advantage to the Oracle server. Before enabling parallel execution operation we should need to understand our Oracle server system. If you don’t know the Oracle server resources and characteristics then instead of improving performance of Oracle database it would be degrading performance of Oracle server.

Means we can say that usage of parallel execution may not provide guarantee to improve performance of Oracle database, but sometime it also risk to degrade the performance of Oracle server. Parallel operation is more usable in large databases, high end server, more CPU resources and optimized SQL coding. Without knowing these all things if we are enabling parallel option may be provide us negative impact in performance of server.

Partition table is more helpful to improve performance while using parallel execution. Partition table with more CPU is more target to increase scalability of database with parallel operations.

Checklist of Remote database services and remote database monitoring we should need to check system resource utilization when parallel queries are running.

Oracle improves parallel operation usage in own new features like RMAN, Parallel recovery, SQL Loader, EXPDP, IMPDP etc. Export doesn’t has parallel feature but datapump (expdp) has parallel execution feature in Oracle 10g. Rman is becoming more faster while using parallel feature for backup.

You can find out more Oracle DBA tips articles from our website in Oracle DBA tips section.

Wish you all the best

Thanks and regards,

Expert Remote DBA team – Dbametrix Solutions

Tags: , , , , , , , , ,

Category: blog, blogging, oracle 10g, oracle 10g dba, oracle 11g, oracle 11g dba, Oracle 11g RAC, oracle 9i dba, oracle performance, Oracle performance tuning, oracle tuning

Comments are closed.