Enabling Parallel Query

System Parameters and Status Variables

• Table 1 lists the supported system parameters.

  Table 1 System parameters

  Parameter	Level	Description
  force_parallel_execute	Global, Session	Enables or disables parallel query. If this parameter is set to ON, parallel query is enabled. Value range: ON and OFF Default value: OFF
  parallel_max_threads	Global	Maximum number of active threads allowed for parallel execution. If the number of active threads in the current system exceeds the value of this parameter, parallel execution cannot be enabled for new queries. Value range: 0 to 4294967295 Default value: 64
  parallel_default_dop	Global, Session	Default parallelism degree for parallel execution. If the parallelism degree is not specified in query statements, this parameter value is used. Value range: 0 to 1024 Default value: 4
  parallel_cost_threshold	Global, Session	Cost threshold for enabling parallel execution. If the parallel execution cost of query statements exceeds the value of this parameter, parallel execution is enabled. Value range: 0 to 4294967295 Default value: 1000
  parallel_queue_timeout	Global, Session	Waiting time of the parallel execution. If the waiting time exceeds the value of this parameter, new queries will be executed in single-thread mode. Value range: 0 to 4294967295 Default value: 0
  parallel_memory_limit	Global	Maximum available memory for parallel execution. If the amount of memory used for parallel execution exceeds the value of this parameter, new queries will not be executed in parallel mode. Value range: 0 to 4294967295 Default value: 104857600

• Table 2 lists the supported status variables.

  Table 2 Status variables

  Variable	Level	Description
  PQ_threads_running	Global	Total number of concurrent threads that are running.
  PQ_memory_used	Global	Total memory used for parallel execution.
  PQ_threads_refused	Global	Total number of queries that fail to be executed in parallel due to the limit on the total number of threads.
  PQ_memory_refused	Global	Total number of queries that fail to be executed in parallel due to the limit on the total memory.

Enabling Parallel Query

You can enable or disable parallel query by configuring system parameters in the console or using hints in SQL statements.

• Method 1: Configuring system parameters in the console

  Log in to the console and go to the Parameters page to configure the following system parameters:

  force_parallel_execute: determines whether to forcibly enable parallel execution.

  parallel_default_dop: indicates the parallelism degree for parallel execution. It controls the number of concurrent threads.

  parallel_cost_threshold: indicates the cost threshold for enabling parallel execution.

  Theses parameters can be modified at any time. The modifications will take effect immediately and you do not need to reboot the instance.

  For example, if you want to forcibly enable parallel execution, set the parallelism degree to 4, and set the minimum execution cost to 0, configure the parameters as follows:

  SET force_parallel_execute=ON
  SET parallel_default_dop=4
  SET parallel_cost_threshold=0

• Method 2: Using hints in SQL statements

  Hints can be used to control whether a single statement is executed in parallel. If parallel execution is disabled by default, uses hints to enable parallel execution for specific SQL statements. You can also use hints to disable parallel execution for specified SQL statements.

  Enabling parallel execution:

  Enabling parallel execution: SELECT /*+ PQ() */... FROM...

  Enabling parallel execution and setting the parallelism degree to 8: SELECT /*+ PQ(8) */... FROM...

  Enabling parallel execution and set the parallel-executed table to t1: SELECT /*+ PQ(t1) */... FROM...

  Enabling parallel execution, set the parallel-executed table to t1, and set the parallelism degree to 8: SELECT /*+ PQ(t1 8) */... FROM...

  

  SELECT is followed by PQ (Hints). Otherwise, the hints do not take effect. dop indicates the parallelism degree of a parallel query and its value ranges from 1 to min(parallel_max_threads, 1024).

  When the dop value exceeds the normal range, parallel query does not take effect.

  Disabling parallel execution: When parallel query is enabled, use the NO_PQ to disable parallel execution of a single SQL statement.

  SELECT /*+ NO_PQ */ … FROM …

  

  NO_PQ (Hints) takes precedence over PQ (Hints). If an SQL statement contains NO_PQ (Hints), the SQL statement will not be executed concurrently even if PQ (Hints) is configured.

Checking the Statuses of Query Statements Executed in Parallel

Run the following SQL statement to display the statuses of query statements executed in parallel, as shown in Figure 1.

show status like "%PQ%"

Figure 1 Status

Use EXPLAIN to display the parallel execution plans of the query statements, as shown in Figure 2.

Figure 2 Parallel execution plan

Compared with a traditional execution plan, a parallel execution plan has one more row of records. In the first row of the query result, the parallel-executed tables and parallelism degree are displayed.