Oracle parallel operations-from serial to parallel ____oracle



The parallel (Parallel) operational feature is a technical feature of Oracle to make full use of server hardware processing resources, improve the operation efficiency of certain large amount of data, and deal with mass production data. Typically, the SQL processing we do is a single process operation, corresponding to a single CPU unit of operation. The so-called parallel operation, that is, in the context of software and hardware conditions allow Oracle to invoke a number of processing units processed separately, the final convergence of the results of the process. A planned parallel operation of some SQL can significantly improve the operating speed of the system.

1. The predicament of massive data

The internet era has brought many changes to the world, one of which is the explosion of information and the exponential growth of data. The database has entered the massive, the foreign class time, the TB level database already more and more frequently appears in each kind of application scene. Developers and DBAs have to face a dilemma if they handle massive amounts of data.

In traditional times, one of the most common means of optimization is indexing. Gets the data row rowid that meets the criteria by retrieving fewer blocks of data. "Indexing into the execution plan, replacing full table scanning" became an important guide for our optimization. However, the index path is not the user's final experience. As the amount of data increases (g on a single table), even if our execution plan is perfect, the time lost in retrieving Index + block positioning may still not meet customer needs. Secondly, some specific system types (such as OLAP systems), may be to perform a full table scan operation, all data retrieval processing. In these situations, the traditional Index center optimization is useless.

In computer science, the age-old idea of "divide and conquer" has always played an important role, both in software algorithms and in hardware systems. In recent years, hardware technology, especially multi-core CPU, the gradual popularization of multi-CPU computers, so that multi-core computers, servers began to appear in the field of enterprise applications. Multi-core CPUs and multiple CPUs are essentially the basis of parallel processing units. Dividing a task into several subtasks, handing it over to different processing units at the same time, and then summarizing the result set, is a common single task multi process model. It should be said that the parallel technology is based on the hardware and software technology, where the hardware technology is a decisive factor.

2. Oracle Parallel Processing

The Oracle Parallel Processing (Parallel processing) feature is primarily parallel to SQL statement processing. Oracle's current support for parallel operations includes the following types:

• Parallel query operation;

U-parallel DDL. DDL operations on Data objects;

U parallel DML. To carry out parallel data updating and modification;

In the specific application scenario, there are the following scenarios:

U data table, index range scanned query;

• Bulk Add, modify and delete operations;

• Data table, Index object creation;

• "Dbms_stats" collection of statistics;

• Backup Recovery manager recovery manager usage;

In Oracle, a total of three processes process types are included for client process client processes, service process server processes, and background processes background process. Among them, the data block reads, modifies writes and so on the actual operation is the service process server processes. The Server process is a one-to-one relationship between client processes and the proxy process on the database instance. As a result, the general SQL statements are single process serial operation.

The processes involved in Oracle Parallel operations are basically similar to the models for general parallel operations, including the processing unit and the two class of coordination unit processes. First, the single task that is processed is divided into several subtasks. Under the action of Coordination process (coordinator), parallel operation is divided into several subtasks, which are passed to multiple concurrent service processes for processing respectively. Each process then aggregates the processing results and delivers them to the coordination process, which is returned as a uniform result.

and processing in Oracle is a configuration process that includes many parameter configurations and conceptual elements. The two most important concepts are parallelism and parallel server process pooling. The following two concepts and related configuration items are described separately.

3, parallelism and parallel server process pool

The parallelism degree Dop (Degree of Parallel) defines the number of parallel streams for parallel operations. DOP can be understood as the number of parallel processing units that are divided into parallel processing. However, in the actual case, the number of processes in parallel processing is often twice times the DOP, that is, a parallel flow is actually two processing processes in the processing work. Such configurations are usually designed to achieve coordination between processes and ease the speed mismatch on various processing links.

The degree of parallelism DOP is an important configuration parameter in Oracle parallel operation. The current Oracle version supports the manual configuration and automatic configuration of DOP in two ways. If you specify the degree of parallelism manually, you need to set the number of degrees of parallelism on the SQL statement by hint. Automatic configuration of DOP means that Oracle calculates an automatic adaptive parallelism based on the current actual load and the status of the parallel process pool. The following are some of the parameters related to parallelism in Oracle.

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_degree_limit string CPU

Parallel_degree_policy string MANUAL

Parallel_execution_message_size integer 16384

Parallel_force_local Boolean FALSE

Parallel_instance_group string

parallel_io_cap_enabled Boolean FALSE

Parallel_max_servers Integer 10

Parallel_min_percent integer 0

Parallel_min_servers integer 0

Parallel_min_time_threshold string AUTO

Parallel_server Boolean FALSE

Parallel_server_instances Integer 1

Parallel_servers_target Integer 4

PARALLEL_THREADS_PER_CPU Integer 2

Recovery_parallelism integer 0

The meaning of the above parameters will be gradually used and introduced in this series.

DOP is a configuration project, and actual parallel work requires real-world processes to complete. From the background described above, a session corresponds to only one server process, and operation processing actually requires the server process to complete. In parallel processing, Oracle introduces the concept of parallel server process pooling. The parallel server process pool is a collection of processes that allocate an appropriate number of service processes from the processing pool for SQL operations that require parallel processing.

Some of the parameters related to the parallel service process are as follows:

Sql> Show Parameter Parallel_m

NAME TYPE VALUE

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

Parallel_max_servers Integer 10

Parallel_min_percent integer 0

Parallel_min_servers integer 0

4, an example of parallel query

The following is a demonstration of the execution plan for parallel SQL for a large data scale. First and foremost, environmental preparedness:

Sql> SELECT * from V$version where rownum<2;

BANNER

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

Oracle Database 11g Enterprise Edition release 11.2.0.1.0-production

Sql> Select COUNT (*) from T;

COUNT (*)

----------

1110542

The most common way to use parallel operations is through hint (or, of course, by the object parallel property). Here is a demo of the differences without using parallelism and using parallel two execution plans.

Sql> set Linesize 10000;

Sql> set pagesize 10000;

Sql> explain plan for SELECT * from T;

has been explained.