Oracle picks more parallel slaves then what is defined in PARALLEL HINT

  

We  had strange scenario where  application team  defined  parallel  10  in   parallel  hint   eg    " SELECT /*+ parallel(10)*/ "   ,   however   we  were seeing  huge parallel slaves sessions  around 100+  . 

This  kind  of  behavior is normal  when parallel_degree_policy is set to true  ,   but we had  parallel_degree_policy=manual  and   still we  were seeing huge parallel slaves  far above what is defined in hint  

parallel_degree_policy=manual

parallel_degree_limit = CPU

On checking it as found that  When we dont add table name in parallel hint , it will enable parallel auto  dop   . We can see  this in  sql plan . 

  Note

-----

- cpu costing is off (consider enabling it)

- automatic DOP: Computed Degree of Parallelism is 124 because of degree limit

If we  dont   want  to enable auto dop when parallel_degree_policy -is set to  manual  , we need to  add parallel hint  on specific tables   eg .  /*+ PARALLEL(employee_table, 35) */

Parallel Statement Queuing  .

Parallel Statement Queuing has been introduced from 12c and is  key feature .  Parallel Statement Queuing is enabled by default  when we enable Parallel  auto dop  by setting parallel_degree_policy to auto .

In case when parallel_degree_policy is set to manual  , we can still enable Parallel Statement Queuing  by 2 ways .  i,e  

1) using parameter  "_parallel_statement_queuing"=true  

     

select a.ksppinm "Parameter",b.ksppstvl "Session Value",c.ksppstvl "Instance Value" from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c where a.indx = b.indx and a.indx = c.indx and a.ksppinm in ('_fix_control','_parallel_statement_queuing') order by 1;

2) using  hint  - select /*+PARALLEL(4) STATEMENT_QUEUING*/ .

STATEMENT_QUEUING Hint can be deployed through patch 

begin

sys.dbms_sqldiag_internal.i_create_patch(sql_id=>'<SQL_ID>', hint_text=>'OPT_PARAM(''_parallel_statement_queuing'' ''true'')', name=> 'SQL_Patch_parallel_statement_queuing_sql_id'); >>>>>>>>>>>>>>>REPLACE <SQL_ID> with your sql_id

end;

/

References :

Automatic Degree of Parallelism (AutoDOP) is enabled when "PARALLEL_DEGREE_POLICY=MANUAL and PARALLEL HINT" is Used at SQL Statement (Doc ID 1542556.1)

How to Achieve Parallel Statement Queuing for an SQL When PARALLEL_DEGREE_POLICY=MANUAL (Doc ID 1902069.1)

How To Enable Parallel Query For A Function? ( Doc ID 1093773.1 )

https://blogs.oracle.com/datawarehousing/post/px-server-sets-parallelizers-dfo-trees-parallel-groups-lets-talk-about-terminology

SRDC - Data Collection for Query executing in Parallel returning error (Doc ID 2076943.1)

Other Known Issues related to parallel hint in 19c  :

9c: Duplicate PQ workers created at instance level (Doc ID 2719466.1)

BUG:32342582 - WRONG RESULTS WITH PARALLEL AFTER UPGRADE TO 19.9

Bug 19849585 : PARALLEL HINT IGNORED WHEN USING GROUP BY

Issue with parallel hint on 19c CDB database where the hint is not honored and going for serial scans. Apparently seems to be Oracle bug Bug 30618478 

Number of Preallocated Parallel Workers Are More Than Parallel_min_servers (Doc ID 2654231.1)

 Bug 31195441 : 18C: LONG PARSING TIME WHEN QUERY EXECUTING IN PARALLEL

Views :

col username for a12

col "QC SID" for A6

col "SID" for A6

col "QC/Slave" for A8

col "Req. DOP" for 9999

col "Actual DOP" for 9999

col "Slaveset" for A8

col "Slave INST" for A9

col "QC INST" for A6

set pages 300 lines 300

col wait_event format a30

select

decode(px.qcinst_id,NULL,username,

' - '||lower(substr(pp.SERVER_NAME,

length(pp.SERVER_NAME)-4,4) ) )"Username",

decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,

to_char( px.server_set) "SlaveSet",

to_char(s.sid) "SID",

to_char(px.inst_id) "Slave INST",

decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE,

case sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,

decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",

to_char(px.qcinst_id) "QC INST",

px.req_degree "Req. DOP",

px.degree "Actual DOP"

from gv$px_session px,

gv$session s ,

gv$px_process pp,

gv$session_wait sw

where px.sid=s.sid (+)

and px.serial#=s.serial#(+)

and px.inst_id = s.inst_id(+)

and px.sid = pp.sid (+)

and px.serial#=pp.serial#(+)

and sw.sid = s.sid

and sw.inst_id = s.inst_id

order by

decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),

px.QCSID,

decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),

px.SERVER_SET,

px.INST_ID ;

set pages 300 lines 300

col wait_event format a30

select

sw.SID as RCVSID,

decode(pp.server_name,

NULL, 'A QC',

pp.server_name) as RCVR,

sw.inst_id as RCVRINST,

case sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,

decode(bitand(p1, 65535),

65535, 'QC',

'P'||to_char(bitand(p1, 65535),'fm000')) as SNDR,

bitand(p1, 16711680) - 65535 as SNDRINST,

decode(bitand(p1, 65535),

65535, ps.qcsid,

(select

sid

from

gv$px_process

where

server_name = 'P'||to_char(bitand(sw.p1, 65535),'fm000') and

inst_id = bitand(sw.p1, 16711680) - 65535)

) as SNDRSID,

decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE

from

gv$session_wait sw,

gv$px_process pp,

gv$px_session ps

where

sw.sid = pp.sid (+) and

sw.inst_id = pp.inst_id (+) and

sw.sid = ps.sid (+) and

sw.inst_id = ps.inst_id (+) and

p1text = 'sleeptime/senderid' and

bitand(p1, 268435456) = 268435456

order by

decode(ps.QCINST_ID, NULL, ps.INST_ID, ps.QCINST_ID),

ps.QCSID,

decode(ps.SERVER_GROUP, NULL, 0, ps.SERVER_GROUP),

ps.SERVER_SET,

ps.INST_ID ;

set pages 300 lines 300

col "Username" for a12

col "QC/Slave" for A8

col "Slaveset" for A8

col "Slave INST" for A9

col "QC SID" for A6

col "QC INST" for A6

col "operation_name" for A30

col "target" for A30

select

decode(px.qcinst_id,NULL,username,

' - '||lower(substr(pp.SERVER_NAME,

length(pp.SERVER_NAME)-4,4) ) )"Username",

decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,

to_char( px.server_set) "SlaveSet",

to_char(px.inst_id) "Slave INST",

substr(opname,1,30) operation_name,

substr(target,1,30) target,

sofar,

totalwork,

units,

start_time,

timestamp,

decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",

to_char(px.qcinst_id) "QC INST"

from gv$px_session px,

gv$px_process pp,

gv$session_longops s

where px.sid=s.sid

and px.serial#=s.serial#

and px.inst_id = s.inst_id

and px.sid = pp.sid (+)

and px.serial#=pp.serial#(+)

order by

decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),

px.QCSID,

decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),

px.SERVER_SET,

px.INST_ID ;

 

select inst_id

, status

, count(1) px_servers#

from gv$px_process

group by inst_id, status

order by inst_id, status ;

SELECT DECODE(px.qcinst_id, NULL, username, ' – ' || lower(SUBSTR(pp.SERVER_NAME, LENGTH(pp.SERVER_NAME)-4,4) ) ) "Username",

 DECODE(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave",

 TO_CHAR( px.server_set) "SlaveSet",

 TO_CHAR(s.sid) "SID",

 TO_CHAR(px.inst_id) "Slave INST",

 DECODE(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) AS STATE,

  CASE sw.state

    WHEN 'WAITING'

    THEN SUBSTR(sw.event,1,30)

    ELSE NULL

  END AS wait_event,

  DECODE(px.qcinst_id, NULL ,TO_CHAR(s.sid) ,px.qcsid) "QC SID",

  TO_CHAR(px.qcinst_id) "QC INST",

  px.req_degree "Req. DOP",

  px.degree "Actual DOP"

FROM gv$px_session px,

  gv$session s ,

  gv$px_process pp,

  gv$session_wait sw

WHERE px.sid   =s.sid (+)

AND px.serial# =s.serial#(+)

AND px.inst_id = s.inst_id(+)

AND px.sid     = pp.sid (+)

AND px.serial# =pp.serial#(+)

AND sw.sid     = s.sid

AND sw.inst_id = s.inst_id

ORDER BY DECODE(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),

  px.QCSID,

  DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),

  px.SERVER_SET,

  px.INST_ID,

  pp.SERVER_NAME  ;

column name format a50

column value format 999,999

SELECT NAME, VALUE

FROM GV$SYSSTAT

WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'

  OR UPPER (NAME) LIKE '%PARALLELIZED%'

  OR UPPER (NAME) LIKE '%PX%'

;

Select

decode(px.qcinst_id,NULL,username,

' - '||lower(substr(pp.SERVER_NAME,

length(pp.SERVER_NAME)-4,4) ) )"Username",

decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,

to_char( px.server_set) "SlaveSet",

to_char(px.inst_id) "Slave INST",

substr(opname,1,30) operation_name,

substr(target,1,30) target,

sofar,

totalwork,

units,

start_time,

timestamp,

decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",

to_char(px.qcinst_id) "QC INST"

from gv$px_session px,

gv$px_process pp,

gv$session_longops s

where px.sid=s.sid

and px.serial#=s.serial#

and px.inst_id = s.inst_id

and px.sid = pp.sid (+)

and px.serial#=pp.serial#(+)

order by decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),px.QCSID,

decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET,px.INST_ID;

select

   ps.qcsid,

   ps.sid,

   p.spid,

   ps.inst_id,

   ps.degree,

   ps.req_degree

from

   gv$px_session ps

   join

   gv$session s

      on ps.sid=s.sid

         and

         ps.inst_id=s.inst_id

   join

   gv$process p

      on p.addr=s.paddr

         and

         p.inst_id=s.inst_id 

order by

   qcsid,

   server_group desc,

   inst_id,

   sid;