Database Reference
In-Depth Information
The reason extra sessions are created is because one SQL statement executed in parallel requires not only one
session for the query coordinator, but also one session for each slave process. As a result, even though an end-user
logs in only once, he might require several sessions. Therefore, depending on how the
sessions_per_user
parameter
is set, the degree of parallelism might be limited.
■
I don't advise limiting resources through user profiles. To do that, you should ideally use the resource Manager.
I present the user profile approach just because you must be aware that user profiles can limit the degree of parallelism.
Tip
Downgrades
Downgrades take place when the number of slave processes that the query coordinator requests is higher than the
number of slave processes it actually gets. This happens in two situations:
•
When the degree of parallelism is limited by the techniques described in the preceding
section. In other words, when the degree of parallelism is limited by either adaptive
parallelism, the Resource Manager (for manual degree of parallelism only), or a user profile.
•
When the query coordinator requests from the pool a number of slave processes that's higher
than the number of slave processes actually available.
In fact, the database engine, depending on how many slave processes are already running at the time the query
coordinator requests some of them, might not be able to fulfill the request. For example, if the maximum number of
slave processes is set to 40, for the execution plan illustrated in Figure
15-7
(that requires 8 slave processes) only 5
concurrent SQL statements (40/8) can be executed with the required degree of parallelism. When the limit is reached,
there are three possibilities:
•
Either the degree of parallelism is downgraded (in other words, reduced),
•
An
ORA-12827: insufficient parallel query slaves available
error is returned to the
query coordinator, or
•
The execution of the SQL statement is put on hold until the necessary number of slave
processes is available.
The latter approach is used from 11.2 onward, and only if statement queuing is enabled (the next section covers
this). If statement queuing isn't enabled, one of the other two possibilities has to be used. To configure which one
is used, you have to set the
parallel_min_percent
initialization parameter. It can be set to an integer value ranging
from 0 to 100. There are three main situations:
•
0
: This value (which is the default) specifies that the degree of parallelism can be silently
downgraded. In other words, the database engine can provide as many slave processes as
possible. If less than two slave processes are available, the execution is serialized. This means
that the SQL statements are always executed, and the ORA-12827 error is never raised.
•
1-99
: The values ranging from 1 to 99 define a limit for the downgrade. At least the specified
percentage of the slave processes must be provided; otherwise, the ORA-12827 error is raised.
For example, if it's set to 25 and 16 slave processes are requested, at least 4 (16*25/100) must
be provided to avoid the error.
•
100
: With this value, either all the requested slave processes are provided or the ORA-12827
error is raised.
