Database Hangs with parallel_degree_policy = AUTO; Serial statement holding JX Enqueue (Doc ID 1333867.1)

Last updated on APRIL 14, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.2 [Release 11.2]
Information in this document applies to any platform.

Symptoms

Intermittently, the PX statement queue becomes hung when parallel_degree_policy = AUTO.   Neither the parallel_target_servers, nor the parallel_max_servers values has been reached, yet statements no longer come out of the PX statement queue.

When the PX queue becomes hung, it can be seen by this query from v$sql_monitor:

select status, sql_id, PX_SERVERS_ALLOCATED, PX_SERVERS_REQUESTED
from gv$sql_monitor
where process_name = 'ora' and
status in ( 'EXECUTING', 'QUEUED')
--and PX_SERVERS_ALLOCATED is not null
order by status;

--Note the sql_ids were removed from this output
STATUS PX_SERVERS_ALLOCATED PX_SERVERS_REQUESTED
------------------- -------------------- --------------------
EXECUTING 24 24
EXECUTING
EXECUTING 24 24
EXECUTING 24 24
EXECUTING 24 24
EXECUTING 24 24
EXECUTING 24 24
EXECUTING 24 24
EXECUTING 24
QUEUED <--- serial; this one never unqueued
QUEUED
QUEUED
QUEUED



Another helpful query to diagnose the issue in 11.2.0.2 is this one (query not valid for 11.2.0.1):

select inst_id, sid, current_consumer_group_id, current_pq_queued_time,
dop from gv$rsrc_session_info where state = 'PQ QUEUED' order by
current_pq_queued_time;


The AWR showed  "PX Queuing: statement queue" as the top timed foreground event.   The systemstate dump showed a number of statements with this wait, and the resource holder:


waiting for 'enq: JX - SQL statement queue'[Enq JX-00000001-00000000]
. . .
Resource Holder State
Enq JX-00000001-00000000 91: waiting for 'PX Queuing: statement queue'


The following px trace was taken with these trace settings. Because the hang was intermittent, and it was not possible to know on which statement the error would occur, the application was modified to turn the px tracing on at the start of a procedure and off at the end.

alter system set events 'trace[px_messaging|px_queuing] disk=highest';



Here is the output of interest from the trace.  Note the negative value for "Load" and "GrantedSlv" in the tracefile contents. A negative value in any of these fields, including "Target," would indicate the same or similar issue:

kxfxqInstInfo
inst[cpus:mxslv]
1[16:310]
kxfxqLocalInstLoad
local inst(load:user:pct:fact:queued:admitted:started:granted:active)
1 (6:1:100:0:0:0:0:0:4)
kxfxqInstLoad
inst(load:user:pct:fact:queued:admitted:started:granted:active)
1(local) (6:1:100:0:0:0:0:0:4)
kxfxqInstList
load information of 1 instances (single inst) initialized
kxfxqGrantedDOP
Computing granted DOP.
kxfxqGrantedDOP
RequestedDOP=8 GrantedDOP=1 Target=250 Load=-68 GrantedSlv=-68 AdmittedP
Q=65519 DefaultDOP=32 users=1 sets=2 force_admit=false
kxfxqRemainQueued
queued because granted dop:1 < requested dop:8







Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms