H.2.1.1 PARALLEL_QUERY
DISABLE
When the PARALLEL_QUERY option in the FSI_PARALLEL_PARAMETERS table is set to DISABLE, the engine will begin each rule by issuing the following statement:
ALTER SESSION DISABLE PARALLEL QUERY;Setting Parallel Query to DISABLE will ensure that no SELECT statement issued by the allocation engine will run in parallel.
ENABLE
When the PARALLEL_QUERY option in the FSI_PARALLEL_PARAMETERS table is set to ENABLE, the engine will begin each rule by issuing the following statement:
ALTER SESSION ENABLE PARALLEL QUERY;When Parallel Query is enabled, the database may elect to execute a SELECT statement in parallel if it is run against an object (table or index) that has been declared or altered with the use of the PARALLEL clause.
Two examples of the use of the PARALLEL clause to alter a table follow:
ALTER TABLE FSI_D_MANAGEMENT_LEDGER PARALLEL 8;ALTER TABLE FSI_D_MANAGEMENT_LEDGER PARALLEL (DEGREE DEFAULT);In the earlier first example, the database may elect to parallelize a SELECT statement against the FSI_D_MANAGEMENT_LEDGER table using a Degree of Parallelism equal to 8. In the second example, the database may elect to parallelize a SELECT statement against the FSI_D_MANAGEMENT_LEDGER table using the default Degree of Parallelism as determined by the database but only if PARALLEL_DEGREE_POLICY is set to Auto (default parallelism and PARALLEL_DEGREE_POLICY are discussed further as follows).
The database may also elect to execute a SELECT statement in parallel if a parallel SQL hint is included in the SELECT statement. A parallel SQL hint will override the Degree of Parallelism assigned to a table or index through the PARALLEL clause. If the tables and indexes against which a SELECT statement is executed have not been defined using the PARALLEL clause and if the SELECT statement does not include a parallel hint, then the SQL statement will always run serially (in a single thread).
If the tables and indexes against which a SELECT statement is executed have been defined using the PARALLEL clause or if the SELECT statement includes a parallel hint, the database may elect to run in parallel if it is possible to do so. The conditions necessary for the database to choose to parallelize a query are complex and beyond the scope of this discussion – for details, see the listing of documentation resources found at the end of this appendix.
FORCE
When the PARALLEL_QUERY option in the FSI_PARALLEL_PARAMETERS table is set to FORCE and the PARALLEL_DEGREE parameter is set to NULL, the engine will begin each rule by issuing the following statement:
ALTER SESSION FORCE PARALLEL QUERY;After this ALTER SESSION statement, subsequent SELECT statements are executed with a default Degree of Parallelism unless (a) the objects against which the SELECT statement operates is defined as having a specific Degree of Parallelism with the use of the PARALLEL clause or (b) the SELECT statement includes a parallel hint. Again, a parallel hint will override the Degree of Parallelism specified through a PARALLEL clause.
The default Degree of Parallelism is determined by the database and is generally a function of the number of CPUs on your server. For details on how the Oracle database determines the default Degree of Parallelism, see Oracle Database VLDB and Partitioning Guide.
FORCE with PARALLEL_DEGREE
When the PARALLEL_QUERY option in the FSI_PARALLEL_PARAMETERS table is set to FORCE and the PARALLEL_DEGREE parameter is set to a non-NULL integer value (8 in this example), the engine will begin each rule by issuing the following statement:
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;The subsequent behavior of Parallel Query is the same as where no specific Degree of Parallelism is specified except that the database will employ the Degree of Parallelism you have set in your PARALLEL_DEGREE parameter instead of using the database's default Degree of Parallelism. Your PARALLEL_DEGREE parameter will override any Degree of Parallelism stemming from a PARALLEL clause associated with a table or index, but a Degree of Parallelism specified in a parallel hint will override your PARALLEL_DEGREE parameter setting.
Regardless of its value, the PARALLEL_DEGREE parameter is ignored when the PARALLEL_QUERY parameter is set to either DISABLE or ENABLE.
PARALLEL_DML
Limitations in Using Parallel DML:
A Parallel DML operation's lock requirements are very different from the serial DML requirements. For these and other reasons, the database imposes some restrictions on Parallel DML operations.
One such restriction is that while a single transaction can contain multiple parallel DML statements that modify different tables after a parallel DML statement modifies a table no subsequent statement (DML or query) can access the same table again in the same transaction. For this reason, Parallel DML is disabled regardless of your parameter settings, for any allocation rule that both debit and credit the same table. For allocation rules that both debit and credit the same table and which you want to ensure run in parallel, you divide your one allocation rule into two rules (one rule for the debit side and one rule for the credit side).
Parallel DML cannot be set (regardless of the parameter settings) for any allocation rule where the Output tables (Credit/Debit) are the same as the Source/Driver tables. For more information regarding the restrictions on Parallel DML, see the Oracle Database Data Warehousing Guide.
DISABLE
When the PARALLEL_DML option in the FSI_PARALLEL_PARAMETERS table is set to DISABLE, the engine will begin each rule by issuing the following statement:
ALTER SESSION DISABLE PARALLEL DML;Setting Parallel DML to DISABLE will ensure that no DML statement issued by the allocation engine will ever run in parallel.
ENABLE
When the PARALLEL_DML option in the FSI_PARALLEL_PARAMETERS table is set to ENABLE, the engine will begin each rule by issuing the following statement:
ALTER SESSION ENABLE PARALLEL DML;When the Parallel DML is enabled, the database may elect to execute a DML statement in parallel if it is run against an object (table or index) that has been declared (or altered) using the PARALLEL clause.
The database may also elect to execute a DML statement in parallel if a parallel SQL hint is included in the DML statement. A parallel SQL hint will override the Degree of Parallelism assigned to a table or index through the PARALLEL clause. If the tables and indexes against which a SELECT statement is executed have not been defined using the PARALLEL clause and if the SELECT statement does not include a parallel hint, then the SQL statement will always run serially (in a single thread).
FORCE
When the PARALLEL_DML option in the FSI_PARALLEL_PARAMETERS table is set to FORCE and the PARALLEL_DEGREE parameter is set to NULL, the engine will begin each rule by issuing the following statement:
ALTER SESSION FORCE PARALLEL DML;After this ALTER SESSION statement, subsequent DML statements are executed with the default Degree of Parallelism for the database unless (a) the objects against which the SELECT statement operates have been defined as having a specific Degree of Parallelism through the use of the PARALLEL clause or (b) the SELECT statement includes a parallel hint. A parallel hint overrides the Degree of Parallelism specified through a PARALLEL clause. Again, for details on how the Oracle database determines the default Degree of Parallelism, see Oracle Database VLDB and Partitioning Guide.
FORCE with PARALLEL_DEGREE
When the PARALLEL_DML option in the FSI_PARALLEL_PARAMETERS table is set to FORCE and the PARALLEL_DEGREE parameter is set to a non-NULL integer value (4 in this example), the engine will begin each rule by issuing the following statement:
ALTER SESSION FORCE PARALLEL DML PARALLEL 4;The subsequent behavior of Parallel DML is the same as where no specific Degree of Parallelism has been specified except that the database will employ the Degree of Parallelism you have set in your PARALLEL_DEGREE parameter instead of using the database's default Degree of Parallelism. Your PARALLEL_DEGREE parameter will override any Degree of Parallelism stemming from a PARALLEL clause associated with a table or index, but a Degree of Parallelism specified in a parallel hint will override your PARALLEL_DEGREE parameter setting.
Regardless of its value, the PARALLEL_DEGREE parameter is ignored when the PARALLEL_QUERY parameter is set to either DISABLE or ENABLE.