For a good primer on parallel query in oracle please read the white paper, Oracle Sql Parallel Execution
- The degree of parallelism for a query can be specified
- By specifying the parallel clause during table/index creation (Or later using alter)
- Using a parallel hint in the sql statement
- Using ‘alter session force parallel query parallel integer’ statement
- If One of the options in the above statement has not been specified (To enable parallelism) then, irrespective of whether the table or index is partitioned or not, oracle does not use parallelism in the query
- When using The dedicated server connection model (As opposed to the shared server model), the sessions shadow process acts as the query co-ordinator.
- The query uses as many “parallel execution servers” as determined by the query optimizer, to execute the query (This is based on a varitey of factors)
- When there is No parallel degree specified at the table level, in the query hint or at the session level, but parallelism is enabled at the session level (using alter session force parallel query) then the optimizer uses the default degree of parallelism.
- The Default Degree of Parellism (DOP) is determined by cpu_count x parallel_threads_per_cpu
- If you specified parallelism by issueing ‘alter session force parallel query parallel integer’ statement, then the value used for integer is used for the degree of parallelism (If a parallel hint is not specified in the query).
- All of the above statements assume that there are enough query servers available in the pool and parallel_adaptive_multi_user does not reduce the number of parallel execution servers.
- If interoperation parallelism can be used then you could end up using double the number of parallel execution servers as the degree of parallelism
- If for some reason your query cannot be allocated enough parallel execution servers (typically when there are multiple sessions and all the parallel execution servers (gated by parallel_max_servers) are currently in use) and parallel_min_percent > 0 (0 is the default so your query will still get run without parallelism), your query will get an error and you can try again later.
Ok enough basics.
Once a query is running, How do you determine what it is doing ? Things like, is it using parallel query ? What is the degree of parallelism it is using ?
As the query you want to diagnose is still executing, you can run the following query from another session connected with DBA privileges.
SELECT qcsid,
sid,
NVL(server_group,0) server_group,
server_set,
degree,
req_degree
FROM SYS.V_$PX_SESSION
ORDER BY qcsid,
NVL(server_group,0),
server_set;
You will get an output similar to the table below
QCSID SID SERVER_GROUP SERVER_SET DEGREE REQ_DEGREE
--------- ---------- ------------ ---------- ---------- ----------
170 170 0
170 136 1 1 5 5
170 129 1 1 5 5
170 134 1 1 5 5
170 130 1 1 5 5
170 138 1 1 5 5
170 137 1 2 5 5
170 133 1 2 5 5
170 135 1 2 5 5
170 139 1 2 5 5
170 131 1 2 5 5
How do you check how many parallel execution servers are currently in use by running ?
select
statistic,
value
from
v$pq_sysstat
where
statistic like ‘Servers Busy%’;
You will get an output similar to the table below
STATISTIC VALUE
------------------------------ ----------
Servers Busy 10
How do you check, which parallel execution servers are in use, by which session ?
select * from v$px_process;
You will see a result similar to the table below
SERV STATUS PID SPID SID SERIAL#
---- --------- ---------- ------------ ---------- ----------
P000 IN USE 27 9555 129 47
P001 IN USE 29 9557 131 14
P003 IN USE 31 9561 132 119
P009 IN USE 37 9573 133 13
P008 IN USE 36 9571 134 10
P004 IN USE 32 9563 135 7
P007 IN USE 35 9569 136 9
P002 IN USE 30 9559 137 10
P005 IN USE 33 9565 138 9
P006 IN USE 34 9567 139 6
How can you monitor the progress of the query ?
SELECT a.qcsid,
a.sid,
a.server_group,
a.server_set,
substr(b.name,1,20) operation,
a.value
FROM v$px_sesstat a,
v$statname b
WHERE a.statistic# = b.statistic#
AND UPPER(b.name) = ‘PHYSICAL READS’
ORDER BY a.qcsid,
a.server_group,
a.server_set;
You will see results similar to the Table below
QCSID SID SERVER_GROUP SERVER_SET OPERATION VALUE
---------- ---- ------------ ---------- -------------------- ----------
170 139 1 1 physical reads 0
170 130 1 1 physical reads 0
170 134 1 1 physical reads 0
170 135 1 1 physical reads 0
170 129 1 1 physical reads 0
170 131 1 2 physical reads 6980
170 136 1 2 physical reads 7136
170 137 1 2 physical reads 6404
170 133 1 2 physical reads 6436
170 138 1 2 physical reads 5988
170 170 physical reads 766852
How do you check, what the parallel execution servers, are waiting on ?
SELECT px.SID “SID”, p.PID, p.SPID “SPID”, px.INST_ID “Inst”,
px.SERVER_GROUP “Group”, px.SERVER_SET “Set”,
px.DEGREE “Degree”, px.REQ_DEGREE “Req Degree”, w.event “Wait Event”
FROM GV$SESSION s, GV$PX_SESSION px, GV$PROCESS p, GV$SESSION_WAIT w
WHERE s.sid (+) = px.sid AND s.inst_id (+) = px.inst_id AND
s.sid = w.sid (+) AND s.inst_id = w.inst_id (+) AND
s.paddr = p.addr (+) AND s.inst_id = p.inst_id (+)
ORDER BY DECODE(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID), px.QCSID,
DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID;
You will see results like in the table below
SID PID SPID Inst Group Set Degree Req Degree Wait Event
---- ---- ------ ---- ----- ---------- ------ ---------- ------------------------------
170 24 9473 1 PX Deq: Execute Reply
131 27 10236 1 1 1 5 5 PX Deq: Execution Msg
132 29 10238 1 1 1 5 5 PX Deq: Execution Msg
129 20 10234 1 1 1 5 5 PX Deq: Execution Msg
134 31 10242 1 1 1 5 5 PX Deq: Execution Msg
139 30 10240 1 1 1 5 5 PX Deq: Execution Msg
135 36 10252 1 1 2 5 5 resmgr:cpu quantum
136 34 10248 1 1 2 5 5 resmgr:cpu quantum
137 35 10250 1 1 2 5 5 direct path read
130 33 10246 1 1 2 5 5 resmgr:cpu quantum
133 32 10244 1 1 2 5 5 direct path read
If you start seeing a lot of waits on the event “PX Deq Credit: send blkd”, you can start troubleshooting this by identifying the consumers who are blocking the send (Once you identify them you can probably drill down into that session using ash reports). Metalink Note 304317.1 has a query that can be used to identify the blocking consumers.
Once a statement has finished execution, how do you check if it used parallel query, and how was the statement parellelized ?
Issue the following statement from the same session that the query executed
break on tq_id on server_type
SELECT dfo_number, tq_id, server_type, process, num_rows, bytes
FROM v$pq_tqstat
ORDER BY dfo_number DESC, tq_id, server_type DESC , process;
You will see results similar to the table below
DFO_NUMBER TQ_ID SERVER_TYP PROCESS NUM_ROWS BYTES
---------- ---------- ---------- ---------- ---------- ----------
1 0 Producer P005 287 2582
1 P006 287 2582
1 P007 287 2582
1 P008 287 2582
1 P009 287 2582
1 Consumer P000 315 2825
1 P001 340 3035
1 P002 270 2450
1 P003 250 2280
1 P004 260 2320
1 1 Producer P000 1 32
1 P001 1 32
1 P002 1 32
1 P003 1 32
1 P004 1 32
1 Consumer QC 5 160
How do you review all the parallel activity happening in your environment ?
select name,value from v$sysstat
where upper(name) like ‘%PARALLEL OPERATIONS%’
or upper(name) like ‘%PARALLELIZED%’ or upper(name) like ‘%PX%’;
You will see results similar to the table below
NAME VALUE
---------------------------------------------------------------- ----------
queries parallelized 10
DML statements parallelized 0
DDL statements parallelized 0
DFO trees parallelized 10
Parallel operations not downgraded 10
Parallel operations downgraded to serial 0
Parallel operations downgraded 75 to 99 pct 0
Parallel operations downgraded 50 to 75 pct 0
Parallel operations downgraded 25 to 50 pct 0
Parallel operations downgraded 1 to 25 pct 0
PX local messages sent 2350
PX local messages recv'd 2350
PX remote messages sent 0
PX remote messages recv'd 0
This should be a good starting point to start analyzing parallel query behaviour
