Oracle – How to Find Current Setting of FORCE PARALLEL QUERY

oracleoracle-11g-r2oracle-12c

I can change (override) the current level of parallelism in an Oracle session using e.g.:

alter session force parallel query parallel 6;

Is there any system view of function that will return me the value 6 that I have requested?

I would like to check (in a stored procedure) if my current session has changed this (ideally the setting for a alter session force parallel dml as well)

Best Answer

Tested on 12.1 but should work if you have appropriate privileges in versions where this view and feature is available. you will see rows only if value is non-default. If query returns 0 rows, it means value is default. Please test and adjust view names for your environment.

Select name, value From Sys.V_$ses_Optimizer_Env 
Where Sid = (Select Distinct Sid From Sys.V_$mystat)
And Name In ('parallel_query_forced_dop','parallel_dml_forced_dop');
Related Question