Oracle Parallel Concept Chapter __oracle

Oracle Parallelism (concept)

Concept

Serial execution: when serial execution, the SQL statement is handled by one server process (running on only one CPU), so the resources that the SQL statement can use are CPU-bound, excluding asynchronous I/O server process
Disk cannot be accessed while processing CPU operations.
Parallel Processing: that is, multiple slave processes work together on the same SQL statement, and the server process acts as query Coordinator (creating slave process, assigning tasks, collecting merge slave The results of the process pass back to the client).
Parallel execution of the work granularity:
1. Partition granularity (Paratition granule): A partition or a child partition is assigned to a work content (partition segment). is to operate the PX block iterator.
2. Chunk range granularity (Block Range granule): Segment the last range of blocks, runtime dynamic definition (not parse). is the operation of PX Partion iterator.

Comments:
*oracle tends to block range granule, because the Block Range granule allows slave process to get a more average workload, and partitioning granularity may make the partition that some slave process gets a lot of work, resulting in longer response times can affect the overall efficiency of concurrent execution.

The *sql statement performs 2 or more independent operations (for example, a non-dependency operation Join,scan (such as a full table scan, etc.) &order by this will use 2 groups of slave process, a set of scan, a set of order by)
* Press Scan&order by this example, 2 groups of slave process, the first set of select, then the parallel in select (slave process operation) is called the operation of parallelism , that is, a single operation of parallelism. Then scan and order by are 2 operations in parallel.
The *2 group slave process is used to execute an SQL statement called parallelism between operations (Group 1 SACN, Group 2 by operation between operations is parallelism ).
* When the operation is in parallel , there will be many groups of slave process communication , such as group 1scan data to group 2orderby, Group 1 is the producer (producer), group 2 is the consumer (consumer), Executed by the producer and the consumer.

different operations, you will choose a different assignment (data) record way to another operation:
1. Broadcasting: Each producer sends all data to the consumer
2.range: The producer will issue a range of records to different consumers, will apply dynamic range zoning to determine which record to which consumer (for Orde by action in the ORDER BY clause field range partition)
3.loop: Records will be divided evenly between each consumer (i.e., the producer loops once per loop, sending a record to a consumer)
4.hash: The producer uses the hash function to send the data to the consumer, dynamically applies the hash partition to decide which record gives to which consumer (for group by according to the GROUP BY clause uses the field to hash)
5.QC Random: Each producer sends all records to query Coordinator (random), which is a common method
6.QC Order: Each producer sends all records to query Coordinator (order is important), in parallel to send the data to query Coordinator (server process)

#上面的广播方法就是执行计划中 px Send QC (random/order) operation or px send(QC Random) operation, its parent operation is PX coordator (query scheduling).

The above range/hash/loop is the implementation plan px send QC range/hash/loop operation, its parent operation is PX recive (recipient, consumer).

Relationship between parallel operations (in-out part of the execution plan)
P-s: Sending data in parallel to serial
P-p: (Used with 2 groups of slaves process), a parallel operation sends the data to another parallel operation
PCWP: The same slave process performs one operation and its parent operation in parallel, without communication
PCWC: The same slave process performs an operation and its child operations in parallel, without communication
S-P: Serial send data to parallel, inefficient (1. A single process produces data without multiple processes consuming data quickly, and consumers spend a lot of time waiting for data rather than processing data; 2. Serial execution and parallel execution there are some unnecessary communications to send data)

Parameter control parallelism:
1.parallel_min_servers:

instance the number of slaves process that is automatically created when the database instance is started, so that when the server process request does not need to be recreated, it can be used directly . When the number of server process requests exceeds this setting, a new slave process is created, and the newly created will manage a slave process in slave process pool(Oracle) after use is completed Pool,query Coordinator process Both the server process requests the slave Process,sql statement from this pool and returns the pool after using the slave process.

Save 5 minutes to release if not yet used , so the parameter Parallel_min_servers controls the number of slave process in this pool, Defalut 0, which means it takes time to create.
when you need to modify this parameter: This value is typically modified when an OS thread startup event (which indicates that the SQL statement spends time waiting for the slave process) is changed .

2.parallel_max_server:

Control the maximum number of slave process in pool (can be set to Cpu_count, 8-10 times).

If parallel_adaptive_multi_user (boolean parameter) is True (10G default is True), only a limited number of users are allowed to default to the degree of parallelism run.

This limit is controlled by _parallel_adaptive_max_users . _parallel_adaptive_max_users says maximum number of users running with default DOP. For example,_parallel_adaptive_max_users is 2, which specifies that it is now displayed as 2 user.

#查看server Process Pool State
Sql> SELECT * from V$px_process_sysstat where upper (statistic) like ' server% ';

Statistic VALUE
—————————— ———-
Servers in use 0~~
Servers Available 2~~
Servers started 2~~~ started a few
Servers Shutdown 0
Servers Highwater 2~~~ Highest arrival
Servers cleaned up 0
Server Sessions 2

7 rows selected.

* A table queuememory in the SGA sends data to the consumer, each pair (producer & consumer) corresponds to a table queue to handle 2 groups of subordinate interprocess communication, and thetable queue can be placed in shared pool, You can also large pool
( in fact, the table queue is the PX msg pool) and is present when the 10G sga_target>0 or 11g memory_target>0 large pool,parallel_automatic _tuning=true placed in large pool,10g defaults to true,11g
The default is to place the large pool,parallel_automatic_tuning in the 11G has been discarded backwards compatible reserved (the parallel_automatic_tuning parameter is deprecated. It is retained to backward compatibility only.)

A more detailed description of the parallel_automatic_tuning
When parallel_automatic_tuning are set to True, Oracle determines the default values for parameters this control PARALLEL E Xecution. In addition to setting this parameter, your must specify the PARALLEL for the target tables in the system. Oracle then tunes all subsequent parallel operations automatically.

If you are used parallel execution in a previous release and are now enabling parallel_automatic_tuning, then you should reduc E The amount of memory allocated from the "shared pool to" for "decreased demand on" that pool. This memory'll is allocated from the large pool, and'll be computed automatically if large_pool_size are not specif Ied.

As part of the automatic tuning, Oracle would enable the Parallel_adaptive_multi_user parameter. You can override any of the system-provided defaults if desired.

Each table queue consists of 3 buffer (blocks), 4 in the RAC. Each buffer size is controlled by the parallel_execution_message_size, and the default is that 2152bytes,parallel_automatic_tuning=true,parallel_ execution_message_size=4096 bytes.
you can set a large 16k,32k,64k (memory is sufficient).

######## #large Pool Size
large_pool_size>=parallel_max_servers^2.parallel_execution_message_size.3 (RAC. 4)

sql> SELECT * from V$sgastat where upper (name) = ' PX MSG POOL ';

POOL NAME BYTES
———— ————————– ———-
Large Pool PX msg pool 902160

So you can assume that table queue is PX msg pool.

sql> Select Pool,name, bytes/1024/1024/1024 from V$sgastat where Upper (pool) like '%large% ';

POOL NAME bytes/1024/1024/1024
———— ————————– ——————–
Large Pool PX msg pool. 000840202
Large Pool free Memory 003066048

Article Author: Xuhui
This article address: http://xuguohao.gotoip55.com/?p=142
Copyright © reproduced must be linked to the form of the author and the original source.