Shulou(Shulou.com)06/01 Report--

I. A brief description of the parallel mechanism

In fact, the mechanism of parallel processing is to divide a data set to be scanned into many small data sets. Oracle will start several parallel service processes to process these small data sets at the same time according to the value of the initialization parameter PARALLEL_MIN_SERVERS=n, and finally summarize these results and return them to the user as the final processing result.

Second, parallel usage scenarios

1. Parallel query (parallel query)

The following conditions are required to execute a parallel query:

There are Hint hints in An and SQL statements, such as Parallel or Parallel_index.

The objects referenced in the B and SQL statements are set with parallel properties.

C. In a multi-table association, at least one table performs a full table scan (Fulltable scan) or cross-partition Index range SCAN.

2. Parallel DDL (parallel DDL operations, such as table creation, indexing, etc.)

Such as: createtable xx parallel 4 as select * from xxx

Create index xxx on tab_xx (column) parallel 4

3. Parallel DML (parallel DML operations, such as insert, update, delete, etc.)

For example, insert/*+parallel (T2) * / into t select / * + parallel (T1) * / * from T1

Let's give an example of a parallel easy-to-step pit from each of the above three scenarios.

III. The impact of parallelism on the implementation of the plan

One day, the developer suddenly came to me: Hello, DBA? The SQL execution plan of a test environment is different from that of the production environment, which seriously affects the test progress. I remember it was something like this at that time, when the other party threw me a SQL with a problem with the implementation plan, and then did not speak. As a rookie, I hastened to compare the production execution plan with the test environment, and found that it was really different, and it took a long time to find that one of the tables in the SQL had a parallelism of 8, resulting in an abnormal execution plan. Remember that the table is TB-level size, is a multi-table management query, parallelism of 8 after the full table scan (Full table scan), you can imagine how slow it is. Because it's a test environment, it doesn't matter who does what and then doesn't turn off parallelism. Take a look at the oracle online documentation: http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/ex_plan.htm#PFGRF94687 's interpretation of the execution plan for parallel processing.

SQL > createtable emp2 as select * from scott.emp; SQL > altertable emp2 parallel 2;-- you can view the dba_tables table degree column SQL > explainplan for select sum (sal) from emp2 group by deptno; SQL > select * fromtable (dbms_xplan.display ()) PLAN_TABLE_OUTPUT- -Plan hash value: 3939201228- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | TQ | IN-OUT | PQ Distrib |-| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0) | 00:00:01 | | 1 | PX COORDINATOR | | 2 | PX SEND QC (RANDOM) |: TQ10001 | 1 | 26 | 2 (0) | 00:00:01 | Q1Power01 | P-> S | QC (RAND) | 3 | HASH GROUP BY | 1 | 26 | 2 (0) | 00:00:01 | Q1Magol 01 | PCWP | | 4 | PX RECEIVE | | | 1 | 26 | 2 (0) | 00:00:01 | Q1HASH GROUP BY 01 | PCWP | 5 | PX SEND HASH |: TQ10000 | 1 | 26 | 2 (0) | 00:00:01 | Q1Magol 00 | P-> P | HASH | 6 | HASH GROUP BY | | 1 | | 26 | 2 (0) | 00:00:01 | Q1Magi 00 | PCWP | | 7 | PX BLOCK ITERATOR | | 1 | 26 | 2 (0) | 00:00:01 | Q1Magin00 | PCWC | | 8 | TABLE ACCESS FULL | EMP2 | 1 | 26 | 2 (0) | 00 | : 00:01 | Q1Page00 | PCWP | |- -Note--dynamic sampling used for this statement (level=2) 19 rows selected.

When parallel execution is used, there is an extra column in SQL's execution plan: in-out. This column helps us understand how the data flow is executed. Some of its values mean the following:

Parallel to Serial (P-> S): indicates that a parallel operation sends data to a serial operation, usually the parallel incheng sends the data to the parallel scheduling process.

Parallel to Parallel (P-> P): indicates that one parallel operation sends data to another parallel operation, and the field is the data exchange between two subordinate processes.

Parallel Combined with parent (PCWP): parallel operations performed by the same slave process, while parent operations are also parallel.

Parallel Combined with Child (PCWC): parallel operations performed by the same slave process, and child operations are also parallel.

Serial to Parallel (S-> P): a serial operation sends data to a parallel operation, which occurs if the select part is a serial operation.

If you know these implications, it is easy to parse the execution steps of the execution plan. It is emphasized here that after dealing with certain operations, parallelism is turned on for objects such as tables or indexes, and be sure to close it, otherwise the consequences will be serious.

IV. The influence of parallel building unique index of primary key

On another day, when the developer wanted to build a unique index of the primary key on a large table in the test environment (not planned in the previous stage), the statement was executed for a long time, because others still need to call the table, but the locking table for a long time caused it to be unavailable, so I asked DBA for help. After I read the statement, I suggested that the index should be built in a parallel and nologing manner, and then let the developer execute it himself, but soon. The developer looked for it again and said it was still slow, and after checking that there was no blocking, he decided to take a good look to see if it was really slow. After a lot of twists and turns, I finally know the reason for the slowness, and parallelism is not used when building primary key constraints. The solution is divided into two steps, first establishing a uniqueness constraint, and then adding a primary key constraint. As follows:

1. Reconstruct the unique index in parallel:

Create unique index schema.xxx onschema.table_name (column1,column2) parallel 16

2. Cancel parallelism: alter indexschema.xxx noparallel;-after the index is built, remember to cancel parallelism

3. Create primary key constraint: alter tableschame.xxx add constraint xxx primary key (column1,column2);-- parallelism of primary key creation has no effect.

The relevant tests will not be demonstrated here, the test method is very simple, in the indexing process to query the degree column of the dba_tables table on the line. What needs to be emphasized here is that the design planning of the table must be done well in the early stage.

5. Parallel DML cannot take effect

SQL > explain plan for insert/*+parallel (a select 4) * / into emp2 a select * from emp; Explained. SQL > select * fromtable (dbms_xplan.display ()) PLAN_TABLE_OUTPUT-Plan hash value: 3956160932- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- -- | 0 | INSERT STATEMENT | | 1 | 87 | 2 (0) | 00:00:01 | | 1 | LOAD TABLE CONVENTIONAL | EMP2 | 2 | TABLE ACCESS FULL | EMP | 1 | 87 | 2 (0) | 00:00:01 |-Note--dynamic sampling used for this statement (level=2) 13 rows selected.

You can see that the DML statement does not use parallelism when prompted by HINT, so how can it be made to use parallelism? Quite simply, you only need to execute alter session enable parallel dml;. Here you can also imagine that it is different from the previous parallel queries and parallel DDL.

SQL > alter session enable parallel dml; Session altered. SQL > explain plan for insert/*+parallel (a select 4) * / into emp2 a select * from emp; Explained. SQL > select * fromtable (dbms_xplan.display ()) PLAN_TABLE_OUTPUT- -Plan hash value: 883381916- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | TQ | IN- OUT | PQ Distrib |-| 0 | INSERT STATEMENT | | 1 | 87 | 2 (0) | 00:00:01 | | 1 | PX COORDINATOR | | 2 | PX SEND QC (RANDOM) |: TQ10001 | 1 | 87 | 2 (0) | 00:00:01 | Q1 01 | P-> S | QC (RAND) | 3 | LOAD AS SELECT | EMP2 | Q1Magin01 | PCWP | | 4 | PX RECEIVE | 1 | 87 | 2 (0) | 00:00:01 | Q1Power01 | PCWP | | 5 | PX SEND ROUND-ROBIN |: TQ10000 | 1 | 87 | 2 (0) | 00:00:01 | | S- > P | RND-ROBIN | | 6 | TABLE ACCESS FULL | EMP | 1 | 87 | 2 (0) | 00:00:01 | | | |-Note- | -dynamic sampling used for this statement (level=2) 17 rows selected.

It works well after execution, and it is clear from the execution plan that the DML statement takes advantage of parallelism. The hint method used here prompts statements to use parallelism. What if the table itself sets the degree of parallelism? The same is true for parallelism, which requires the execution of alter session enable parallel dml; DML statements.

It is also important to note that for tables that are not committed after parallelism is enabled, subsequent transactions are bound to fail with the following error: ORA-12838: cannot read/modify an object after modifying it inparallel.

SQL > alter table emp2 parallel 2; Table altered. SQL > alter session enable parallel dml; Session altered. SQL > insert into emp2 a select * fromemp; 0 rows created. SQL > insert into emp2 a select * fromemp;insert into emp2 a select * fromemp * ERROR at line 1:ORA-12838: cannot read/modify an objectafter modifying it in parallel

This problem must be noted when writing stored procedures, transactions are committed in time, but here there is another performance problem, so try not to open the parallelism of these objects in the table.

Conclusion: parallelism can indeed improve performance and efficiency, but everything has two sides, abuse of parallelism will lead to program controversy, excessive consumption of resources, parallelism will produce sorting, so understand the nature of clearing parallelism, use parallelism in your spare time, and plan reasonably.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.