I had a customer who complains about performance issues on one of the oracle production database.
Oracle Database 12c R1 (188.8.131.52) 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):
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 184.108.40.206). 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.
One thought on “Oracle Database Performance Issue: High waits for “PX Deq: Slave Session Stats””
Can you please share the bug fix patch for the 19c database?