0

深入剖析:oracle 的并行机制

刘盛 2016-09-08
149


刘盛,网名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   这是我的时间


执行3sql语句

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


如何加入"云和恩墨大讲堂"微信群

搜索 盖国强(Eygle) :eyygle,或者扫描下面二维码,备注:云和恩墨大讲堂,即可入群。每周与千人共享免费技术分享,与讲师在线讨论。


近期文章

巧用触发器,实现DDL监控

Oracle中实现split相关方法总结

巧用SQL:oracle pl/sql split函数

AMDU数据抽取恢复

Null is Not Null引发的成本误区

Cache Buffer Chain Latch等待事

资源下载

(OraNews)回复关键字获取

2016DTCC, 2016数据库大会PPT;

DBALife,"DBA的一天"精品海报大图;

12cArch,“Oracle 12c体系结构”精品海报;

DBA01,《Oracle DBA手记》第一本下载;

YunHe“云和恩墨大讲堂”案例文档下载;


「喜欢文章,快来给作者赞赏墨值吧」
文章转载自刘盛,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论