Oracle Database Performance Issue: High waits for “PX Deq: Slave Session Stats”

I had a customer who complains about performance issues on one of the oracle production database.

Environment

Oracle Database 12c R1 (12.1.0.2) on Linux with Oracle RAC (two nodes).

To start with, I collected some AWR reports around the complain time, and I found the top wait events (most of the time) as follows:

So, what is “PX Deq: Slave Session Stats”? and why it is one of the top wait events?

With some quick search, I found the following document:

Waits for “PX Deq: Slave Session Stats” Showing in Top 5 Timed Events in AWR (Doc ID 1481457.1), which defined the wait event as:

I also, found a similar issue here (but it is relevant for a single instance nor RAC environment):
https://oracle-randolf.blogspot.com/2016/02/big-nodes-concurrent-parallel-execution.html

Also, I found some people who reported similar issue after upgrading to Oracle 19c.

Let us start our analysis:

From the AWR top SQL statements, I found that the sql with sqlid=”a3vfsb1vvtr3s” is usually there on the top:

Please note that this statement is a system (not application) statement.

Also, please note that any queries from “GV$” is run by default in parallel with some Advanced Queuing functionality (this is a default behavior).

Following is the full text of this statement:

select queue_id, queue_schema, queue_name, subscriber_id, subscriber_name, inst_id, latency_state, latency, dequeue_requests, active_shards, active_listener, flags, con_id from gv$aq_subscriber_load order by con_id, queue_id, subscriber_id, inst_id

Querying ASH views (using some of the Tanel Poder scripts):

I was able also to link between the wait event with the statement a3vfsb1vvtr3s:

Doing some more search, I found the following Oracle document:
ppa* Processes – Slow Query Performance in RAC (Doc ID 2304268.1)

This document mentioned that this is bug #25036065, and its fix is to download and apply patch 25036065 (for 12.1.0.2). If you have 12.2, you should apply patch 27393570.

I implemented this fix (applied patch 25036065) on both nodes, and if fixed the issue. Following is the “Top Wait Events” section after applying the patch:

Note that the number of waits of this event didn’t decrease, but the average wait time dropped from more than 8 seconds to about 5 ms.

Thanks
Ahmed