Oracle Parallel mode

First, Parallel

1. Use

Forcibly enables parallelism to execute the current SQL. This version can be used after Oracle 9i, and the previous version does not have an environment to test now. That said, with this description, you can forcibly enable the multithreading capabilities of Oracle. For example, like a computer with multi-core CPUs, but most of the time will not be fully multi-core simultaneous (more than 2 cores more obvious), using parallel instructions, will be multi-core work simultaneously to improve efficiency.

But the ability to start the function itself is also to consume resources and performance. All, generally will be used when the number of return records is greater than 1 million, the effect will be more obvious.

2. Grammar

/*+parallel (table_short_name,cash_number) */

This can be added to the back of INSERT, delete, update, select (similar to the use of rule, with the opportunity to share the use of rule)

The statements that turn on the parallel feature are:

Alter session enable parallel DML;

This statement is a DML statement, OH, if it is used in a program, it is opened with the Execute method.

3. Example description

Use the transaction in ERP to explain it. This table records all the transaction, and the amount of data per day is relatively large (depending on the volume of business itself). Let's say we're going to look at the sales and marketing of each month in comparison to last year, so it's generally written as:

Select To_char (transaction_date, ' yyyymm ') Txn_month,

Sum

Decode

Sign (transaction_quantity), 1,transaction_quantity,0
)

) In_qty,

Sum

Decode

Sign (transaction_quantity), -1,transaction_quantity,0
)

) Out_qty

From Mtl_material_transactions MMT

where Transaction_date >= add_months (

To_date (

To_char (sysdate, ' yyyy ') | | ' 0101 ', ' YYYYMMDD '),

-12)

and Transaction_date <= add_months (

To_date (

To_char (sysdate, ' yyyy ') | | ' 1231 ', ' YYYYMMDD '),

-12)

Group BY To_char (Transaction_date, ' yyyymm ')

This SQL executes, if the transaction_date above has the index, the efficiency is also counted, but if does not add index, the estimate will not be executed in half an hour. This is where you can add the parallel instructions after the Select. For example:
Select/*+parallel (mmt,10) */
To_char (transaction_date, ' yyyymm ') Txn_month,

...

This will greatly improve the efficiency of implementation. If you want to insert the retrieved results into another table Tmp_count_tab, you can also write:
Insert/*+parallel (t,10) */
Into Tmp_count_tab

(

Txn_month,

In_qty,

Out_qty

)

Select/*+parallel (mmt,10) */
To_char (transaction_date, ' yyyymm ') Txn_month,

...

The insertion mechanism is similar to the retrieval mechanism, so adding parallel after insert will also accelerate. On the insert mechanism, this is not a point.
The larger the number behind the parallel, the higher the execution efficiency. However, it seems that the configuration of the server and the configuration of Oracle, the increase to a certain value, the effect is not obvious. Therefore, the general use of 8,10,12,16 is more common. I tried using 30 and found the same effect as 16. However, the larger the number, the more resources will be consumed. If it is written in some package, function or procedure, do not write so large, so that the use of too much resources by the DBA to open K.

4. Parallel can also be used for multi-table

Multi-table words, is in the first after the addition of the other can be. The specific wording is as follows:

/*+parallel (t,10) (b,10) */

5. Summary

As for the efficiency of execution, it is advisable to increase the effect by the index method. Oracle has its own approach to Explan Road, and before executing it, take a look at the execution plan route and execute it after the written SQL tuned. There is no way, and then use the parallel method. Parallel is more evil, for developers, not good things, will develop bad habits, resulting in many poor SQL will not burst, SQL tuning ability is not improved. I've seen some people create a table, never create index or primary key, and think that adding parallel when writing SQL is possible.

Oracle Parallel mode