How does Oracle enhance the parallel statement queue

This article mainly introduces how Oracle enhances the knowledge of parallel statement queues. The editor shows you the operation process through actual cases. The operation method is simple, fast, and practical. I hope this article on how Oracle enhances parallel statement queues can help Everyone solves the problem.

1 Description

Enhancing parallel statement queuing provides more flexibility to address business requirements for mission-critical environments.

Enhanced Parallel Statement Queuing provides more flexibility to meet the business needs of mission-critical environments.

When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database queues SQL statements that require parallel execution if the necessary number of parallel execution server processes are not available. After the necessary resources become available, the SQL statement is dequeued and allowed to execute. The default dequeue order is a simple first in, first out queue based on the time a statement was issued.

• 1. Parse SQL and automatically determine DOP (degree of parallelism)

• 2. Check whether there are enough parallel resources (PARALLEL_SERVERS_TARGET current environment default value 16) – cannot be larger than PARALLEL_MAX_SERVERSSmall

• a. If there is (the available parallel resource is greater than the current SQL required), and there is no SQL being executed before, then the SQL will be executed Queued until sufficient resources are available to execute the SQL.

If there is a statement queue waiting phenomenon, it will be shown as a waiting event: resmgr:pq queued.

2 Use hint to manage Parallel Statement Queuing

When PARALLEL_DEGREE_POLICY is AUTO , use NO_STATEMENT_QUEUING and STATEMENT_QUEUING to affect whether the statement uses the Parallel Statement Queuing queue.

2.1 NO_STATEMENT_QUEUING

Using NO_STATEMENT_QUEUING will bypass the parallel statement queue. But this may exceed the maximum number of parallel executions specified by PARALLEL_SERVERS_TARGET.

For example:

SELECT /*+ NO_STATEMENT_QUEUING */ last_name, department_name 

FROM employees e, departments d 

WHERE e.department_id = d.department_id;

2.2 STATEMENT_QUEUING

STATEMENT_QUEUING HINTLet the SQL statement use the parallel statement queue, but only run it if there are enough parallel processes available. Before enabling the queue, the number of parallel execution servers available is equal to the difference between the number of parallel execution servers used and the maximum number allowed in the system, as defined by the PARALLEL_SERVERS_TARGET parameter.

For example:

SELECT /*+ STATEMENT_QUEUING */ last_name, department_name 

FROM employees e, departments d 

WHERE e.department_id = d.department_id;

3 PARALLEL_SERVERS_TARGET settings

PARALLEL_SERVERS_TARGET The default value of the parameter is not fixed, but calculated by a formula.

Formula: PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 2

Such as: my current environment

PARALLEL_THREADS_PER_CPU = 2 SQL> show parameter PARALLEL_THREADS_PER_CPU NAME TYPE VALUE ----------------------------------- -------------- -------- parallel_threads_per_cpu integer 2 CPU_COUNT = 1 SQL> show parameter CPU_COUNT NAME TYPE VALUE ----------------------------------- -------------- -------- cpu_count integer 1

The concurrent_parallel_users parameter value is divided into three situations:

• 1. If MEMORY_TARGET or SGA_TARGET is set, then concurrent_parallel_users=4< /p>

• 2. If MEMORY_TARGET or SGA_TARGET is not set, and the size of PGA_AGGREGATE_TARGE is also set, then concurrent_parallel_users=2

• 3. If the size of PGA_AGGREGATE_TARGE is not set, then concurrent_parallel_users = 1 Set PGA_AGGREGATE_TARGET. So the second chapter is not satisfied

  SQL> show parameter MEMORY_TARGET NAME TYPE VALUE ----------------------------------- -------------- -------------------------------------- memory_target big integer 1G SQL> show parameter SGA_TARGET NAME TYPE VALUE ----------------------------------- -------------- -------------------------------------- sga_target big integer 0 SQL> show parameter PGA_AGGREGATE_TARGET NAME TYPE VALUE ----------------------------------- -------------- -------------------------------------- pga_aggregate_target big integer 0 SQL> show parameter PARALLEL_SERVERS_TARGET NAME TYPE VALUE ----------------------------------- -------------- -------------------------------------- parallel_servers_target integer 16

  So, PARALLEL_SERVERS_TARGET= 2 * 1 *4 * 2 = 16, which is consistent with the query result.

  3.1 Modify parameter values

  can only be modified at the system level in CDB , between 0 and PARALLEL_MAX_SERVERS, restart to take effect

  SQL> alter system set parallel_servers_target = 17 scope = spfile; System altered. SQL> startup force ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 8801008 bytes Variable Size 1023411472 bytes Database Buffers 37748736 bytes Redo Buffers 3780608 bytes Database mounted. Database opened. SQL> show parameter PARALLEL_SERVERS_TARGET NAME TYPE VALUE ----------------------------------- -------------- -------------------------------------- parallel_servers_targetInteger 17

  This is the end of the introduction on how Oracle enhances the parallel statement queue, thank you for reading. If you want to know more industry-related knowledge, you can pay attention to the industry information channel of Yisu Cloud. The editor will update different knowledge points for you every day.