最近机器学习很火,公司也想着往这方面做一些试水项目,想着从大量的历史数据中预测用户行为。由于需要用到大量的数据做分析,所以得从生产环境中获取这些数据进行相关的分析。那么问题来了,我们总不可能直接对生产环境的数据进行操作然后分析吧(以免影响生产环境的业务),所以必须从生产环境中将大量的数据复制到本地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通讯上。