Parallel execution in RAC Environment

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s