Oracle concurrency Adjustment

Oracle concurrency uses multiple CPUs and Io to perform database operations, which can significantly improve the operation performance of large databases, such as DSS or data warehouses.

Applicable

Large Table query, join, and partition index Query

Create a large number of Indexes

Create a large number of tables (including curing views)

Batch insert, update, delete

Applicable scenarios

Symmetric multi-processor, cluster, parallel system

Sufficient bandwidth

Insufficient CPU utilization

Enough memory for other operations, sorting, hash, and Cache

Row query execution is suitable for OLTP systems with DSS and data warehouses and batch operations. It is not suitable for DML or select operations with OLTP introduction.

Parallel execution is not suitable for scenarios

Very short query or transaction

This includes the majority of OLTP, because the cost of parallel coordination is higher than the benefits of parallel

Massive Io, CPU, and memory operations

Basic hardware requirements

Parallel Execution design requires multiple CPUs and Io for fast query. Each hardware should be maintained at the same throughput.

Which operations can be performed in parallel?

Full Table query, partition query, and index quick query

Join Operation

Nested loop, sort merge, hash, and star Transformation

DDL statement

CREATE TABLE AS SELECT,CREATE INDEX,REBUILD INDEX,REBUILD INDEX PARTITION,
AndMOVE/SPLIT/COALESCE PARTITION

DML statements

INSERT AS SELECT, UPDATE, DELETE,
and MERGE operations


Parallel Execution Plan

Run

EXPLAIN PLAN FORSELECT /*+ PARALLEL(4) */ customers.cust_first_name, customers.cust_last_name,   MAX(QUANTITY_SOLD), AVG(QUANTITY_SOLD)FROM sales, customersWHERE sales.cust_id=customers.cust_idGROUP BY customers.cust_first_name, customers.cust_last_name;

View execution plan

Select plan_table_output from table (dbms_xplan.display ());

Execution concurrency

ALTER TABLES sales PARALLEL 8;ALTER TABLE customers PARALLEL 4

Default degree of Parallelism

Single Instance: parallel_threads_per_cpux
Cpu_count

Cluster:PARALLEL_THREADS_PER_CPUx
CPU_COUNT x INSTANCE_COUNT

parallel_threads_per_cpuQueryshowparameter
parallel_threads_per_cpu

Automatic Parallel Management

Setting parallel_degree_policy to auto indicates that Oracle determines parallel execution.

Automatic Parallel Process

SQL Publishing

Optimize Oracle parsing and generate execution plan

View the parallel_min_time_threshold parameter value

If the execution time is smaller than this value, serial execution is performed. Otherwise, parallel execution is performed.

Set automatic concurrency

Method1

altersession set parallel_degree_policy=limited;

altertable paralle(degree
default);

Method2

Use a statement to set the degree of Parallelism

Degree of Parallelism10

SELECT /*+ parallel(10) */ ename, dname FROM emp e, dept dWHERE e.deptno=d.deptno;

Non-degree of Parallelism

SELECT /*+ no_parallel */ ename, dname FROM emp e, dept dWHERE e.deptno=d.deptno;

Automatic concurrency

SELECT /*+ parallel(auto) */ ename, dname FROM emp e, dept dWHERE e.deptno=d.deptno;

In-memory parallel

Parallel_degree_policy is set to auto to indicate that buffer cache data is used for parallel execution,

Parallel_adaptive_multi_user is set to true by default to ensure adaptive parallel execution. Oracle determines whether to execute parallel execution based on the execution time to avoid overloading.

The number of parallel_degree_policy parameters determines whether automatic DOP, parallel statement queue, and in-meory Parallel Execution

Has the following values:

Manual disables auto DOP, runs parallel statement queues, and runs in-memory in parallel. The default value is 11.2.

Limited disables auto DOP, but enables parallel statement queue. In-memory parallelism allows you to add DOP settings to the statement to execute parallel execution.

Auto enables all three items

Parallel DML

Alter session enable parallel DML;

Only when DML parallelism is set can parallel statements be called.

Use APPEND In the insert SQL statement, for example:

Insert/* + append */into T select * from T1;

When Oracle directly loads data, the data is directly appended to the end of the data segment. It does not need to spend time in the segment to find space, and the data does not pass through the data
The buffer is directly written to the data file, which is more efficient than the traditional loading method.