Re: 11.2.0.4 (RAC): "resmgr:pq queued" events even with no PQ running
From: Thomas P S <royxavier_at_yahoo.com>
Date: Tue, 13 May 2014 07:05:38 -0700 (PDT)
Message-ID: <1399989938.68002.YahooMailNeo_at_web162405.mail.bf1.yahoo.com>
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_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 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
Date: Tue, 13 May 2014 07:05:38 -0700 (PDT)
Message-ID: <1399989938.68002.YahooMailNeo_at_web162405.mail.bf1.yahoo.com>
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_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 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
-- http://www.freelists.org/webpage/oracle-lReceived on Tue May 13 2014 - 16:05:38 CEST