Re: 11.2.0.4 (RAC): "resmgr:pq queued" events even with no PQ running
- From: "Thomas P S" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "royxavier@xxxxxxxxx" for DMARC)
- To: "martin.a.berger@xxxxxxxxx" <martin.a.berger@xxxxxxxxx>, Oracle-L oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 13 May 2014 07:05:38 -0700 (PDT)
Hello Martin,
I felt, under auto parallelism (parallel_degree_policy='AUTO') , you have
reached maximum number of parallel server slaves and not able to allocate 7
slaves so that it is queuing - known as Parallel Statement Queuing. At that
point, please check how many parallel process are allocated, and your problem
might get resolved by increasing the parameter - parallel_max_servers=24 to
higher number.
Thanks,
Thomas Saviour
Thomas Saviour's Blog
Thomas Saviour's Blog
My Oracle Life
View on ora600tom.wordpres... Preview by Yahoo
On Tuesday, May 13, 2014 6:27 PM, Martin Berger <martin.a.berger@xxxxxxxxx>
wrote:
Dear List,
Currently I'm investigating a situation which I can not explain:
I have a 2 node RAC (Linux64, RedHat) in Production.
(It's a Peoplesoft application, but I don't think this is of any importance).
I often have sessions with wait event "resmgr:pq queued".
resource_manager_plan is unset
parallel_degree_policy='AUTO'
parallel_force_local=true
parallel_max_servers=24
parallel_servers_target=24
parallel_min_percent=0
parallel_min_servers=0
(if other parameters are of any interest, I will provide them).
at the moment (but this changes, I just don't know why/how it changes!)
select * from gv$session where event='resmgr:pq queued';
get me 7 lines, both instances, all status='ACTIVE', different users (up to
SYS)
select s.inst_id, s.sid, s.serial#, s.username, s.sql_id, s.event,
count(px.saddr) count
from gv$session s,
gv$px_session px
where s.inst_id = px.qcinst_id
and s.sid= px.qcsid
and s.serial# = px.qcserial#
group by s.inst_id, s.sid, s.serial#, s.username, s.sql_id, s.event
order by count(px.saddr) desc;
shows my only my session (as I'm querying gv$ ...)
and a select on gv$sql_monitor
shows 8 sessions "EXECUTING" (over both instances again), but 5 in status
QUEUED. - all of them has PX_SERVERS_ALLOCATED = <NULL> as well as
PX_SERVERS_REQUEST= <NULL>.
Even a crosscheck on v$active session history does not show reasonable PQ
activity:
select QC_SESSION_ID, count(*)
from v$active_session_history
where SAMPLE_TIME > (sysdate- (1/24)/10)
group by QC_SESSION_ID;
<NULL> 3108
1793
2491
So on the one hand (wait events and gv$sql_monitor) the statements are marked
as QUEUED, but I don't see any reason why they should not run?
Obviously I'm missing something. Can anyone tell me what I'm missing?
thank you,
Martin
Other related posts:
