Shulou(Shulou.com)05/31 Report--

This article introduces how to understand Oracle database parallel operation common waiting events and scripts, the content is very detailed, interested friends can refer to, hope to be helpful to you.

The editor mainly introduces the waiting events and common scripts that slave process and QC process often encounter in the process of parallel operation of Oracle database.

1. PX Deq: Execution Msg,PX Deq: Execute Reply wait event

1. PX Deq: Execution Msg

Occurs when a parallel slave is waiting to be told what to do. This is normally considered an idle event, but can cause excessive CPU in some cases.

This event is a common event in parallel queries. This event occurs when the PQ slave process is waiting for QC to tell it what to do (eg: when waiting to be told parse / execute / fetch etc..)

The parameters corresponding to the wait event in v$session_wait:

P1 = sleeptime/senderid

P2 = passes

P3 = not used

We can use the following statement to get information about the transformation of sleeptime/senderid:

Set SERVEROUTPUT on undef p1 declare inst varchar (20); sender varchar (20); begin select bitand (& & p1, 16711680)-65535 as SNDRINST, decode (bitand (& & p1, 65535), 65535, 'QC',' P' | | to_char (bitand (& & p1, 65535), 'fm000') as SNDR into inst, sender from dual where bitand (& p1, 268435456) = 268435456; dbms_output.put_line (' Instance ='| | inst); dbms_output.put_line ('Sender =' | sender); end /

If the value of P1 is empty, it means that slave does not need to wait for any process

For example, if the value of p1 is 268501004, the above sql will return:

Instance = 1 Sender = P012

The number of times the passes process rotates and waits before getting the information

The wait event is an idle wait event, and when the wait event occurs, the process will continue to wait and gradually increase the number of times to wait until the information is obtained!

Solution:

Process, as a Coordinator, reacts too slowly when getting the data of the Slave process, which causes some Slave operations to have to wait because the Queue is full, which slows down the whole parallel execution.

This is often due to an insufficient number of CPU or too many processes running on the system. Consider reducing the degree of parallelism.

2. PX Deq: Execute Reply

Occurs when the query coordinator is waiting for a response from a parallel slave. This is normally considered an idle event, but can cause excessive CPU in some cases.

Waiting Process: QC

The coordinator is waiting for a response (confirmation notification) from the slaves process to control information or expecting data from the slave process set. This wait event means that QC waits for slaves to finish executing sql and sends the result set to QC

The parameters corresponding to the wait event in v$session_wait:

P1 = sleeptime/senderid

P2 = passes

P3 = not used

We can use the following statement to get information about the transformation of sleeptime/senderid:

Set SERVEROUTPUT on undef p1 declare inst varchar (20); sender varchar (20); begin select bitand (& & p1, 16711680)-65535 as SNDRINST, decode (bitand (& & p1, 65535), 65535, 'QC',' P' | | to_char (bitand (& & p1, 65535), 'fm000') as SNDR into inst, sender from dual where bitand (& p1, 268435456) = 268435456; dbms_output.put_line (' Instance ='| | inst); dbms_output.put_line ('Sender =' | sender); end /

If the value of P1 is empty, it means that slave does not need to wait for any process

For example, if the value of p1 is 268501004, the above sql will return:

Instance = 1 Sender = P012

Wait time: this is the non-idle wait time, and the QC waits for the response from slave or the data result of the query

Solution: non-optimized sql statements can be the cause of this wait event: it takes a long time for slaves to execute the sql statement and qc is waiting for slave to return data.

Optimize sql, view the statements that slave is executing and its execution plan, and optimize as much as possible to reduce the time it takes for slave to execute sql statements!

II. Related scripts

1. Gives an overview of all running parallel queries with all slaves.It shows the if a slave is waiting and for what event it waits.

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 ssw.sid = s.sid and ssw.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 /

2. Shows for the PX Deq events the processes that are exchange data.

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, bitand (p1, 16711680)-65535 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, 16711680)-65535) 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

3. Shows for long running processes what are the slaves do.

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

On how to understand Oracle database parallel operations common waiting events and scripts are shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.