Oracle Parallel operations

SQL statements are executed serially on a single CPU (Serial), even if there are multiple CPUs in the system, and an SQL statement cannot take advantage of them. This allows a CPU to use resources that determine the performance of the SQL statement. Another way to improve performance is at the same time-parallel processing-while working with multiple CPUs.

Orthodox theorists believe that there are two types of database in Enterprise, OLTP and OLAP, the former is used for business processing, it is characterized by a large number of transaction-type SQL statements with short running time, and the function of parallel operation in this system is limited. The latter is used for data analysis, which is characterized by a large number of query-type SQL statements, here is the stage of parallel operation. However, in a real-world enterprise environment, a separate OLAP data warehouse may not be deployed due to cost constraints. Functional boundaries can not be absolutely cut, many 7x24 business database also support data statistical analysis function, although in theory these functions should be in a separate data warehouse to complete. Therefore, parallel processing is a very useful tool for both data warehousing and OLTP systems.

This address: http://blog.csdn.net/sunansheng/article/details/51219039

Parallel architectures

Oracle's parallel processing began in 1996 with the Oracle 7.3 release, which continues to evolve and grow as Oracle databases evolve. It is customary for users to feel that only data warehouses can benefit from parallel capabilities, which is not fully understood. Oracle's parallel processing is not limited to query functions (SELECT), DML operations (INSERT, UPDATE, DELETE), DDL operations (Create TABLE, create INDEX), collect statistics, load data, Backup and recovery functions have been supported in parallel. Because parallel processing can take advantage of multiple hardware resources, the higher the utilization, the more efficient the processing. With parallel processing, terabytes of data can be completed in a few 10 minutes without having to wait a day or more, but the processing power is not a linear relationship, 7-1, because database processing involves multiple parts, including CPU, memory, and disk IO, which, if not extended synchronously, Then the overall performance improvement is limited by the weakest Link, the short board theory.

Oracle's so-called parallel operations make it possible to make full use of system resources, including multi-CPU, multi-IO channels, by breaking down a task into many small, independent parts so that a large task that can only be done by one process becomes a small task that could be done simultaneously by multiple processes. You can use a real-life example to explain what parallel processing is: Calculate how many cars are on a street, and if you're going to do it by yourself, you'll need to walk from the street to the end of the street to figure out how many cars there are. If there are two people to complete this task, then one can walk from the street to the end of the street, another person from the end of the street to the street, if the ability of two people, in theory, only half the original time to complete the task.

NOTE: Only Oracle Enterprise Edition supports parallel processing.

Oracle's parallel architecture involves processes, memory, and parameters, which are described in detail below.

Process

Parallel operations use two types of processes, called Slave process and Query coodinator, respectively. The former consists of a number of processes that have the same status and that each process is responsible for part of the work or a unit of work. For example, a full-table scan, each Slave process is responsible for scanning a portion of the Segment, such a set of processes is also called a PS set (Parallel Server set).

The Query coodinator process is usually the process that emits the SQL statement, which is responsible for splitting a task into several task slices, applying PS Set, specifying a work fragment for each Slave process, and collecting the processing results returned by each Slave process and Aggregated into the final result is returned to the user. In general, the role of two processes can be understood in this way, Slave process is the real work, and the Query coordinator process is just a finger-pointing, coordinating, of course, occasionally will work.

In a parallel architecture, the Query coornidator process divides the entire work into units of a certain granularity (granules), each Slave process assigns a unit of work, and Slave process completes the unit assigned to it, such as If there are remaining units of work, continue assigning the next unit until all the cells are complete. The Query coornidator process divides the granularity of the work cell into two types.

• Partitioning: If the object is partitioned, the unit of work can be partitioned in partitions.
• Block range: Each unit of work is a number of data blocks in a Segment.

Note: The partitioning of the unit of work is performed during the execution of the statement, not during the parsing phase.

Related parameters

There are multiple parallelism-related parameters in the Oracle database, and these parameters vary greatly with the version, and readers can use the show PARAMETER command to view these parameters, for example, the following are the results of the query in Oracle 10.2.0.1:

sql> Show parameter parallel NAME TYPE VALUE fast_start_parallel_rollback string Low parallel_adaptive _multi_user Boolean TRUE parallel_automatic_tuning boolean FALSE parallel_execution_message_size Integ Er 2152 parallel_instance_group string parallel_max_servers integer parallel_min_percent I Nteger 0 parallel_min_servers Integer 0 parallel_server boolean FALSE parallel_server_instances Integer 1 parallel_threads_per_cpu integer 2 recovery_parallelism integer 0 

These parameters are directly related to parallel functions, but there are many parameters that indirectly affect the parallelism capability, and some of the most important parameters are described below.

1.paralle_max_servers

Oracle's parallel processing is done through a dedicated process, which is the Slave process mentioned earlier. Parallel processes are used in database-wide sharing, where all the parallel processes in the database form a process pool (PX Server pool), and whenever parallel operations are required, a sufficient number of parallel processes are taken from the pool, and when the operation is completed, the parallel process is returned to the pool. The paralle_max_servers parameter is the maximum number of parallel processes that control the database scope. This is also a self-protection mechanism, and it is easy to consume optical system resources if you do not control the number of parallel processes.

The default value of the parameter is =cpu number x10. Setting this parameter to 0 is equivalent to disabling parallelism within the database scope.

2.parallel_min_servers

The Parallel_min_servers parameter defines the minimum number of concurrent processes to keep in the process pool, which is started when the database starts, and remains running for the lifetime of the database, even if no parallel operation is used.

If the required Slave process exceeds this amount, Oracle will dynamically increase the Slave process, which will wait 5 minutes after completing the task, and if there are no new assignments in 5 minutes, Oracle will end this Some processes.

The default value for the Parallel_min_servers parameter is 0, so you can observe when testing is parallel that when an SQL statement is issued, it usually takes a while before you see the p00n process build. At this point the session performance waits on the "OS Thread Startup" event. If you set this parameter to a value other than 0, when the DB instance starts, it starts several parallel processes at the same time, which reduces the latency of starting a new parallel process.

To view the usage of PS Server:

sql> SELECT * from v$px_process_sysstat where statistic like ' server% '; Statistic VALUE----------------------------------------------------------------------Servers in use 0 Serve RS Available Servers Started 1924 Servers Shutdown 1904 Servers highwater-Servers Cl eaned up 0 Server Sessions 9488  

• Server in use: There are currently 0 working.
• Servers Available: The currently available process is 20 because the Parallel_min_servers parameter is defined as 20, so there are always 20 processes that can be used.
• Server Started: The number of processes started since the database started.
• Server Shutdown: The number of processes that are closed.

3.parallel_execution_message_size

Inter-process communication that is required in parallel operations (including between Slave process and Query coordinator, and between Slave processes in Producer/consumer mode) is implemented through the Table Queue, so-called Tab Le Queue is actually shared memory, which can be in the Large pool of the SGA, or in the shared pool of the SGA. Oracle recommends that it be placed in the Larege pool to avoid creating a Shared pool fragment.

Use the following statement to determine which pool the Table Queue is assigned to, and you can query by the following statement:

sql> SELECT * from V$sgastat 2 where name = ' PX msg pool '; POOL NAME BYTES------------------------------------------------shared pool PX msg pool 774720  

The result of this query shows that the Table Queue is allocated in the Shared Pool, which is a situation that should be avoided. If you are allocating in the Large Pool, the query results are as follows:

For Oracle 10g, there are two ways to place the Table Queue in the Large Pool:

• Using the SGA automatic adjustment, that is, setting the Sga_target parameter to a value other than 0;
• If automatic management of the SGA is not used, it can be implemented by setting the parameter parallel_automatic_tuning to TRUE, which defaults to FALSE.

Each Table Queue consists of 3 (4) Buffer in the RAC environment, which defines the size of each buffer, in Bytes, and the default value is 2152Bytes. The default value in most cases is small, you can adjust it to 16KB, 32KB, 64KB and so on.

Before adjusting this parameter, you need to consider the memory consumption situation, you can use the following formula to estimate:

Where parallel_max_serversxparalle_max_servers represents the maximum number of table queues, because in Producer/consumer mode, Slave Process is paired.

4.parallel_automatic_tunning

The parallel_automatic_tunning parameter if set to True,oracle will do two things: first, adjust some PARALLEL parameter values, and secondly, put the Table qieue in the Large Pool. Although in Oracle 10g, this is already an obsolete parameter. However, the latter function of this parameter can still be used to force Oracle to place the Table Queue in the Large Pool.

5. PARALLEL _adaptive_multi_user

Parallel processing is a performance accelerator, but if used improperly, it can be a performance killer. This is also well understood, and if all operations use parallel processing, the system resources will be exhausted. For example, the degree of parallelism of Table A is 4, then all this access to the table will use 4 Slave Process, if Parallel_max_servers is 40, as long as 10 users can run out of the entire PX Pool. Not only that, a large number of parallel processes will take up CPU, disk resources, so that other users do not have the opportunity to execute the operation at all. Therefore, parallel processing needs to be controlled. The Oracle 10g has two parameters that can be adjusted in parallel, and this parameter is one of them.

Oracle Parallel operations