On application of Oracle Parallel execution feature

Last Update:2014-05-10 Source: Internet

Author: User

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

1. Order

In the historical data transfer test process, through continuous optimization, including SQL tuning and database tuning, from the awr see, basically difficult to do more performance improvement, so prepare to try the characteristics of parallel execution, from the characteristics of this task to analyze, but also more suitable for the use of this technology. This article describes the common SQL used in parallel execution, as well as some of the problems encountered, as well as the results of performance comparison experiments, and shares some of the problem solving experiences, and analyzes the scenarios suitable for parallel execution.

2. Concepts and common grammar

Parallel execution shortens overall time-consuming by leveraging hardware resources to achieve performance gains for specific tasks, distributing one SQL statement to multiple CPUs at the same time.

Oracle's parallel execution includes:

1) parallel query

2) parallel DML(insert,delete,update)

3) parallel DDL(creation of tables and indexes).

For ease of reference use, the following common statements collected are described in three levels of parallel execution:

L Object-level

Sets the degree of parallelism for tables and indexes, so that SQL operations involving these objects are performed at a set degree of parallelism.

For example: ALTER TABLE outpatient expense record parallel 8;

Alter index outpatient expense record _IX_ registration time parallel;

If you do not specify a value for the degree of parallelism, Oracle estimates a default value based on the number of parameters and CPUs.

Disable the degree of parallelism (Specify a degree of parallelism of 1 or use Noparallel):

ALTER TABLE outpatient expense record parallel 1;

Alter index outpatient expense record _IX_ registration time Noparallel;

If you do not change the parallelism attribute of an object, the only way to disable parallel queries is to set the initialization parameter parallel_max_servers to 0.

L Session level

The syntax for manually enabling and disabling is as follows:

ALTER Session ENABLE PARALLEL query[| dml| DDL];

ALTER Session DISABLE PARALLEL query[| dml| DDL];

Forces execution at the specified degree of parallelism.

ALTER Session Force PARALLEL QUERY PARALLEL 8;

ALTER Session Force PARALLEL DML PARALLEL 8;

ALTER Session Force PARALLEL DDL PARALLEL 8;

Forcing parallelism does not work for recursive SQL, but overrides the degree of parallelism defined on the table or index.

Query whether parallel execution is enabled for the current session:

(on Oracle 10.2.0.1, parallel query and DDL are enabled by default and DML is not enabled)

Sql> SELECT pq_status, Pdml_status, Pddl_status

From V$session WHERE sid=sys_context (' userenv ', ' Sid ');

Pq_status Pdml_status Pddl_status

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

Enabled DISABLED enabled

L Statement-level

Specify parallel execution and degree of parallelism by adding hints in SQL.

The optimizer simply considers whether to use parallel execution as specified hints, and does not force it (it chooses the lowest cost execution plan)

For example:

Select/*+ parallel(t1,8) */count (*) from outpatient expense records T1;

Create Table doctor's order execution time parallel 8 as

Select/*+ parallel(t1,8) */* from the time of medical advice execution T1;

Alter index outpatient expense record _IX_ registration time rebuild parallel 8;

Note :

Parallel DML needs to be enabled at the session level, and parallel queries need to be enabled at the same time;

Cases:

Sql>alter Session ENABLE PARALLEL DML;

sql> ALTER Session ENABLE PARALLEL QUERY;

sql> Update/*+ parallel(t1,8) * * Outpatient fee record T1

Set pending turn out = 132

Where Checkout ID in

(Select/*+ parallel(t2,8) */checkout ID from patient pre-pay record T2 Where to turn out = 132);

If a transaction is not committed, subsequent SQL for that session cannot access the modified table, and the query for the table with uncommitted transactions will return an error: "ora-12838: Cannot read, modify a table that has been modified in parallel"

When you define a PRIMARY KEY constraint, you cannot automatically create a primary key index in parallel, but you can take the following workarounds:

CREATE UNIQUE INDEX test specimen record _uq_ specimen number

On test specimen record (nuclear time, instrument ID, specimen serial number, specimen type) PARALLEL 8;

ALTER TABLE test specimen record ADD CONSTRAINT

Test specimen record _uq_ specimen serial number Unique (nuclear collection time, Instrument ID, specimen serial number, specimen category);

The primary KEY constraint created in this way differs from the auto-creation, that is, when the primary key is deleted, the corresponding index is not automatically deleted and the syntax for dropping the index needs to be increased, for example:

ALTER TABLE test specimen record drop

CONSTRAINT test specimen Records _uq_ specimen number cascade DROP index;

In addition , about the parameter configuration of the parallel query, most of the circumstances, no need to adjust, online information is more, here is no longer listed. Only one parameter is stated:

When performing a parallel rebuild index, you may encounter a ora-00600 error, which can be resolved by modifying the parameter parallel_execution_message_size, for example:

Sql> alter system set parallel_execution_message_size=8192 Scope=spfile;

The default value is 2148, which is too small for common parallel tasks.

The database needs to be restarted after modification.

3. Use effect

L parallel Query and parallel DML

Because of the historical data used to turn out, most of the queries are indexed range scan, there is no full table scan, so it is not suitable for parallel execution.

However, due to an unexpected operation: Index compression reconstruction (parallel DDL), after execution causes the indexed properties automatically added parallelism, resulting in the related SQL query automatically enabled parallel query, the results, the execution plan adopted large table full table scan, with hash connection or nested connection, resulting in unusually slow query.

Some of the complex SQL executions took more than one hours, and even the following simple SQL execution took more than 5 hours and still no results were returned:

Update/*+ rule*/Patient's advice on pricing

Set pending turn out = N_ Batch

Where Doctor's ID in (Select ID from patient's medical advice record Where to turn out = n_ batch);

View the execution plan and discover how the full table Scan + nested JOIN index is used.

Also, the hint Word rule fails and the optimizer mode becomes the CBO. Finally, the degree of parallelism of the index is canceled, and then the index is rebuilt, the compression feature is disabled, and the final execution plan returns to normal.

Because normal index range scans do not use parallel queries (except for partitioned indexes), the optimizer chooses a full table scan, but many times this is not the way we want to access it.

Therefore, parallel queries and parallel DML must be cautious, otherwise, the performance difference is very large.

L Direct Path insertion

in order to quickly load large amounts of data, using direct path insertion can significantly improve insert performance.

Direct path insertion automatically takes parallel execution of the insert and select operations, and the target table uses the Nologging minimum log mode, and the test shows that the maximum time to reduce is 5 times times.

Cases:

Insert into/*+ append*/h hospitalization fee record (ID, record nature, NO, ...)

Select ID, Record nature, NO, ... From hospitalization expense record Where to turn out = 132;

L Parallel DDL

In the historical data transfer process, the indexes used in the query on the outbound table need to be rebuilt in order to reclaim space and speed up the query, but the process of rebuilding the index is time consuming. The test environment is configured with 32G memory, a 32-way CPU (4*8), and a RAID10 SCSI hard disk.

The test results show that :

Parallel DDL should be faster than 50% .

When there is no parallelism, it takes 36 minutes to rebuild the index and only 16 minutes to execute in parallel.

The higher the degree of parallelism, the faster it is.

Because disk IO is limited, the degree of parallelism in this environment is 8 o'clock the fastest

L Collect statistics in parallel

When collecting object statistics, there is a parameter to specify the degree of parallelism, the effect of parallelism is very obvious. Experiments show that the collection of all Zlhis objects, parallel execution can shorten the entire time from 1.5 hours to about 30 minutes.

4. Summary

Parallel execution is one of Oracle's OLAP application features, if you have some time-consuming tasks, and the server has a lot of idle resources (CPU, memory, IO bandwidth), it is more appropriate to adopt parallel execution technology, it can give you a multiplier performance improvement effect, otherwise, Parallel execution can affect the normal use of other people due to a large amount of resource consumption. So, in parallel, the general situation, less in OLTP applications, because usually we do not want a person to occupy too much system resources.

5. References

1) Oracle Performance Diagnostics Art. Christian antognini.2009

2) Oracle Performance Optimization Survival Guide. Guy Harrison. 2012

6. attached: Index reconstruction parallel contrast test

--No parallel

22:26:19 sql> exec Zl1_datamove_reb (100, 1, 6);