Parallel Operation in Oracle Server- Does it improve Performance?

Feb. 12, 2011 - PRLog -- 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.

About Gitesh Trivedi:
Gitesh Trivedi has excellent 14+ years exposure in Oracle Database skill set. Present he is working as Technical Lead in Dbametrix Solution and providing online Oracle dba training in kendba.com. You can reach him on http://www.dbametrix.com or http://www.kendba.com

# # #

Expert remote Oracle DBA team offers remote dba support,remote dba services,remote dba work,remote database monitoring,database services,outsourcing with very low cost plan using SLA.