11.2.0.4 (RAC): "resmgr:pq queued" events even with no PQ running

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Tue, 13 May 2014 14:56:10 +0200
Message-ID: <CALH8A91ep4i=_ncjq_OtLc5m5xJgz_rzTPaD8vsy+annjWfMYQ_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 13 2014 - 14:56:10 CEST