Applies to:
Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.
Symptoms
GV$SQL_PLAN_MONITOR shows DOP is downgraded due to reason 'DOP downgrade due to adaptive DOP'', yet the queries execute with expected DOP, and v$sql_monitor does not show any downgrading.
For 12.1
select SID,sql_id,sql_exec_id, sql_exec_start,otherstat_2_value,
case otherstat_2_value
when 350 then 'DOP downgrade due to adaptive DOP'
when 351 then 'DOP downgrade due to resource manager max DOP'
when 352 then 'DOP downgrade due to insufficient number of processes'
when 353 then 'DOP downgrade because workers failed to join'
end reason_for_downgrade
from GV$SQL_PLAN_MONITOR where otherstat_2_value in (350,351,352,353)
/
-- Example output
SID SQL_ID SQL_EXEC_ID EXEC_START REASON_FOR_DOWNGRADE
1234 3k9wsj4xvgknu 16777216 04-DEC-18 350 DOP downgrade due to adaptive DOP
1068 12uf046q98p7v 16777494 04-DEC-18 350 DOP downgrade due to adaptive DOP
2149 12uf046q98p7v 16777469 04-DEC-18 350 DOP downgrade due to adaptive DOP
2875 12uf046q98p7v 16777493 04-DEC-18 350 DOP downgrade due to adaptive DOP
For 12.2
select SID,sql_id,sql_exec_id, sql_exec_start,otherstat_2_value,
case otherstat_2_value
when 351 then 'DOP downgrade due to adaptive DOP'
when 352 then 'DOP downgrade due to resource manager max DOP'
when 353 then 'DOP downgrade due to insufficient number of processes'
when 354 then 'DOP downgrade because workers failed to join'
end reason_for_downgrade
from GV$SQL_PLAN_MONITOR where otherstat_2_value in (351,352,353,354);
For 18c and 19c
select SID,sql_id,sql_exec_id, sql_exec_start,otherstat_2_value,
case otherstat_2_value
when 352 then 'DOP downgrade due to adaptive DOP'
when 353 then 'DOP downgrade due to resource manager max DOP'
when 354 then 'DOP downgrade due to insufficient number of processes'
when 355 then 'DOP downgrade because workers failed to join'
end reason_for_downgrade
from GV$SQL_PLAN_MONITOR where otherstat_2_value in (352,353,354,355);
Changes
Cause
When parallel_degree_policy = manual, the PX code may be triggered to mark a SQL as using adaptive DOP, even though the DOP isn't actually downgraded; this is not considered a bug.
Solution
Ignore the downgrade reason if no actual downgrade occurs. You can see actual DOP used from a real-time sqlmon report, or the first query in "Script to monitor parallel queries Document 457857.1."
Provide a script to monitor parallel queries/SQL.
Solution
Purpose
The script has 4 SQL commands. The first 1 gives an overview of all running parallel queries with all parallel worker processes. It shows whether a parallel worker is waiting and for what event it waits. The second command shows for the PX Deq events the processes that are exchange data. The third 1 shows for long running processes what are the parallel worker processes do. The fourth 1 shows what SQLs are currently executing, and what is queued.
Requirements
This is designed to work in SQL Plus. It also works with other tools, but than the formatting is lost.
Configuring
The script needs privileges to do a select on gv$px_session, gv$px_process gv$session_longops , gv$session_wait, gv$session and gv$sql_monitor
Instructions
You can run the the complete script or use only some of the SQL commands to monitor your database.
Script
NOTE: This script is coded for working on single and RAC environments, however, there is no guarantee that it will work for all cases.
col username for a12
col "QC SID" for A6
col "SID" for A6
col "QC/Slave" for A8
col "Req. DOP" for 9999
col "Actual DOP" for 9999
col "Slaveset" for A8
col "Slave INST" for A9
col "QC INST" for A6
set lines 250 pages 5000
col wait_event format a30
select
decode(px.qcinst_id,NULL,username,
' - '||lower(substr(pp.SERVER_NAME,
length(pp.SERVER_NAME)-4,4) ) )"Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "SlaveSet",
to_char(s.sid) "SID",
to_char(px.inst_id) "Slave INST",
decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE,
case sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
to_char(px.qcinst_id) "QC INST",
px.req_degree "Req. DOP",
px.degree "Actual DOP"
from gv$px_session px,
gv$session s ,
gv$px_process pp,
gv$session_wait sw
where px.sid=s.sid (+)
and px.serial#=s.serial#(+)
and px.inst_id = s.inst_id(+)
and px.sid = pp.sid (+)
and px.serial#=pp.serial#(+)
and sw.sid = s.sid
and sw.inst_id = s.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
/
set lines 250 pages 5000
col wait_event format a30
select
sw.SID as RCVSID,
decode(pp.server_name,
NULL, 'A QC',
pp.server_name) as RCVR,
sw.inst_id as RCVRINST,
case sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,
decode(bitand(p1, 65535),
65535, 'QC',
'P'||to_char(bitand(p1, 65535),'fm000')) as SNDR,
mod(bitand(p1,268369920) , 65536) as SNDRINST,
decode(bitand(p1, 65535),
65535, ps.qcsid,
(select
sid
from
gv$px_process
where
server_name = 'P'||to_char(bitand(sw.p1, 65535),'fm000') and
inst_id = bitand(sw.p1, 268369920) - 65536)
) as SNDRSID,
decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE
from
gv$session_wait sw,
gv$px_process pp,
gv$px_session ps
where
sw.sid = pp.sid (+) and
sw.inst_id = pp.inst_id (+) and
sw.sid = ps.sid (+) and
sw.inst_id = ps.inst_id (+) and
p1text = 'sleeptime/senderid' and
bitand(p1, 268435456) = 268435456
order by
decode(ps.QCINST_ID, NULL, ps.INST_ID, ps.QCINST_ID),
ps.QCSID,
decode(ps.SERVER_GROUP, NULL, 0, ps.SERVER_GROUP),
ps.SERVER_SET,
ps.INST_ID
/
set lines 250 pages 5000
col "Username" for a12
col "QC/Slave" for A8
col "Slaveset" for A8
col "Slave INST" for A9
col "QC SID" for A6
col "QC INST" for A6
col "operation_name" for A30
col "target" for A30
select
decode(px.qcinst_id,NULL,username,
' - '||lower(substr(pp.SERVER_NAME,
length(pp.SERVER_NAME)-4,4) ) )"Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "SlaveSet",
to_char(px.inst_id) "Slave INST",
substr(opname,1,30) operation_name,
substr(target,1,30) target,
sofar,
totalwork,
units,
start_time,
timestamp,
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
to_char(px.qcinst_id) "QC INST"
from gv$px_session px,
gv$px_process pp,
gv$session_longops s
where px.sid=s.sid
and px.serial#=s.serial#
and px.inst_id = s.inst_id
and px.sid = pp.sid (+)
and px.serial#=pp.serial#(+)
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
/
set lines 250 pages 5000
col sql_text for a50
col sid for 99999
col status for a11
col slv_req for 99999
col slv_alloc for 99999
col sql_id format a16
col secs_in_q for 99999
SELECT sql_id, sid,
session_serial# sess#,
status ,
px_servers_requested slv_req,
px_servers_allocated slv_alloc,
substr(sql_text,1,50)||'...' sql_text,
queuing_time/1000000 secs_in_q
FROM gv$sql_monitor
WHERE status in ('QUEUED','EXECUTING') and sql_text is not null
ORDER BY status desc, secs_in_q desc, sql_id
/
Sample Output
Some sample outputs from the script.
Output from the first script
Username QC/Slave SlaveSet SID Slave INS STATE WAIT_EVENT QC SID QC INS Req. DOP Actual DOP
------------ -------- -------- ------ --------- -------- ------------------------------ ------ ------ -------- ----------
<USER> QC 923 1 WAIT db file sequential read 923
- p003 (Slave) 1 935 1 WAIT PX Deq Credit: send blkd 923 1 4 4
- p001 (Slave) 1 961 1 WAIT PX Deq: Execution Msg 923 1 4 4
- p002 (Slave) 1 1035 1 WAIT PX Deq: Execution Msg 923 1 4 4
- p004 (Slave) 1 977 1 WAIT PX Deq Credit: send blkd 923 1 4 4
- p006 (Slave) 2 609 1 WAIT PX Deq: Execution Msg 923 1 4 4
- p007 (Slave) 2 642 1 WAIT PX Deq: Execution Msg 923 1 4 4
- p008 (Slave) 2 970 1 WAIT PX Deq: Execution Msg 923 1 4 4
- p005 (Slave) 2 953 1 WAIT PX Deq: Execution Msg 923 1 4 4
<USER> QC 1003 1 WAIT SQL*Net message from client 1003
- p015 (Slave) 1 608 1 WAIT PX Deq Credit: send blkd 1003 1 8 8
- p011 (Slave) 1 639 1 WAIT PX Deq Credit: send blkd 1003 1 8 8
- p012 (Slave) 1 1115 1 WAIT PX Deq: Execution Msg 1003 1 8 8
- p000 (Slave) 1 1253 1 WAIT PX Deq Credit: send blkd 1003 1 8 8
- p010 (Slave) 1 1420 1 WAIT PX Deq: Execution Msg 1003 1 8 8
- p009 (Slave) 1 1421 1 WAIT PX Deq Credit: send blkd 1003 1 8 8
- p014 (Slave) 1 1417 1 WAIT PX Deq: Execution Msg 1003 1 8 8
- p013 (Slave) 1 1180 1 WAIT PX Deq: Execution Msg 1003 1 8 8
- p020 (Slave) 2 1422 1 WAIT PX Deq: Execution Msg 1003 1 8 8
- p023 (Slave) 2 1423 1 WAIT PX Deq: Execution Msg 1003 1 8 8
- p018 (Slave) 2 1424 1 WAIT PX Deq: Execution Msg 1003 1 8 8
- p021 (Slave) 2 1426 1 WAIT PX Deq: Execution Msg 1003 1 8 8
- p019 (Slave) 2 1428 1 WAIT PX Deq: Execution Msg 1003 1 8 8
- p016 (Slave) 2 1429 1 WAIT PX Deq: Execution Msg 1003 1 8 8
- p022 (Slave) 2 1427 1 WAIT PX Deq: Execution Msg 1003 1 8 8
- p017 (Slave) 2 1425 1 WAIT PX Deq: Execution Msg 1003 1 8 8
This output shows 2 queries. 1 is running with degree 4 and the other with degree 8. Is shows also that all slaves are currently waiting.
The next script analyze in more detail the "PX Deq: .." wait events
RCVSID RCVR RCVRINST WAIT_EVENT SNDR SNDRINST SNDRSID STATE
---------- ---- ---------- ------------------------------ ----- ---------- ---------- --------
935 P003 1 PX Deq Credit: send blkd QC 1 923 WAIT
961 P001 1 PX Deq: Execution Msg QC 1 923 WAIT
977 P004 1 PX Deq Credit: send blkd QC 1 923 WAIT
1035 P002 1 PX Deq: Execution Msg QC 1 923 WAIT
609 P006 1 PX Deq: Execution Msg QC 1 923 WAIT
642 P007 1 PX Deq: Execution Msg QC 1 923 WAIT
970 P008 1 PX Deq: Execution Msg QC 1 923 WAIT
953 P005 1 PX Deq: Execution Msg QC 1 923 WAIT
608 P015 1 PX Deq Credit: send blkd QC 1 1003 WAIT
1180 P013 1 PX Deq: Execution Msg QC 1 1003 WAIT
1253 P000 1 PX Deq Credit: send blkd QC 1 1003 WAIT
1417 P014 1 PX Deq: Execution Msg QC 1 1003 WAIT
1421 P009 1 PX Deq Credit: send blkd QC 1 1003 WAIT
1420 P010 1 PX Deq: Execution Msg QC 1 1003 WAIT
1115 P012 1 PX Deq: Execution Msg QC 1 1003 WAIT
639 P011 1 PX Deq Credit: send blkd QC 1 1003 WAIT
1422 P020 1 PX Deq: Execution Msg QC 1 1003 WAIT
1423 P023 1 PX Deq: Execution Msg QC 1 1003 WAIT
1424 P018 1 PX Deq: Execution Msg QC 1 1003 WAIT
1425 P017 1 PX Deq: Execution Msg QC 1 1003 WAIT
1426 P021 1 PX Deq: Execution Msg QC 1 1003 WAIT
1427 P022 1 PX Deq: Execution Msg QC 1 1003 WAIT
1428 P019 1 PX Deq: Execution Msg QC 1 1003 WAIT
1429 P016 1 PX Deq: Execution Msg QC 1 1003 WAIT
Here we see that the slaves are communicating with the Query Coordinator. All this looks fine.
The third query shows what slaves that have an entry in v$session_longops to get an idea what slave or the QC are doing.
Username QC/Slave SlaveSet Slave INS OPERATION_NAME TARGET SOFAR TOTALWORK UNITS START_TIM QC SID QC INS
------------ -------- -------- --------- ------------------------------ ------------------------------ ---------- ---------- -------------------------------- --------- ------ ------
<USER> QC 1 Index Fast Full Scan EMP 680893 680893 Blocks 10-SEP-07 923
<USER> QC 1 Index Fast Full Scan EMP 680893 680893 Blocks 10-SEP-07 923
- p003 (Slave) 1 1 Sort Output 21997 33383 Blocks 10-SEP-07 923 1
- p001 (Slave) 1 1 Sort Output 94196 94196 Blocks 10-SEP-07 923 1
- p011 (Slave) 1 1 Hash Join 589 589 Blocks 11-SEP-07 1003 1
The fourth query shows the Queuing & Executing Status and the requested and allocated number of parallel worker processes. The requested and allocated number of workers is the DOP x the number of needed parallel workers sets (usually at least 2). In the following example, the requested DOP was 2 for SID 24, and 4 for SID 400.
SQL_ID SID SESS# STATUS SLV_REQ SLV_ALLOC SQL_TEXT SECS_IN_Q
------------- ----- ---------- ---------- ------- --------- -------------------------------------------------- ---------
7gyvxupmw5g5b 15 215 QUEUED select /*+ parallel(16) */ * from dba_segments... 18
3fad5ncd059sp 24 65 EXECUTING 4 4 select /*+ parallel */ * from dba_extents... 336
2wa6b9sk275md 400 35733 EXECUTING 8 8 select /*+ parallel */ * from dba_objects... 0
扫一扫
1514
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
