Application of Oracle Parallel Execution features

During the historical data transfer-out test, through continuous optimization, including SQL adjustment and database adjustment, we can see from AWR that it is basically difficult to improve performance, so we are ready to try it.

During the historical data transfer-out test, through continuous optimization, including SQL adjustment and database adjustment, we can see from AWR that it is basically difficult to improve performance, so we are ready to try it.

1. Order

During the historical data transfer-out test, through continuous optimization, including SQL adjustment and database adjustment, we can see from AWR that it is basically difficult to improve performance, therefore, we are prepared to try the features of parallel execution. We can analyze the features of this task and use this technology more easily. This article describes common SQL statements used in parallel execution, some problems encountered, and the results of performance comparison tests. It also shares some experience in solving these problems, analyzed the scenarios suitable for parallel execution.

Considerations for setting parallelism in Oracle

2. Concepts and common syntax

Parallel Execution improves the performance of a specific task by making full use of hardware resources. It distributes an SQL statement to multiple CPUs for execution at the same time, thus reducing the total time consumption.

Parallel Oracle execution includes:

1) parallel query

2) Parallel DML (insert, delete, update)

3) Parallel DDL (table and index creation ).

For reference, the following describes the commonly used statements collected at three levels of parallel execution:

L object-level

Set the degree of parallelism between tables and indexes, so that SQL operations involving these objects are executed according to the set degree of parallelism.

Example: alter table outpatient expense record parallel 8;

Alter index outpatient expense record _ IX _ registration time parallel;

If the parallelism value is not specified, Oracle estimates a default value based on the parameter and number of CPUs.

Disable the degree of parallelism (specify the degree of parallelism to 1 or use noparallel ):

Alter table outpatient expense record parallel 1;

Alter index outpatient expense record _ IX _ registration time noparallel;

If the object's parallelism attribute is not changed, the only way to disable parallel query is to set the initialization parameter parallel_max_servers to 0.

L session level

The manual enable and disable syntax is as follows:

ALTER Session enable parallel query [| DML | DDL];

ALTER Session disable parallel query [| DML | DDL];

It is executed in a 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;

Forced parallelism does not work for Recursive SQL statements, but overwrites the degree of parallelism defined on tables or indexes.

Check whether parallel execution is enabled for the current session:

(Parallel QUERY and DDL are enabled by default on Oracle 10.2.0.1, but 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

Add a prompt in SQL to specify the parallel execution and degree of parallelism.

The optimizer only considers whether to use parallel execution based on the specified prompts and does not forcibly Use It (it selects the execution plan with the lowest cost)

For example:

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

Create table doctor's guide execution time parallel 8

Select/* + parallel (t1, 8) */* from doctor's advice execution time t1;

Alter index outpatient expense record _ IX _ registration time rebuild parallel 8;

Note:

Parallel DML must be enabled at the session level and concurrent query must be enabled at the same time;

Example:

SQL> ALTER Session ENABLE PARALLEL DML;

SQL> ALTER Session ENABLE PARALLEL QUERY;

SQL> Update/* + parallel (t1, 8) */outpatient expense record t1

Set to be transferred out = 132

Where checkout id In

(Select/* + parallel (t2, 8) */Checkout id From patient pre-submission record t2 Where to be transferred = 132 );

If no transaction is committed, subsequent SQL OF THE SESSION cannot access the modified table, querying the table for uncommitted transactions will return an error: "ora-12838: A table that has been concurrently modified cannot be read or modified"

When defining a primary key constraint, you cannot automatically create a primary key index in parallel, but you can use the following work und:

Create unique index test specimen record _ UQ _ specimen No.

ON test specimen record (Nuclear collection time, instrument ID, specimen serial number, specimen type) PARALLEL 8;

Alter table test specimen record ADD CONSTRAINT

Specimen record _ UQ _ specimen serial number Unique (Nuclear collection time, instrument ID, specimen serial number, specimen type );

The primary key constraint created in this way is different from that created automatically. When deleting a primary key, the corresponding index is not automatically deleted. You need to add the syntax for deleting the index. For example:

Alter table test specimen record drop

CONSTRAINT test specimen record _ UQ _ specimen No. cascade drop index;

In addition, you do not need to adjust the parameter configurations of parallel queries in most cases. There are a lot of online materials and I will not list them here. Only one parameter is described:

A ora-00600 error may occur when you execute parallel re-indexing, which can be solved by modifying the parallel_execution_message_size parameter, for example:

SQL> alter system set parallel_execution_message_size = 8192 scope = spfile;

The default value is 2148. This value is too small for general parallel tasks.

After modification, restart the database.

For more details, please continue to read the highlights on the next page: