并发执行SQL

Oracle并行FAQ
?什么是并行？
并行是Oracle为了提高大数据量的运算效率而提供多进程协作技术，它可以让多个CPU同时处理一个计算任务，充分使用系统资源，提高计算效率。
?什么操作支持并行？
大部分的DML(insert/update/delete/merge)、DDL、Query都支持并行操作。
?什么情况下需要启用并行？
并不是所有的SQL都应该使用并行。要使用并行需满足以下两个条件，否则结果可能适得其反：
1） 机器有充分的空闲资源(CPU、内存等)
2） 参与运算的数据量大。在当前系统初定于参与运算数据量大于10GB或者SQL运行时间超过30分钟可考虑使用并行。
?如何启用并行？
可以用hint、alter session或者设置对象并行属性三种方式设置启用并行。三种方式任意一种就可以使并行生效，如果多种方式同时存在的话，则优先级顺序是：hint -> alter session -> table/index degree。

hint
alter session
table/index degree
Query
select /+ parallel(a,8) /count(1) from table_name a;
alter session force query parallel 8;
select count(1) from table_name a;
alter table table_name parallel 8;
select count(1) from table_name a;
DML
默认情况下，parallel dml是禁用的，需要先用alter session启用：
alter session enable parallel dml;
update /+ parallel(a,8) / table_name a set col1=1;
alter session force parallel dml parallel 8;
update table_name a set col1=1;
alter session enable parallel dml;
alter table table_name parallel 8;
update table_name a set col1=1;
以上三步缺一不可。
DDL
无
alter session force parallel ddl parallel 8;
create table table_name as select from ……
create table table_name parallel 8 as select from ……
create index index_name …… parallel 8;

注意：
1) 上述的alter session enable只是表示让当前会话支持并行，最终并行需要通过hint或者table/index degree来实现；而alter session force表示强制并行，无需hint等配合使用。
2) 建议在hint或者alter session中控制并行，不要通过修改表或者索引的属性(degree)来控制。在查询频繁的情况下，把表或者索引的并行度改大可能会导致严重的性能问题。
?对于insert ……select ……如何设置并行？
insert……select….包含两个部分，query和dml，可以为这两个部分分别设置并行度。因为insert操作是dml，因此还需要通过alter session方式把pdml启用，如：
alter session enable parallel dml;
insert /+ append parallel(a,4) / into table_a a
select /+ parallel(b,8) / from table_b b where ……;
?在存储过程/包中如何启用并行？
参考上文所述。唯一的区别是如果需要执行alter session，则需要用动态语句执行，如：
execute immediate ‘alter session enable parallel dml’;
?并行度该设置多大？
一般来说，并行度越大SQL的执行效率越高，但是不建议设置超过CPU核数的并行度。在当前的RAC中，考虑到同时会有多个任务在跑，为了不影响其他任务，并行度需要严格控制在32个以下，一般的建议值是8和16。
另外，建议并行度设置为2的n次方，如2/4/8/16/32等。
?既有DML又有query的SQL如何设置并行度？如insert …..select…..
1) 如果写入量大，则在insert上加并行会明显提升性能；否则在insert上加并行基本没有什么意义。如：
insert into t(game_name,num) select game_name,count(1) cnt from popt_total_login_all_his group by game_name;
这种SQL主要瓶颈在查询上，写入量很少，因此只需要在查询部分设置并行即可。
2) 如果查询量大，则在查询上加并行会明显提升性能；
3) 如果写入和查询量都大，则在两个部分都要加并行，不要让其中一方成为瓶颈。
总结起来就是：瓶颈在部分，就在这部分上加并行；如果都有瓶颈，则都加并行。
对于pdml，建议：
1) 由于并行dml有诸多限制和弊端，因此在写入量不大的情况下，尽量不启用并行dml。
2) insert和query的并行度不一定要一致，可根据实际调整，一般设置query并行度大于等于insert并行度。并行度最好设置为2的n次方。
3) 并行度不要设置超过CPU的个数
?如何查询并行是否起作用？
1) 在SQL执行的时候，在PL/SQL DEV看是否有多个活动会话执行一个SQL
2) SQL执行完了以后，在同一个会话查询v$pq_sesstat
SQL> select from v$pq_sesstat;
STATISTIC LAST_QUERY SESSION_TOTAL

---

Queries Parallelized 1 1
DML Parallelized 0 0
DDL Parallelized 0 0
上面的结果中，如果last_query有非0的值，表示并行起作用了。
其他方法先不告诉你了……
?如何控制并行度？
可以用hint、alter session以及table/index degree指定并行度，详细请参考上文【如何启用并行】部分。
?如何跨实例并行？
在当前的RAC环境，为了减少cache fusion，提高效率，默认情况把SQL并行限制在同一个节点执行。如果有超超大的SQL需要多个节点同时并行，则可以用如下语句来控制并行可跨越的实例：
alter session set parallel_instance_group=dw; --可跨越4个节点
alter session set parallel_instance_group=dw1; --限制在节点1执行
alter session set parallel_instance_group=dw12; --限制在节点1和2执行
alter session set parallel_instance_group=dw124; --限制在节点1/2/4三个节点执行
以此类推。
原则上不允许跨实例并行，如果必须跨实例，则使用前需征得DBA同意。
?为什么我的SQL达不到我设定的并行度？
并行度受以下(不限于)条件限制：
1) 系统的session、process参数的设定(一般不会超过)
2) parallel_max_serversx限制。这个限定目前是256，意味着一个节点最多启动256个并行从属进程。在多用户多SQL同时执行的情况下，很容易达到这个上限。
3) 对于个人用户，当前最多只允许4个或者10个连接同时访问数据库。这意味着个人用户下，并行度不能超过4或者10。(systemuser等程序账号不在此限制范围之内)
?并行有什么弊端？
1） 并行dml会浪费空间，并行度越高，浪费越厉害
2） 被并行dml影响的表需要提交或者回滚后才能被后续的SQL使用，否则会报错，这可能会影响事务的一致性。
3） 并行容易触发异常或者bug，降低系统和程序的稳定性
4） ……
一、Parallel

1． 用途

强行启用并行度来执行当前SQL。这个在Oracle 9i之后的版本可以使用，之前的版本现在没有环境进行测试。也就是说，加上这个说明，可以强行启用Oracle的多线程处理功能。举例的话，就像电脑装了多核的CPU，但大多情况下都不会完全多核同时启用（2核以上的比较明显），使用parallel说明，就会多核同时工作，来提高效率。

但本身启动这个功能，也是要消耗资源与性能的。所有，一般都会在返回记录数大于100万时使用，效果也会比较明显。

2． 语法

/+parallel(table_short_name,cash_number)/

这个可以加到insert、delete、update、select的后面来使用（和rule的用法差不多，有机会再分享rule的用法）

开启parallel功能的语句是：

alter session enable parallel dml;

这个语句是DML语句哦，如果在程序中用，用execute的方法打开。

3． 实例说明

用ERP中的transaction来说明下吧。这个table记录了所有的transaction，而且每天数据量也算相对比较大的（根据企业自身业务量而定）。假设我们现在要查看对比去年一年当中每月的进、销情况，所以，一般都会写成：

select to_char(transaction_date,'yyyymm') txn_month,

   sum(

    decode(

        sign(transaction_quantity),1,transaction_quantity,0
          )

      ) in_qty,

   sum(

    decode(

        sign(transaction_quantity),-1,transaction_quantity,0
          )

      ) out_qty

from mtl_material_transactions mmt

where transaction_date >= add_months(

                        to_date(    

                            to_char(sysdate,'yyyy')||'0101','yyyymmdd'),

                            -12)

and transaction_date <= add_months(

                        to_date(

                            to_char(sysdate,'yyyy')||'1231','yyyymmdd'),

                            -12)

group by to_char(transaction_date,'yyyymm')

这个SQL执行起来，如果transaction_date上面有加index的话，效率还算过的去；但如果没有加index的话，估计就会半个小时内都执行不出来。这是就可以在select 后面加上parallel说明。例如：
select /+parallel(mmt,10)/
to_char(transaction_date,'yyyymm') txn_month,

...

这样的话，会大大提高执行效率。如果要将检索出来的结果insert到另一个表tmp_count_tab的话，也可以写成：
insert /+parallel(t,10)/
into tmp_count_tab

(

txn_month,

in_qty,

out_qty

)

select /+parallel(mmt,10)/
to_char(transaction_date,'yyyymm') txn_month,

...

插入的机制和检索机制差不多，所以，在insert后面加parallel也会加速的。关于insert机制，这里暂不说了。
Parallel后面的数字，越大，执行效率越高。不过，貌似跟server的配置还有oracle的配置有关，增大到一定值，效果就不明显了。所以，一般用8,10,12,16的比较常见。我试过用30，发现和16的效果一样。不过，数值越大，占用的资源也会相对增大的。如果是在一些package、function or procedure中写的话，还是不要写那么大，免得占用太多资源被DBA开K。

4． Parallel也可以用于多表

多表的话，就是在第一后面，加入其他的就可以了。具体写法如下：

/+parallel(t,10) (b,10)/

5． 小结

关于执行效率，建议还是多按照index的方法来提高效果。Oracle有自带的explan road的方法，在执行之前，先看下执行计划路线，对写好的SQL tuned之后再执行。实在没办法了，再用parallel方法。Parallel比较邪恶，对开发者而言，不是好东西，会养成不好习惯，导致很多bad SQL不会暴漏，SQL Tuning的能力得不到提升。我有见过某些人create table后，从不create index或primary key，认为写SQL时加parallel就可以了。

/
Select Count() From HSC_RECDL_PROXYLOG Where transid Like '20120613%';
Select Count() From HSC_CU_INDIVIDUAL Where
Select Count() From HSC_CU_CUSTOMER Where custid Like '%20120613%';
Select From HSC_CU_INDIVIDUAL Order By custid Desc;
Select From HSC_CU_CUSTOMER Where hometel='15678654357' Order By custid Desc;
*/
---节点三户资料_ALL
--开启并发:
alter session Enable parallel dml;

--开启表的并发模式:
Alter Table HSC_CU_INDIVIDUAL Parallel;

Alter Table HSC_REC_PROXYLOG Parallel;

--关闭操作日志:
/
Alter Table HSC_CU_INDIVIDUAL NOLOGGING;
Alter Table HSC_REC_PROXYLOG NOLOGGING;
/

delete /+ parallel(a,12) / From HSC_CU_INDIVIDUAL a Where custid like '2012%';

delete /+ parallel(a,12) / From HSC_REC_PROXYLOG a Where transid like '2012%';
Commit;

--关闭并发:
Alter Table HSC_CU_INDIVIDUAL noParallel;

Alter Table HSC_REC_PROXYLOG noParallel;

--打开操作日志:
/*
Alter Table HSC_CU_INDIVIDUAL LOGGING;

Alter Table HSC_REC_PROXYLOG LOGGING;
*/