11.2.0.4 (RAC): "resmgr:pq queued" events even with no PQ running
- From: Martin Berger <martin.a.berger@xxxxxxxxx>
- To: Oracle-L oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 13 May 2014 14:56:10 +0200
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
179 3
249 1
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:
