Feature | Command Line | Description |
Enable parallel query | SELECT /*+PARALLEL(x)*/ ... FROM ...; | x indicates the parallelism for the SQL statement, which should be greater than 0 . |
Disable parallel query | SELECT /*+PARALLEL(x)*/ ... FROM ...; | If x is set to 0 , it indicates to disable parallel query. |
Specify the parallel table | You can specify the table to be included in or excluded from the parallel query execution plan in either of the following ways: Specify the table to be included in the plan through PARALLEL . SELECT /*+PARALLEL(t)*/ ... FROM ...; Specify the table to be excluded from the plan through NO_PARALLEL . SELECT /*+NO_PARALLEL(t)*/ ... FROM ...; | t is the table name. |
Specify both the parallel table and parallelism | SELECT /*+PARALLEL(t x)*/ * ... FROM ...; | x indicates the parallelism for the SQL statement, which should be greater than 0 . t is the table name. |
Set the session parameter through the HINT statement, which takes effect only for the specified SQL statement | SELECT /*+SET_VAR(var=n)*/ * ... FROM ...; | var is the parallel query parameter in the session scope. |
select /*+PARALLEL()*/ * FROM t1,t2;
Set the parallelism to the value of txsql_parallel_degree
(default) for the parallel query. If a statement does not meet the parallel query execution condition, serial query will be used.select /*+PARALLEL(4)*/ * FROM t1,t2;
Set the parallelism of the statement to 4
regardless of the default value, i.e., txsql_parallel_degree = 4
. If the statement does not meet the parallel query execution condition, serial query will be used.select /*+PARALLEL(t1)*/ * FROM t1,t2;
Include the t1
table in the parallel query and use the default parallelism. If t1
is smaller than the value of txsql_parallel_table_record_threshold
, serial query will be used.select /*+PARALLEL(t1 8)*/ * FROM t1,t2;
Include the t1
table in the parallel query and set the parallelism to 8
. If t1
is smaller than the value of txsql_parallel_table_record_threshold
, serial query will be used.select /*+NO_PARALLEL(t1)*/ * FROM t1,t2;
Exclude the t1
table from the parallel query. If t1
is greater than the value of txsql_parallel_table_record_threshold
, serial query will be used.select /*+SET_VAR(txsql_parallel_degree=8)*/ * FROM t1,t2;
Set the parallelism of the statement to 8
regardless of the default value, i.e., txsql_parallel_degree = 8
.select /*+SET_VAR(txsql_parallel_cost_threshold=1000)*/ * FROM t1,t2
Set txsql_parallel_cost_threshold=1000
for the statement. If its execution penalty is greater than 1000
, parallel query can be used.select /*+SET_VAR(txsql_optimizer_context_max_mem_size=500000)*/ * FROM t1,t2
Set txsql_optimizer_context_max_mem_size=500000
for a statement, which means to adjust the maximum memory size it can apply for in the parallel query plan environment to 500000
.
Was this page helpful?