Oracle Parallel Query Option

Last updated on September 14th, 2016 at 09:19 pm

What Parallel Query Option can perform

1.To speed up access to data by using multiple processes
2.To maximise machine resource usage in query processing
3.To reduce elapsed time by increasing resource usage

Operations That Can Run with Parallel Query Option
2. Full table scans  or table move
3.Fast full index scans
4.Create index or rebuild index
5.Create table as select

How Parallel Query works
1.Query coordinator parses the query and partitions work between the slaves
2.Query Slaves do the work and pass results back to the QC
3. Table Queues (TQ) are used to move data between processes
4.Query Coordinator passes results back to user
5.Query Slaves are background processes

Query Coordinator
1.The server shadow process of the session running the parallel query
2.Parses the query and determines degree of parallelism
3. Controls the query and sends instructions to the PQ slaves
4.Determines the work ranges for the PQ slaves
5. Produces the final output to the user

Parallel Query Slave
1 Controlled by the Query Coordinator process (hence the name Slave)
2. The processes which do most of the work in a parallel query
3. Slaves are allocated in slave sets, whichact as either PRODUCERS or CONSUMERS
4. A slave set may act as both PRODUCER and CONSUMER at different stages within a query

Parameters of Parallel Query Option
parallel_min_servers (default 0)- the number of slaves created at instance startup
parallel_max_servers (default 5)-slaves can be dynamically spawned as needed up to this
parallel_server_idle_time (default 5) -The amount of time (in minutes) after which a
dynamically spawned query slave will die if unused
Degree of Parallelism
Can be set as an attribute of a table or index
Can be specified as a hint in a query
Can be set to DEFAULT

In case of RAC and 11gr2

PARALLEL_FORCE_LOCAL =true . This set the parallel slave on the instance only where query was fired

To lists users running a parallel query and their associated slaves.

col username for a12
col “QC SID” for A6
col SID for A6
col “QC/Slave” for A10
col “Requested DOP” for 9999
col “Actual DOP” for 9999
col “slave set” for A10
set pages 100

‘ – ‘||lower(substr(s.program,length(s.program)-4,4) ) ) “Username”,
decode(px.qcinst_id,NULL, ‘QC’, ‘(Slave)’) “QC/Slave” ,
to_char( px.server_set) “Slave Set”,
to_char(s.sid) “SID”,
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) “QC SID”,
px.req_degree “Requested DOP”, “Actual DOP”
v$px_session px,
v$session s
px.sid=s.sid (+)
order by 5 , 1 desc

How to use Parallel Query Option
select /*+ PARALLEL(JN, 10) */ count(*) from ar.RA_INTERFACE_LINES_ALL jn;

Leave a Reply