oracle 并行调整

                                                       并行执行有关的参数
1、PARALLEL_AUTOMATIC_TUNING-自动并行执行调整，缺省值为FALSE
当该参数置为TRUE时，ORACLE 服务器自动调整控制并行执行，它将自动调节影响以下参数：PARALLEL_ADAPTIVE_MULTI_USER，-PROCESSES，SESSIONS，PARALLEL_MAX_SERVERS
，LARGE_POOL_SIZE，PARALLEL_EXECUTION_MESSAGE_SIZE。

2、PARALLEL_THREADS_PER_CPU

3、PARALLEL_MAX_SERVERS

4、SHARED_POOL_SIZE

5、TRANSACTIONS

6、PARALLEL_ADAPTIVE_MULTI_USER

7、PROCESSES

8、SESSIONS

9、TRANSACTIONS

10、LARGE_POOL_SIZE


                                                                            并行执行调整
1、 小型数据系统
系统硬件配置：
CPUS = 4
Main Memory = 750MB
Disk = 40GB
Users = 16

DBA 做如下设置:
PARALLEL_AUTOMATIC_TUNING = TRUE
SHARED_POOL_SIZE = 12MB
TRANSACTIONS =系统缺省值

Oracle自动调整以下参数的值
PARALLEL_MAX_SERVERS = 64
PARALLEL_ADAPTIVE_MULTI_USER = TRUE
PARALLEL_THREADS_PER_CPU = 2
PROCESSES = 76
SESSIONS = 88
TRANSACTIONS = 96
LARGE_POOL_SIZE = 29MB

Parameter Settings for DOP and the Adaptive Multi-User Feature
The DBA parallelizes every table having more than 10,000 rows using a command
similar to the following:
ALTER TABLE employee PARALLEL;
In this example, because PARALLEL_THREADS_PER_CPU is 2 and the number of
CPUs is 4, the DOP is 8. Because PARALLEL_ADAPTIVE_MULTI_USER is set to
TRUE, Oracle may reduce this DOP in response to the system load that exists at the
time of the query's initiation.

2、 中型数据系统
系统硬件配置：
CPUS = 8
Main Memory = 2GB
Disk = 80GB
Users = 40
The DBA makes the following settings:
n PARALLEL_AUTOMATIC_TUNING = TRUE
n PARALLEL_ADAPTIVE_MULTI_USER = FALSE
n PARALLEL_THREADS_PER_CPU = 4
n SHARED_POOL_SIZE = 20MB
The DBA also sets other parameters unrelated to parallelism. As a result, Oracle
responds by automatically adjusting the following parameter settings:
n PROCESSES = 307
n SESSIONS = 342
n TRANSACTIONS = 376
n PARALLEL_MAX_SERVERS = 256
n LARGE_POOL_SIZE = 78MB
Parameter Settings for DOP and the Adaptive Multi-User Feature
The DBA parallelizes some tables in the data warehouse while creating other views
for special users:
ALTER TABLE sales PARALLEL;
CREATE VIEW invoice_parallel AS SELECT /*+ PARALLEL(P) */ * FROM invoices P;
The DBA allows the system to use the PARALLEL_THREADS_PER_CPU setting of
4 with 8 CPUs. The DOP for the tables is 32. This means a simple query uses 32
processes while more complex queries use 64.

3、 大型数据中心
系统硬件配置：
24 Nodes, 1 CPU per node
Uses MPP Architecture (Massively Parallel Processing)
Main Memory = 750MB per node
Disk = 200GB
Users = 256
The DBA uses manual parallel tuning by setting the following:
n PARALLEL_AUTOMATIC_TUNING = FALSE
n PARALLEL_THREADS_PER_CPU = 1
n PARALLEL_MAX_SERVERS = 10
n SHARED_POOL_SIZE = 75MB
n PARALLEL_SERVER_INSTANCES = 24
n PARALLEL_SERVER = TRUE
n PROCESSES = 40
n SESSIONS = 50
n TRANSACTIONS = 60
The DBA also sets other parameters unrelated to parallel execution. Because
PARALLEL_AUTOMATIC_TUNING is set to FALSE, Oracle allocates parallel
execution buffers from the SHARED_POOL.
Parameter Settings for DOP and the Adaptive Multi-User Feature
The DBA parallelizes tables in the data warehouse by explicitly setting the DOP
using syntax similar to the following:
ALTER TABLE department1 PARALLEL 10;
ALTER TABLE department2 PARALLEL 5;
CREATE VIEW current_sales AS SELECT /*+ PARALLEL(P, 20) */ * FROM sales P;
In this example, Oracle does not make calculations for parallel execution because
the DBA has manually set all parallel execution parameters.

4、 超大规模数据中心
系统硬件配置：
CPUS = 64
Main Memory 32GB
Disk = 3TB
Users = 1,000
The DBA makes the following settings:
n PARALLEL_AUTOMATIC_TUNING = TRUE
n PARALLEL_MAX_SERVERS = 600
n PARALLEL_MIN_SERVER = 600
n LARGE_POOL_SIZE = 1,300MB
n SHARED_POOL_SIZE = 500MB
n PROCESSES = 800
n SESSIONS = 900
n TRANSACTIONS = 1,024
Parameter Settings for DOP and the Adaptive Multi-User Feature
The DBA has carefully evaluated which users and tables require parallelism and
has set the values according to their requirements. The DBA has taken all steps
mentioned in the earlier examples, but in addition, the DBA also uses the following
command during peak user hours to enable the adaptive DOP algorithms:
ALTER SYSTEM SET PARALLEL_ADAPTIVE_MULTI_USER = TRUE;
During off hours when batch processing is about to begin, the DBA disables
adaptive processing by issuing the command:
ALTER SYSTEM SET PARALLEL_ADAPTIVE_MULTI_USER = FALSE;