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

From: Powell, Mark <mark.powell2_at_hp.com>
Date: Tue, 13 May 2014 16:57:08 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD11F54DDC2_at_G6W2491.americas.hpqcorp.net>

Martin did you perform the calibrate IO statistics step? If not, "When PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database determines whether the statement should run in parallel based on the cost of the operations in the execution plan and the hardware characteristics. The hardware characteristics include I/O calibration statistics so these statistics must be gathered otherwise Oracle Database does not use the automatic degree policy feature."

Source: http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm#sthref888

Also you should have included the full Oracle version in your post. Bugs, specific issues, and features are often version specific so this is pretty much always useful information.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Fitzjarrell Sent: Tuesday, May 13, 2014 11:19 AM
To: dmarc-noreply_at_freelists.org; martin.a.berger_at_gmail.com; Oracle-L oracle-l Subject: Re: 11.2.0.4 (RAC): "resmgr:pq queued" events even with no PQ running

Parallel Universe<http://dfitzjarrell.wordpress.com/2011/08/26/parallel-universe/>

[image]<http://dfitzjarrell.wordpress.com/2011/08/26/parallel-universe/>

Parallel Universe<http://dfitzjarrell.wordpress.com/2011/08/26/parallel-universe/> An oft-used (and subsequently oft-abused) execution path is parallel execution, usually 'instigated' by some sort of parallel hint. Developers, albeit with the best...

View on dfitzjarrell.wordpres...<http://dfitzjarrell.wordpress.com/2011/08/26/parallel-universe/>

Preview by Yahoo

David Fitzjarrell
Principal author, "Oracle Exadata Survival Guide" On Tuesday, May 13, 2014 8:06 AM, Thomas P S <dmarc-noreply_at_freelists.org<mailto:dmarc-noreply_at_freelists.org>> wrote: 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<http://ora600tom.wordpress.com/>

[image]<http://ora600tom.wordpress.com/>

Thomas Saviour's Blog<http://ora600tom.wordpress.com/> My Oracle Life

View on ora600tom.wordpres...<http://ora600tom.wordpress.com/>

Preview by Yahoo

On Tuesday, May 13, 2014 6:27 PM, Martin Berger <martin.a.berger_at_gmail.com<mailto:martin.a.berger_at_gmail.com>> 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
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 - 18:57:08 CEST