You receive the following error message:
ORA-12827: insufficient parallel query slaves available
Which three parameter settings could you change to avoid this error?
A.
Decrease the value of PARALLEL_MIN_PERCENT
B.
Increase the value of PARALLEL_MAX_SERVERS
C.
Increase the value of PARALLEL_MIN_SERVERS
D.
Reduce the value of PARALLEL_MIN_TIME_THRESHOLF
E.
Increase the value of PARALLEL_DEGREE_LIMIT
F.
Set the PARALLEL_DEGREE_POLICY = AUTO
G.
Set the PARALLEL_DEGREE_POLICY = LIMITED
Explanation:
A: ORA-12827:
insufficient parallel query slaves available
Cause: PARALLEL_MIN_PERCENT parameter was specified and fewer than minimum slaves
were acquired
Action: either re-execute query with lower PARALLEL_MIN_PERCENT or wait until some running
queries are completed, thus freeing up slaves
B: Your query doesn’t run because you’ve told Oracle not to run it unless at least 5% of the parallel
execution processes are available for your query.Set PARALLEL_MIN_PERCENT=0 or increase
the number of parallel execution processes by increasing the PARALLEL_MAX_SERVERS
parameter.
G: PARALLEL_DEGREE_POLICY
PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism,
statement queuing, and in-memory parallel execution will be enabled.
LIMITED
Enables automatic degree of parallelism for some statements but statement queuing and inmemory Parallel Execution are disabled. Automatic degree of parallelism is only applied to those
statements that access tables or indexes decorated explicitly with the PARALLEL clause. Tables
and indexes that have a degree of parallelism specified will use that degree of parallelism.
Note:
PARALLEL_MIN_PERCENT operates in conjunction with PARALLEL_MAX_SERVERS and
PARALLEL_MIN_SERVERS. It lets you specify the minimum percentage of parallel execution
processes (of the value of PARALLEL_MAX_SERVERS) required for parallel execution. Settingthis parameter ensures that parallel operations will not execute sequentially unless adequate
resources are available. The default value of 0 means that no minimum percentage of processes
has been set.
Consider the following settings:
PARALLEL_MIN_PERCENT = 50
PARALLEL_MIN_SERVERS = 5
PARALLEL_MAX_SERVERS = 10
If 8 of the 10 parallel execution processes are busy, only 2 processes are available. If you then
request a query with a degree of parallelism of 8, the minimum 50% will not be met.
You can use this parameter in conjunction with PARALLEL_ADAPTIVE_MULTI_USER. In a multiuser environment, an individual user or application can set PARALLEL_MIN_PERCENT to a
minimum value until sufficient resources are available on the system and an acceptable degree of
parallelism is returned.
why not F?
A,B,F
The answer are indeed correct.
F is wrong because Adaptive Parallelism & downgrades only happen when PARALLEL_DEGREE_POLICY = (MANUAL,LIMITED)
Ref: Troubleshooting Oracle Performance, 2nd ed, Chris Antognini, note on top of the page 620.
If PARALLEL_DEGREE_POLICY=AUTO, you have Statement Queuing (alongside Resource Manager) instead of downgrades.
A,B,F
A,B,F
PARALLEL_MIN_PERCENT operates in conjunction with PARALLEL_MAX_SERVERS and
PARALLEL_MIN_SERVERS.
So i think A,B,C