An application accessing your database got the following error in response to SQL query:
ORA-12827: insufficient parallel query slaves available
View the parallel parameters for your instance:
No hints are used and the session use default parallel settings.
What four changes could you make to help avoid the error and ensure that the query executes in
parallel?
A.
Set PARELLEL_DEGREE_POLICY to AUTO.
B.
Increase the value of PARELLEL_MAX_SERVERS.
C.
Increase PARELLEL_SERVERS_TARGET.
D.
Decrease PARELLEL_MIN_PERCENT.
E.
Increase PARELLEL_MIN_SERVERS.
F.
Decrease PARELLEL_MIN_TIME_THRESHOLD.
G.
Increase PARELLEL__MIN_TIME_THRESHOLD.
Explanation:
C: PARALLEL_SERVERS_TARGET specifies the number of parallel server
processes allowed to run parallel statements before statement queuing will be used. When the
parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle will queue SQL statements that
require parallel execution, if the necessary parallel server processes are not available. Statement
queuing will begin once the number of parallel server processes active on the system is equal to
or greater than PARALLEL_SERVER_TARGET.By default, PARALLEL_SERVER_TARGET is set lower than the maximum number of parallel
server processes allowed on the system (PARALLEL_MAX_SERVERS) to ensure each parallel
statement will get all of the parallel server resources required and to prevent overloading the
system with parallel server processes.
D:
Note: 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
A, G: PARALLEL_MIN_TIME_THRESHOLD specifies the minimum execution time a statement
should have before the statement is considered for automatic degree of parallelism. By default,
this is set to 30 seconds. Automatic degree of parallelism is only enabled
if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.
I think A,B,C,D should be correct.
No G because increasing PARALLEL_MIN_TIME_THRESHOLD we can make the query run serially. But the question insists on parallel execution.
I think , G is correct as when we will increase the PARALLEL_MIN_TIME_THRESHOLD then resource availability might be there to run the query in parallel with out above error .
Suggest.
I Think ACDF is correct
Increased B value -Does not have any impact on error or influencing parallel
Increased E value -Does not have any impact on error or influencing parallel
Increased E value -Does not have any impact on error but influence serial
corrected the Typo error
Increased B value -Does not have any impact on error or influencing parallel
Increased E value -Does not have any impact on error or influencing parallel
Increased G value -Does not have any impact on error but influence serial
A, B, C and D
Not E, bacause this parameter hhis parameter has no effect in this situation.
“PARALLEL_MIN_SERVERS specifies the minimum number of parallel execution processes for the instance. This value is the number of parallel execution processes Oracle creates when the instance is started.”
Source: http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams155.htm#REFRN10160
Not F, not G, because the first sentence:
“PARALLEL_MIN_TIME_THRESHOLD specifies the minimum execution time a statement should have before the statement is considered for automatic degree of parallelism. By default, this is set to 10 seconds. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.”
Source: https://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams185.htm
We have “ORA-12827: insufficient parallel query slaves available” so automatic degree of parallelism is not only considered, but it is a fact.
NOT C because parallel_servers_target is relevant only if PARALLEL_DEGREE_POLICY is set to AUTO.
I think A,B,D,E