Oracle Parallel Introduction

Parallel DML operations should be noted for 2 points:

1), display in session execution: Alter sessions enable parallel DML;

2, for Delete, Update, merge operation, only the object is the partition table, Oracle will enable parallel operation;

3), for inserts only INSERT INTO ... select * from this form is useful;

Examples of parallel operations are as follows:

--Parallel Delete

Delete/*+ Parallel (t,2) * * from t;

--Parallel update

Update/*+ Paralle (T, 2) */t Set name=name| | ' ';

--Parallel merge

Merge/*+ Parallel (t1,2) */into T1 ...;

--Insert Parallel

Insert/*+ Parallel (t,4)/into T-select/*+ parallel (T1 4) * * from T1;

In fact, parallelism can only achieve better performance if the system has sufficient resources; If the system is heavily burdened and improperly set in parallel, performance will be low because there will be a lot of waiting events;

How do you set the degree of parallelism for SQL that has both DML and query? such as insert ..... Select ...

1 if the amount of writing is large, adding parallelism on the insert will significantly improve performance; otherwise, adding parallelism on the insert has little meaning. Such as:

Insert into T (game_name,num) Select Game_name,count (1) CNT from Popt_total_login_all_his Group by Game_name;

This SQL main bottleneck in the query, the number of writes is very small, so only need to set up in the query part of parallelism.

2 If the query volume is large, then adding parallel on the query will obviously improve performance;

3 If the amount of write and query is large, then in two parts should be added in parallel, do not let one of the bottlenecks.

Summed up is: The bottleneck in the section, in this part of the addition of parallel, if there are bottlenecks, then add in parallel.

For DML, it is recommended that:

1 because of the limitations and drawbacks of parallel DML, it is not possible to enable parallel DML in the case of a small amount of writing.

2 Insert and query parallelism is not necessarily consistent, according to the actual adjustment, general settings query parallelism is greater than or equal to the insert parallelism degree. The degree of parallelism is best set to the N-th side of 2.

3 the degree of parallelism do not set more than the number of CPUs

What's the downside of parallelism?

1) Parallel DML will waste space, the higher the degree of parallelism, the more waste

2 The table affected by the parallel DML needs to be submitted or rolled back to be used by subsequent SQL, or it will report an error, which may affect the consistency of the transaction.

3) Parallel easily trigger the exception or bug, reduce the stability of the system and the program

Examples of applications are as follows:

Execute Immedaite ' alter session enable Paralle DML ';--open parallel switch

Execute Immedaite ' alter session enable Paralle query ';

Insert/*+ Append */into a nologging

()

Select/*+ Parallel (b 4) * *

()

from B;

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/