但行好事莫问前程

Oracle Database Service

Posted on 2016年5月6日 by YongJing(敬勇)

数据库服务(Database Services)
数据库服务是表示一个或多个数据库实例的命名方式。服务能让你组合数据库工作负载并将一个特定的工作请求分发到一个合适的实例。一个数据库服务代表了一个单独的数据库。这个数据库可以是单实例数据库或者RAC数据库。一个全局数据库服务通过数据复制的多数据库同步机制来提供服务。

数据库服务将单数据库的工作负载分成多个互不相交的工作组。每个数据库服务使用常见的属性，服务级别阈值与优先级来代表一个工作负载。分组是基于工作属性的，它可能包含被使用的应用功能，执行应用功能的优先级，被管理的job类型或者应用功能中使用的数据范围或job类型。

数据库连接请求可以包含一个数据库服务名。因此中间层应用与C/S应用可以通过在TNS连接数据中指定数据库服务作为连接的一部分来使用服务。如果没有包含数据为服务名并且Net服务文件listener.ora指定了一个缺省数据库服务，那么连接将会使用这个缺省的数据库服务。

数据库服务可以对单个数据库配置工作负载，管理，启用与禁用，并且可以作为单个实体来检测工作负载。可以使用标准工具比如DBCA,NETCA和Cloud Control来进行操作。Cloud Control支持查看与操作服务。

在RAC环境中，数据库服务可以跨一个或多个实例并且基于事务性能来达到工作负载平衡。这种功能提供了无人值守的恢复，回滚与完全的位置透明。RAC也能让你使用Cloud Control，DBCA与SRVCLT来管理多个数据库服务功能。

数据库服务描述了应用程序，应用程序功能和功能服务或数据依赖服务的数据范围。功能服务通常映射到工作负载。会话使用特定功能被分组在一起。相反，数据领带根据数据键值将会话路由到数据服务。工作请求映射会在应用程序服务与TP监控的对象关系映射层中出现。例如，在RAC中，因为数据库是共享的，这些范围可能基于需要动态的完成。

另外数据库服务可以通过应用程序来使用，Oracle数据库也支持两种内部数据库服务:SYS$BACKGROUND只能由后台进程使用，SYS$USERS是用户会话的缺省数据库服务，它不与服务相关联。

使用数据库服务请求不需要改变应用等程序代码。客户端可以连接到一个命名的数据服务进行操作。服务端，比如Oracle调度，并行执行与Oracle数据库高级队列，将数据库服务名设置为工作负载定义的一部分。使用数据库服务执行的工作请求继承了服务的性能阈值与作为服务一部分的测量。

数据库服务与性能
数据库服务在性能调整中也提供了一种额外的维度。在大多数系统中所有会话都是匿名与共享的，可以使用”服务与SQL”调整来替代”会话与SQL”调整。使用数据库服务，工作负载可见且可以被检测。通过应用程序的资源消耗与等待属性来实现。另外，指定到数据库服务的资源当负载增加或减少时可以扩展。动态资源分配对于满足你出现的需求是最具成本效益的解决方案。例如，数据库服务会自动被
检测并与服务级别阈值进行比较。性能问题会报告给Cloun Control，并执行自动或调度解决方案。

使用数据库服务的数据库功能
有些Oracle数据库功能支持数据库服务。AWR管理服务的性能。AWR记录了数据库服务的性能，包含执行时间，等待类型与服务的资源消耗。当数据库服务响应时间超过了阈值AWR会发出警告。动态性能视图使用一小时历史数据来报告当前服务的性能度量。每个数据库服务对于响应时间与CPU消耗都有服务质量阈值。

数据库资源客理器可以将数据库服务映射到用户组。因此，可以自动管理数据库服务的优先级。可以使用用户组来定义相对优先级或资源消耗。

可以为数据库服务指定一个编辑属性。编辑可以使数据库中的相同对象有两个或多个版本。当你对数据库服务指定编辑属性时，所有后续的连接将使用这个编辑属性来初始化会话。

对数据库服务指定一个编辑属性可以更容易的管理资源使用。例如，使用编辑属性的数据库服务可以被置于RAC环境中的单独实例中，数据库资源管理器通过使用不同编辑属性的相关数据库服务所关联的资源计划来管理资源的使用。

对于Oracle调度，可以选择在创建job类型时指定数据库服务。在执行时间，job被指派到job类型，并且job类型可以使用数据库服务来运行。指定job类型的数据库服务可以确保通过job调度的执行被工作负载管理所识别与执行调整。

对于并行查询与并行DML，查询协调者连接到数据库服务就像其它客户端连接数据一样。对于执行时间并行查询进程继承数据库服务。在查询执行结束后，并行执行进程会归还给缺省的数据库服务。

创建数据库服务
依赖于数据库的配置有几种创建数据库服务的方式
1.如果是单实例数据库且由Oracle Restart管理，可以使用srvctl工具来创建数据库服务

srvctl add service -db db_unique_name -service service_name

[oracle@oracle12c admin]$ srvctl add service -db jycs -service jycs_service

检查服务状态

[oracle@oracle12c admin]$ srvctl status service -db jycs
Service jycs_service is not running.

查看服务配置信息

[oracle@oracle12c admin]$ srvctl config service -db jycs
Service name: jycs_service
Cardinality: SINGLETON
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
TAF failover retries:
TAF failover delay:
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Session State Consistency:
GSM Flags: 0
Service is enabled

启动服务

[oracle@oracle12c admin]$ srvctl start service -db jycs -service jycs_service

查看监听是否注册了服务

[grid@oracle12c ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2016 11:56:23

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                05-MAY-2016 08:41:25
Uptime                    0 days 3 hr. 14 min. 58 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/oracle12c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1/db/admin/jycs/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "jycs" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsXDB" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycs_service" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp1" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...

可以看到jycs_service服务已经被监听注册，在客户端配置tns并验证通过服务jycs_service能否登录数据库

C:\Users\Administrator>tnsping 12c_jycs_service

TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 05-5月 -
2016 13:21:38

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

已使用的参数文件:
D:\oracle\app\Administrator\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.241)(PORT
= 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = jycs_service)))
OK (20 毫秒)

C:\Users\Administrator>sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on 星期四 5月 5 14:44:06 2016

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



SQL> conn sys/system@12c_jycs_service as sysdba
已连接。
SQL>

从执行命令的结果来看通过服务jycs_service可以登录数据库

2.如果是单实例且没有使用Oracle Restart来管理数据库，可以通过以下一种方式来创建数据库服务

SQL> show parameter service_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      jycs

[grid@oracle12c ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2016 14:59:06

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                05-MAY-2016 14:57:53
Uptime                    0 days 0 hr. 1 min. 13 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/oracle12c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1/db/admin/jycs/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "jycs" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsXDB" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycs_service" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp1" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp10" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp2" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp3" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp4" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp5" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp6" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp7" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp8" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp9" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...

增加一个名为jycs_service_2的服务名

SQL> alter system set service_names='jycs,jycs_service_2' scope=both;

System altered.

检查监听是否注册了服务jycs_service_2

[grid@oracle12c ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2016 15:02:08

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                05-MAY-2016 14:57:53
Uptime                    0 days 0 hr. 4 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/oracle12c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1/db/admin/jycs/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "jycs" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsXDB" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycs_service_2" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
The command completed successfully

从这里可以看到jycs_service_2服务已被监听所注册,但是通过这种方式创建服务后，原来PDB的服务从监听中消失了，这可能是12.1.0.2的bug（猜测)，如是重启数据库就可以解决这个问题。

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             377487464 bytes
Database Buffers          687865856 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.

[grid@oracle12c ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2016 15:02:58

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                05-MAY-2016 14:57:53
Uptime                    0 days 0 hr. 5 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/oracle12c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/jycs/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "jycs" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsXDB" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycs_service" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycs_service_2" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp1" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp10" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp2" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp3" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp4" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp5" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp6" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp7" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp8" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp9" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
The command completed successfully

3.执行dbms_service.create_service过程来创建数据库服务名

SQL>dbms_service.create_service(service_name => 'jycs_service_3',network_name => 'jycs_service_3');

执行dbms_service.create_service过程来创建数据库服务名jycs_service_3后，在v$services视图中是没有该服务的行记录，但在dba_services视图中有该服务的行记录,需要调整数据库参数service_names，并重启。

SQL> select * from v$services;

SERVICE_ID NAME                        NAME_HASH NETWORK_NAME    CREATION_DATE CREATION_DATE_HASH GOAL         DTP AQ_HA_NOTIFICATION CLB_GOAL COMMIT_OUTCOME RETENTION_TIME REPLAY_INITIATION_TIMEOUT SESSION_STATE_CONSISTENCY      GLOBAL PDB          SQL_TRANSLATION_PROFILE  MAX_LAG_TIME       CON_ID
---------- -------------------------- ---------- --------------- ------------- ------------------ ------------ --- ------------------ -------- -------------- -------------- ------------------------- ------------------------------ ------ ------------ ------------------------ -------------- ----------
         0 jycsp10                    2786476993 jycsp10                                        0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP10                                                      12
         0 jycsp9                     3094752551 jycsp9                                         0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP9                                                       11
         0 jycsp8                     2804702749 jycsp8                                         0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP8                                                       10
         0 jycsp7                      651053443 jycsp7                                         0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP7                                                        9
         0 jycsp6                     2502944067 jycsp6                                         0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP6                                                        8
         0 jycsp5                     1822500990 jycsp5                                         0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP5                                                        7
         0 jycsp4                     1917126355 jycsp4                                         0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP4                                                        6
         0 jycsp3                     2193443928 jycsp3                                         0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP3                                                        5
         0 jycsp2                     3609153374 jycsp2                                         0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP2                                                        4
         0 jycsp1                     1271175711 jycsp1                                         0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP1                                                        3
         3 jycs_service               1423956612 jycs_service    2016/5/5 11:5          668160606 NONE         N   NO                 LONG     NO                      86400                       300 DYNAMIC                        NO     CDB$ROOT                              ANY                     1
         7 jycs_service_2             2320947470 jycs_service_2  2016/5/5 15:0         4274618966 NONE         N   NO                 LONG     NO                      86400                       300                                NO     CDB$ROOT                                                      1
         5 jycsXDB                    1180545090 jycsXDB         2016/3/31 20:         3827618340 NONE         N   NO                 LONG     NO                      86400                       300                                NO     CDB$ROOT                                                      1
         6 jycs                       1105513663 jycs            2016/3/31 20:         3827618340 NONE         N   NO                 LONG     NO                      86400                       300                                NO     CDB$ROOT                                                      1
         1 SYS$BACKGROUND              165959219                 2014/7/7 5:39          977152970 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     CDB$ROOT                                                      1
         2 SYS$USERS                  3427055676                 2014/7/7 5:39          977152970 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     CDB$ROOT                                                      1

16 rows selected

SQL> select * from dba_services;

SERVICE_ID NAME                        NAME_HASH NETWORK_NAME    CREATION_DATE CREATION_DATE_HASH FAILOVER_METHOD    FAILOVER_TYPE    FAILOVER_RETRIES FAILOVER_DELAY MIN_CARDINALITY MAX_CARDINALITY GOAL         DTP ENABLED AQ_HA_NOTIFICATIONS CLB_GOAL EDITION     COMMIT_OUTCOME RETENTION_TIMEOUT REPLAY_INITIATION_TIMEOUT SESSION_STATE_CONSISTENCY     GLOBAL_SERVICE PDB         SQL_TRANSLATION_PROFILE   MAX_LAG_TIME    GSM_FLAGS
---------- -------------------------- ---------- --------------- ------------- ------------------ ------------------ ---------------- ---------------- -------------- --------------- --------------- ------------ --- ------- ------------------- -------- ----------- -------------- ----------------- ------------------------- ----------------------------- -------------- ----------- ------------------------- -------------- ----------
         1 SYS$BACKGROUND              165959219                 2014/7/7 5:39          977152970                                                                                                                  N   NO      NO                  LONG                 NO                                                                                       NO             CDB$ROOT
         2 SYS$USERS                  3427055676                 2014/7/7 5:39          977152970                                                                                                                  N   NO      NO                  LONG                 NO                                                                                       NO             CDB$ROOT
         3 jycs_service               1423956612 jycs_service    2016/5/5 11:5          668160606                                                    0              0                                 NONE         N   NO      NO                  LONG                 NO                         86400                       300 DYNAMIC                       NO             CDB$ROOT                              ANY                     0
         4 jycs_service1              3627910471 jycs_service1   2016/5/5 14:5          153848850                                                                                                                  N   NO      NO                  LONG                 NO                                                                                       NO             CDB$ROOT
         5 jycsXDB                    1180545090 jycsXDB         2016/3/31 20:         3827618340                                                                                                                  N   NO      NO                  LONG                 NO                                                                                       NO             CDB$ROOT
         6 jycs                       1105513663 jycs            2016/3/31 20:         3827618340                                                                                                                  N   NO      NO                  LONG                 NO                                                                                       NO             CDB$ROOT
         7 jycs_service_2             2320947470 jycs_service_2  2016/5/5 15:0         4274618966                                                                                                                  N   NO      NO                  LONG                 NO                                                                                       NO             CDB$ROOT
         8 jycs_service_3             1197864246 jycs_service_3  2016/5/5 15:0         2019662977                                                                                                     NONE         N   NO      NO                  LONG                 NO                         86400                       300 DYNAMIC                       NO             CDB$ROOT                              ANY                     0

8 rows selected

[grid@oracle12c ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2016 15:18:05

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                05-MAY-2016 14:57:53
Uptime                    0 days 0 hr. 20 min. 12 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/oracle12c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/jycs/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "jycs" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsXDB" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycs_service" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycs_service_2" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp1" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp10" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp2" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp3" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp4" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp5" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp6" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp7" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp8" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp9" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
The command completed successfully

设置参数service_names并重启数据库

SQL> alter system set service_names='jycs,jycs_service_2,jycs_service_3' scope=both;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             377487464 bytes
Database Buffers          687865856 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.

SQL> select * from v$services;

SERVICE_ID NAME                 NAME_HASH NETWORK_NAME    CREATION_DATE CREATION_DATE_HASH GOAL         DTP AQ_HA_NOTIFICATION CLB_GOAL COMMIT_OUTCOME RETENTION_TIME REPLAY_INITIATION_TIMEOUT SESSION_STATE_CONSISTENCY      GLOBAL PDB           SQL_TRANSLATION_PROFILE   MAX_LAG_TIME      CON_ID
---------- ------------------- ---------- --------------- ------------- ------------------ ------------ --- ------------------ -------- -------------- -------------- ------------------------- ------------------------------ ------ ------------- ------------------------- ------------- ----------
         3 jycs_service        1423956612 jycs_service    2016/5/5 11:5          668160606 NONE         N   NO                 LONG     NO                      86400                       300 DYNAMIC                        NO     CDB$ROOT                                ANY                    1
         0 jycsp10             2786476993 jycsp10                                        0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP10                                                       12
         0 jycsp9              3094752551 jycsp9                                         0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP9                                                        11
         0 jycsp8              2804702749 jycsp8                                         0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP8                                                        10
         0 jycsp7               651053443 jycsp7                                         0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP7                                                         9
         0 jycsp6              2502944067 jycsp6                                         0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP6                                                         8
         0 jycsp5              1822500990 jycsp5                                         0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP5                                                         7
         0 jycsp4              1917126355 jycsp4                                         0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP4                                                         6
         0 jycsp3              2193443928 jycsp3                                         0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP3                                                         5
         0 jycsp2              3609153374 jycsp2                                         0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP2                                                         4
         0 jycsp1              1271175711 jycsp1                                         0 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     JYCSP1                                                         3
         8 jycs_service_3      1197864246 jycs_service_3  2016/5/5 15:0         2019662977 NONE         N   NO                 LONG     NO                      86400                       300                                NO     CDB$ROOT                                ANY                    1
         7 jycs_service_2      2320947470 jycs_service_2  2016/5/5 15:0         4274618966 NONE         N   NO                 LONG     NO                      86400                       300                                NO     CDB$ROOT                                                       1
         5 jycsXDB             1180545090 jycsXDB         2016/3/31 20:         3827618340 NONE         N   NO                 LONG     NO                      86400                       300                                NO     CDB$ROOT                                                       1
         6 jycs                1105513663 jycs            2016/3/31 20:         3827618340 NONE         N   NO                 LONG     NO                      86400                       300                                NO     CDB$ROOT                                                       1
         1 SYS$BACKGROUND       165959219                 2014/7/7 5:39          977152970 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     CDB$ROOT                                                       1
         2 SYS$USERS           3427055676                 2014/7/7 5:39          977152970 NONE         N   NO                 SHORT    NO                      86400                       300                                NO     CDB$ROOT                                                       1

查看监听是否注册了jycs_service_3，可以看到在设置service_names参数并重启数据库后监听注册了该服务

[grid@oracle12c ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2016 15:21:10

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                05-MAY-2016 14:57:53
Uptime                    0 days 0 hr. 23 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/oracle12c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/jycs/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "jycs" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsXDB" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycs_service" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycs_service_2" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycs_service_3" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp1" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp10" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp2" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp3" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp4" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp5" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp6" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp7" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp8" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycsp9" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
The command completed successfully

Oracle的并行

Posted on 2016年5月4日2016年12月3日 by YongJing(敬勇)

当我们要在操作中处理大量数据，比如全表扫描大表或者创建一个大的索引，可以将这个工作通过使用多个进程来分成多个小任务来完成，这就叫作并行执行或者并行处理。并行执行当要访问大量数据时能够减少响应时间，但不是任何时候并行执行都比串行执行快。并行执行在以下情况下是非常有用的:
查询要使用大表扫描，连接或分区索引扫描
创建大索引和大表
批量插入，更新和删除
聚合操作

并行执行的工作原理
一个查询提交到Oracle服务器并解析。在优化时如果考虑使用并行执行，那么在执行时间用户影子进程将成为查询协调者(QC)并且根据需要为会话分配并行子进程。QC根据并行子进程的数量并使用基于rowid范围或分区（从8i开始）来将工作进行分解。为了达到平均分布工作量，一个简单的分布算法被使用。生产者读取数据并将数据存储在表队列中由消费者或者QC来从表队列中读取数据。

如果SQL语句要执行排序，那么由消费者并行子进程从生产者相关的表队列中读取数据并且进行排序并将排序后的数据存储到与消费者并行子进程相关的新表队列中。这些队列然后将由QC进程进行读取。

如果SQL语句不执行排序，那么QC将直接读取生产者子进程相关的表队列。

Query Coordinator(QC)查询协调者:由会话派生出来的前台进程，用来从查询子进程中接收数据

Slaves:子进程从磁盘或表队列结构（也可能是其它的子进程）中读取数据并且将数据存储到它们自己的表队列中。当子进程从磁盘读取数据时，将会执行直接I/O路么读。这意味着将会跨过buffer cache。事实上，子进程将会强制将已经被更新但还没有被刷新到磁盘的数据块从buffer cache中刷新到磁盘，然后使用直接路径I/O来读取数据。

子进程将等待数据进入队列和离开队列的消息。有两种类型的子进程
生产者和消费者:
生产者子进程根据QC所给定的rowid范围或分区来查看数据块和检索相关数据。然后这些数据会被存储到表队列中由QC或消费者子进程来进行读取

消费者子进程当需要从由生产者子进程填充的表队列中读取数据时才产生。读取数据后经过消费者子进程处理后返回给QC处理。

在一个最简单的查询中(数据不需要进行排序），那么不需要产生消费者进程并且QC会直接从生产者子进程表队列中读取数据。

出现生产者和消费者的原因是因为当并行查询需要排序时并行度有时要求双倍数量的查询子进程。

SQL> select /*+ parallel */ * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2873591275

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    14 |   532 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| EMP      |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2


Statistics
----------------------------------------------------------
        130  recursive calls
          4  db block gets
        155  consistent gets
         18  physical reads
          0  redo size
       1401  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
         14  rows processed

上面的查询没有要求排序所以只产生的生产者，也只有一个表队列TQ10000,一组并行子进程就是生产者子进程

SQL> select /*+ parallel */ * from scott.emp order by ename;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3979194000

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |    14 |   532 |     3  (34)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (ORDER)    | :TQ10001 |    14 |   532 |     3  (34)| 00:00:01 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    SORT ORDER BY        |          |    14 |   532 |     3  (34)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |          |    14 |   532 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE      | :TQ10000 |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | P->P | RANGE      |
|   6 |       PX BLOCK ITERATOR |          |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| EMP      |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2


Statistics
----------------------------------------------------------
         19  recursive calls
          4  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
       1406  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         14  rows processed

上面的查询要求排序，所以出现了生产者与消费者，生产者表队列为:TQ10000，消费者表队列为TQ10001

子进程从表队列中等待离队信息时，’PX Deque wait’等待是并行查询正常操作的一部分。子进程在工作之前必须等待离队的消息才能开始进行。

Table Queues(TQ)队列它允许进程将行记录发送给其它进程。比如数据从生产者子进程发送到消费者子进程。消费者子进程也可以将数据发送给QC。

与并行执行相关的数据库参数
parallel_max_servers
一个实例可以使用的最大并行子进程数，如果为0则意味着不能并行执行语句

parallel_min_servers
实例在启动时产生的最小并行子进程数，这些子进程在实例的生命周期内都是被保留存在的。

parallel_min_percent
如果优化器已经决定了查询将以并行方式来执行，但执行时系统没有足够的资源来满足所请求的并行度，那么缺省情况下，查询将以串行方式来执行并且不会给用户返回任何信息。这就很容易造成一个查询的执行时间非常长。这个参数提供了一个方法当没有足够资源可用时来阻查询以串行方式来执行并显示错误信息。它定义了一个并行查询可以执行的最小资源数。它用可以并行执行子进程的百分比
来表示。

如果这个参数没有被设置且并行执行所需要的资源不能满足那么查询将会以串行方式来执行。
如果这个参数被设置并且期望的查询子进程的百分比不满足，那么将会报错(ORA-12827)而不是串行执行语句。

如果并行执行需要的资源不可用，当parallel_min_percent=0时，语句将会串行执行而不会报错。当parallel_min_percent=50，意味着查询执行是最佳并行执行时间的2倍是可以接受的。当parallel_min_percent=100，意味着除非并行查询所需要的资源完全可用才会并行执行否则会报错ORA-12827。

parallel_adaptive_multi_user
当parallel_adaptive_multi_user设置为true时，启用自适应算法来使用并行执行提高多用户环境中的性能。算法会根据查询开始执行时间的系统负载来自动减少所请求的并行度。实际上有效的并行度是基于缺省并行度，或都表的并行度或hints除以减小因子。

例如:在一个有17个CPU的主机上缺省并行度可以被设置为32。如果用户执行一个并行查询，得到的并行度是32，能有效的使用系统中的所有CPU和内存。当第二个用户登录系统，并执行一个并行行查询,得到的并行度将是16，当系统中的用户增加时，算法将继续减小并行度直到用户使用的并行度等于1为止，也就是系统有32个用户登录时。

parallel_automatic_tuning
设置parallel_automatic_tuning参数将会对PX参数有影响,当parallel_automatic_tuning=false时:
parallel_executon_message_size的缺省值为2Kbyte
parallel_adaptive_multi_user的缺省值为false
large_pool_size不受影响
prcoesses不受影响
parallel_max_servers 5

当parallel_automatic_tuning=true时:
parallel_executon_message_size的缺省值为4Kbyte
parallel_adaptive_multi_user的缺省值为true
large_pool_size 将基于其它条种参数来进行复杂计算来计算出增加值
prcoesses 如果processes parallel_max_servers 如果parallel_adaptive_multi_user=true时
(cpus * parallel_threads_per_cpu * _parallel_adaptive_max_users * 5)，否则
(cpus * parallel_threads_per_cpu * _parallel_adaptive_max_users * 8)

parallel_theads_per_cpu缺省值依赖于操作系统，缺省值为2

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

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)

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

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时才生效。

parallel_servers_target
Parallel_servers_target用于控制在自动并行被完全开启的情况下，待并行执行的目标SQL是立即被执行还是进入并行执行队列，它的默认值为4*cpu_count*parallel_threads_per_cpu（在Oracle 11gr2中，如果你开启了SGA和PGA的自动调整，则parallel_servers_target的默认值为8*cpu_count*parallel_threads_per_cpu）。

优化器
并行执行只能用于CBO。在有些情况下,当表或索引有非0并行度时将会强制使用CBO。有以下hint影响并行执行:
.parallel
.noparallel
.pq_distribute
.parallel_index
.noparallel_index

如果使用RBO，那么任何并行hints都会被忽略。

如何判断语句是否使用并行执行
1.检查执行计划
如果没有并行的相关信息，那么没有使用并行执行。检查plan table中由并行子进程所使用的其它列。如果SQL包含hints比如/*+ rowid(a1) */并且这些hints没有出现在原代码中，那么可能是并行查询所使用的。ROWID hint是一种内部方法用于处理并行查询。注意ROWID hint有特定的意思，意味首跨过了buffer cache。事实上它会造成buffer被刷新到磁盘因此对基本的数据文件可以执行直接
I/O。

有两个脚本用来格式化plan_table的查询。一个用来格式化串行执行计划的输出(utlxpls.sql)，这些脚本都可以到$ORACLE_HOME/rdbms/admin/目录下找到。为了避免截断输出执行以下设置
‘set charwidth 108’ in svrmgrl
‘set linesize 108’ in SQL*Plus

2.执行查询

SQL> select * from v$pq_sesstat; 

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    0             0
DML Parallelized                        0             0
DDL Parallelized                        0             0
DFO Trees                               0             0
Server Threads                          0             0
Allocation Height                       0             0
Allocation Width                        0             0
Local Msgs Sent                         0             0
Distr Msgs Sent                         0             0
Local Msgs Recv'd                       0             0
Distr Msgs Recv'd                       0             0

11 rows selected.

SQL> select count(*) from t1;

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

SQL> select * from v$pq_sesstat; 

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    1             1
DML Parallelized                        0             0
DDL Parallelized                        0             0
DFO Trees                               1             1
Server Threads                          2             0
Allocation Height                       1             0
Allocation Width                        2             0
Local Msgs Sent                        29            29
Distr Msgs Sent                        29            29
Local Msgs Recv'd                      29            29
Distr Msgs Recv'd                      29            29

11 rows selected.

上面查询信息的第一行可以看到这个会话的最后一个查询是并行执行。

3.检查子进程活动视图
查询v$pq_slave两次

SQL> SELECT slave_name,status, cpu_secs_total FROM v$pq_slave;

SLAV STAT CPU_SECS_TOTAL
---- ---- --------------
PZ99 IDLE              0

SQL> select count(*) from t1;

  COUNT(*)
----------
  22040576
SQL> SELECT slave_name,status, cpu_secs_total FROM v$pq_slave;

SLAV STAT CPU_SECS_TOTAL
---- ---- --------------
P000 BUSY              0
PZ99 IDLE              0

如果没有返回记录，说明没有并行子进程运行。如果在2次运行之间CPU利用率没有差异，那么在这期间没有CPU活动。

可以通过v$session_wait来查询PQ活动,对于8以下的版本可以执行以下查询

SQL> SELECT sid, event, seq#,p1,p2,p3, wait_time FROM v$session_wait WHERE upper(event) like ('PX%') ORDER BY 1;
       SID EVENT
---------- ----------------------------------------------------------------
      SEQ#         P1         P2         P3  WAIT_TIME
---------- ---------- ---------- ---------- ----------
        33 PX Deq: Execute Reply
     31451        200          1          0          0

4.使用event 10046 level 12诊断事件来跟踪查询协调者QC
并行执行的性能
以并行方式来执行查询而不是串行，可以提高性能。然而，并行查询是否最有效，有许多因素需要考虑。多个并行子进程会比单个进程消耗更多的CPU，每个并行子进程都有它自己的地址空间(内存分配)

对于读取大量数据除了使用并行执行来减少执行时间之外没有其它选择。通过将读取工作负载分解到多个处理器或并行子进程来执行最大化了数据吞吐量。排序操作也使用子进程的’table queue structures’来进行管理。这将有效地增加整个系统的CPU负载。如果系统当前CPU利用率已经最大化（或接近最大化），并行查询不会得到任何好处。如果没有更多的CPU可以使用，那么并行查询操作将以串行方式来执行。

另外并行子进程的读取数据的额外IO请求也会对I/O子系统的负载产生压力。在有些情况下磁盘I/O可能会成为瓶颈。跨多个磁盘来分布数据会所有帮助。

另外对于适用索引查询而不是并行查询的语句串行方式执行可能会运行地更快
.nested loops vs hash/sort merge join
通常来说并行查询是使用快速表扫描来访问数据并且基于rowid范围来分给并行子进程。通常nestedloop join对于快速表扫描不是很有效（除非驱动表输入的记录数很小）。Hash Joins与Sort Merge在处理大理数据时会更有效。然而，对于HJ与SMJ不会对来自驱动表(也就是驱动行记录）进行行消除。这种行消除会减小被调用数据集的大小。因为被消除的数据量这就可能意味着使用索引访问数据的串
行执行会比并行执行更快。

.创建并行子进程，平均分解数据并将数据传给多个进程和合并结果的总成本可能会比串行执行的成本更高

.数据倾斜
并行执行是基于rowid范围来将数据平均分给并行子进程。因为分给每个并行子进程的相同数据块包含首相同记录。事实上，有些数据块完全是空的。这种问题在大量数据被归档或被删除之后更为突出，因此造成了许多空块或稀疏的数据块。这会造成数据的不均匀分布进而造成并行查询比串行查询运行的更慢，因为一个并行子进程执行工作（例如一个并行子进程的对分布的数据串行访问会引起瓶颈）。
对于这种情况除了重新组织数据几乎没有方法可以解决。

性能概述
并不是所有的并行查询都比串行查询执行快。有些查询适合并行查询，有些适合串行查询。如果使用并行执行，那么应该尽量最大化磁盘I/O的吞吐量。确保:
.有足够的并行子进程来有效检索数据
.不能有太多的并行子进程(避免超过CPU)
.设置内存参数(sort_area_size等）因此不会内存溢出与引起内存交换
.数据均匀分布给多个磁盘，因此并行子进程没有I/O竞争
.需要并行执行的查询类型在适合并行执行
.查看并行子进程是否出现数据的不均匀分布这就说明了数据倾斜

位图索引

Posted on 2016年5月4日2016年5月4日 by YongJing(敬勇)

常规的B树索引对包含每行记录的ROWID与索引键值。位图索引不会直接存储ROWID，每个不同的键值都有一个位图，这就是为什么创建位图索引的列要有较少的distinct值的原因。位图中的每一位映射到一个可能的ROWID，位图中每一位的特定值代表是否存在有价值的记录，因此位图中存储了关于特定行和相关的ROWID。如果ROWID的值与条件匹配在rowid的位置存储“1”，不匹配存储“0”。Oracle会压
缩位图的存储。

创建位图索引
create bitmap index index_name on table_name(columns);Oracle将创建一系列的位图，列中每个特定值都会被使用。例如，如果创建位图索引的列有’East’和’Central’两个值，那么将会为’East’和’Central’创建位图。如果是复合位图索引，那么位图将是由
任何一组可能的排列值组成。

位图索引的使用
1.列有较低的基数:较少的distinct值
2.位图索引对于包含较长where子句或聚合查询(包含sum,count或其它聚合函数）的ad hoc查询很有帮助
3.表行记录很多(比如有1,000,000行记录有10,000个distinct值)
4.对于表执行ad hoc查询很频繁
5.数据仓库环境(DSS系统）。位图索引对于联机事务处理(OLTP)不适用这是由于位图索引的锁机制造成的，只锁定单个位图的位置是不能实现的。能够被锁定的最小位图量是一个位图段，它的大小可以达到数据块的一半。改变一行记录会造成一个位图段被锁定，而实际上只改变了一行记录。当有许多update,insert或delete语句被执行时，影响会更明显。在数据仓库中当数据批量加载或更新时这种影响不是问题。
6.位图连接索引是9中引入的，通过连接可以避免在连接条件上预先创建位图索引的必要

位图索引的限制
1.不能用于RBO
2.不能用于分区表的全局索引
3.不支持联机创建或重建
4.对位图索引使用直接路径加载，”SORTED_INDEX”标记不能应用
5.位图索引不能用于引用完整性
6.位图索引不能定义为UNIQUE
7.在9i之前，当创建一个索引组织表时不能使用位图索引，从9i开始才支持。
8.对域索引不能使用bitmap

与B树索引相比的优点
1.减少了许多ad hoc查询的响应时间
2.大幅减少了存储空间
a)有少量distinct值的单列位图索引
如果位图索引是创建在一个唯一键上，它将比常规B树索引使用的空间更多。然后，如果列中有成百上千个重复值时，位图索引通常要比常规B树索引所使用的空间减少25%。位图是以压缩的格式来进行存储的。

b)在多列上创建位图索引
位图索引与B树索引相比可以大量节省存储空间。在数据库中只包含B树索引，必须对查询中所使用的列进行预测并对这些列创建一个复合B树索引。多列复合B树索引不仅需要大量的空间，还要进行排序。对于在(marital_status,region,gender)上创建的B树索引，对于只访问REGION与DENDER在前导列marital_status有太多distinct值的情况下是没有用的。为了使用索引，必须对这些列的其它组合方
式创建索引。简单来说，对于三个低基数列就有6种组合的B树索引。必须对创建那种组合的B树索引和需要的存储空间进行考虑。B树索引可以解决这种问题。位图索引可以在查询执行时进行有效的组合，因此三个小的单列位图索引可以完成6个三列B树索引所做的事情。

3.非常影响并行DML与加载
位图索引适合数据仓库程序但不适合有高并发insert,update与delete系统。在数据仓库环境中，数据通常是批量插入和更新的。索引维护操作直到每个DML操作结束才执行。

4.包含null值的记录

位图索引的使用技巧
1.对所有可能的列定义not null约束将会减少存储空间，因为这将不会为null值创建位图。
2.使用固定长度的数据类型将会减少存储空间
3.增加create_bitmap_area_size参数可以提高查询处理速度。这个参数决定了对位图创建所分配的内存大小。这个参数决定了用于合并位图从范围扫描索引执行检索所使用的内存。

位图索引的例子
MARITAL_ STATUS REGION GENDER INCOME_LEVEL
————— ——– ——- ————
101 single east male bracket_1
102 married central female bracket_4
103 married west female bracket_2
104 divorced west male bracket_4
105 single central female bracket_2
106 married central female bracket_3

国灰marital_status,region,gender与income_level都是低基数列（对于matil_status与region只有三个可能的值，对于gender有两个可能的值，income_level列有四个可能的值）。在这些列上适合创建位图索引。在customer#上不适合创建位图索引，因为这个列有高基数。相反，一个唯一的B树索引将会提供最好的检索效率。

在这个例子中，region列的位图索引，它包括三个单独的位图
REGION=’east’ REGION=’central’ REGION=’west’ ## CUSTOMER #
1 0 0 < == 101 0 1 0 <== 102 0 0 1 <== 103 0 0 1 <== 104 0 1 0 <== 105 0 1 0 <== 106 位图中的每个条目或位关联到customer表中的单独一行记录。每一位的值依赖于表中相关行的值。例如，位REGION='east'包含一个1作为它的第一位。这是因为region='east'在表customer中的第一行存在。region='east'在其它行位为0,是因为其它行的region列不包含'east'。下面的查询是要查询在中部或西部地区有多少已婚的客户: SELECT COUNT(*) FROM CUSTOMER WHERE MARITAL_STATUS = 'married' AND REGION IN ('central','west'); 位图索引可以非常有效的通过仅仅计算结果位图中为1的数量来进行处理。 status = 'married' region = 'central' region = 'west' 0 0 0 1 1 0 1 0 1 0 AND ( 0 OR 1 ) 0 1 0 1 1 0 0 0 0 1 1 1 ==> 2nd row
= 1 1 1 ==> 3rd row
0 AND 1 = 0
0 1 0
1 1 1 ==> last row

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

Posted on 2016年4月20日 by YongJing(敬勇)

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跨实例控制并行

Posted on 2016年4月18日2016年4月18日 by YongJing(敬勇)

在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

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

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

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

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

Oracle 10g RAC跨实例控制并行

Posted on 2016年4月18日 by YongJing(敬勇)

在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中控制并行的新参数

Posted on 2016年4月18日 by YongJing(敬勇)

在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的值设置为一个具体的值，以达到明确控制实际并行度的目的。

具体数字
当自动并行度被激活时，指定一个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数据库

Posted on 2016年4月17日2016年11月30日 by YongJing(敬勇)

刚好有一个网友问我，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参数启用归档。

Oracle性能优化之虚拟索引

Posted on 2016年4月13日 by YongJing(敬勇)

虚拟索引是定义在数据字典中的伪索引，但没有相关的索引段。虚拟索引的目的是模拟索引的存–而不用真实的创建一个完整索引。这允许开发者创建虚拟索引来查看相关执行计划而不用等到真实创建完索引才能查看索引对执行计划的影响，并且不会增加存储空间的使用。如果我们观察到优化器生成了一个昂贵的执行计划并且SQL调整指导建议我们对某些的某列创建索引，但在生产数据库环境中创建索引与测试并不总是可以操作。我们需要确保创建的索引将不会对数据库中的其它查询产生负面影响，因此可以使用虚拟索引。

下面举例进行说明
1.创建一个测试表test

SQL> create table test as select * from dba_objects;

Table created.

2.从表test查询object_name等于standard的记录

SQL> select * from test where object_name='STANDARD';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED      LAST_DDL_TIM TIMESTAMP           STATUS  T G S
------------ ------------ ------------------- ------- - - -
SYS
STANDARD
                                      888                PACKAGE
19-APR-10    19-APR-10    2003-04-18:00:00:00 VALID   N N N


OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED      LAST_DDL_TIM TIMESTAMP           STATUS  T G S
------------ ------------ ------------------- ------- - - -
SYS
STANDARD
                                      889                PACKAGE BODY
19-APR-10    19-APR-10    2010-04-19:10:22:58 VALID   N N N

3.查询上面查询的执行计划

SQL> set autotrace traceonly explain
SQL> select * from test where object_name='STANDARD';

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     8 |  1416 |   155   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST |     8 |  1416 |   155   (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME"='STANDARD')

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

4.在表test的object_name列上创建一个虚拟索引

SQL> create index test_index on test(object_name) nosegment;

Index created.

为了创建虚拟索引必须在create index语句中指定nosegment子句，并且不会创建索引段。

5.来验证虚拟索引不会创建索引段

SQL> set autotrace off
SQL> select index_name from dba_indexes where table_name = 'TEST' and index_name = 'TEST_INDEX';

no rows selected

SQL> col OBJECT_NAME format a20;
SQL> select object_name, object_type from dba_objects where object_name = 'TEST_INDEX';

OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------
TEST_INDEX           INDEX

从上面的结果可以看到索引对象已经创建，但没有创建索引段。

6.重新执行sql查看创建的虚拟索引是否被使用

SQL> set autotrace traceonly explain
SQL> select * from test where object_name='STANDARD';

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     8 |  1416 |   155   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST |     8 |  1416 |   155   (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME"='STANDARD')

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

从上面的执行计划可以清楚地看到创建的虚拟索引并没有被使用

7.为了能使用所创建的虚拟索引，需要将_USE_NOSEGMENT_INDEXES设置为true

SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true;

Session altered.

8.重新执行sql查看创建的虚拟索引是否被使用

SQL> set long 900
SQL> set linesize 900
SQL> select * from test where object_name='STANDARD';

Execution Plan
----------------------------------------------------------
Plan hash value: 2627321457

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     8 |  1416 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST       |     8 |  1416 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_INDEX |   238 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME"='STANDARD')

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

从上面的执行计划可以看到当设置隐含参数_USE_NOSEGMENT_INDEXES后，优化器将会使用创建的虚拟索引。在使用虚拟索引需要注意，我们可以分析虚拟索引，但不能重建虚拟索引，如果重建虚拟索引会收到ORA-8114: “User attempted to alter a fake index”错误提示，可以删除虚拟索引。

Oracle 11gr2中的自动并行度

Posted on 2016年4月13日 by YongJing(敬勇)

在Oracle 11.2.0.2中只有I/O统计数据被收集才能使用自动并行度。当parallel_degree_policy被设置为auto时，Oracle数据库将会基于执行计划中操作的成本和硬件特性来判断是否使用并行，当在语句级别使用parallel或parallel(auto)暗示不管parallel_degree_policy设置为何值都会使用自动并行。

IO Calibration
硬件特性包括IO Calibration统计数据，因此这些统计数据必须被收集否则Oracle数据库将不会使用自动并行这个功能。下面的执行计划是在没有收集IO Calibration统计数据时生成的，在执行计划的note部分可以看到”skipped because of IO calibrate statistics are missing”这样的信息

SQL> set long 900
SQL> set linesize 900
SQL> set autotrace traceonly explain
SQL> select /*+ parallel */ * from emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 2873591275

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    14 |  1218 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    14 |  1218 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |    14 |  1218 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| EMP      |    14 |  1218 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
   - automatic DOP: skipped because of IO calibrate statistics are missing

Oracle提供了PL/SQL包dbms_resource_manager.calibrate_io来收集IO Calibration的统计数据。收集IO Calibration统计数据的持续时间由num_disks变量与RAC中节点数决定的。

SQL> select * from V$IO_CALIBRATION_STATUS;

STATUS        CALIBRATION_TIME
------------- ---------------------------------------------------------------------------
NOT AVAILABLE

SET SERVEROUTPUT ON
DECLARE
   lat INTEGER;
   iops INTEGER;
   mbps INTEGER;
BEGIN
    --DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat);
    DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat);
   DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
   DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
   dbms_output.put_line('max_mbps = ' || mbps);
END;
/

注意DBMS_RESOURCE_MANAGER.CALIBRATE_IO的前两个参数分别为num_disks,max_latency是输入变量，并且有三个输出变量。

num_disks:为了获得最精确的结果，最好提供数据库所使用的真实物理磁盘数。如果是使用ASM来管理数据库文件，那么就是指存储数据的磁盘组，那么只有存储数据的磁盘组中的物理磁盘作为num_disks变量值，不包含FRA磁盘组中的物理磁盘。

latency:对数据库块IO操作允许的最大延迟


SQL> set long 900
SQL> set linesize 900
SQL> SET SERVEROUTPUT ON
DECLARE
SQL>   2     lat INTEGER;
  3     iops INTEGER;
  4     mbps INTEGER;
  5  BEGIN
  6      --DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat);
  7      DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat);
  8     DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
  9     DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
 10     dbms_output.put_line('max_mbps = ' || mbps);
 11  END;
 12  /
max_iops = 390
latency = 9
max_mbps = 112

PL/SQL procedure successfully completed.

为了验证是否IO Calibration统计信息收集成功，在执行dbms_resource_manager.calibrate_io后查询v$io_calibration_status

SQL> select * from V$IO_CALIBRATION_STATUS;

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

再次执行看是否能使用自动并行度

SQL> set autotrace traceonly explain
SQL> select /*+ parallel */ * from emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 2873591275

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    14 |   532 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| EMP      |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2

可以看到在收集IO Calibration统计信息后，执行计划使用自动并行度。

当使用自动并行度，可以还需要调整一些调整参数。parallel_servers_target参数应该总是比parallel_max_servers参数值小，parallel_servers_target总是处于parallel_max_servers的75%到50%。如果开始看到大量并行度下降，那么应该使用这两个参灵敏的差距增大。