Parallel execution in RAC Environment
In RAC, a SQL statement is executed in parallel can run across all the nodes in the cluster environment.
Handle the parallel execution in RAC as follows
PARALLEL_FORCE_LOCAL parameter is true. with this parameter parallel execution happen only on the node where the SQL started execution.
Services: Parallel execution happen on only that instance which is the member of the services. Client connect direct to this service.
PARALLEL_INSTANCE_GROUP: It specify the service name or instance group where the parallel execution need to be happen. It can be set at session or system level.
Get the sql id for sql query which try to execute parallel as follows:
alter table tran_sales parallel 10;
select * from tran_sales order by tran_id;
-- Finding SQL Id for sql queries
select sql_id from v$sql where sql_fulltext like '%select * from tran_sales order by tran_id%' and sql_fulltext not like '%V$SQL%';
Check the parallel operation running on all nodes:
select INST_ID,USERNAME,OWNERID,PQ_STATUS from gv$session where sqlid = 'abcdefghi' order by inst_id;
Change the behaviour with parameter:
ALTER session set parallel_instance_group='SERVICE1';
SERVICE1 is used only one node as preferred server to run parallel execution in one node only.
Check all the service running in ORCL database:
srvctl status service -db orcl