How the Parallel hint works

Question

One of the first things I see a crowd suggest to boost the performance of a query in the Oracle is the use of hint Parallel. What benefits/risks the use of this hint can bring, I may have problems with competition?

Answer 1

The Parallel hint divides the internal statements made by db by the amount of processors / colors up to the limit set by you and allowed in the Oracle configuration. It is recommended that this hint in results with aggregation - group by (sum(), min(), max(), etc.), in queries that will be done full table scan. The benefits will always be the decrease of the query time, and the main risk is the excessive consumption of server resources.

Answer 2

Parallelism, available in the version Oracle Database Enterprise Edition, offers the idea of breaking a task into pieces so that, instead of a single process doing all the work, many processes can simultaneously execute the parts and in the end present a single result in less time than the one executed by a single process.

When an SQL query is executed in parallel, a process called Query Coordinator (QC) and several other processes Queries Slaves, called Pn, where n varies according to the number of parallelism indicated in the command. For example, when we use hint /*+ parallel (P,4) */, we are creating a QC process and the Slaves: P1, P2, P3 and P4.

We have the operation of a Parallel query taking as a starting point the consultation:

select /*+ paralel (e, 4) */ from emp order by ename ;

Represented by the figure:

The Query Coordinator (QC) is responsible for coordinating the implementation of Queries Slaves, gather the information received by each process slave and present the result set to the user who sent the SQL query. The Queries Slaves are truly responsible for collecting the data of the SQL query itself. When Queries Slaves end the reading of the data, buffer.

These messages have their size specified by the parameter parallel_execution_message_size and are exchanged in a specific area of buffer.

The parameter parallel_automatic_tuning define the place of this message buffer. If the parameter is set to TRUE, the buffer will be used in large pool, if set to FALSE the memory area used shall be Shared pool. Therefore, it is recommended that if the database performs many parallel queries, study an appropriate size for this memory area and in addition set minimum values for these memory areas.

This shows that the use of parallelism in Sqls should be aligned with the DBA and well tested, so that memory parameters are configured coherently. Another important point to be mentioned is that the opening of several parallel processes in an SQL causes slowness also in the own opening of processes.

Benefits:
Parallel execution provides performance improvements by optimizing the use of server hardware resources, that is, we can use all the Cpus of a server in a single activity which would provide a significant gain in the process, for example. This same distribution can be performed in an environment clustered (RAC), where we can use, if necessary, the resources of all nodes for a single task/process.

Hazards We should always be aware of the consumption of server resources such as CPU, memory and disks, because parallelism can completely saturate a machine, being then in charge of the DBA carefully balancing the number of people performing parallel operations as to the degree of parallelism used so that the server’s resources are not exhausted.

About Problems with competition, I prefer not to explain in that answer, since in that question "It’s always guaranteed that a multi-threaded application runs faster than using a single thread?", the response of Maniero, explain the competition in a very interesting way.

Reading recommendation:
What is a racing condition?

Bibliography:

GRAF, Anderson. Parallel execution of SQL statements: Parallel Query, DML and DDL. Accessed on: 01 August 2017.

FIAMENGUI, José Eduardo. Parallelism: Myths and Facts. Accessed on: 01 August 2017.