oracle12c_parallel_process
飞总：oracle12c在启动数据库的时候启动的parallel进程比较多，研究过吗？cpu_count=48,启动数据库直接启动200+ Pxxx进程。
自己的环境如下SQL> show parameter parallel
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean FALSE
parallel_automatic_tuning boolean FALSE
parallel_degree_level integer 100
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_fault_tolerance_enabled boolean FALSE
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 40
parallel_min_percent integer 0
parallel_min_servers integer 2
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 16
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
SQL> show parameter cpu_count
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
cpu_count integer 1
SQL> !ps -ef | grep ora_p
oracle 3000 10 22:07 ? 00:00:00 ora_pmon_orcl
oracle 3004 10 22:07 ? 00:00:00 ora_psp0_orcl
oracle 3142 10 22:07 ? 00:00:03 ora_p000_orcl
oracle 3146 10 22:07 ? 00:00:04 ora_p001_orcl
oracle 3190 10 22:07 ? 00:00:00 ora_p002_orcl
oracle 3198 10 22:07 ? 00:00:00 ora_p003_orcl
oracle 4276 10 22:48 ? 00:00:00 ora_p004_orcl
oracle 4280 10 22:48 ? 00:00:00 ora_p005_orcl
oracle 432843190 22:52 pts/1 00:00:00 /bin/bash -c ps -ef | grep ora_p
oracle 433043280 22:52 pts/1 00:00:00 grep ora_p
SQL> 抽时间研究下，
在12c中，有PARALLEL_ADAPTIVE_MULTI_USER参数使得数据库根据系统的情况，自动决定并行度
PARALLEL_ADAPTIVE_MULTI_USER, when set to true, enables an adaptive algorithm designed to improve performance in multiuser environments that use parallel execution. The algorithm automatically reduces the requested degree of parallelism based on the system load at query startup time. The effective degree of parallelism is based on the default degree of parallelism, or the degree from the table or hints, divided by a reduction factor.
The algorithm assumes that the system has been tuned for optimal performance in a single-user environment.SQL> show parameter cpu;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 2
parallel_threads_per_cpu integer 2
resource_manager_cpu_allocation integer 2
SQL>
SQL> set pages 1000
SQL>show parameter parallel;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE --注意是true
parallel_automatic_tuning boolean FALSE
parallel_degree_level integer 100
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 80
parallel_min_percent integer 0
parallel_min_servers integer 8 ----注意最小8个并行
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 32
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
SQL> select startup_time from v$instance;
STARTUP_T
---------
25-APR-14
SQL> !ps -ef|grep ora_p0|grep -v grep --注意现在是14个进程，而且有8个进程启动时间是4月25日，另外几个进程的启动时间是5月2日10:53（刚刚创建）
oracle 26112 10 10:53 ? 00:00:00 ora_p008_ora12c
oracle 26114 10 10:53 ? 00:00:00 ora_p009_ora12c
oracle 26116 10 10:53 ? 00:00:00 ora_p00a_ora12c
oracle 26118 10 10:53 ? 00:00:00 ora_p00b_ora12c
oracle 26120 10 10:53 ? 00:00:00 ora_p00c_ora12c
oracle 26122 10 10:53 ? 00:00:00 ora_p00d_ora12c
oracle 32223 10 Apr25 ? 00:06:24 ora_p000_ora12c
oracle 32225 10 Apr25 ? 00:05:45 ora_p001_ora12c
oracle 32227 10 Apr25 ? 00:06:00 ora_p002_ora12c
oracle 32229 10 Apr25 ? 00:05:34 ora_p003_ora12c
oracle 32231 10 Apr25 ? 00:05:31 ora_p004_ora12c
oracle 32233 10 Apr25 ? 00:05:30 ora_p005_ora12c
oracle 32235 10 Apr25 ? 00:00:17 ora_p006_ora12c
oracle 32237 10 Apr25 ? 00:00:16 ora_p007_ora12c
SQL> alter system set parallel_adaptive_multi_user=false; --设置参数为false
System altered.
SQL> !ps -ef|grep ora_p0|grep -v grep ---进程只剩下参数配置的min数量
oracle 32223 10 Apr25 ? 00:06:24 ora_p000_ora12c
oracle 32225 10 Apr25 ? 00:05:45 ora_p001_ora12c
oracle 32227 10 Apr25 ? 00:06:00 ora_p002_ora12c
oracle 32229 10 Apr25 ? 00:05:34 ora_p003_ora12c
oracle 32231 10 Apr25 ? 00:05:31 ora_p004_ora12c
oracle 32233 10 Apr25 ? 00:05:30 ora_p005_ora12c
oracle 32235 10 Apr25 ? 00:00:17 ora_p006_ora12c
oracle 32237 10 Apr25 ? 00:00:16 ora_p007_ora12c
页:
[1]