在11.2以前,当用户发出并行查询,Oracle会将负载分配到所有的实例上,而在11.2中,Oracle提供了初始化参数,可以限制并行只运行在当前实例上。
RAC环境对并行查询的支持:http://yangtingkun.itpub.net/post/468/468445
看一个简单的例子:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create table t
2 as select a.*
3 from dba_objects a;
Table created.
SQL> insert into t
2 select *
3 from t;
72122 rows created.
SQL> insert into t
2 select *
3 from t;
144244 rows created.
SQL> insert into t
2 select *
3 from t;
288488 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from t;
COUNT(*)
----------
576976
下面在另外的会话检查系统中并行进程的使用:
SQL> select * from gv$px_process;
INST_ID SERV STATUS PID SPID SID SERIAL#
---------- ---- --------- ---------- ------------------------ ---------- ----------
2 PZ99 IN USE 47 2568 266 3388
1 PZ99 IN USE 46 16790 26 2657
下面设置会话级别的parallel_force_local参数:
SQL> show parameter parallel_force
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_force_local boolean FALSE
SQL> alter session set parallel_force_local = true;
Session altered.
SQL> select /*+ parallel(4) */ count(*) from t;
COUNT(*)
----------
576976
检查并行进程使用情况:
SQL> select * from gv$px_process;
INST_ID SERV STATUS PID SPID SID SERIAL#
---------- ---- --------- ---------- ------------------------ ---------- ----------
2 PZ99 IN USE 47 2568 266 3416
1 PZ99 IN USE 46 16790 32 3928
1 P003 AVAILABLE 52 22527
1 P002 AVAILABLE 51 22519
1 P001 AVAILABLE 45 22517
1 P000 AVAILABLE 37 22515
6 rows selected.
可以看到,所有的并行都集中在实例1上,下面设置parallel_force_local为false:
SQL> alter session set parallel_force_local = false;
Session altered.
SQL> select /*+ parallel(4) */ count(*) from t;
COUNT(*)
----------
576976
再次检查平行进程使用情况:
SQL> select * from gv$px_process;
INST_ID SERV STATUS PID SPID SID SERIAL#
---------- ---- --------- ---------- ------------------------ ---------- ----------
1 PZ99 IN USE 46 16790 32 3932
1 P003 AVAILABLE 52 22527
1 P002 AVAILABLE 51 22519
1 P001 AVAILABLE 45 22517
1 P000 AVAILABLE 37 22515
2 PZ99 IN USE 47 2568 265 649
2 P003 AVAILABLE 48 17432
2 P002 AVAILABLE 46 17424
2 P001 AVAILABLE 43 17413
2 P000 AVAILABLE 40 17411
10 rows selected.
可以看到,并行这次分配到两个实例上。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-684317/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-684317/