Oracle "Parallel execution" of the three--parameters

Excerpt from the VLDB and partitioning guide

Parameters are directly involved in the system parameters, see table 8-3, including the default values for each parameter. The default values for these parameters depend on the parameters Cpu_count and PARALLEL_THREADS_PER_CPU when the database starts. These parameters can be adjusted manually to increase or decrease their values to suit system configuration needs or performance objectives, such as:

• For systems that do not require parallel execution, the parameter parallel_max_servers can be set to 0.
• For systems with ample SGA memory, you can increase the value of the Parallel_execution_message_size parameter to improve throughput.

650) this.width=650; "title=" clipboard "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clipboard "src=" http://s3.51cto.com/wyfs02/M02/83/9C/ Wkiol1d35kpj7kg0aah2tiiap6o273.png "" 710 "height=" 579 "/>

650) this.width=650; "title=" clipboard[1] "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clipboard[1] "src=" http://s3.51cto.com/wyfs02/M02/83/9C/ Wkiol1d35kttzj8oaabin7n8nt8193.png "" 715 "height="/>

Note: The above parallel related parameters may also be limited by other system parameters. For example, if the processes parameter is set to 20, then the system cannot get 25 parallel server sub-processes, even if the parallel_max_servers parameter is set to 25.

Parallel_force_local

This parameter sets whether to qualify the parallel execution statement on a single instance in the RAC environment. Setting this parameter to True will restrict the parallel server to run on the same instance as the parallel coordinator. The default value is False.

Parallel_max_servers

The default values are:

PARALLEL_THREADS_PER_CPU x Cpu_count x concurrent_parallel_users x 5

Where: The Concurrent_parallel_users value depends on how the memory is managed. If automatic memory management is disabled, the Concurrent_parallel_users value is 1, and if PGA automatic memory management is enabled, the Concurrent_parallel_users value is 2 If you enable global memory management or SGA memory management (in addition to enabling PGA Automatic memory Management), the Concurrent_parallel_users value is 4.

For situations that require a higher degree of parallelism, you can also set:

2 x DOP x number_of_concurrent_users

For example, if each query allows two sets of parallel servers with a DOP of 8, setting this parameter to 64 allows the system to run 4 query statements concurrently.

When to increase the Parallel_max_servers value: The system has sufficient memory and IO bandwidth to increase this parameter value. The Performance monitoring tool observes how much free memory, swap space, and IO bandwidth The system has, observes the CPU and disk queue length, and the service time of the IO operation. Limiting the number of parallel servers may limit the number of concurrent users (possibly running parallel statements), but this will make the system more stable.

Use consumer groups to limit the amount of resources used by a concurrent user. The way to do this is to set up a consumer group for this user and then set the degree of parallelism for that consumer group.

Parallel_min_percent

This parameter (greater than 0 o'clock) allows concurrent users to wait for an acceptable DOP, if the system meets the required number of parallel servers within a certain time, the system will prompt an error: ora-12827:insufficient parallel query slaves available.

The default value of this parameter is 0, which means that if there are not enough parallel servers, the ORA-12827 error will not be reported if there is a direct conversion to serial execution.

Parallel_min_servers

You can use this parameter to set up a parallel server that starts with a single instance when it starts. These parallel servers persist until the DB instance is closed.

Parallel_min_time_threshold

This parameter is used to set the minimum execution time that the SQL statement needs to meet when considering auto DOP. This minimum execution time is evaluated by the system. The default is 10 seconds, which means that only statements that have an execution time of more than 10 seconds will consider auto DOP. Also, this parameter will only take effect if Auto DOP is enabled, that is, only the parameter parallel_degree_policy is set to auto or limited.

Parallel_servers_target

This parameter is used to set the maximum number of current parallel servers before the SQL statement is queued. The default value is:

PARALLEL_THREADS_PER_CPU * Cpu_count * concurrent_parallel_users * 2

The Concurrent_parallel_users calculation method is shown in the Parallel_max_servers parameter section.

When the parameter parallel_degree_policy is auto, subsequent SQL statements enter the queue if the number of parallel servers being used by the current system equals or exceeds the Parallel_servers_target parameter value. The main starting point of this parameter is to prevent excessive use of parallel servers, resulting in system performance overload.

Shared_pool_size

Parallel execution requires additional memory, in addition to the same memory resources as serial execution. These additional memory is used to query the server, Query Coordinator communication and transfer data.

The database allocates memory for the parallel server from Shared_pool. The main rules for tuning are as follows:

1) first for the shared pool of other users to consider, such as cursors, stored procedures, etc.;

2) Remember that a larger value in a multiuser environment can improve performance, while smaller values save memory;

3) You can monitor the number of buffer used in parallel execution and compare the PX msg pool value in the large pool with the maximum number of buffer (buffers HWM) used in parallel in V$px_process_sysstat.

Examples are as follows:

1) Execute SQL query large pool in PX msg pool value

SELECT POOL, NAME, SUM (BYTES) from V$sgastat WHERE POOL like '%pool% '

GROUP by ROLLUP (POOL, NAME);

POOL NAME SUM (BYTES)

Large Pool PX msg pool 38092812

2)

SELECT * from V$px_process_sysstat WHERE statistic like ' buffers% ';

Statistic VALUE

-------------------                      -----

Buffers Allocated 23225

Buffers Freed 23225

Buffers Current 0

Buffers HWM 3620

3) The number of memory buffer currently in use can be obtained by buffers current and buffers HWM statistics. This number is multiplied by the parallel_execution_message_size parameter value, which is the number of bytes of memory used. Use the high water level memory bytes to compare the PX msg pool values to determine if the memory you are allocating is too large or too small. The above PX msg pool value is 38092812, which is 38MB. The buffers HWM value is 3620, multiplied by the parallel_execution_message_size parameter value, 4096, equals 14827520, or 15MB. The parallel buffer high water level occupies 40% of the allocated memory amount.

Pga_aggregate_target

Use the Pga_aggregate_target parameter to control the amount of memory used by the process, or to control the number of process. The Pga_aggregate_target and Memory_target parameters are also set, and you can continue to enable automatic tuning, but not when the pga_aggregate_target is below the specified value.

Parallel_execution_message_size

This parameter specifies the buffer size used for parallel execution. The default value depends on the operating system platform, most of which is 16K, and this value can meet the needs of most applications.

Transactions

For parallel DML and DDL, a single transaction is enabled for each parallel server. The parallel execution coordinator uses the two-phase commit protocol to commit a transaction, so the number of transactions increases with the DOP increase. So, in parallel execution, you may need to increase the value of the transactions parameter.

This parameter is used to specify the maximum number of concurrent transactions. Parallelism is not considered by default. For example, if you have a parallel statement with a DOP of 20, the system will have 20 new transactions (40 new transactions if there are 2 parallel server groups) and a coordinator transaction. In this case, you should add a value of 21 or 41 to the transactions parameter. If this parameter is not explicitly set, the default is 1.1 x SESSIONS.

Fast_start_parallel_rollback

If the system crashes with uncommitted parallel DML and DDL transactions, setting this parameter can speed up the recovery of the transaction (when the system restarts). This parameter determines the DOP of the transaction recovery. The default value for this parameter is twice times the value of the Cpu_count parameter. If the default value does not meet the requirements, you can set this parameter to high (4 times times the value of the Cpu_count parameter).

Dml_locks

This parameter sets the maximum number of DML locks. Its value should be equal to the number of locks on all tables under all users. The number of locks in parallel DML, which is completely different from serial DML. Parallel DML requires more locks, so the dml_locks parameter value needs to be increased.

Table 8–4 lists the different types of parallel DML statements whose parallel coordinators are required by the parallel server for the lock situation. Depending on this information, you can decide how to set this parameter value.

650) this.width=650; "title=" clipboard[2] "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clipboard[2] "src=" http://s3.51cto.com/wyfs02/M02/83/9D/ Wkiom1d35kwylz6raaeqnx1prve339.png "" 624 "height=" 439 "/>

650) this.width=650; "title=" clipboard[3] "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clipboard[3] "src=" http://s3.51cto.com/wyfs02/M00/83/9C/ Wkiol1d35kbijafdaadxkxnbvli016.png "" 622 "height=" 365 "/>

For example, there is a 600-partition table with a DOP of 100. Assume that all partitions are involved in a parallel update or DELETE statement and that there is no row migration. So

The parallel coordinator will get:

1 table Lock SX

Partition Locks X

The parallel server will get:

Table Locks SX

Partition Locks NULL

Partition-wait Locks S

Db_cache_size

When you perform a parallel update, merge, or delete, the buffer cache behaves as if a large number of update operations are performed in the OLTP system.

Db_block_size

It is recommended to use a larger value, such as 8 KB or KB.

Db_file_multiblock_read_count

This parameter determines the number of database blocks that can be read by the operating system "read" at a time. Its default value is the maximum IO size, which is dependent on the specific system platform (this value is 1M for most platforms). If the Db_file_multiblock_read_count parameter is set too high, this parameter is reduced to the maximum IO size when the database is started.

Disk_asynch_io and Tape_asynch_io

It is recommended that both values be true.

These two parameters can enable the asynchronous IO functionality of the operating system. They allow both IO requests and CPU processing while performing a table scan. If the operating system allows asynchronous IO, it is recommended to set these parameters to true. Figure 8–6 describes how asynchronous reading works.

650) this.width=650; "title=" clipboard[4] "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clipboard[4] "src=" http://s3.51cto.com/wyfs02/M00/83/9D/ Wkiom1d35kfws8xbaabpydhnx78199.png "" 469 "height=" 291 "/>

Asynchronous Operations support Parallel table scans, hash joins, sorting, and serial table scans. This feature requires the configuration of the operating system and may not support some system platforms.

Oracle "Parallel execution" of the three--parameters