深入剖析：oracle 的并行机制
刘盛，网名leonarding，the first ACEA in china， Oracle OCM10g&11g RHCE， ACOUG Core Member Blogger， DATAGURU Oracle数据库版主， ITPUB HADOOP版版主， DB2中国专家组成员。
所谓并行，就是把一个工作分成几份，分给不同进程同时进行处理，这是进程层面的。今天我们通过两个实例深入分析其原理机制
（1）用一个并行的SQL示例，比较10391事件和 V$PQ_TQSTAT结果的异同
10391事件可以跟踪整个并行流程，我们查看10391事件trace文件就可以知道sql语句并行执行情况
清理环境
LEO1@LEO1> drop table leo5 purge; Table dropped.
创建leo5表
LEO1@LEO1> create table leo5 as select * fromdba_objects; Table created.
表分析
LEO1@LEO1> executedbms_stats.gather_table_stats(ownname=>'LEO1',tabname=>'LEO5') PL/SQL procedure successfully completed.
启动10391事件
LEO1@LEO1> alter session set events '10391 trace namecontext forever,level 12'; Session altered.
在执行下面3条语句的时候记下当前时间，干什么呢后面会揭晓（非常重要哦）
2013-01-25 15:25 这是我的时间
执行3次sql语句
LEO1@LEO1> select /*+ parallel(leo5,4) */ count(*)from leo5; COUNT(*) ---------- 72011
在10391事件trace文件中找到并行信息
LEO1@LEO1> select /*+ parallel(leo5,4) */ count(*)from leo5; COUNT(*) ---------- 72011
我们执行几次就要在trace文件中找到几段
LEO1@LEO1> select /*+ parallel(leo5,4) */ count(*)from leo5; COUNT(*) ---------- 72011
下面还可以执行一些其他SQL语句用于刷trace
LEO1@LEO1> alter session set events '10391 trace namecontext off'; Session altered.
查看当前会话写入trace文件名，我们看到文件所在目录不在当前目录
打开trace文件选，会发现没有并行salve进程信息
LEO1@LEO1> !vim/u01/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_ora_16433.trc
难道oracle 11g不把并行信息写入trace文件？其实oracle跟我们玩了一次躲猫猫，它把信息藏在了log buffer cache 中，因为还没有刷到trace文件里，所以我们才没看到。那oracle多长时间清理一次缓冲区脏数据呢？
在我的oracle 11g数据库大约每11分钟刷一次（oracle 10g相对较短所以大家在10g上会立刻看到），这也和缓冲区中数据量有关，如果在生产环境中不停的产生trace log可能时间间隔会缩短，只有把并行信息刷到trace文件中，我们才能看到，这就是原因所在！
接下来我们对比几条SQL语句的并行信息在10G和11G中的区别。
ORACLE 11G版
2013-01-25 15:25 LEO1@LEO1> select /*+ parallel(leo5,4) */ count(*) from leo5; *** 2013-01-25 15:25:32.042 kxfrDmpUpdSys allocated slave set: nsset:1 nbslv:4 tinst=1 ipq=0 Slave set 0: #nodes:1 Min # slaves 4: Max # slaves:4 List of Slaves: slv: 0 nid:0 instid:1 sid:00 slv: 1 nid:0 instid:1 sid:01 slv: 2 nid:0 instid:1 sid:02 slv: 3 nid:0 instid:1 sid:03 List of Nodes: node 0
这是第一条语句的并行信息，对着时间找比较容易，使用了4个slave进程
ORACLE 10G版
kxfrDmpUpdSys allocated slave set: nsset:1 nbslv:4 Slave set 0: #nodes:1 Min # slaves 4: Max # slaves:4 List of Slaves: slv 0 nid:0 slv 1 nid:0 slv 2 nid:0 slv 3 nid:0 List of Nodes: node 0
可以看到，11G要比10G的多些信息，包括实例信息 会话信息等
*** 2013-01-25 15:25:34.115
dumping system information arch:255 (unknown) sess:133 myiid:1 mynid:1 ninst:1 maxiid:1, mnodes:0 Instances running on that system: inum:0 iid:1 kxfrDmpUpdSys allocated slave set: nsset:1 nbslv:4 tinst=1 ipq=0 Slave set 0: #nodes:1 Min # slaves 4: Max # slaves:4 List of Slaves: slv: 0 nid:0 instid:1 sid:00 slv: 1 nid:0 instid:1 sid:01 slv: 2 nid:0 instid:1 sid:02 slv: 3 nid:0 instid:1 sid:03 List of Nodes: node 0
这是第二条语句的并行信息
*** 2013-01-25 15:25:37.975
dumping system information arch:255 (unknown) sess:133 myiid:1 mynid:1 ninst:1 maxiid:1, mnodes:0 Instances running on that system: inum:0 iid:1 kxfrDmpUpdSys allocated slave set: nsset:1 nbslv:4 tinst=1 ipq=0 Slave set 0: #nodes:1 Min # slaves 4: Max # slaves:4 List of Slaves: slv: 0 nid:0 instid:1 sid:00 slv: 1 nid:0 instid:1 sid:01 slv: 2 nid:0 instid:1 sid:02 slv: 3 nid:0 instid:1 sid:03 List of Nodes: node 0
这是第三条语句的并行信息
我们来比较10391事件和 V$PQ_TQSTAT视图
都显示使用了4个并行服务进程
（2）演示一个DDL和DML操作的并行执行示例
并行常用于以下三种情况
1.FTS_Full Table Scan 2.IFFS_Index Fast Full Scan 3.分区表
DDL并行测试
如果想捕捉DDL并行信息，我们可以采用10046事件来过滤并行信息，因为10046事件记录了sql解析、执行、取操作的性能指标和等待事件，在等待事件中我们就可以看到PX事件来说明DDL的并行操作
清理环境
LEO1@LEO1> drop table leo6 purge; Table dropped.
启动10046事件
LEO1@LEO1> alter session set events '10046 trace namecontext forever,level 12'; Session altered.
使用4个并行进程来创建表
LEO1@LEO1> create table leo6 parallel 4 as select *from dba_objects; Table created.
使用4个并行进程来创建索引
LEO1@LEO1> create index leo6_index on leo6(object_id)parallel 4; Index created.
重建索引可以这种写法
LEO1@LEO1> alter index leo6_index rebuild parallel4; Index altered.
关闭10046事件
LEO1@LEO1> alter session set events '10046 trace namecontext off'; Session altered.
查看当前会话写入trace文件名
LEO1@LEO1> select name,value from v$diag_info wherename='Default Trace File'; NAME VALUE ------------------------------ ------------------------------------------------------------------------------- Default Trace File /u01/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_ora_16433.trc
注：oracle提供了一个tkprof工具来对trace文件进行格式化翻译，过滤出有用的信息
查看日志
LEO1@LEO1> !vim /u01/app/oracle/diag/rdbms/leo1/LEO1/trace/10046.txt
把并行信息截取出来
上面的PX信息充分说明了我们在进行DDL操作时使用了并行技术
DML并行测试
前提：首先说明oracle对DML并行操作是有限制的，必须设置启用会话并行度，否则即使SQL指定了并行，oracle也不会执行DML并行操作，其次oracle只对partition table分区表做并行处理（有几个分区就开几个并行），普通表oracle不做并行处理（即使设置了并行度oracle也不做并行处理），只限delete update merge操作
我们做个insert并行测试吧
在insert测试中只有insert into .. select 做并行才有意义，insert into..values 单条插入没有意义
LEO1@LEO1> explain plan for insert /*+ parallel(leo54) */ into leo5 select /*+ parallel(leo6 2) */ * from leo6;
Explained：这种写法只生成执行计划不做实际插入操作，下面我们来看看执行计划情况
LEO1@LEO1> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT
执行计划 insert和select操作别分使用了并行，它们是相互独立的互不干涉
-----the end