Oracle并行处理

PARALLEL_MAX_SEVERS参数设置并行执行可用的最大进程数量

SQL> show parameter parallel_max_servers
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     5
 

Disabling Parallel SQL Execution

You disable parallel SQL execution with an ALTER SESSION DISABLE PARALLEL DML|DDL|QUERY statement. All subsequent DML (INSERT, UPDATE, DELETE), DDL (CREATE, ALTER), or query (SELECT) operations are executed serially after such a statement is issued. They will be executed serially regardless of any PARALLEL clause associated with the statement or parallel attribute associated with the table or indexes involved.

The following statement disables parallel DDL operations:

 

ALTER SESSION DISABLE PARALLEL DDL;

Enabling Parallel SQL Execution

You enable parallel SQL execution with an ALTER SESSION ENABLE PARALLEL DML|DDL|QUERY statement. Subsequently, when a PARALLEL clause or parallel hint is associated with a statement, those DML, DDL, or query statements will execute in parallel. By default, parallel execution is enabled for DDL and query statements.

A DML statement can be parallelized only if you specifically issue an ALTER SESSION statement to enable parallel DML:

 

ALTER SESSION ENABLE PARALLEL DML;

Forcing Parallel SQL Execution

You can force parallel execution of all subsequent DML, DDL, or query statements for which parallelization is possible with the ALTER SESSION FORCE PARALLEL DML|DDL|QUERY statement. Additionally you can force a specific degree of parallelism to be in effect, overriding any PARALLEL clause associated with subsequent statements. If you do not specify a degree of parallelism in this statement, the default degree of parallelism is used. However, a degree of parallelism specified in a statement through a hint will override the degree being forced.

The following statement forces parallel execution of subsequent statements and sets the overriding degree of parallelism to 5:

 

ALTER SESSION FORCE PARALLEL DDL PARALLEL 5;

 

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

PARALLEL(table [,integer] [,integer])

 

Explicitly specifies the actual number of concurrent query servers that will be used to service the query. The first optional value specifies the degree of parallelism (number of query servers) for the table. This is the number of processes assigned to perform. the scan of the specified table in parallel on a single instance. The second optional value specifies the number of Oracle parallel server instances to split the query across. If you specify PARALLEL(EMP, 4 2), there will be four parallel query processes running on two separate parallel server instances. If no parameters are specified, the default (calculated) degree of parallelism and number of parallel servers is sourced from the parameters specified in the INIT.ORA file.

 

The hint can be used for selects, updates, deletes, and inserts. To get performance improvements using the parallel hint, your datafiles must be striped across multiple disks. Don't set the degree of parallelism higher than the number of disks that the table is striped over. Having multiple processors will make the operation run even faster, but only if the table is striped.

 

SELECT /*+ PARALLEL (x 4) */ COUNT(*)FROM x;

SELECT /*+ PARALLEL (x 4 2) */ COUNT(*)FROM x;

UPDATE /*+ PARALLEL (x 4) */ x SET position = position+1;

DELETE /*+ parallel(x 4) */ from x;

INSERT INTO x SELECT /*+ PARALLEL(winners 4) */ * FROM winners;