About Managing Parallel Statement Queuing with Hints

You can use the NO_STATEMENT_QUEUING and STATEMENT_QUEUING hints in SQL statements to influence whether or not a statement is queued with parallel statement queuing.

• NO_STATEMENT_QUEUING

  When PARALLEL_DEGREE_POLICY is set to AUTO, this hint enables a statement to bypass the parallel statement queue. However, a statement that bypasses the statement queue can potentially cause the system to exceed the maximum number of parallel execution servers defined by the value of the PARALLEL_SERVERS_TARGET initialization parameter, which determines the limit at which parallel statement queuing is initiated.

  There is no guarantee that the statement that bypasses the parallel statement queue receives the number of parallel execution servers requested because only the number of parallel execution servers available on the system, up to the value of the PARALLEL_MAX_SERVERS initialization parameter, can be allocated.

  For example:

  SELECT /*+ NO_STATEMENT_QUEUING */ last_name, department_name 
    FROM employees e, departments d 
    WHERE e.department_id = d.department_id; 
  

• STATEMENT_QUEUING

  When PARALLEL_DEGREE_POLICY is not set to AUTO, this hint enables a statement to be considered for parallel statement queuing, but to run only when enough parallel processes are available to run at the requested DOP. The number of available parallel execution servers, before queuing is enabled, is equal to the difference between the number of parallel execution servers in use and the maximum number allowed in the system, which is defined by the PARALLEL_SERVERS_TARGET initialization parameter.

  For example:

  SELECT /*+ STATEMENT_QUEUING */ last_name, department_name 
    FROM employees e, departments d 
    WHERE e.department_id = d.department_id;