oracle 10046事件，解决dblink跨oracle server复制数据效率慢踩到的坑

           最近机器学习很火，公司也想着往这方面做一些试水项目，想着从大量的历史数据中预测用户行为。由于需要用到大量的数据做分析，所以得从生产环境中获取这些数据进行相关的分析。那么问题来了，我们总不可能直接对生产环境的数据进行操作然后分析吧（以免影响生产环境的业务），所以必须从生产环境中将大量的数据复制到本地oracle db，再做分析。本人对于这次的跨环境跨库数据复制有着深深的体会，特别是oracle dblink这种方式，总之是给它坑了一把，让我下面说一下如何进坑再出坑的，这个过程学到不少。（本人不是什么DBA，踩坑是家常便饭）。

1. 首先看看这次要复制的数据的量有多大，有两个表（为了避免泄露公司的信息，表名用了虚拟名，a表示生产环境上的表，b表示本地已经处理过数据的表），如下：

                 

2.  接着要对这两个表的内容进行关联，取出一些有利于分析的数据放到本地一张新表中。对于数据的抽取，一开始自己有两个想法：①这两个表的数据也不少，是否需要使用ETL工具进行数据复制呢？但是本人不会什么ETL工具，想到学起来也挺浪费时间的，所以干脆放弃了；②第二想法是使用sql进行数据的抽取建表，最后决定选择该方法。

3. 选择了第二个想法后就开始工作啦。那么既然选择了sql这种方式，毫无疑问可以使用oracle的dblink进行跨oracle server进行数据访问。关于dblink，可以自行百度谷歌啥的，但这货容易连接超时。首先第一感觉就是使用create table as方式进行数据复制，虚拟代码如下：

.

执行代码后，发现执行速度十分慢，测试过600条数据都需要30秒，这速度也太慢了，按道理create table as这种方式速度是超快的。。。

4. 速度遇到瓶颈了，只能谷歌搜索解决方案，谷歌查找了如何通过sql快速复制数据的方案，有些人推荐使用create table as(简称ctas) + parallel(oracle的并行)，也有推荐使用merge+parallel+nologging，等等。本人都尝试了各种方案，包括并行开到最大，nologging，最后发现效果都不明显，而且跟3中的执行效率几乎一样，谷歌啥的都没找到类似的问题。经过这么一折腾，决定查看一下3中select那块的sql执行计划如何，虚拟代码如下：

 然并卵，反复看了执行计划也看不出哪里不对劲。

5. 后来发现可以通过oracle的10046 event进行sql性能的监测，果断用一把，步骤如下：

   ①开启10046事件跟着：alter session set events '10046 trace name context forever, level 12';

   ②执行自己的sql

   ③停止10046事件跟踪：alter session set events '10046 trace name context off';

   ④找到本次跟踪生成的log文件：

          select distinct(m.sid),p.pid,p.tracefile from v$mystat m,v$session s,v$process p where m.sid=s.sid and s.paddr=p.addr;

   ⑤通过oracle自带的工具tkprof.exe查看上一个步骤产生的跟踪文件(tkprof.exe可以便于分析跟踪文件)，在cmd命令中输入如下：

       >tkprof D:\APP\LIWA\diag\rdbms\orcl\orcl\trace\orcl_ora_65988.trc D:\test.prf

     这样会在d盘生成一个便于分析查看的test.prf文件

   ⑥用notepad++打开test.prf，信息如下：

      

    神奇的发现红色框框的内容，原来大部分的时间都消耗在dblink的连接时长上了，终于都找到原因所在了。

6.最后sql要么改成远程库的表之间直接进行关联最后复制数据到本地oracle db，要不改成先将生产环境的数据全部复制到本地oracle db，然后再在本地oracle server进行表连接查询复制，这样可以大大减少远程库和本地库通过dblink通讯的时间。最后得出来的复制效率相当快，几千万数据十几分钟就能完成复制。终于都搞掂了，心情真舒畅！！！

总结：如果通过dblink这种方式进行跨环境（db server）的数据复制操作，千万不能跨环境进行表的连接查询并复制数据，这样大量时间会消耗在dblink通讯上。