Parallel Query Instance parsing _oracle Oracle parallel operation

The parallel operation characteristic of Oracle database is to forcibly extract the free resources of database server (mainly CPU resources), and to divide and treat some high load data. Parallel operation is a non-deterministic optimization strategy, which needs to be treated carefully when choosing. Currently, there are several main aspects of using parallel operational features:

Parallel query: Parallel queries, using multiple operating system-level server process to complete an SQL query at the same time;
Parallel DML: Parallel DML operation. Similar to parallel Query. When you want to do DML operations on large data scales, such as INSERT, update, and delete, you can consider using;
Parallel DDL: Parallel DDL operations. such as the construction of large capacity data table, index rebuild and other operations;
Parallel Recovery, parallel recovery. The parallel recovery technology can be initiated when the database instance crashes, or when the storage media is restored. So as to achieve the goal of reducing recovery time;
Procedural Parallel, Process code parallelization. The code fragment, stored procedure or function that we write can realize parallelization of execution, thus speeding up the execution efficiency;

1, parallel queries parallel query

Parallel query of Oracle database is the basic technology, and it is also a kind of parallel technology frequently used by OLAP and Oracle Data Warehouse. The same elements that have been highlighted earlier in this series determine some prerequisites for hardware and software before you determine the use of parallel technologies:

Tasks task requirements. Alternate task tasks that perform parallel operations must be a large task assignment, such as a lengthy query. Task times can usually be counted in minutes and hours. Only such tasks and needs are worth the risk of using parallel operation schemes;
Resource idle condition. Parallel processing can only be considered when the database server resources are idle. If the frequent rush, the use of parallelism can only aggravate the contention of resources.

The greatest risk of parallel operation is that the efficiency of parallel contention is not rise and fall. Therefore, in order to determine the two premises, then the parallel planning process.

2. Environmental preparation

First, prepare the experimental environment. Because I use the general home PC virtual machine, so the degree of parallelism and storage capacity can not reflect the real requirements, forgive me.

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 (*)
----------
1160704

Select 11GR2 server environment, data table t total data volume exceeds 1 million.

First, let's look at the execution without using parallelism.

Extracts the use of cursor information; sql> Select Sql_text, sql_id, version_count from V$sqlarea where Sql_text like ' select COUNT (*) from t% '; Sql_text sql_id version_count--------------------------------------------------------Select COUNT (*) FR Om T 2jkn7rpsbj64t 2 sql> SELECT * FROM table (dbms_xplan.display_cursor (' 2jkn7rpsbj64t ', format => ' advance
d ', cursor_child_no => 0)); Plan_table_output--------------------------------------------------------------------------------sql_id 2JKN7RPSBJ64T, child number 0-------------------------------------select COUNT (*) from T-plan hash value:2966233522--- ----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU) |
Time |  -------------------------------------------------------------------
| 0 |   SELECT STATEMENT |    | |     4464 (100) |  |
| 1 |   SORT AGGREGATE |   |      1 |     |  |
|  2 | TABLE ACCESS full| T | 1160k| 4464 (1) |
00:00:54 | -------------------------------------------------------------------  

The parallel feature is not used in the execution plan for a full table scan. The execution time is 54s.

3. Parallel query plan

First, we set the corresponding degree of parallelism. There are two ways to set the degree of parallelism, one of which is to use hint to add to a specific SQL statement. The other is to set the parallelism attribute on a large object.

The advantage of the former is a certain mandatory and targeted. is to specify a specific SQL statement for parallel processing. This advantage is easy to control the degree of parallelism, the disadvantage is with a strong force, when the amount of data is small, the use of parallel advantages is not. And if you show a specified degree of parallelism, it can also have the disadvantage of poor porting scalability.

The latter specifies the degree of parallelism through the properties of the object. As a means of execution, parallelism is provided to the optimizer for selection. In this way, the CBO performs the execution plan generation based on the situation of the resources in the system and the actual data. The plan may or may not be parallel. The advantage of this is that the parallel or not to the optimizer CBO to judge, the disadvantage is the risk of concurrent abuse.

Here, the author sets the way to automatically determine the degree of parallelism.

Sql> ALTER TABLE t parallel;
Table Altered
sql> Select COUNT (*) from T;
 COUNT (*)
----------
1160704

When the query is started, the parallel server process pool in Oracle determines the number of concurrent processes allocated, based on the load and actual factors in the system. At this point, we can view the connection information in the process pool through the view v$px_process.

Sql> select * from v$px_process;
server_name STATUS      PID SPID       SID  serial#
-------------------------------------------------------- ------------------
P000    AVAILABLE     5776                
P001    AVAILABLE     

Note that the parallel server process is a special kind of slave process, which is essentially a shareable one . Private connection mode, the general server process and client process is a "die and die" relationship, life-long service in a client process. While the server slave process is managed through a process pool, once initialized, it will reside in the system for a certain period of time, waiting for the next parallel processing to arrive.

At this point, we check the v$process view, or we can find the corresponding information.

Sql> select * from v$process;
PID SPID     pname USERNAME      serial# program    
---------------------------------------------------------- ---------------------------------------------
5776      P000 Oracle         oracle@oracle11g (P000)   
5778      P001 Oracle          6 oracle@oracle11g (P001)   
(space for reasons, omitted ...) )
Selected rows

corresponding to the OS, there is also a corresponding real process to wait.

[Oracle@oracle11g ~]$ Ps-ef | grep Oracle
(space for reasons, omitted ...) )
Oracle  5700   1 0 17:29?    00:00:02 Oraclewilson (local=no)
Oracle  5723   1 0 17:33?    00:00:00 Ora_smco_wilson
Oracle  5764   1 2 17:40?    00:00:05 Oraclewilson (local=no)
Oracle  5774   1 0 17:42?    00:00:00 Oraclewilson (local=no)
Oracle  5776   1 0 17:43?    00:00:00 Ora_p000_wilson
Oracle  5778   1 0 17:43?    00:00:00 Ora_p001_wilson
Oracle  5820   1 1 17:44?    00:00:00 Ora_w000_wilson

Because the query has ended at this time, the corresponding parallel session information has disappeared and is not visible.

Sql> select * from V$px_session;
Saddr      SID  serial#   qcsid qcserial# 
------------------------------------------------

However, if the task is long, it is possible to capture the corresponding information.

From the above scenario, when we perform a parallel operation, Oracle obtains the corresponding parallel process from the server process pool to operate. When the operation is complete, the server waits for a certain amount of time and then reclaims it.

The resource consumption of the parallel operation process, viewed through the V$px_sysstat view.

sql> col statistic for A30;
Sql> select * from V$px_process_sysstat;
Statistic              VALUE
----------------------------------------Servers in-use             0
Servers Available            0
Servers started             2
Servers Shutdown            2
Servers highwater            2
Servers cleaned up           0
Server Sessions             6
Memory Chunks allocated         4
Memory Chunks freed           0
Memory Chunks Current          4
Memory Chunks HWM            4
Buffers Allocated           buffers
freed
Buffers Current             0
buffers HWM               8
rows selected

Next, let's check the execution plan information.

Sql> set pagesize 10000; Sql> select * FROM table (dbms_xplan.display_cursor (' 2jkn7rpsbj64t ', Format => ' Advanced ', Cursor_child _no => 1)
); Plan_table_output---------------------------------------------------------------------------------------------  -------sql_id 2JKN7RPSBJ64T, child number 1-------------------------------------select COUNT (*) from T-plan hash value:  3126468333----------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU) |  Time | TQ | In-out|  PQ Distrib----------------------------------------------------------------------------------------------------| 0 |     SELECT STATEMENT |    | |     2478 (100) |    |   |      
|  | 1 |     SORT AGGREGATE |   |      1 |     |    |   |      
|  |  2 |     PX Coordinator |    |      |     |    |   |      
|  |  3 | PX SEND QC (RANDOM) |   : TQ10000 |      1 |     | | q1,00 | P->s |  QC (RAND) |   4 | SORT AggreGATE |   |      1 |     | | q1,00 |      
PCWP |  |   5 |     PX Block Iterator | | 1160k| 2478 (1) | 00:00:30 | q1,00 |      
PCWC |    |* 6 | TABLE ACCESS full| T | 1160k| 2478 (1) | 00:00:30 | q1,00 |      
PCWP | ----------------------------------------------------------------------------------------------------predicate Information (identified by Operation ID):---------------------------------------------------6-access (: Z>=:z and: Z<=:Z)

From the execution of the planned conditions (6-access (: Z>=:z and:z<=:z)), we can see the assignment of the task, followed by a full table scan. The last sort calculates the count, the process of merging the results.
 
4, conclusion
 
Oracle Parallel Query is a parallel operation technique that is often used. Compared to DDL, DML and other types of operations, parallel query can be used as a step to the system function.
&NBSP
The biggest risk for parallel queries is the presence of parallel abuse and runaway. This is what Oracle has been trying to solve. In ORACLE11GR2, the technical characteristics of parallel Statement Queuing (PSQ) are introduced. In general, Oracle introduces as many parallel processes as possible to operate as long as the parallel server pool allows. The advent of PSQ technology is to add parallel control from the point of view of resources.
&NBSP
When the system is busy, PSQ will take some parallel operations to the waiting state to prevent the deterioration of the parallel environment. When the environment improves, the parallel statements waiting in the queue enter the execution state. This feature can effectively prevent the emergence of parallel abuse.