RE: 11.2.0.4 (RAC): "resmgr:pq queued" events even with no PQ running
- From: "Powell, Mark" <mark.powell2@xxxxxx>
- To: Oracle-L oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 13 May 2014 16:57:08 +0000
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of David Fitzjarrell
Sent: Tuesday, May 13, 2014 11:19 AM
To: dmarc-noreply@xxxxxxxxxxxxx; martin.a.berger@xxxxxxxxx; 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@xxxxxxxxxxxxx<mailto:dmarc-noreply@xxxxxxxxxxxxx>> 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@xxxxxxxxx<mailto: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
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:
