Linux 11G RAC启用HugePages与AMM的禁用

HugePages是集成到Linux kernel 2.6中的一个功能。启用HugePages可以使用操作系统来支持比缺省的内存页(4KB)更大的内存页。使用非常大的内存页大小可以通过减少访问页表条目所需要的系统资源数量而提高系统性能。HugePages对于32位与64位系统都是有效的。HugePage的大小范围从2MB到256MB,依赖于内核版本和硬件架构。对于Oracle数据库,使用HugePages减少操作系统维护内存页
状态并增加Translation Lookaside Buffer(TLB)的撞击率。

1.使用HugePages来优化SGA
不使用HugePages时,操作系统将保持每个内存页大小为4KB,当为SGA分配内存页时,操作系统内核必须对分配给SGA的每个4KB页使用页生命周期(脏,可用,映射到进程,等等)持续更新。

使用HugePages时,操作系统页表(虚拟内存到物理内存的映射)很小,因为每个页表条目指向的内存页大小从2MB到256MB。同时内核有比较少的内存页生命周期被监控。例如,如果64位硬件使用HugePages,并且想要映射256MB的内存,你可能只需要一个页表条目(PTE)。如果不使用HugePages并且想要映射256MB内存,那么必须有256*1024KB/4KB=65536个PTEs。

HugePages提供了以下优点:
通过增加TLB撞击率来提高性能
内存页被锁定在内存中并且不会发生交换,对共享内存结构比如SGA提供了随机访问
连续内存页预分配除了用于系统的共享内存比如SGA不能用于其它的目的
因为使用大的内存页大小所以虚拟内存相关的内核有较少性能开销

2 对Linux配置HugePages
运行以下命令来判断内核是否支持HugePages:

[root@jyrac1 ~]# uname -r
2.6.18-164.el5

[root@jyrac1 ~]# grep Huge /proc/meminfo
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB

有一些Linux缺省情况下是不支持HugePages的。 对于这样的系统使用config_hugetlbfs和config_hugetlb_page配置选项来构建Linux内核。config_hugetlbfs位于文件系统并且当你选择config_hugetlbfs时需要同时选择config_hugetlb_page。

编辑/etc/security/limits.conf文件来设置memlock。memlock设置以KB为单位,并且当启用HugePages内存时,最大锁定内存限制应该被设置为当前可随机访问内存的90%,当没有启用HugePages内存时,最大锁定内存限制应该被设置成至少3145728KB(3GB)。例如,如果有2G可随机访问内存,并且增加以下条目来增加最大锁定内存地址空间:

[root@jyrac1 ~]# vi /etc/security/limits.conf
grid soft memlock 2097152
grid hard memlock 2097152
oracle soft memlock 2097152
oracle hard memlock 2097152

也可以将memlock的值设置为比SGA的值大

以grid用户登录,并执行ulimit -l命令来验证新设置的memlock是否生效

[grid@jyrac1 ~]$ ulimit -l
2097152

以oracle用户登录,并执行ulimit -l命令来验证新设置的memlock是否生效

[oracle@jyrac1 ~]$ ulimit -l
2097152

运行以下命令来显示Hugepagesize变量:

[oracle@jyrac1 ~]$ grep Hugepagesize /proc/meminfo
Hugepagesize:     2048 kB

完成以下过程来创建一个脚本用来为当前共享内存段计算hugepages配置的建议值创建一个hugepages_settings.sh脚本并增加以下内容:

[root@jyrac1 /]# vi hugepages_settings.sh
#!/bin/bash
#
# hugepages_settings.sh
#
# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
# on Oracle Linux
#
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.
#
# This script is provided by Doc ID 401749.1 from My Oracle Support 
# http://support.oracle.com

# Welcome text
echo "
This script is provided by Doc ID 401749.1 from My Oracle Support 
(http://support.oracle.com) where it is intended to compute values for 
the recommended HugePages/HugeTLB configuration for the current shared 
memory segments on Oracle Linux. Before proceeding with the execution please note following:
 * For ASM instance, it needs to configure ASMM instead of AMM.
 * The 'pga_aggregate_target' is outside the SGA and 
   you should accommodate this while calculating SGA size.
 * In case you changes the DB SGA size, 
   as the new SGA will not fit in the previous HugePages configuration, 
   it had better disable the whole HugePages, 
   start the DB with new SGA size and run the script again.
And make sure that:
 * Oracle Database instance(s) are up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not setup 
   (See Doc ID 749851.1)
 * The shared memory segments can be listed by command:
     # ipcs -m


Press Enter to proceed..."

read

# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`

# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk '{print $2}'`
if [ -z "$HPG_SZ" ];then
    echo "The hugepages may not be supported in the system where the script is being executed."
    exit 1
fi

# Initialize the counter
NUM_PG=0

# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | cut -c44-300 | awk '{print $1}' | grep "[0-9][0-9]*"`
do
    MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
    if [ $MIN_PG -gt 0 ]; then
        NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
    fi
done

RES_BYTES=`echo "$NUM_PG * $HPG_SZ * 1024" | bc -q`

# An SGA less than 100MB does not make sense
# Bail out if that is the case
if [ $RES_BYTES -lt 100000000 ]; then
    echo "***********"
    echo "** ERROR **"
    echo "***********"
    echo "Sorry! There are not enough total of shared memory segments allocated for 
HugePages configuration. HugePages can only be used for shared memory segments 
that you can list by command:

    # ipcs -m

of a size that can match an Oracle Database SGA. Please make sure that:
 * Oracle Database instance is up and running 
 * Oracle Database 11g Automatic Memory Management (AMM) is not configured"
    exit 1
fi

# Finish with results
case $KERN in
     '2.2') echo "Kernel version $KERN is not supported. Exiting." ;;
    '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
           echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
    '2.6') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '3.8') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '3.10') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '4.1') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
esac

# End

执行以下命令来改变hugepages_settings.sh脚本的权限

[root@jyrac1 /]# chmod +x hugepages_settings.sh

运行hugepages_settings.sh脚本来计算hugepages配置的参数值

[root@jyrac1 /]# ./hugepages_settings.sh
This script is provided by Doc ID 401749.1 from My Oracle Support 
(http://support.oracle.com) where it is intended to compute values for 
the recommended HugePages/HugeTLB configuration for the current shared 
memory segments on Oracle Linux. Before proceeding with the execution please note following:
 * For ASM instance, it needs to configure ASMM instead of AMM.
 * The 'pga_aggregate_target' is outside the SGA and 
   you should accommodate this while calculating SGA size.
 * In case you changes the DB SGA size, 
   as the new SGA will not fit in the previous HugePages configuration, 
   it had better disable the whole HugePages, 
   start the DB with new SGA size and run the script again.
And make sure that:
 * Oracle Database instance(s) are up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not setup 
   (See Doc ID 749851.1)
 * The shared memory segments can be listed by command:
     # ipcs -m


Press Enter to proceed...

***********
** ERROR **
***********
Sorry! There are not enough total of shared memory segments allocated for 
HugePages configuration. HugePages can only be used for shared memory segments 
that you can list by command:

    # ipcs -m

of a size that can match an Oracle Database SGA. Please make sure that:
 * Oracle Database instance is up and running 
 * Oracle Database 11g Automatic Memory Management (AMM) is not configured

从上面的信息可以看到需要确认Oracle实例是否正在运行,如果是Oracle 11g不能使用AMM

[root@jyrac1 ~]# ps -ef | grep pmon
grid      4116     1  0 Apr18 ?        00:00:03 asm_pmon_+ASM1
oracle    4944     1  0 Apr18 ?        00:00:03 ora_pmon_jyrac1
root     18184 29273  0 15:15 pts/1    00:00:00 grep pmon

上面信息可以看到Oracle实例正在运行。

[grid@jyrac1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 20 15:20:23 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Real Application Clusters and Automatic Storage Management options

SQL> set long 900
SQL> set linesize 900
SQL> show parameter instance_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
instance_name                        string                 +ASM1
SQL> show parameter memory

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
memory_max_target                    big integer            1076M
memory_target                        big integer            1076M


[oracle@jyrac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 20 15:21:04 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> set long 900
SQL> set linesize 900
SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      jyrac1
SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 2G
memory_target                        big integer 2G
shared_memory_address                integer     0

确实asm与数据库实例都启用了AMM,需要禁用AMM但是可以使用ASMM修改ASM实例,禁用AMM,但使用ASMM,如果是RAC所有节点都需要修改

SQL> alter system set sga_max_size=640M scope=spfile sid='*';

System altered.

SQL> alter system set sga_target=640M scope=spfile sid='*';

System altered.

SQL> alter system set pga_aggregate_target=320M scope=spfile sid='*';

System altered.

SQL>  alter system set memory_target=0 scope=spfile sid='*';

System altered.

这里对于memory_target不能使用reset否则会出现以下错误:

SQL> startup
ORA-01078: failure in processing system parameters
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
ORA-00849: SGA_TARGET 671088640 cannot be set to more than MEMORY_MAX_TARGET 0.
SQL> alter system reset memory_max_target scope=spfile sid='*';

System altered.

修改数据库实例,禁用AMM,但使用ASMM,如果是RAC所有节点都需要修改

SQL> alter system set sga_max_size=640M scope=spfile sid='*';

System altered.

SQL> alter system set sga_target=640M scope=spfile sid='*';

System altered.

SQL> alter system set pga_aggregate_target=320M scope=spfile sid='*';

System altered.

SQL> alter system reset memory_max_target scope=spfile sid='*';

System altered.

SQL> alter system reset memory_target scope=spfile sid='*';

System altered.

重启ASM与数据库实例,如果是RAC所有节点都需要重启,首先停止ASM与数据库实例

[grid@jyrac1 ~]$ srvctl stop asm -n jyrac1 -f
[grid@jyrac1 ~]$ srvctl stop asm -n jyrac2 -f
[grid@jyrac1 ~]$ srvctl stop database -d jyrac



[grid@jyrac1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRSDG.dg
               OFFLINE OFFLINE      jyrac1                                       
               OFFLINE OFFLINE      jyrac2                                       
ora.DATADG.dg
               OFFLINE OFFLINE      jyrac1                                       
               OFFLINE OFFLINE      jyrac2                                       
ora.LISTENER.lsnr
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
ora.asm
               OFFLINE OFFLINE      jyrac1                   Instance Shutdown   
               OFFLINE OFFLINE      jyrac2                   Instance Shutdown   
ora.gsd
               ONLINE  OFFLINE      jyrac1                                       
               ONLINE  OFFLINE      jyrac2                                       
ora.net1.network
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
ora.ons
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
ora.registry.acfs
               OFFLINE OFFLINE      jyrac1                                       
               OFFLINE OFFLINE      jyrac2                                       
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       jyrac2                                       
ora.cvu
      1        ONLINE  ONLINE       jyrac2                                       
ora.jyrac.db
      1        OFFLINE OFFLINE                               Instance Shutdown   
      2        OFFLINE OFFLINE                               Instance Shutdown   
ora.jyrac1.vip
      1        ONLINE  ONLINE       jyrac1                                       
ora.jyrac2.vip
      1        ONLINE  ONLINE       jyrac2                                       
ora.oc4j
      1        ONLINE  ONLINE       jyrac2                                       
ora.scan1.vip
      1        ONLINE  ONLINE       jyrac2             

启动ASM与数据库实例

grid@jyrac1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 20 17:48:32 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ASM instance started

Total System Global Area  669581312 bytes
Fixed Size                  1366724 bytes
Variable Size             643048764 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
ASM diskgroups volume enabled

SQL> show parameter instance_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
instance_name                        string                 +ASM2
SQL> show parameter memory

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
memory_max_target                    big integer            0
memory_target                        big integer            0
SQL> show parameter sga

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
lock_sga                             boolean                FALSE
sga_max_size                         big integer            640M
sga_target                           big integer            640M

grid@jyrac2 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 20 17:48:32 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ASM instance started

Total System Global Area  669581312 bytes
Fixed Size                  1366724 bytes
Variable Size             643048764 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
ASM diskgroups volume enabled

SQL> show parameter instance_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
instance_name                        string                 +ASM2
SQL> show parameter memory

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
memory_max_target                    big integer            0
memory_target                        big integer            0
SQL> show parameter sga

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
lock_sga                             boolean                FALSE
sga_max_size                         big integer            640M
sga_target                           big integer            640M

[grid@jyrac1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRSDG.dg
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
ora.DATADG.dg
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
ora.LISTENER.lsnr
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
ora.asm
               ONLINE  ONLINE       jyrac1                   Started             
               ONLINE  ONLINE       jyrac2                   Started             
ora.gsd
               ONLINE  OFFLINE      jyrac1                                       
               ONLINE  OFFLINE      jyrac2                                       
ora.net1.network
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
ora.ons
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
ora.registry.acfs
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       jyrac1                                       
ora.cvu
      1        ONLINE  ONLINE       jyrac1                                       
ora.jyrac.db
      1        OFFLINE OFFLINE                               Instance Shutdown   
      2        OFFLINE OFFLINE                               Instance Shutdown   
ora.jyrac1.vip
      1        ONLINE  ONLINE       jyrac1                                       
ora.jyrac2.vip
      1        ONLINE  ONLINE       jyrac2                                       
ora.oc4j
      1        ONLINE  ONLINE       jyrac1                                       
ora.scan1.vip
      1        ONLINE  ONLINE       jyrac1                 

从上面的信息可以看到asm实例已经启动了并且禁用了AMM

[grid@jyrac1 ~]$ srvctl start database -d jyrac
[grid@jyrac1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRSDG.dg
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
ora.DATADG.dg
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
ora.LISTENER.lsnr
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
ora.asm
               ONLINE  ONLINE       jyrac1                   Started             
               ONLINE  ONLINE       jyrac2                   Started             
ora.gsd
               ONLINE  OFFLINE      jyrac1                                       
               ONLINE  OFFLINE      jyrac2                                       
ora.net1.network
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
ora.ons
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
ora.registry.acfs
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       jyrac1                                       
ora.cvu
      1        ONLINE  ONLINE       jyrac1                                       
ora.jyrac.db
      1        ONLINE  ONLINE       jyrac1                   Open                
      2        ONLINE  ONLINE       jyrac2                   Open                
ora.jyrac1.vip
      1        ONLINE  ONLINE       jyrac1                                       
ora.jyrac2.vip
      1        ONLINE  ONLINE       jyrac2                                       
ora.oc4j
      1        ONLINE  ONLINE       jyrac1                                       
ora.scan1.vip
      1        ONLINE  ONLINE       jyrac1   

SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      jyrac1
SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
shared_memory_address                integer     0
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 640M
sga_target                           big integer 640M


SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      jyrac2
SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
shared_memory_address                integer     0
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 640M
sga_target                           big integer 640M                   

数据库也已经成功启动并且禁用了AMM

再次执行hugepages_settings.sh脚本计算HugePages的大小

[root@jyrac1 /]# ./hugepages_settings.sh
Recommended setting: vm.nr_hugepages = 649

编辑/etc/sysctl.conf文件增加参数vm.nr_hugepages = 649,并执行sysctl -p命令使用修改立即生效,但oracle实例并没有使用HugePages从HugePages_Total与HugePages_Free相等可以判断出来。

[root@jyrac1 /]# vi /etc/sysctl.conf
vm.nr_hugepages = 649
[root@jyrac1 /]# sysctl -p

[root@jyrac1 /]# grep Huge /proc/meminfo
HugePages_Total:   649
HugePages_Free:    649
HugePages_Rsvd:      0
Hugepagesize:     2048 kB

重启实例

SQL> startup
ASM instance started

Total System Global Area  669581312 bytes
Fixed Size                  1366724 bytes
Variable Size             643048764 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
ASM diskgroups volume enabled

查看asm实例的alert_+ASM1.log可以看到如下信息:

Starting ORACLE instance (normal)
************************ Large Pages Information *******************
Per process system memlock (soft) limit = 2048 MB
 
Total Shared Global Region in Large Pages = 642 MB (100%)
 
Large Pages used by this instance: 321 (642 MB)
Large Pages unused system wide = 328 (656 MB)
Large Pages configured system wide = 649 (1298 MB)
Large Page size = 2048 KB
SQL> startup
ORACLE instance started.

Total System Global Area  669581312 bytes
Fixed Size                  1366724 bytes
Variable Size             243270972 bytes
Database Buffers          419430400 bytes
Redo Buffers                5513216 bytes
Database mounted.
Database opened.

查看实例jyrac1的alert_jyrac1.log可以看到如下信息:

Starting ORACLE instance (normal)
************************ Large Pages Information *******************
Per process system memlock (soft) limit = 2048 MB
 
Total Shared Global Region in Large Pages = 642 MB (100%)
 
Large Pages used by this instance: 321 (642 MB)
Large Pages unused system wide = 7 (14 MB)
Large Pages configured system wide = 649 (1298 MB)
Large Page size = 2048 KB


[root@jyrac1 /]# grep Huge /proc/meminfo
HugePages_Total:   649
HugePages_Free:    239
HugePages_Rsvd:    232
Hugepagesize:     2048 kB

从上面的信息可以看到已经使用了Hugepages

3.HugePages的限制
HugePages有以下限制:
a.对于Oracle 11g及以上版本数据库实例必须对memory_target与memory_max_target参数执行alter system reset命令,但对于ASM实例,对于memory_target参数只能设置为0。
b.AMM与HugePages是不兼容的,当使用AMM,整个SGA内存通过在/dev/shm创建文件来进行内存的分配,当使用AMM分配SGA时,HugePages不会被保留。
c.如果在32位系统中使用VLM,那么对数据库buffer cache不能使用HugePages。但对于SGA中的其它组件比如shared_pool,
large_pool等等可以使用HugePages。对于VLM(buffer cache)分配内存是通过使用共享内存文件系统(ramfs/tmpfs/shmfs)来实现的。
d.HugePgaes在系统启动后不受分配或释放,除非系统管理员通过修改可用页数或改变池大小来改变HugePages的配置。如果在系统启动时内存中没有保留所需要内存空间,那么HugePages会分配失败。
e.确保HugePages配置合理,如果内存耗尽,应用将不能使用HugePages。
f.如果当实例启动用没有足够的HugePages并且参数use_large_pages设置为only,那么Oracle数据库将会启动失败并向alert.log中记录相关信息。

Oracle 11g RAC跨实例控制并行

在Oracle 10g RAC中的并行执行是由两个参数来控制的:instance_groups和parallel_instance_group这两个参数在Oracle 10g RAC中必须同时设置。

instance_groups可以有多个值但是静态值,parallel_instance_group可以在系统级别与会话级别进行修改。

在Oracle 10g RAC中,为了使用并行执行需要设置parallel_instance_group参数,并且parallel_instance_group的值必须是该实例instance_groups所指定的参数值之一。

例如,有一个3节点的Oracle 10g RAC在spfile中有以下设置:
myrac1.instance_groups=’rac’,’rac1′
myrac2.instance_groups=’rac’,’rac2′
myrac3.instance_groups=’rac’,’rac3′
myrac1.parallel_instance_group=’rac1’#节点1的并行执行只能在节点1上运行
myrac2.parallel_instance_group=’rac2’#节点2的并行执行只能在节点2上运行
myrac3.parallel_instance_group=’rac3’#节点3的并行执行只能在节点3上运行

在11gRAC中,因为向后兼容仍然可以使用instance_groups和parallel_instance_group参数。然而,在Oracle 11g RAC中不需要这样做,instance_groups参数已经被废弃并且保留只是为了向后兼容。

在Oracle 11gRAC中并行查询子进程与服务集成在一起,因此不需要再设置instance_groups和parallel_instance_group参数。因为不需要设置instance_groups参数,而是可以直接设置服务名,
例如:alter session set parallel_instance_group=service_name。因为在11g中instance_groups参数已经被废弃,可以继续使用,但是它只是为了向后兼容。

在Oracle 11g RAC中也可以不需要设置parallel_instance_groups参数来限制并行查询子进程在指定的实例上运行。如果以并行方式来执行SQL语句,那么缺省情况下并行进程只会在你通过服务名所连接到的数据库实例上运行。这不会影响其它的并行操作比如并行恢复或gv$视图的查询处理。为了覆盖这种行为,可以设置parallel_instance_group参数。

可以使用srvctl add service命令来创建服务。例如,数据库名是rac且有2个实例,rac1和rac2。
对每个实例创建一个服务

[grid@rac1 ~]$ srvctl add service -d rac -s rac1 -r rac1 -a rac2
[grid@rac1 ~]$ srvctl add service -d rac -s rac2 -r rac2 -a rac1
[grid@rac1 ~]$ srvctl start service -d rac -s rac1
[grid@rac1 ~]$ srvctl start service -d rac -s rac2

上面的语句将创建两个服务,每个实例一个,-r参数指定首选实例,-a参数指定可用实例。
1.如果使用其中的一个服务连接数据库,并且没有显式地设置parallel_instance_groups参数,那么并行执行将被限制在你的连接的实例上执行

例如,如果连接到rac1,因上连接到rac1实例,那么并行查询了进程将只能在rac1的r参数所指定的实例rac1上运行。

SQL> conn sys/system@rac1 as sysdba

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
rac1

SQL> show parameter parallel_instance_group

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_instance_group              string
SQL> show parameter instance_groups

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_groups                      string

SQL> show parameter parallel_degree_policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy               string      auto
SQL> set timing on
SQL> set autotrace on
SQL> select /*+ parallel(t1,60) */ count(*) from t1;

  COUNT(*)
----------
  11121536

Elapsed: 00:00:41.25

Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320

--------------------------------------------------------------------------------
------------------------

| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    T
Q  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------
------------------------

|   0 | SELECT STATEMENT       |          |     1 |   793   (0)| 00:00:01 |
   |      |            |

|   1 |  SORT AGGREGATE        |          |     1 |            |          |
   |      |            |

|   2 |   PX COORDINATOR       |          |       |            |          |
   |      |            |

|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,
00 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,
00 | PCWP |            |

|   5 |      PX BLOCK ITERATOR |          |    10M|   793   (0)| 00:00:01 |  Q1,
00 | PCWC |            |

|   6 |       TABLE ACCESS FULL| T1       |    10M|   793   (0)| 00:00:01 |  Q1,
00 | PCWP |            |

--------------------------------------------------------------------------------
------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)
   - Degree of Parallelism is 60 because of hint


Statistics
----------------------------------------------------------
        430  recursive calls
          0  db block gets
     281608  consistent gets
     158184  physical reads
       3328  redo size
        425  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         21  sorts (memory)
          0  sorts (disk)
          1  rows processed

在执行时查询并行子进程的是否只在rac1上运行

SQL> SELECT inst_id,sid, event, seq#,p1,p2,p3, wait_time FROM gv$session_wait WHERE upper(event) like ('PX%') ORDER BY 1;

   INST_ID        SID EVENT                                                                  SEQ#         P1         P2         P3  WAIT_TIME
---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
         1         29 PX Deq: Execution Msg                                                    54  268566527          1 1113052640         -1
         1        105 PX Deq: Execution Msg                                                     2  268566527          1 1113043256         -1
         1         43 PX Deq: Execution Msg                                                    57  268566527          1 1113057500          0
         1         44 PX Deq: Execution Msg                                                    55  268566527          1 1011415624         -1
         1         50 PX Deq: Execution Msg                                                    45  268566527          1 1113065368         -1
         1         53 PX Deq: Execution Msg                                                    64  268566527          1 1113070008          0
         1         54 PX Deq: Execute Reply                                                  8603        200          1          0          0
         1         55 PX Deq: Execution Msg                                                    59  268566527          1 1113049700         -1
         1         62 PX Deq: Execution Msg                                                    68  268566527          1 1113067520          0
         1         69 PX Deq: Execution Msg                                                    53  268566527          1 1113068028          0
         1         74 PX Deq: Execution Msg                                                    60  268566527          1 1113066504         -1

   INST_ID        SID EVENT                                                                  SEQ#         P1         P2         P3  WAIT_TIME
---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
         1         79 PX Deq: Execute Reply                                                   641        200          1          0          0
         1         80 PX Deq: Execution Msg                                                    59  268566527          1 1113068568         -1
         1         81 PX Deq: Execution Msg                                                    63  268566527          1 1113069048         -1
         1         88 PX Deq: Execution Msg                                                    59  268566527          1 1113051680          0
         1         97 PX Deq: Execution Msg                                                    46  268566527          1 1011409340          0


16 rows selected.

可以看到并行子进程全都是在实例rac1上运行。

2.一旦创建这些服务名之后,服务名rac1和rac2可以作为parallel_instance_groups的参数值,不管你使用那个服务名来进行数据库连接例如,如果使用通用的数据库服务名rac来进行连接,发现连接到了rac1实例,那么并行子进程会在当前所连接的实例或所有实例上运行。

SQL> conn sys/system@rac as sysdba

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
rac1

SQL> show parameter parallel_instance_group

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_instance_group              string
SQL> show parameter instance_groups

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_groups                      string

SQL> show parameter parallel_degree_policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy               string      auto
SQL> set timing on
SQL> set autotrace on
SQL> select /*+ parallel(t1,60) */ count(*) from t1;

  COUNT(*)
----------
  11121536

Elapsed: 00:00:41.25

Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320

--------------------------------------------------------------------------------
------------------------

| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    T
Q  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------
------------------------

|   0 | SELECT STATEMENT       |          |     1 |   793   (0)| 00:00:01 |
   |      |            |

|   1 |  SORT AGGREGATE        |          |     1 |            |          |
   |      |            |

|   2 |   PX COORDINATOR       |          |       |            |          |
   |      |            |

|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,
00 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,
00 | PCWP |            |

|   5 |      PX BLOCK ITERATOR |          |    10M|   793   (0)| 00:00:01 |  Q1,
00 | PCWC |            |

|   6 |       TABLE ACCESS FULL| T1       |    10M|   793   (0)| 00:00:01 |  Q1,
00 | PCWP |            |

--------------------------------------------------------------------------------
------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)
   - Degree of Parallelism is 60 because of hint


Statistics
----------------------------------------------------------
        430  recursive calls
          0  db block gets
     281608  consistent gets
     158184  physical reads
       3328  redo size
        425  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         21  sorts (memory)
          0  sorts (disk)
          1  rows processed

在执行时查询并行子进程的是否只在rac1上运行

SQL> SELECT inst_id,sid, event, seq#,p1,p2,p3, wait_time FROM gv$session_wait WHERE upper(event) like ('PX%') ORDER BY 1;
   INST_ID        SID EVENT                                                                  SEQ#         P1         P2         P3  WAIT_TIME
---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
         1         29 PX Deq: Execution Msg                                                    43  268566527          1 1113052640          0
         1         33 PX Deq: Execute Reply                                                 60924        200          1          0          0
         1        104 PX Deq: Execution Msg                                                    51  268566527          1 1113063216          0
         1        103 PX Deq: Execution Msg                                                    48  268566527          1 1011409820          0
         1        101 PX Deq: Execution Msg                                                    53  268566527          1 1113042688          0
         1        100 PX Deq: Execution Msg                                                    53  268566527          1 1011414604          0
         1         99 PX Deq: Execution Msg                                                    54  268566527          1 1113050660          0
         1         91 PX Deq: Execution Msg                                                    59  268566527          1 1113054620          0
         1         90 PX Deq: Execution Msg                                                    47  268566527          1 1011408860          0
         1         88 PX Deq: Execution Msg                                                    60  268566527          1 1113043256          0
         1         87 PX Deq: Execution Msg                                                    45  268566527          1 1113046760          0
         1         86 PX Deq: Execution Msg                                                    52  268566527          1 1113065936          0
         1         85 PX Deq: Execution Msg                                                    45  268566527          1 1113062708          0
         1         84 PX Deq: Execution Msg                                                    59  268566527          1 1011409340          0
         1         83 PX Deq: Execution Msg                                                    45  268566527          1 1113043764          0
         1         82 PX Deq: Execution Msg                                                    46  268566527          1 1113059480          0
         1         81 PX Deq: Execution Msg                                                    52  268566527          1 1113064800          0
         1         80 PX Deq: Execution Msg                                                    43  268566527          1 1113066504          0
         1         76 PX Deq: Execution Msg                                                    61  268566527          1 1113056540          0
         1         69 PX Deq: Execution Msg                                                    48  268566527          1 1113067012          0
         1         68 PX Deq: Execution Msg                                                    48  268566527          1 1113049700          0
         1         66 PX Deq: Execution Msg                                                    61  268566527          1 1011415624          0
         1         64 PX Deq: Execution Msg                                                     2  268566527          1 1113068028         -1
         1         62 PX Deq: Execution Msg                                                    39  268566527          1 1113067520          0
         1         59 PX Deq: Execution Msg                                                    49  268566527          1 1113044840          0
         1         58 PX Deq: Execution Msg                                                    47  268566527          1 1113048740          0
         1         57 PX Deq: Execution Msg                                                    50  268566527          1 1113064232          0
         1         54 PX Deq: Execute Reply                                                 11610        200          1          0          0
         1         53 PX Deq: Execution Msg                                                    52  268566527          1 1113065368          0
         1         43 PX Deq: Execution Msg                                                    42  268566527          1 1113047780          0
         2         80 PX Deq: Execution Msg                                                   133  268566527          2 1112541100          0
         2         83 PX Deq: Execution Msg                                                   119  268566527          2 1112529160          0
         2         78 PX Deq: Execution Msg                                                   107  268566527          2 1112534620          0
         2         77 PX Deq: Execution Msg                                                   144  268566527          2 1112535100          0
         2         76 PX Deq: Execution Msg                                                   146  268566527          2 1112540140          0
         2         74 PX Deq: Execution Msg                                                   118  268566527          2 1112530660          0
         2         73 PX Deq: Execution Msg                                                   150  268566527          2 1112536660          0
         2         72 PX Deq: Execution Msg                                                   125  268566527          2 1112531140          0
         2         71 PX Deq: Execution Msg                                                   157  268566527          2 1112533660          0
         2         70 PX Deq: Execution Msg                                                   146  268566527          2 1112530180          0
         2         69 PX Deq: Execution Msg                                                   135  268566527          2 1112532640          0
         2         67 PX Deq: Execution Msg                                                   128  268566527          2 1112556120          0
         2         66 PX Deq: Execution Msg                                                   135  268566527          2 1112532100          0
         2         65 PX Deq: Execution Msg                                                   124  268566527          2 1112555640          0
         2         63 PX Deq: Execution Msg                                                   126  268566527          2 1112528620          0
         2         62 PX Deq: Execution Msg                                                   183  268566527          2 1112537140          0
         2         60 PX Deq: Execution Msg                                                   182  268566527          2 1112541580          0
         2         59 PX Deq: Execution Msg                                                   103  268566527          2 1112535580          0
         2         58 PX Deq: Execution Msg                                                   160  268566527          2 1112539600          0
         2         57 PX Deq: Execution Msg                                                   164  268566527          2 1112538580          0
         2         56 PX Deq: Execution Msg                                                   124  268566527          2 1112527528          0
         2         53 PX Deq: Execution Msg                                                   145  268566527          2 1112539060          0
         2         49 PX Deq: Execution Msg                                                   140  268566527          2 1112538100          0
         2         37 PX Deq: Execution Msg                                                   112  268566527          2 1112537620          0
         2         36 PX Deq: Execution Msg                                                   153  268566527          1 1112536120          0
         2         34 PX Deq: Execution Msg                                                    14  268566527          2 1112531620         -1
         2         81 PX Deq: Execution Msg                                                   157  268566527          2 1112533180          0
         2         79 PX Deq: Execution Msg                                                   128  268566527          2 1112557244          0

58 rows selected.

从上面的信息可以看到通过通用服务名rac虽然连接到的实例是rac1,在执行并行查询时并行子进程在实例rac1与rac2上运行,并不是只在rac1实例上运行。

如果你想限制你的查询只在rac1实例上执行,那么执行
alter session set parallel_instance_group=rac1命令,这将限制并行子进程只在指定服务名所相关的实例上运行,比如rac1。

SQL> alter session set parallel_instance_group='rac1';

Session altered.

SQL> show parameter parallel_instance_group

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_instance_group              string      rac1

SQL> select /*+ parallel(t1,60) */ count(*) from t1;

  COUNT(*)
----------
  11121536

Elapsed: 00:00:41.25

Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320

--------------------------------------------------------------------------------
------------------------

| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    T
Q  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------
------------------------

|   0 | SELECT STATEMENT       |          |     1 |   793   (0)| 00:00:01 |
   |      |            |

|   1 |  SORT AGGREGATE        |          |     1 |            |          |
   |      |            |

|   2 |   PX COORDINATOR       |          |       |            |          |
   |      |            |

|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,
00 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,
00 | PCWP |            |

|   5 |      PX BLOCK ITERATOR |          |    10M|   793   (0)| 00:00:01 |  Q1,
00 | PCWC |            |

|   6 |       TABLE ACCESS FULL| T1       |    10M|   793   (0)| 00:00:01 |  Q1,
00 | PCWP |            |

--------------------------------------------------------------------------------
------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)
   - Degree of Parallelism is 60 because of hint


Statistics
----------------------------------------------------------
        430  recursive calls
          0  db block gets
     281608  consistent gets
     158184  physical reads
       3328  redo size
        425  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         21  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT inst_id,sid, event, seq#,p1,p2,p3, wait_time FROM gv$session_wait WHERE upper(event) like ('PX%') ORDER BY 1;

   INST_ID        SID EVENT                                                                  SEQ#         P1         P2         P3  WAIT_TIME
---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
         1         43 PX Deq: Execution Msg                                                    61  268566527          2 1113060496          0
         1         53 PX Deq: Execution Msg                                                    71  268566527          2 1011415624          0
         1         54 PX Deq: Parse Reply                                                   28202        200          1          0         -1
         1         55 PX Deq: Execution Msg                                                    64  268566527          1 1011418240          0
         1         57 PX Deq: Execution Msg                                                    88  268566527          1 1113048740          0
         1         58 PX Deq: Execution Msg                                                    53  268566527          1 1113061632          0
         1         62 PX Deq: Execution Msg                                                    66  268566527          3 1011420904          0
         1         64 PX Deq: Execution Msg                                                    58  268566527          1 1011407468          0
         1         66 PX Deq: Execution Msg                                                    75  268566527          2 1113061124          0
         1         67 PX Deq: Execution Msg                                                     2  268566527          1 1113079488         -1
         1         68 PX Deq: Execution Msg                                                    76  268566527          2 1011417732          0
         1         69 PX Deq: Execution Msg                                                    56  268566527          3 1011420424          0
         1         76 PX Deq: Execution Msg                                                    70  268566527          1 1011408860          0
         1         79 PX Deq: Execute Reply                                                   672        200          1          0          0
         1         80 PX Deq: Execution Msg                                                    75  268566527          1 1011419944          0
         1         81 PX Deq: Execution Msg                                                    75  268566527          1 1011409340          0
         1         82 PX Deq: Execution Msg                                                    63  268566527          1 1113054620          0
         1         83 PX Deq: Execution Msg                                                    62  268566527          2 1011407948          0
         1         85 PX Deq: Execution Msg                                                    65  268566527          3 1113049700          0
         1         86 PX Deq: Execution Msg                                                    54  268566527          2 1113047780          0
         1         88 PX Deq: Execution Msg                                                    65  268566527          3 1113059480          0
         1         90 PX Deq: Execution Msg                                                    83  268566527          2 1113049220          0
         1         91 PX Deq: Execution Msg                                                    65  268566527          1 1113052640          0
         1        101 PX Deq: Execution Msg                                                    69  268566527          2 1113053600          0
         1        102 PX Deq: Execution Msg                                                    56  268566527          2 1113056540          0
         1        103 PX Deq: Execution Msg                                                    70  268566527          2 1113044840          0
         1        104 PX Deq: Execution Msg                                                    70  268566527          2 1113046760          0
 
28 rows selected.

可以看到在设置parallel_instance_group=’rac1’后,并行子进程只能在实例rac1上运行了。

3.如果连接的服务名运行在两个实例上,比如:srvctl add service -d rac -s rac -r rac1,rac2那么,并行查询的并行子进程将会运行在两个实例上,不管你所连接的是那个实例。

SQL> conn sys/system@rac as sysdba

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
rac2

SQL> show parameter parallel_instance_group

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_instance_group              string
SQL> show parameter instance_groups

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_groups                      string

SQL> show parameter parallel_degree_policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy               string      auto
SQL> set timing on
SQL> set autotrace on
SQL> select /*+ parallel(t1,60) */ count(*) from t1;

  COUNT(*)
----------
  11121536

Elapsed: 00:00:41.25

Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320

--------------------------------------------------------------------------------
------------------------

| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    T
Q  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------
------------------------

|   0 | SELECT STATEMENT       |          |     1 |   793   (0)| 00:00:01 |
   |      |            |

|   1 |  SORT AGGREGATE        |          |     1 |            |          |
   |      |            |

|   2 |   PX COORDINATOR       |          |       |            |          |
   |      |            |

|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,
00 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,
00 | PCWP |            |

|   5 |      PX BLOCK ITERATOR |          |    10M|   793   (0)| 00:00:01 |  Q1,
00 | PCWC |            |

|   6 |       TABLE ACCESS FULL| T1       |    10M|   793   (0)| 00:00:01 |  Q1,
00 | PCWP |            |

--------------------------------------------------------------------------------
------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)
   - Degree of Parallelism is 60 because of hint


Statistics
----------------------------------------------------------
        430  recursive calls
          0  db block gets
     281608  consistent gets
     158184  physical reads
       3328  redo size
        425  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         21  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT inst_id,sid, event, seq#,p1,p2,p3, wait_time FROM gv$session_wait WHERE upper(event) like ('PX%') ORDER BY 1;
   INST_ID        SID EVENT                                                                  SEQ#         P1         P2         P3  WAIT_TIME
---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
         1         29 PX Deq: Execution Msg                                                    43  268566527          1 1113052640          0
         1         33 PX Deq: Execute Reply                                                 60924        200          1          0          0
         1        104 PX Deq: Execution Msg                                                    51  268566527          1 1113063216          0
         1        103 PX Deq: Execution Msg                                                    48  268566527          1 1011409820          0
         1        101 PX Deq: Execution Msg                                                    53  268566527          1 1113042688          0
         1        100 PX Deq: Execution Msg                                                    53  268566527          1 1011414604          0
         1         99 PX Deq: Execution Msg                                                    54  268566527          1 1113050660          0
         1         91 PX Deq: Execution Msg                                                    59  268566527          1 1113054620          0
         1         90 PX Deq: Execution Msg                                                    47  268566527          1 1011408860          0
         1         88 PX Deq: Execution Msg                                                    60  268566527          1 1113043256          0
         1         87 PX Deq: Execution Msg                                                    45  268566527          1 1113046760          0
         1         86 PX Deq: Execution Msg                                                    52  268566527          1 1113065936          0
         1         85 PX Deq: Execution Msg                                                    45  268566527          1 1113062708          0
         1         84 PX Deq: Execution Msg                                                    59  268566527          1 1011409340          0
         1         83 PX Deq: Execution Msg                                                    45  268566527          1 1113043764          0
         1         82 PX Deq: Execution Msg                                                    46  268566527          1 1113059480          0
         1         81 PX Deq: Execution Msg                                                    52  268566527          1 1113064800          0
         1         80 PX Deq: Execution Msg                                                    43  268566527          1 1113066504          0
         1         76 PX Deq: Execution Msg                                                    61  268566527          1 1113056540          0
         1         69 PX Deq: Execution Msg                                                    48  268566527          1 1113067012          0
         1         68 PX Deq: Execution Msg                                                    48  268566527          1 1113049700          0
         1         66 PX Deq: Execution Msg                                                    61  268566527          1 1011415624          0
         1         64 PX Deq: Execution Msg                                                     2  268566527          1 1113068028         -1
         1         62 PX Deq: Execution Msg                                                    39  268566527          1 1113067520          0
         1         59 PX Deq: Execution Msg                                                    49  268566527          1 1113044840          0
         1         58 PX Deq: Execution Msg                                                    47  268566527          1 1113048740          0
         1         57 PX Deq: Execution Msg                                                    50  268566527          1 1113064232          0
         1         54 PX Deq: Execute Reply                                                 11610        200          1          0          0
         1         53 PX Deq: Execution Msg                                                    52  268566527          1 1113065368          0
         1         43 PX Deq: Execution Msg                                                    42  268566527          1 1113047780          0
         2         80 PX Deq: Execution Msg                                                   133  268566527          2 1112541100          0
         2         83 PX Deq: Execution Msg                                                   119  268566527          2 1112529160          0
         2         78 PX Deq: Execution Msg                                                   107  268566527          2 1112534620          0
         2         77 PX Deq: Execution Msg                                                   144  268566527          2 1112535100          0
         2         76 PX Deq: Execution Msg                                                   146  268566527          2 1112540140          0
         2         74 PX Deq: Execution Msg                                                   118  268566527          2 1112530660          0
         2         73 PX Deq: Execution Msg                                                   150  268566527          2 1112536660          0
         2         72 PX Deq: Execution Msg                                                   125  268566527          2 1112531140          0
         2         71 PX Deq: Execution Msg                                                   157  268566527          2 1112533660          0
         2         70 PX Deq: Execution Msg                                                   146  268566527          2 1112530180          0
         2         69 PX Deq: Execution Msg                                                   135  268566527          2 1112532640          0
         2         67 PX Deq: Execution Msg                                                   128  268566527          2 1112556120          0
         2         66 PX Deq: Execution Msg                                                   135  268566527          2 1112532100          0
         2         65 PX Deq: Execution Msg                                                   124  268566527          2 1112555640          0
         2         63 PX Deq: Execution Msg                                                   126  268566527          2 1112528620          0
         2         62 PX Deq: Execution Msg                                                   183  268566527          2 1112537140          0
         2         60 PX Deq: Execution Msg                                                   182  268566527          2 1112541580          0
         2         59 PX Deq: Execution Msg                                                   103  268566527          2 1112535580          0
         2         58 PX Deq: Execution Msg                                                   160  268566527          2 1112539600          0
         2         57 PX Deq: Execution Msg                                                   164  268566527          2 1112538580          0
         2         56 PX Deq: Execution Msg                                                   124  268566527          2 1112527528          0
         2         53 PX Deq: Execution Msg                                                   145  268566527          2 1112539060          0
         2         49 PX Deq: Execution Msg                                                   140  268566527          2 1112538100          0
         2         37 PX Deq: Execution Msg                                                   112  268566527          2 1112537620          0
         2         36 PX Deq: Execution Msg                                                   153  268566527          1 1112536120          0
         2         34 PX Deq: Execution Msg                                                    14  268566527          2 1112531620         -1
         2         81 PX Deq: Execution Msg                                                   157  268566527          2 1112533180          0
         2         79 PX Deq: Execution Msg                                                   128  268566527          2 1112557244          0

58 rows selected.

从上面的信息可以看到通过通用服务名rac虽然连接到的实例是rac2,在执行并行查询时并行子进程在实例rac1与rac2上运行,并不是只在rac2实例上运行。

4.如果所连接的实例已经设置了instance_groups,那么也可以设置parallel_instance_group来进行限制,就像oracle 10g rac一样。

实例rac1:

SQL> alter system set instance_groups='rac','rac1' scope=spfile sid='rac1';

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  669581312 bytes
Fixed Size                  1366724 bytes
Variable Size             306185532 bytes
Database Buffers          356515840 bytes
Redo Buffers                5513216 bytes
Database mounted.
Database opened.


SQL> show parameter instance_groups

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_groups                      string      rac, rac1
SQL> show parameter parallel_instance_group

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_instance_group              string

将parallel_instance_group设置为rac,并行子进程可能会在实例rac1与rac2上运行

SQL> alter session set parallel_instance_group='rac'; 

Session altered.

SQL> show parameter parallel_instance_group

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_instance_group              string      rac
SQL> set autotrace on
SQL> select /*+ parallel(t1,60) */ count(*) from t1;

  COUNT(*)
----------
  11121536


Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320

--------------------------------------------------------------------------------
------------------------

| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    T
Q  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------
------------------------

|   0 | SELECT STATEMENT       |          |     1 |   793   (0)| 00:00:01 |
   |      |            |

|   1 |  SORT AGGREGATE        |          |     1 |            |          |
   |      |            |

|   2 |   PX COORDINATOR       |          |       |            |          |
   |      |            |

|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,
00 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,
00 | PCWP |            |

|   5 |      PX BLOCK ITERATOR |          |    10M|   793   (0)| 00:00:01 |  Q1,
00 | PCWC |            |

|   6 |       TABLE ACCESS FULL| T1       |    10M|   793   (0)| 00:00:01 |  Q1,
00 | PCWP |            |

--------------------------------------------------------------------------------
------------------------


Note
-----
   - dynamic sampling used for this statement (level=5)
   - Degree of Parallelism is 60 because of hint


Statistics
----------------------------------------------------------
        250  recursive calls
          4  db block gets
     162870  consistent gets
     158172  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> SELECT inst_id,sid, event, seq#,p1,p2,p3, wait_time FROM gv$session_wait WHERE upper(event) like ('PX%') ORDER BY 1;
   INST_ID        SID EVENT                                                                  SEQ#         P1         P2         P3  WAIT_TIME
---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
         1          1 PX Deq: Execute Reply                                                 11283        200          1          0          0
         1         35 PX Deq: Execution Msg                                                    45  268566527          1 1113085912          0
         1         86 PX Deq: Execution Msg                                                    36  268566527          1 1039760268          0
         1         85 PX Deq: Execution Msg                                                    42  268566527          1 1039763628          0
         1         84 PX Deq: Execution Msg                                                    40  268566527          1 1039764108          0
         1         83 PX Deq: Execution Msg                                                    49  268566527          2 1039764588          0
         1         81 PX Deq: Execution Msg                                                    42  268566527          1 1113034840          0
         1         80 PX Deq: Execution Msg                                                    33  268566527          1 1113035320          0
         1         78 PX Deq: Execution Msg                                                    37  268566527          1 1113036280          0
         1         77 PX Deq: Execution Msg                                                    54  268566527          1 1113058080          0
         1         75 PX Deq: Execution Msg                                                     2  268566527          1 1039758828         -1
         1         74 PX Deq: Execution Msg                                                    50  268566527          2 1113088316          0
         1         73 PX Deq: Execution Msg                                                    54  268566527          1 1113037780          0
         1         72 PX Deq: Execution Msg                                                    50  268566527          1 1113062540          0
         1         70 PX Deq: Execution Msg                                                    46  268566527          1 1113063500          0
         1         69 PX Deq: Execution Msg                                                    54  268566527          1 1113038740          0
         1         68 PX Deq: Execution Msg                                                    60  268566527          1 1113060560          0
         1         66 PX Deq: Execution Msg                                                    43  268566527          2 1113061520          0
         1         65 PX Deq: Execution Msg                                                    56  268566527          1 1113039760          0
         1         63 PX Deq: Execution Msg                                                    64  268566527          1 1113040780          0
         1         62 PX Deq: Execution Msg                                                    59  268566527          1 1113040240          0
         1         61 PX Deq: Execution Msg                                                    69  268566527          1 1113059600          0
         1         60 PX Deq: Execution Msg                                                    70  268566527          2 1113057120          0
         1         58 PX Deq: Execution Msg                                                    46  268566527          2 1113041260          0
         1         50 PX Deq: Execute Reply                                                  5402        200          1          0          0
         1         36 PX Deq: Execution Msg                                                    41  268566527          1 1113037300          0
         2         29 PX Deq: Execution Msg                                                   175  268566527          2 1113084304          0
         2         79 PX Deq: Execution Msg                                                    99  268566527          2 1113067788          0
         2         77 PX Deq: Execution Msg                                                   123  268566527          2 1113068868          0
         2         76 PX Deq: Execution Msg                                                   119  268566527          2 1113069348          0
         2         72 PX Deq: Execution Msg                                                   146  268566527          2 1113071268          0
         2         71 PX Deq: Execution Msg                                                   131  268566527          2 1113071868          0
         2         70 PX Deq: Execution Msg                                                   105  268566527          2 1113072348          0
         2         69 PX Deq: Execution Msg                                                   147  268566527          2 1113072828          0
         2         68 PX Deq: Execution Msg                                                   109  268566527          2 1113073308          0
         2         63 PX Deq: Execution Msg                                                   155  268566527          2 1113087304          0
         2         62 PX Deq: Execution Msg                                                   129  268566527          2 1113074268          0
         2         61 PX Deq: Execution Msg                                                   113  268566527          2 1113096428          0
         2         59 PX Deq: Execution Msg                                                   149  268566527          2 1113087784          0
         2         58 PX Deq: Execution Msg                                                   134  268566527          2 1113085264          0
         2         57 PX Deq: Execution Msg                                                   155  268566527          2 1113084784          0
         2         56 PX Deq: Execution Msg                                                   156  268566527          2 1113082384          0
         2         54 PX Deq: Execution Msg                                                   167  268566527          2 1113081784          0
         2         53 PX Deq: Execution Msg                                                   147  268566527          2 1113085864          0
         2         49 PX Deq: Execution Msg                                                   144  268566527          2 1113083344          0
         2         48 PX Deq: Execution Msg                                                   167  268566527          2 1113095304          0
         2         47 PX Deq: Execution Msg                                                   128  268566527          2 1113083824          0
         2         44 PX Deq: Execution Msg                                                    14  268566527          2 1113067308         -1
         2         37 PX Deq: Execution Msg                                                   151  268566527          2 1113086344          0
         2         36 PX Deq: Execution Msg                                                   148  268566527          2 1113082864          0
         2         35 PX Deq: Execution Msg                                                   133  268566527          2 1113094824          0
         2         78 PX Deq: Execution Msg                                                   102  268566527          2 1113068388          0

52 rows selected.

从上面的信息可以看到当instance_groups设置为’rac’,’rac1’,且parallel_instance_group设置为’rac’时,在实例rac1上执行并行查询时并行子进程能在rac1与rac2实例上运行。

将parallel_instance_group设置为rac1时,并行子进程就只能在rac1实例上运行

SQL> alter session set parallel_instance_group='rac1';

Session altered.

SQL> show parameter parallel_instance_group 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_instance_group              string      rac1

SQL> select /*+ parallel(t1,60) */ count(*) from t1;

  COUNT(*)
----------
  11121536


Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320

--------------------------------------------------------------------------------
------------------------

| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    T
Q  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------
------------------------

|   0 | SELECT STATEMENT       |          |     1 |   793   (0)| 00:00:01 |
   |      |            |

|   1 |  SORT AGGREGATE        |          |     1 |            |          |
   |      |            |

|   2 |   PX COORDINATOR       |          |       |            |          |
   |      |            |

|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,
00 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,
00 | PCWP |            |

|   5 |      PX BLOCK ITERATOR |          |    10M|   793   (0)| 00:00:01 |  Q1,
00 | PCWC |            |

|   6 |       TABLE ACCESS FULL| T1       |    10M|   793   (0)| 00:00:01 |  Q1,
00 | PCWP |            |

--------------------------------------------------------------------------------
------------------------


Note
-----
   - dynamic sampling used for this statement (level=5)
   - Degree of Parallelism is 60 because of hint


Statistics
----------------------------------------------------------
        140  recursive calls
          0  db block gets
     161150  consistent gets
     158026  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT inst_id,sid, event, seq#,p1,p2,p3, wait_time FROM gv$session_wait WHERE upper(event) like ('PX%') ORDER BY 1;

   INST_ID        SID EVENT                                                                  SEQ#         P1         P2         P3  WAIT_TIME
---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
         1          1 PX Deq: Execute Reply                                                 12755        200          1          0          0
         1         34 PX Deq: Execution Msg                                                    61  268566527          1 1113057120          0
         1         35 PX Deq: Execution Msg                                                    75  268566527          1 1113075080          0
         1         36 PX Deq: Execution Msg                                                    56  268566527          1 1113058640          0
         1         47 PX Deq: Execution Msg                                                    39  268566527          1 1113071544          0
         1         50 PX Deq: Execute Reply                                                  8426        200          1          0          0
         1         58 PX Deq: Execution Msg                                                    63  268566527          1 1113036280          0
         1         60 PX Deq: Execution Msg                                                    67  268566527          1 1113087836          0
         1         61 PX Deq: Execution Msg                                                    70  268566527          1 1113078440          0
         1         62 PX Deq: Execution Msg                                                    51  268566527          1 1113085912          0
         1         63 PX Deq: Execution Msg                                                    64  268566527          1 1039759788          0
         1         64 PX Deq: Execution Msg                                                    87  268566527          1 1113077960          0
         1         65 PX Deq: Execution Msg                                                    51  268566527          1 1113088316          0
         1         67 PX Deq: Execution Msg                                                    58  268566527          1 1113062540          0
         1         68 PX Deq: Execution Msg                                                    67  268566527          1 1113077000          0
         1         69 PX Deq: Execution Msg                                                    50  268566527          1 1113063500          0
         1         70 PX Deq: Execution Msg                                                    55  268566527          1 1113076520          0
         1         72 PX Deq: Execution Msg                                                    63  268566527          1 1113076040          0
         1         73 PX Deq: Execution Msg                                                    60  268566527          1 1113061520          0
         1         74 PX Deq: Execution Msg                                                    64  268566527          1 1113075560          0
         1         75 PX Deq: Execution Msg                                                    75  268566527          1 1113072052          0
         1         76 PX Deq: Execution Msg                                                    45  268566527          1 1039764108          0
         1         78 PX Deq: Execution Msg                                                    48  268566527          1 1113074060          0
         1         79 PX Deq: Execution Msg                                                    53  268566527          1 1113073520          0
         1         80 PX Deq: Execution Msg                                                    73  268566527          1 1113058080          0
         1         81 PX Deq: Execution Msg                                                    55  268566527          1 1113073040          0
         1         82 PX Deq: Execution Msg                                                    64  268566527          1 1039759308          0
         1         83 PX Deq: Execution Msg                                                    63  268566527          1 1113072560          0
         1         84 PX Deq: Execution Msg                                                    58  268566527          1 1039758348          0
         1         86 PX Deq: Execution Msg                                                    55  268566527          1 1113059600          0
         1         87 PX Deq: Execution Msg                                                    79  268566527          1 1113074540          0
         1         88 PX Deq: Execution Msg                                                    35  268566527          1 1113035320          0
         1         89 PX Deq: Execution Msg                                                    38  268566527          1 1113070976          0
         1         92 PX Deq: Execution Msg                                                    38  268566527          1 1039753980          0
         1         93 PX Deq: Execution Msg                                                    38  268566527          1 1113069840          0
         1         94 PX Deq: Execution Msg                                                    54  268566527          1 1039753500          0
         1         95 PX Deq: Execution Msg                                                    51  268566527          1 1113069332          0
         1         96 PX Deq: Execution Msg                                                    49  268566527          1 1039753020          0
         1         98 PX Deq: Execution Msg                                                     2  268566527          1 1113063980         -1

40 rows selected.

从上面的信息可以看到当instance_groups设置为’rac’,’rac1’,且parallel_instance_group设置为’rac1’时,在实例rac1上执行并行查询时并行子进程只能在rac1上运行。

parallel_instance_group
在Oracle 10g中 parallel_instance_group的参数值必须是instance_groups的参数列表之一。在11g中这个列表已经扩展包含了所有当前被启用的服务。因此,parallel_instance_group在11g中它的值可以在instance_groups的参数值中找到数据库当前已经启用的服务名之一

parallel_force_local
在11gr2中,parallel_force_local控制着Oracle RAC环境中的并行执行。缺省情况下, 并行执行的并行子进程可以在任何RAC节点或所有节点上运行。通过设置parallel_force_lcoal为true,并行子进程将会受限制,因此它们只能在与查询协调者(QC)所在实例上执行。

SQL> alter session set parallel_force_local=true;

Session altered.

SQL> show parameter parallel_force_local

NAME TYPE VALUE
———————————— ———– ——————————
parallel_force_local boolean TRUE

SQL> select /*+ parallel(t1,60) */ count(*) from t1;

COUNT(*)
———-
11121536

Execution Plan
———————————————————-
Plan hash value: 3110199320

——————————————————————————–
————————

| Id | Operation | Name | Rows | Cost (%CPU)| Time | T
Q |IN-OUT| PQ Distrib |

——————————————————————————–
————————

| 0 | SELECT STATEMENT | | 1 | 793 (0)| 00:00:01 |
| | |

| 1 | SORT AGGREGATE | | 1 | | |
| | |

| 2 | PX COORDINATOR | | | | |
| | |

| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,
00 | P->S | QC (RAND) |

| 4 | SORT AGGREGATE | | 1 | | | Q1,
00 | PCWP | |

| 5 | PX BLOCK ITERATOR | | 10M| 793 (0)| 00:00:01 | Q1,
00 | PCWC | |

| 6 | TABLE ACCESS FULL| T1 | 10M| 793 (0)| 00:00:01 | Q1,
00 | PCWP | |

——————————————————————————–
————————

Note
—–
– dynamic sampling used for this statement (level=5)
– Degree of Parallelism is 60 because of hint

Statistics
———————————————————-
140 recursive calls
0 db block gets
161150 consistent gets
158026 physical reads
0 redo size
425 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> SELECT inst_id,sid, event, seq#,p1,p2,p3, wait_time FROM gv$session_wait WHERE upper(event) like (‘PX%’) ORDER BY 1;

INST_ID SID EVENT SEQ# P1 P2 P3 WAIT_TIME
———- ———- —————————————————————- ———- ———- ———- ———- ———-
1 1 PX Deq: Execute Reply 12755 200 1 0 0
1 34 PX Deq: Execution Msg 61 268566527 1 1113057120 0
1 35 PX Deq: Execution Msg 75 268566527 1 1113075080 0
1 36 PX Deq: Execution Msg 56 268566527 1 1113058640 0
1 47 PX Deq: Execution Msg 39 268566527 1 1113071544 0
1 50 PX Deq: Execute Reply 8426 200 1 0 0
1 58 PX Deq: Execution Msg 63 268566527 1 1113036280 0
1 60 PX Deq: Execution Msg 67 268566527 1 1113087836 0
1 61 PX Deq: Execution Msg 70 268566527 1 1113078440 0
1 62 PX Deq: Execution Msg 51 268566527 1 1113085912 0
1 63 PX Deq: Execution Msg 64 268566527 1 1039759788 0
1 64 PX Deq: Execution Msg 87 268566527 1 1113077960 0
1 65 PX Deq: Execution Msg 51 268566527 1 1113088316 0
1 67 PX Deq: Execution Msg 58 268566527 1 1113062540 0
1 68 PX Deq: Execution Msg 67 268566527 1 1113077000 0
1 69 PX Deq: Execution Msg 50 268566527 1 1113063500 0
1 70 PX Deq: Execution Msg 55 268566527 1 1113076520 0
1 72 PX Deq: Execution Msg 63 268566527 1 1113076040 0
1 73 PX Deq: Execution Msg 60 268566527 1 1113061520 0
1 74 PX Deq: Execution Msg 64 268566527 1 1113075560 0
1 75 PX Deq: Execution Msg 75 268566527 1 1113072052 0
1 76 PX Deq: Execution Msg 45 268566527 1 1039764108 0
1 78 PX Deq: Execution Msg 48 268566527 1 1113074060 0
1 79 PX Deq: Execution Msg 53 268566527 1 1113073520 0
1 80 PX Deq: Execution Msg 73 268566527 1 1113058080 0
1 81 PX Deq: Execution Msg 55 268566527 1 1113073040 0
1 82 PX Deq: Execution Msg 64 268566527 1 1039759308 0
1 83 PX Deq: Execution Msg 63 268566527 1 1113072560 0
1 84 PX Deq: Execution Msg 58 268566527 1 1039758348 0
1 86 PX Deq: Execution Msg 55 268566527 1 1113059600 0
1 87 PX Deq: Execution Msg 79 268566527 1 1113074540 0
1 88 PX Deq: Execution Msg 35 268566527 1 1113035320 0
1 89 PX Deq: Execution Msg 38 268566527 1 1113070976 0
1 92 PX Deq: Execution Msg 38 268566527 1 1039753980 0
1 93 PX Deq: Execution Msg 38 268566527 1 1113069840 0
1 94 PX Deq: Execution Msg 54 268566527 1 1039753500 0
1 95 PX Deq: Execution Msg 51 268566527 1 1113069332 0
1 96 PX Deq: Execution Msg 49 268566527 1 1039753020 0
1 98 PX Deq: Execution Msg 2 268566527 1 1113063980 -1

使用parallel_force_local要并使用instance_groups与parallel_instance_group参数来进行限制要方便很多。

Oracle 10g RAC跨实例控制并行

在Oracle 10g RAC中的并行执行由两个参数:instance_groups和parallel_instance_group来控制。并且这两个参数必须同时使用才能生效。

instance_groups可以设置多个参数值,但它是静态参数,parallel_instance_groups可以在系统级别和会话级别运态进行修改。

在Oracle 10g RAC中,为了让并行执行可以在设置了parallel_instance_group参数的会话中执行,那么parallel_instance_group的参数值必须是该实例instance_groups参数列表中的一个。

例如,两节点的Oracle 10g RAC,在spfile文件中有以下设置
myrac1.instance_groups=’jyrac’,’jyrac1′
myrac2.instance_groups=’jyrac’,’jyrac2′
myrac1.parallel_instance_group=’jyrac1’#节点1的并行执行只能在节点1上运行
myrac2.parallel_instance_group=’jyrac2’#节点2的并行执行只能在节点2上运行

SQL> alter system set instance_groups='jyrac','jyrac1' scope=spfile sid='jyrac1';

System altered.

SQL> alter system set instance_groups='jyrac','jyrac2' scope=spfile sid='jyrac2';

SQL> alter system set parallel_instance_group='jyrac1' scope=spfile sid='jyrac1';

System altered.

SQL> alter system set parallel_instance_group='jyrac2' scope=spfile sid='jyrac2';

System altered.

上面的设置意味着在任何节点上启动的并行只能在该节点上执行。

SQL> show parameter instance_group

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_groups                      string      jyrac, jyrac1
parallel_instance_group              string      jyrac1

SQL>set autotrace on

SQL> select /*+ parallel(t1,20) */ count(*) from t1;

  COUNT(*)
----------
  25962496


Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |  4320   (1)| 00:00:52 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |    27M|  4320   (1)| 00:00:52 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T1       |    27M|  4320   (1)| 00:00:52 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         45  recursive calls
          0  db block gets
     532872  consistent gets
     355028  physical reads
       2132  redo size
        414  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>SELECT inst_id,sid, event, seq#,p1,p2,p3, wait_time FROM gv$session_wait WHERE upper(event) like ('PX%') ORDER BY 1;

   INST_ID        SID EVENT                                                                  SEQ#         P1         P2         P3  WAIT_TIME
---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
         1        124 PX Deq: Execution Msg                                                     1  268566527          1          0         -1
         1        126 PX Deq: Execution Msg                                                  8409  268566527          1          0         11
         1        127 PX Deq: Execution Msg                                                  7775  268566527          1          0          0
         1        128 PX Deq: Execution Msg                                                  8462  268566527          1          0          0
         1        129 PX Deq: Execute Reply                                                  3151        200          1          0          0
         1        134 PX Deq: Execution Msg                                                  9208  268566527          1          0          0
         1        136 PX Deq: Execution Msg                                                  9136  268566527          1          0         20
         1        140 PX Deq: Execution Msg                                                  8348  268566527          1          0          0
         1        153 PX Deq: Signal ACK                                                    59131         10          3          0          0

从上面的信息可以看到确实在实例jyrac1上执行的并行查询并行子进程只在jyrac1实例上运行

SQL> show parameter instance_group

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_groups                      string      jyrac, jyrac2
parallel_instance_group              string      jyrac2

SQL> select /*+ parallel(t1,20) */ count(*) from t1;

  COUNT(*)
----------
  25962496


Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |  4320   (1)| 00:00:52 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |    27M|  4320   (1)| 00:00:52 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T1       |    27M|  4320   (1)| 00:00:52 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         60  recursive calls
          0  db block gets
     533765  consistent gets
     354821  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>SELECT inst_id,sid, event, seq#,p1,p2,p3, wait_time FROM gv$session_wait WHERE upper(event) like ('PX%') ORDER BY 1;

   INST_ID        SID EVENT                                                                  SEQ#         P1         P2         P3  WAIT_TIME
---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
         2        120 PX Deq: Execution Msg                                                     1  268632063          1          0          0
         2        121 PX Deq: reap credit                                                      13          0          0          0         -1
         2        122 PX Deq: Execution Msg                                                     1  268632063          1          0          0
         2        129 PX Deq: Execution Msg                                                     1  268632063          1          0          0
         2        153 PX Deq: Join ACK                                                       3664  268566529          8          0          0
         2        131 PX Deq: Execution Msg                                                     1  268632063          1          0          0
         2        132 PX Deq: Execution Msg                                                     1  268632063          1          0          0
         2        133 PX Deq: Execution Msg                                                     1  268632063          1          0          0
         2        135 PX Deq: Execution Msg                                                     1  268632063          1          0          0
         2        139 PX Deq: Execution Msg                                                     1  268632063          1          0          0
         2        140 PX Deq: Execution Msg                                                     1  268632063          1          0          0
         2        141 PX Deq: Execution Msg                                                     1  268632063          1          0          0
         2        147 PX Deq: Execution Msg                                                     1  268632063          1          0          0
         2        119 PX Deq: Execution Msg                                                     1  268632063          1          0          0
         2        118 PX Deq: Execution Msg                                                     1  268632063          1          0          0
         2        130 PX Deq: Execution Msg                                                     1  268632063          1          0          0

从上面的信息可以看到确实在实例jyrac2上执行的并行查询并行子进程只在jyrac2实例上运行

可以根据需要使用相同的实例组来完成不同的需求
myrac1.instance_groups=’jyrac’,’jyrac1′
myrac2.instance_groups=’jyrac’,’jyrac2′
myrac1.parallel_instance_group=’jyrac1’#节点1的并行执行只能在节点1上运行
myrac2.parallel_instance_group=’jyrac1’#节点2的并行执行只能在节点1上运行
myrac1.parallel_instance_group=’jyrac’#节点1的并行执行能在所有节点上运行

将实例jyrac1的parallel_instance_group设置为’jyrac1’来测试节点1的并行执行是否只能在节点1上运行

SQL> show parameter instance_group

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_groups                      string      jyrac, jyrac1
parallel_instance_group              string      jyrac1

SQL>set autotrace on

SQL> select /*+ parallel(t1,20) */ count(*) from t1;

  COUNT(*)
----------
  25962496


Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |  4320   (1)| 00:00:52 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |    27M|  4320   (1)| 00:00:52 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T1       |    27M|  4320   (1)| 00:00:52 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         45  recursive calls
          0  db block gets
     532872  consistent gets
     355028  physical reads
       2132  redo size
        414  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>SELECT inst_id,sid, event, seq#,p1,p2,p3, wait_time FROM gv$session_wait WHERE upper(event) like ('PX%') ORDER BY 1;

   INST_ID        SID EVENT                                                                  SEQ#         P1         P2         P3  WAIT_TIME
---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
         1        124 PX Deq: Execution Msg                                                     1  268566527          1          0         -1
         1        126 PX Deq: Execution Msg                                                  8409  268566527          1          0         11
         1        127 PX Deq: Execution Msg                                                  7775  268566527          1          0          0
         1        128 PX Deq: Execution Msg                                                  8462  268566527          1          0          0
         1        129 PX Deq: Execute Reply                                                  3151        200          1          0          0
         1        134 PX Deq: Execution Msg                                                  9208  268566527          1          0          0
         1        136 PX Deq: Execution Msg                                                  9136  268566527          1          0         20
         1        140 PX Deq: Execution Msg                                                  8348  268566527          1          0          0
         1        153 PX Deq: Signal ACK                                                    59131         10          3          0          0

从上面的信息可以看到确实在实例jyrac1上执行的并行查询并行子进程只在jyrac1实例上运行将实例jyrac2的parallel_instance_group设置为’jyrac1’来测试节点2的并行执行是否只能在节点1上运行

SQL> alter session set parallel_instance_group='jyrac1';

Session altered.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
jyrac2

SQL> show parameter instance_group

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_groups                      string      jyrac, jyrac2
parallel_instance_group              string      jyrac1



SQL> select /*+ parallel(t1,20) */ count(*) from t1;

  COUNT(*)
----------
  25962496


Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |  4320   (1)| 00:00:52 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |    27M|  4320   (1)| 00:00:52 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T1       |    27M|  4320   (1)| 00:00:52 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         21  recursive calls
          0  db block gets
     532751  consistent gets
     354823  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT inst_id,sid, event, seq#,p1,p2,p3, wait_time FROM gv$session_wait WHERE upper(event) like ('PX%') ORDER BY 1;
   INST_ID        SID EVENT                                                                  SEQ#         P1         P2         P3  WAIT_TIME
---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
         1        125 PX Deq: Execution Msg                                                  8662  268632063          2  699436536          0
         1        126 PX Deq: Execution Msg                                                  9290  268632063          2  699434348          0
         1        127 PX Deq: Execution Msg                                                  8000  268632063          2  699433976          0
         1        129 PX Deq: Execute Reply                                                  7722        200          1          0          0
         1        136 PX Deq: Execution Msg                                                     1  268566527          1          0         -1
         1        148 PX Deq: Execution Msg                                                  8697  268632063          2  699439808          0
         1        137 PX Deq: Execution Msg                                                  8719  268632063          3  699432860          0
         1        140 PX Deq: Execution Msg                                                  8660  268632063          2  699433604          0

从上面的信息可以看到在实例jyrac2执行并行查询的并行子进程确实在jyrac1实例上运行的。

将实例jyrac1的parallel_instance_group设置为jyrac,来测试在jyrac1实例上执行并行查询的并行子进程将会在实例jyrac1,jyrac2上运行

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
jyrac1

SQL> alter session set parallel_instance_group='jyrac';

Session altered.

SQL> show parameter instance_group

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_groups                      string      jyrac, jyrac1
parallel_instance_group              string      jyrac


SQL> select /*+ parallel(t1,20) */ count(*) from t1;

  COUNT(*)
----------
  25962496


Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |  4320   (1)| 00:00:52 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |    27M|  4320   (1)| 00:00:52 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T1       |    27M|  4320   (1)| 00:00:52 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         59  recursive calls
          0  db block gets
     533297  consistent gets
     354823  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT inst_id,sid, event, seq#,p1,p2,p3, wait_time FROM gv$session_wait WHERE upper(event) like ('PX%') ORDER BY 1;

   INST_ID        SID EVENT                                                                  SEQ#         P1         P2         P3  WAIT_TIME
---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
         1        125 PX Deq: Execution Msg                                                  4678  268566527          1          0          0
         1        126 PX Deq: Execution Msg                                                  4637  268566527          1          0          0
         1        128 PX Deq: Execution Msg                                                  4658  268566527          1          0          0
         1        129 PX Deq: Execute Reply                                                 10795        200          1          0          0
         1        130 PX Deq: Execution Msg                                                     1  268566527          1          0         -1
         1        140 PX Deq: Execution Msg                                                  4627  268566527          1          0          0
         1        148 PX Deq: Execution Msg                                                  4653  268566527          1          0          0
         1        153 PX Deq: Execute Reply                                                 59889        200          1          0          0
         2        122 PX Deq: Execution Msg                                                  3675  268566527          2  699422552          0
         2        124 PX Deq: Execution Msg                                                  3709  268566527          2  699424100          0
         2        126 PX Deq: Execution Msg                                                  4066  268566527          2  699426332          0
         2        135 PX Deq: reap credit                                                      13          0          0          0         -1
         2        139 PX Deq: Execution Msg                                                  4375  268566527          2  699420320          0
         2        141 PX Deq: Execution Msg                                                  4004  268566527          2  699423356          0

从上面的信息可以看到在jyrac1实例上执行并行查询的并行子进程确实在实例jyrac1,jyrac2上运行

Oracle 11.2中控制并行的新参数

在Oracle 11.2中引入了几个新的并行查询参数。对于数据仓库应用来说经常利用并行处理来快速有效地处理信息,尤其是查询非常大的表或加入了复杂的算式更应该使用并行查询。在Oracle之前的版本中,我们不得不或多或秒的来决定自动并行度。决定一个最佳并行度是非常困难的。真实最佳并行度依赖于数据块在磁盘上的物理位置以及服务器的CPU数量(cpu_count),为了解决并行查询的这些问题
在Oracle11.2中引入了以下新的并行查询参数
1.parallel_degree_policy
parallel_degree_policy参数可以被设置为manual,auto或limited在Oracle11.1中parallel_degree_policy缺省设置为manual(禁用了automatic degree of parallelism,statement queuing与in-memory parallel execution)


SQL> show parameter parallel_degree_policy;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy               string      manual
SQL> set autotrace on   
SQL> select count(*) from t1;

  COUNT(*)
----------
  22040576


Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 | 84998   (1)| 00:00:06 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   |    21M| 84998   (1)| 00:00:06 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         22  recursive calls
          0  db block gets
     469904  consistent gets
     313229  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

但我们可以手动指定并行度

SQL> show parameter parallel_degree_policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy               string      manual
SQL> set autotrace on
SQL> select /*+ parallel */ count(*) from t1;

  COUNT(*)
----------
  22040576


Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 | 47183   (1)| 00:00:04 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |    21M| 47183   (1)| 00:00:04 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T1       |    21M| 47183   (1)| 00:00:04 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=5)
   - automatic DOP: Computed Degree of Parallelism is 2


Statistics
----------------------------------------------------------
         20  recursive calls
          4  db block gets
     470138  consistent gets
     313225  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

parallel_degree_policy=auto就会启用以下新功能:
并行度(DOP)将会基于SQL语句中的操作类型和表的大小来自动计算。例如对大表排序的并行度(DOP)可能比对小表操作的并行度高。

如果请求或请求的并行度(DOP)因为并行服务进程正处于繁忙状态而不能获得满足,那么Oracle直到有足够的并行子进程可用之前将不会执行语句,而不是降低并行度或串行执行SQL语句。在11gr2之前的版本中,当没有足够的并行进程服务进程满足所请求的并行度(DOP)时,可以会出现以下三种情况中的一种:
SQL语句将会降低并行度(DOP)来以并行方式执行
SQL语句以串行方式来执行
如果parallel_min_percent被设置将收到”ORA-12827:insufficient parallel query slaves available”

Oracle并行子进程可能使用buffered IO而不是直接IO。例如”in-memory parallel execution”

SQL> show parameter parallel_degree_policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy               string      AUTO

SQL> select degree,instances from user_tables where table_name = 'T1';

DEGREE               INSTANCES
-------------------- --------------------
         1                    1

Elapsed: 00:00:00.00
SQL> set autotrace on
SQL> select count(*) from t1;

  COUNT(*)
----------
  22040576

Elapsed: 00:00:18.50

Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 | 47183   (1)| 00:00:04 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |    21M| 47183   (1)| 00:00:04 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T1       |    21M| 47183   (1)| 00:00:04 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
   - automatic DOP: Computed Degree of Parallelism is 2


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     469841  consistent gets
     313226  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

parallel_degree_policy设置为limited
对某些语句启用自动并行度,但statement queuing与in-memory parallel execution被禁用。只会对访问使用parallel子句来设置DEFAULT并行度的表或索引应用自动并行度。

SQL> select degree,instances from user_tables where table_name = 'T1';

DEGREE               INSTANCES
-------------------- --------------------
         1                    1

SQL> show parameter parallel_degree_policy 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy               string      LIMITED
SQL> set autotrace on;
SQL> select count(*) from t1;

  COUNT(*)
----------
  22040576


Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 | 84998   (1)| 00:00:06 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   |    21M| 84998   (1)| 00:00:06 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
     469898  consistent gets
     313399  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

因为表的并行度是1,而不是default,现在使用parallel子句来修改表t1的并行度

SQL> alter table t1 parallel;

Table altered.

SQL> select degree,instances from user_tables where table_name = 'T1';

DEGREE               INSTANCES
-------------------- --------------------
   DEFAULT              DEFAULT

SQL> set autotrace on
SQL> select count(*) from t1;

  COUNT(*)
----------
  22040576


Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 | 47183   (1)| 00:00:04 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |    21M| 47183   (1)| 00:00:04 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T1       |    21M| 47183   (1)| 00:00:04 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=5)
   - automatic DOP: Computed Degree of Parallelism is 2


Statistics
----------------------------------------------------------
         83  recursive calls
          0  db block gets
     470167  consistent gets
     313413  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed



2.parallel_min_time_threshold
parallel_min_time_threshold参数用来指定SQL语句是否并行执行一个阈值,也就是当优化器根据统计信息所估算的执行时间如果大于这个参数值就是使用并行,如果估算的执行时间小于这个参数值就会串行执行。这个参数值缺省值是10秒。并且自动并行度只要在parallel_degree_policy参数被设置为auto或limited时才会生效。从下面的信息可以看到到语句的执行时间小于10秒时,优化器以是串行而不是并行方式来执行的

SQL> show parameter parallel_degree_policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy               string      AUTO
SQL> set autotrace on
SQL> select count(*) from t1;

  COUNT(*)
----------
   2755072

Elapsed: 00:00:02.66

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 | 10627   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   |  2569K| 10627   (1)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      57150  consistent gets
      39162  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

再次增加表t1的数据记录

SQL> insert into t1 select * from t1;

5510144 rows created.


SQL> commit;

Commit complete.



SQL> alter system flush buffer_cache;

System altered.

SQL> set autotrace on
SQL> select count(*) from t1;

  COUNT(*)
----------
  11020288

Elapsed: 00:00:09.05

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 | 42507   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   |    11M| 42507   (1)| 00:00:03 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     223549  consistent gets
     156619  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看到执行时间为9.05秒,Oracle使用串行执行,继续向表t1增加记录

SQL> insert into t1 select * from t1;

11020288 rows created.


SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

SQL> show parameter parallel_degree_policy 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy               string      AUTO
SQL> select * from V$IO_CALIBRATION_STATUS;

STATUS        CALIBRATION_TIME
------------- ---------------------------------------------------------------------------
READY         13-APR-16 10.12.58.413 PM

Elapsed: 00:00:00.08
SQL> set autotrace on
SQL> select count(*) from t1;

  COUNT(*)
----------
  22040576

Elapsed: 00:00:18.50

Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 | 47183   (1)| 00:00:04 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |    21M| 47183   (1)| 00:00:04 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T1       |    21M| 47183   (1)| 00:00:04 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
   - automatic DOP: Computed Degree of Parallelism is 2


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     469841  consistent gets
     313226  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

现在可以看到当parallel_degree_policy=auto,parallel_min_time_threshold=auto时,sql执行时间超长10秒时就会使用自动并行。

3.parallel_degree_limit
使用自动并行度时,Oracle会自动决定是否以并行方式来执行SQL语句以及所使用的并行度。优化根据语句所请求的资源来决定一个语句的并行度。然而优化器所使用的并行度是受限制的以防止并行进程击垮系统。也就是是系统中所能使用的并行度的上限为parallel_degree_limit参数值。它有三个参数值可以选择:
CPU
最大并行度由系统中的CPU数量来限制。其计算公式为parallel_degree_limit=parallel_thread_per_cpu*cpu_count
当然,你也可以将parallel_degree_limit的值设置为一个具体的值,以达到明确控制实际并行度的目的。

IO
优化器能使用的最大并行度由系统的I/O能力来限制。这个值等于系统总吞吐量除以每个进程的最大I/O带宽。但在Oracle 11.2中为了将parallel_degree_limit设置为IO必须执行dbms_resource_manager.calibrate_io过程来收集系统的I/O统计信息。这个过程将会计算系统的总吞吐量与每个进程的最大IO带宽。

具体数字
当自动并行度被激活时,指定一个SQL语句所能使用的最大并行度。这个参数只有当parallel_degree_policy设置为auto或limited时才生效。

4.parallel_force_local
parallel_force_local参数控制RAC环境中的并行执行。缺省情况下,优化器可以从RAC中的任何节点或所有节点中选择并行执行SQL语句的并行进程。当parallel_force_local设置为true时,那么并行进程就只能是与查询协调者(执行sql语句的节点)在同一个RAC节点中,也就是说并行进程是不能跨节点的.

dbca -silent创建rac数据库

刚好有一个网友问我,dbca -silent是否能创建rac数据库,是否能启用归档,下面是测试过程
一.先删除已经存在的rac数据库jyrac

ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576      5120     4892                0            4892              0             N  BACKUPDG/
MOUNTED  EXTERN  N         512   4096  1048576      5120     4724                0            4724              0             Y  CRSDG/
MOUNTED  EXTERN  N         512   4096  1048576     10240        2                0               2              0             N  DATADG/
ASMCMD> ls -lt
Type           Redund  Striped  Time             Sys  Name
                                                 Y    TEMPFILE/
                                                 Y    PARAMETERFILE/
                                                 Y    ONLINELOG/
                                                 Y    DATAFILE/
                                                 Y    CONTROLFILE/
                                                 N    spfilejyrac.ora => +DATADG/JYRAC/PARAMETERFILE/spfile.268.864825131

[oracle@jyrac1 ~]$ dbca -silent -deleteDatabase -sourceDB jyrac -sysDBAUserName sys -sysDBAPassword system
Connecting to database
9% complete
14% complete
19% complete
23% complete
28% complete
33% complete
38% complete
47% complete
Updating network configuration files
48% complete
52% complete
Deleting instances and datafiles
66% complete
80% complete
95% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/jyrac.log" for further details.
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576      5120     4892                0            4892              0             N  BACKUPDG/
MOUNTED  EXTERN  N         512   4096  1048576      5120     4724                0            4724              0             Y  CRSDG/
MOUNTED  EXTERN  N         512   4096  1048576     10240    10138                0           10138              0             N  DATADG/
[root@jyrac1 jyrac]# ps -ef | grep pmon
grid      4185     1  0 Jan26 ?        00:01:59 asm_pmon_+ASM1
root     32023 26194  0 16:57 pts/3    00:00:00 grep pmon

[grid@jyrac2 ~]$ ps -ef | grep pmon
grid      4144     1  0 Jan26 ?        00:02:03 asm_pmon_+ASM2
grid     30617 22435  0 16:57 pts/1    00:00:00 grep pmon

说明rac数据库jyrac确实已经删除了,下面以dbca -silent模式来创建rac数据库jyrac

二.使用dbca -silent来创建rac数据库jyrac,并使用-initParams参数设置归档目录,使用-nodeinfo来指定rac节点,但-nodeinfo参数在dbca的帮助信息中并没有显示。

[oracle11@oracle11g ~]$ dbca -help
dbca  [-silent | -progressOnly | -customCreate] {  }  | { [ [options] ] -responseFile   } [-continueOnNonFatalErrors ]
Please refer to the manual for details.
You can enter one of the following command:

Create a database by specifying the following parameters:
        -createDatabase
                -templateName 
                [-cloneTemplate]
                -gdbName 
                [-sid ]
                [-sysPassword ]
                [-systemPassword ]
                [-emConfiguration 
                        -dbsnmpPassword 
                        -sysmanPassword 
                        [-hostUserName 
                         -hostUserPassword 
                         -backupSchedule ]
                        [-centralAgent ]]
                [-disableSecurityConfiguration 
                [-datafileDestination  |  -datafileNames ]
                [-redoLogFileSize ]
                [-recoveryAreaDestination ]
                [-datafileJarLocation  ]
                [-storageType < FS | ASM >
                        [-asmsnmpPassword     ]
                         -diskGroupName   
                         -recoveryGroupName       
                [-characterSet ]
                [-nationalCharacterSet  ]
                [-registerWithDirService 
                        -dirServiceUserName    
                        -dirServicePassword    
                        -walletPassword    ]
                [-listeners  ]
                [-variablesFile   ]]
                [-variables  ]
                [-initParams ]
                [-sampleSchema   ]
                [-memoryPercentage ]
                [-automaticMemoryManagement ]
                [-totalMemory ]
                [-databaseType ]]

Configure a database by specifying the following parameters:
        -configureDatabase
                -sourceDB    
                [-sysDBAUserName     
                 -sysDBAPassword     ]
                [-registerWithDirService|-unregisterWithDirService|-regenerateDBPassword 
                        -dirServiceUserName    
                        -dirServicePassword    
                        -walletPassword    ]
                [-disableSecurityConfiguration 
                [-enableSecurityConfiguration 
                [-emConfiguration 
                        -dbsnmpPassword 
                        -sysmanPassword 
                        [-hostUserName 
                         -hostUserPassword 
                         -backupSchedule ]
                        [-centralAgent ]]


Create a template from an existing database by specifying the following parameters:
        -createTemplateFromDB
                -sourceDB    ::>
                -templateName      
                -sysDBAUserName     
                -sysDBAPassword     
                [-maintainFileLocations ]


Create a clone template from an existing database by specifying the following parameters:
        -createCloneTemplate
                -sourceSID    
                -templateName      
                [-sysDBAUserName     
                 -sysDBAPassword     ]
                [-maintainFileLocations ]
                [-datafileJarLocation       ]

Generate scripts to create database by specifying the following parameters:
        -generateScripts
                -templateName 
                -gdbName 
                [-scriptDest       ]

Delete a database by specifying the following parameters:
        -deleteDatabase
                -sourceDB    
                [-sysDBAUserName     
                 -sysDBAPassword     ]
Query for help by specifying the following options: -h | -help

下面创建rac数据库jyrac,使用-initParams参数设置了归档目录为+backupdg/,节点为jyrac1,jyrac2

[oracle@jyrac1 ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname jyrac  -sid jyrac -datafileDestination 'datadg/' -redoLogFileSize 50 -recoveryAreaDestination 'datadg/' -storageType ASM -asmsnmpPassword system  -diskGroupName 'datadg' -responseFile NO_VALUE -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -automaticMemoryManagement true -totalMemory 500 -initParams log_archive_dest_1='location=+backupdg/' -nodeinfo jyrac1,jyrac2
Enter SYS user password:

Enter SYSTEM user password:

Copying database files
1% complete
3% complete
9% complete
15% complete
21% complete
27% complete
30% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
44% complete
45% complete
48% complete
50% complete
Creating cluster database views
52% complete
70% complete
Completing Database Creation
73% complete
76% complete
85% complete
94% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/jyrac/jyrac.log" for further details.

三.检查数据库状态

[root@jyrac1 ~]# ps -ef | grep pmon
grid      4185     1  0 Jan26 ?        00:01:59 asm_pmon_+ASM1
oracle    6545     1  0 17:33 ?        00:00:00 ora_pmon_jyrac1
root      7059  6061  0 17:36 pts/4    00:00:00 grep pmon
[grid@jyrac2 ~]$  ps -ef | grep pmon
grid      4144     1  0 Jan26 ?        00:02:03 asm_pmon_+ASM2
oracle    6320     1  0 17:34 ?        00:00:00 ora_pmon_jyrac2
grid      6756 22435  0 17:36 pts/1    00:00:00 grep pmon

[grid@jyrac2 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.BACKUPDG.dg
               ONLINE  ONLINE       jyrac1
               ONLINE  ONLINE       jyrac2
ora.CRSDG.dg
               ONLINE  ONLINE       jyrac1
               ONLINE  ONLINE       jyrac2
ora.DATADG.dg
               ONLINE  ONLINE       jyrac1
               ONLINE  ONLINE       jyrac2
ora.LISTENER.lsnr
               ONLINE  ONLINE       jyrac1
               ONLINE  ONLINE       jyrac2
ora.asm
               ONLINE  ONLINE       jyrac1                   Started
               ONLINE  ONLINE       jyrac2                   Started
ora.gsd
               OFFLINE OFFLINE      jyrac1
               OFFLINE OFFLINE      jyrac2
ora.net1.network
               ONLINE  ONLINE       jyrac1
               ONLINE  ONLINE       jyrac2
ora.ons
               ONLINE  ONLINE       jyrac1
               ONLINE  ONLINE       jyrac2
ora.registry.acfs
               ONLINE  ONLINE       jyrac1
               ONLINE  ONLINE       jyrac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       jyrac2
ora.cvu
      1        ONLINE  ONLINE       jyrac2
ora.jyrac.db
      1        ONLINE  ONLINE       jyrac1                   Open
      2        ONLINE  ONLINE       jyrac2                   Open
ora.jyrac1.vip
      1        ONLINE  ONLINE       jyrac1
ora.jyrac2.vip
      1        ONLINE  ONLINE       jyrac2
ora.oc4j
      1        ONLINE  ONLINE       jyrac2
ora.scan1.vip
      1        ONLINE  ONLINE       jyrac2


[grid@jyrac2 ~]$ srvctl status database -d jyrac
Instance jyrac1 is running on node jyrac1
Instance jyrac2 is running on node jyrac2

[grid@jyrac2 ~]$ srvctl config database -d jyrac
Database unique name: jyrac
Database name: jyrac
Oracle home: /u01/app/oracle/product/11.2.0/db
Oracle user: oracle
Spfile: +DATADG/jyrac/spfilejyrac.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: jyrac
Database instances: jyrac1,jyrac2
Disk Groups: DATADG
Mount point paths:
Services:
Type: RAC
Database is administrator managed

[grid@jyrac2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-APR-2015 17:39:01

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                26-JAN-2015 10:48:15
Uptime                    81 days 6 hr. 50 min. 46 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/11.2.0/crs/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/jyrac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.154)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "jyrac" has 1 instance(s).
  Instance "jyrac2", status READY, has 1 handler(s) for this service...
Service "jyracXDB" has 1 instance(s).
  Instance "jyrac2", status READY, has 1 handler(s) for this service...
The command completed successfully

[grid@jyrac2 ~]$ lsnrctl status listener_scan1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-APR-2015 17:39:33

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                26-JAN-2015 10:48:10
Uptime                    81 days 6 hr. 51 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/11.2.0/crs/network/admin/listener.ora
Listener Log File         /u01/app/product/11.2.0/crs/log/diag/tnslsnr/jyrac2/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.159)(PORT=1521)))
Services Summary...
Service "jyrac" has 2 instance(s).
  Instance "jyrac1", status READY, has 1 handler(s) for this service...
  Instance "jyrac2", status READY, has 1 handler(s) for this service...
Service "jyracXDB" has 2 instance(s).
  Instance "jyrac1", status READY, has 1 handler(s) for this service...
  Instance "jyrac2", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@jyrac1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-APR-2015 17:39:08

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                26-JAN-2015 10:48:32
Uptime                    81 days 6 hr. 50 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/11.2.0/crs/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/jyrac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.153)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "DBUA4235414" has 1 instance(s).
  Instance "DBUA4235414", status BLOCKED, has 1 handler(s) for this service...
Service "jyrac" has 1 instance(s).
  Instance "jyrac1", status READY, has 1 handler(s) for this service...
Service "jyracXDB" has 1 instance(s).
  Instance "jyrac1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@jyrac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 17 17:40:53 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
jyrac1

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            +BACKUPDG
Oldest online log sequence     4
Current log sequence           5

[oracle@jyrac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 17 17:40:35 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
jyrac2

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            +BACKUPDG
Oldest online log sequence     1
Current log sequence           1

数据库虽然指定了归档目录,但是没有启动归档。

四.使用dbca -silent方式创建rac数据库的使用示例,更详细信息请阅读Real Application Clusters Installation Guide

Using DBCA Noninteractive (Silent) Configuration for Oracle RAC

You can perform a noninteractive, or “silent” configuration of Oracle RAC using DBCA. To perform a silent configuration, you must have completed an Oracle Clusterware installation, run the root.sh script from the Oracle Database or Oracle ASM homes, and defined the Oracle home and ASM home directory environment variables.

The following command syntax creates an ASM instance and one disk group:

# su oracle -c “$ORA_ASM_HOME/bin/dbca -silent -configureASM -gdbName NO -sid NO -emConfiguration NONE -diskList ASM_Disks -diskGroupName ASM_Group_Name -datafileJarLocation $ORACLE_HOME/assistants/dbca/templates -nodeinfo $NODE1,$NODE2 -obfuscatedPasswords false -asmSysPassword My_ASM_password -redundancy ASM_redundancy”

In the preceding syntax example:

ASM_Disks represent disk addresses, such as /dev/sda1,/dev/sdb1
ASM_Group_Name represents the name of an ASM disk group
ASM_ Redundancy represents an ASM disk redundancy setting, such as ‘NORMAL’.
My_ASM_password represents the ASM SYS user password.
The following command syntax creates an Oracle RAC database on an existing ASM disk group, where the cluster nodes are node1 and node2, and where the disk group name is ASMgrp1, the passwords are my_password, and the ASM SYS password is My_ASM_Password:

# su oracle -c “$ORACLE_HOME/bin/dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName $DBNAME -sid $SID -sysPassword my_password -systemPassword my_password -sysmanPassword my_password -dbsnmpPassword my_password -emConfiguration LOCAL -storageType ASM -diskGroupName ASMgrp1 -datafileJarLocation $ORACLE_HOME/assistants/dbca/templates -nodeinfo node1,node2 -characterset WE8ISO8859P1 -obfuscatedPasswords false -sampleSchema false -asmSysPassword My_ASM_password”

dbca -silent方式可以创建rac数据库,并指定归档目录,但没有选项设置归档,还是需要执行alter database archivelog来启动归档。网友lhrbest后面通过修改创建数据库的模板文件(创建数据库的模板文件存储在$ORACLE_HOME/assistants/dbca/templates目录中)中的参数 <archivelogmode>false</archivelogmode>,将false修改为true就可以完成归档,下面是通用目的数据库模板文件
[oracle@jyrac1 templates]$ cat General_Purpose.dbc
…省略…
<archivelogmode>false</archivelogmode&gt
…省略…

下面对General_Purpose.dbc文件创建一份副本并启用归档

[oracle@jyrac1 templates]$ cp General_Purpose.dbc General_Purpose_archivelog.dbc
[oracle@jyrac1 templates]$ vi General_Purpose_archivelog.dbc
…省略…
<archivelogmode>true</archivelogmode&gt
…省略…

再次删除rac数据库jyrac

[oracle@jyrac2 ~]$ dbca -silent -deleteDatabase -sourceDB jyrac -sysDBAUserName sys -sysDBAPassword system
Connecting to database
9% complete
14% complete
19% complete
23% complete
28% complete
33% complete
38% complete
47% complete
Updating network configuration files
48% complete
52% complete
Deleting instances and datafiles
66% complete
80% complete
95% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/jyrac.log" for further details.
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576      5120     4892                0            4892              0             N  BACKUPDG/
MOUNTED  EXTERN  N         512   4096  1048576      5120     4724                0            4724              0             Y  CRSDG/
MOUNTED  EXTERN  N         512   4096  1048576     10240    10138                0           10138              0             N  DATADG/

[root@jyrac1 jyrac]# ps -ef | grep pmon
grid      4185     1  0 Jan26 ?        00:01:59 asm_pmon_+ASM1
root     32023 26194  0 16:57 pts/3    00:00:00 grep pmon

[grid@jyrac2 ~]$ ps -ef | grep pmon
grid      4144     1  0 Jan26 ?        00:02:03 asm_pmon_+ASM2
grid     30617 22435  0 16:57 pts/1    00:00:00 grep pmon

使用新创建的模板文件来创建数据库:

[oracle@jyrac1 templates]$ dbca -silent -createDatabase -templateName General_Purpose_archivelog.dbc -gdbname jyrac  -sid jyrac -datafileDestination 'datadg/' -redoLogFileSize 50 -recoveryAreaDestination 'datadg/' -storageType ASM -asmsnmpPassword system  -diskGroupName 'datadg' -responseFile NO_VALUE -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -automaticMemoryManagement true -totalMemory 500 -initParams log_archive_dest_1='location=+backupdg/' -nodeinfo jyrac1,jyrac2
Enter SYS user password:

Enter SYSTEM user password:

Copying database files
1% complete
3% complete
9% complete
15% complete
21% complete
27% complete
30% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
44% complete
45% complete
48% complete
50% complete
Creating cluster database views
52% complete
70% complete
Completing Database Creation
73% complete
76% complete
85% complete
94% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/jyrac/jyrac0.log" for further details.

检查数据库状态:

[grid@jyrac1 ~]$ ps -ef | grep pmon
grid      4185     1  0 Jan26 ?        00:02:02 asm_pmon_+ASM1
oracle   18015     1  0 18:06 ?        00:00:00 ora_pmon_jyrac1
grid     18377 18077  0 18:08 pts/4    00:00:00 grep pmon

[grid@jyrac2 ~]$ ps -ef | grep pmon
grid      4144     1  0 Jan26 ?        00:02:06 asm_pmon_+ASM2
oracle    8184     1  0 18:07 ?        00:00:00 ora_pmon_jyrac2
grid      8398  7717  0 18:08 pts/2    00:00:00 grep pmon
[grid@jyrac2 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.BACKUPDG.dg
               ONLINE  ONLINE       jyrac1
               ONLINE  ONLINE       jyrac2
ora.CRSDG.dg
               ONLINE  ONLINE       jyrac1
               ONLINE  ONLINE       jyrac2
ora.DATADG.dg
               ONLINE  ONLINE       jyrac1
               ONLINE  ONLINE       jyrac2
ora.LISTENER.lsnr
               ONLINE  ONLINE       jyrac1
               ONLINE  ONLINE       jyrac2
ora.asm
               ONLINE  ONLINE       jyrac1                   Started
               ONLINE  ONLINE       jyrac2                   Started
ora.gsd
               OFFLINE OFFLINE      jyrac1
               OFFLINE OFFLINE      jyrac2
ora.net1.network
               ONLINE  ONLINE       jyrac1
               ONLINE  ONLINE       jyrac2
ora.ons
               ONLINE  ONLINE       jyrac1
               ONLINE  ONLINE       jyrac2
ora.registry.acfs
               ONLINE  ONLINE       jyrac1
               ONLINE  ONLINE       jyrac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       jyrac2
ora.cvu
      1        ONLINE  ONLINE       jyrac2
ora.jyrac.db
      1        ONLINE  ONLINE       jyrac1                   Open
      2        ONLINE  ONLINE       jyrac2                   Open
ora.jyrac1.vip
      1        ONLINE  ONLINE       jyrac1
ora.jyrac2.vip
      1        ONLINE  ONLINE       jyrac2
ora.oc4j
      1        ONLINE  ONLINE       jyrac2
ora.scan1.vip
      1        ONLINE  ONLINE       jyrac2
[grid@jyrac2 ~]$ srvctl status database -d jyrac
Instance jyrac1 is running on node jyrac1
Instance jyrac2 is running on node jyrac2

[grid@jyrac2 ~]$ srvctl config database -d jyrac
Database unique name: jyrac
Database name: jyrac
Oracle home: /u01/app/oracle/product/11.2.0/db
Oracle user: oracle
Spfile: +DATADG/jyrac/spfilejyrac.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: jyrac
Database instances: jyrac1,jyrac2
Disk Groups: DATADG
Mount point paths:
Services:
Type: RAC
Database is administrator managed
[grid@jyrac1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-APR-2015 18:09:58

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                26-JAN-2015 10:48:32
Uptime                    83 days 7 hr. 21 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/11.2.0/crs/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/jyrac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.153)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "DBUA4235414" has 1 instance(s).
  Instance "DBUA4235414", status BLOCKED, has 1 handler(s) for this service...
Service "jyrac" has 1 instance(s).
  Instance "jyrac1", status READY, has 1 handler(s) for this service...
Service "jyracXDB" has 1 instance(s).
  Instance "jyrac1", status READY, has 1 handler(s) for this service...
The command completed successfully

Database is administrator managed

[grid@jyrac2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-APR-2015 18:09:54

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                26-JAN-2015 10:48:15
Uptime                    83 days 7 hr. 21 min. 40 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/11.2.0/crs/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/jyrac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.154)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "jyrac" has 1 instance(s).
  Instance "jyrac2", status READY, has 1 handler(s) for this service...
Service "jyracXDB" has 1 instance(s).
  Instance "jyrac2", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@jyrac2 ~]$ lsnrctl status listener_scan1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-APR-2015 18:10:43

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                26-JAN-2015 10:48:10
Uptime                    83 days 7 hr. 22 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/11.2.0/crs/network/admin/listener.ora
Listener Log File         /u01/app/product/11.2.0/crs/log/diag/tnslsnr/jyrac2/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.159)(PORT=1521)))
Services Summary...
Service "jyrac" has 2 instance(s).
  Instance "jyrac1", status READY, has 1 handler(s) for this service...
  Instance "jyrac2", status READY, has 1 handler(s) for this service...
Service "jyracXDB" has 2 instance(s).
  Instance "jyrac1", status READY, has 1 handler(s) for this service...
  Instance "jyrac2", status READY, has 1 handler(s) for this service...
The command completed successfully

检查归档设置:

[oracle@jyrac1 templates]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 19 18:11:17 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +BACKUPDG
Oldest online log sequence     4
Next log sequence to archive   5
Current log sequence           5

SQL> alter system switch logfile;

System altered.

[oracle@jyrac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 19 18:11:51 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +BACKUPDG
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
SQL> alter system switch logfile;

System altered.
ASMCMD> pwd
+backupdg/jyrac/archivelog/2015_04_19
ASMCMD> ls -lt
Type        Redund  Striped  Time             Sys  Name
ARCHIVELOG  UNPROT  COARSE   APR 19 18:00:00  Y    thread_2_seq_2.306.877457527
ARCHIVELOG  UNPROT  COARSE   APR 19 18:00:00  Y    thread_2_seq_1.307.877457191
ARCHIVELOG  UNPROT  COARSE   APR 19 18:00:00  Y    thread_1_seq_5.312.877457503

可以看到确实启用了归档,在执行alter system switch logfile命令后两个实例都生成了归档重做日志,使用模板来创建数据库,还是需要仔细阅读模板文件中的参数,这里谢谢网友lhrbest,dbca- silent是可以创建rac数据库,可以通过-initParams参数设置归档目录,通过修改创建数据库的模板文件中archivelogmode参数启用归档。

Proudly powered by WordPress | Indrajeet by Sus Hill.