How Parallel hint works

hint parallel divides the internal instructions made by db by the amount of processors / colors up to the limit you set 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 query time, and the main risk is the excessive consumption of server resources.

    

Parallelism, available in 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 unique result in less time than the one executed by a single process.

When a SQL query is executed in parallel, a process called Query Coordinator (QC) and several other processes Queries Slaves , called Pn, 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 a parallel query starting from the query:

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

Represented by the figure:

TheQueryCoordinatorisresponsibleforcoordinatingtheexecutionofQueriesSlaves,gatheringtheinformationreceivedbyeachslaveprocessandpresentingtheresultsetfortheuserwhosubmittedtheSQLquery.QueriesSlavesaretherealresponsibleforcollectingtheSQLquerydataitself.WhenQueriesSlavesfinishesreadingthedata,theysendthatdatatotheQCviabuffermessages.

Thesemessageshavetheirsizespecifiedbytheparameterparallel_execution_message_sizeandareexchangedinaspecificareaofbuffer.

Theparameterparallel_automatic_tuningdefinetheplaceofthismessagebuffer.IftheparameterissettoTRUE,thebufferwillbeusedinthelargepool,ifsettoFALSEthememoryareausedwillbethesharedpool.Therefore,itisrecommendedthatifthedatabaseperformsmanyparallelqueries,studyanappropriatesizeforthisareaofmemory,andalsosetminimumvaluesforthesememoryareas.

ThisdemonstratesthattheuseofparallelisminSQLsmustbealignedwiththeDBAandwelltested,sothatthememoryparametersareconfiguredconsistently.AnotherimportantpointtobementionedisthattheopeningofseveralparallelprocessesinaSQLcausesaslowprocessopeningitself.

Benefits:
Parallelexecutionprovidesperformanceimprovementsbyoptimizingtheserver'shardwareresources,iewecanusealltheCPUsofaserverinasingleactivity,whichwouldprovideasignificantgainintheprocess,forexample.Thissamedistributioncanbeperformedinaclusteredenvironment(RAC),wherewecanusetheresourcesofallnodesforasingletask/processifnecessary.

RisksWemustalwayspayattentiontotheconsumptionofserverresourcessuchasCPU,memoryanddisks,sinceparallelismcancompletelysaturateamachine,anditisthenuptotheDBAtocarefullybalancethenumberofpeopleperformingparalleloperationsasthedegreeofparallelismusedtotheserverresourcesarenotdepleted.

AboutCompetitionissues,I'drathernotexplainthisanswer,sinceinthisquestion" It is always guaranteed that one multi-threaded application runs faster than using a single thread? , the response from bigown , explain the competition in a very interesting way.

Read Recommendation:
What is a race 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.