巧用外部表避免大量的insert (r4笔记第71天)

昨天开发咨询我一个问题,希望我对下面的语句进行调优。 语句类似下面的形式

SELECT  subscriber_no 
FROM  SUBSCRIBER S
WHERE SUBSCRIBER_TYPE = 'RM' 
            and CONV_RUN_NO in  (20, 21, 22, 23) 
            and not EXISTS (SELECT s.AGREEMENT_NO 
                       FROM SERVICE_AGREEMENT S, CSM_OFFER C 
                       WHERE s.subscriber_no = S.AGREEMENT_NO 
                                    and SOC = C.SOC_CD 
                                   and SOC_TYPE  ='P' 
                       ) 

这个语句的执行计划如下:

Plan hash value:  1111602366
---------------------------------------------------------------------------------------------------------
|  Id  | Operation                        | Name                 | Rows  | Bytes |  Cost (%CPU)| Time      |
---------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                 |                      |     2 |    22 |  56648   (1)| 00:11:20 |
|*  1 |  FILTER                          |                       |       |       |            |          |
|*  2 |   TABLE ACCESS  FULL              | SUBSCRIBER           |     2 |    22 | 56646   (1)| 00:11:20  |
|   3 |   NESTED LOOPS                   |                      |       |        |            |          |
|   4 |    NESTED LOOPS                  |                       |     2 |    44 |     2   (0)| 00:00:01 |
|*  5 |     INDEX  RANGE SCAN             | SERVICE_AGREEMENT_PK |     8 |   104 |     1   (0)|  00:00:01 |
|*  6 |     INDEX RANGE SCAN             | CSM_OFFER_1IX        |      1 |       |     1   (0)| 00:00:01 |
|*  7 |    MAT_VIEW ACCESS BY INDEX  ROWID| CSM_OFFER            |     1 |     9 |     1   (0)| 00:00:01  |
---------------------------------------------------------------------------------------------------------
Predicate  Information (identified by operation  id):
---------------------------------------------------
   1 - filter(  NOT EXISTS (SELECT 0 FROM "PRDREFSSCA"."CSM_OFFER"
               "C","PRDAPPO"."SERVICE_AGREEMENT" "S" WHERE "S"."AGREEMENT_NO"=:B1 AND  "SOC"="C"."SOC_CD" AND
              "SOC_TYPE"='P'))
   2 -  filter(("CONV_RUN_NO"=20 OR "CONV_RUN_NO"=21 OR "CONV_RUN_NO"=22 OR  "CONV_RUN_NO"=23) AND
              "SUBSCRIBER_TYPE"='RM')
   5 -  access("S"."AGREEMENT_NO"=:B1)
   6 - access("SOC"="C"."SOC_CD")
   7 -  filter("SOC_TYPE"='P')

从语句来看是真没有多少什么调优空间了。为了想尽快得到结果,我建议加了一个hint /*+parallel(4)*/ 我一再叮嘱他,这中语句最好在备份库中执行,因为备份中的数据是相对比较新的,他也同意。备份库当时负载很低,执行大概用了6分钟的样子。输出结果大约有70多万。 我以为这件事就这么过去了,结果今天早上他找到我说,希望把这70多万条记录存到一个一个临时用户下,需要关联查找更多的细节。 这种问题让人有些纠结。不过为了问题的解决,也不能完全怪他。 尝试一: 首先的一个思想就是想吐省事,在中午的时候尝试生成这些记录。 create table issue_sub_list nologging as SELECT /*+parallel(4)*/ subscriber_no FROM SUBSCRIBER S WHERE SUBSCRIBER_TYPE = 'RM' and CONV_RUN_NO in (20, 21, 22, 23) and not EXISTS (SELECT s.AGREEMENT_NO FROM SERVICE_AGREEMENT S, CSM_OFFER C WHERE s.subscriber_no = S.AGREEMENT_NO and SOC = C.SOC_CD and SOC_TYPE ='P' ) 这条语句结果在生产上执行了将近10分钟还没有完成的意思,我查看执行计划的输出列情况,才全表扫描了30%,所以果断终止了这个操作。 这条路不建议使用,我看还有什么别的路可走。 尝试二: 一来就是对得到的数据进一步转换为insert语句。尝试在windows的ultraEdit下面编辑,因为这些id有差不多11M左右,使用文本编辑器没过一会,就提示缓存溢出,卡在那了。 尝试三: 看来文本编辑器还是很有使用限制的,linux环境下的命令方式要相对好很多。百兆上G的日志都没有问题。 我写了如下的脚本。 while read line do sqlplus -s n1/n1@xxx <<EOF insert into issue_sublist values( $line); EOF done < sub_list.log 执行的速度来看,单条语句是没有问题的,但是这条语句执行了10多分钟,还没有完,进步还是让人不够满意。 尝试四: 大家使用外部表来查看数据库的日志信息,这是一种方式,我们也可以应用到这个场景中。来看看外部表的情况。 创建了directory create directory EXT_DATAPUMP_DIR as '/oravl01/oracle/ext_datapump/DUMP'; grant read,write on directory EXT_DATAPUMP_DIR to n1; 然后使用语句创建了外部表。 create table issue_sublist_ext(text varchar2(1000)) organization external (type oracle_loader default directory EXT_DATAPUMP_DIR location('sub_list.log') ); Table created. Elapsed: 00:00:00.01 SQL> select count(*)from issue_sublist_ext; COUNT(*) ---------- 769685 Elapsed: 00:00:00.25 SQL> drop table issue_sublist; Table dropped. Elapsed: 00:00:00.04 我们来创建一个表存储这些数据。 SQL> create table issue_sublist as select *from issue_sublist_ext; Table created. Elapsed: 00:00:00.64 SQL> !exp n1/n1@xxx file=issue_sublist.dmp tables=issue_sublist buffer=910200 About to export specified tables via Conventional Path ... . . exporting table ISSUE_SUBLIST 769951 rows exported Export terminated successfully without warnings. 来看看dump的情况,生成的dump文件和文本的大小差不多大小。 TRUABP4 /oravl01/oracle/ext_datapump/DUMP > ll total 24880 -rw-r--r-- 1 oracle dba 11545275 Mar 11 12:04 sub_list.log -rw-r--r-- 1 oracle dba 357 Mar 11 12:05 ISSUE_SUBLIST_EXT_8860.log -rw-r--r-- 1 oracle dba 13869056 Mar 11 12:07 issue_sublist.dmp 这个dump拷贝到生产中,直接imp速度是相当快的,一秒内搞定。 > time imp n1/n1 file=issue_sublist.dmp tables=issue_sublist buffer=910200 fromuser=n1 touser=cust_prsnl . importing N1's objects into CUST_PRSNL . . importing table "ISSUE_SUBLIST" 769951 rows imported Import terminated successfully without warnings. real 0m1.277s user 0m0.133s sys 0m0.060s 通过这个例子我们可以看到,我们尝试尽可能减少生产的负载,使用外部表间接转换为dump,会减少大量的insert语句执行,而且效率也高。可以作为一种参考。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-03-11

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏idba

死锁案例之四

一 前言 死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想...

9530
来自专栏沃趣科技

应用示例荟萃 | performance_schema全方位介绍(上)

经过前面6个篇幅的学习,相信大家对什么是performance_schema,已经初步形成了一个整体认识,但我想很多同行看完之前的文章之后可能还是一脸懵逼,今天...

26130
来自专栏Netkiller

数据库安全·开发加密插件

以下节选择《Netkiller Architect 手札》 作者:netkiller 地址 http://www.netkiller.cn/archit...

33370
来自专栏杨建荣的学习笔记

merge语句导致的CPU使用率过高的优化(r7笔记第4天)

今天有一个数据库有点反常,早上的时候报出了CPU使用率的警告。 警告内容如下: ZABBIX-监控系统: -----------------------...

34050
来自专栏杨建荣的学习笔记

关于ORA-01555的问题分析(r5笔记第87天)

今天开发的同事发给我一个问题,在运行某一个Job的时候抛出了ORA错误,希望我们看看从数据库层面能不能发现什么。 错误日志如下: Function: Entit...

31460
来自专栏杨建荣的学习笔记

关于等待事件"read by other session"(r3笔记第89天)

在查看数据库负载的时候,发现早上10点开始到12点的这两个钟头,系统负载异常的高。于是抓取了一个awr报告。 Snap IdSnap TimeSessions...

30090
来自专栏乐沙弥的世界

Oracle 聚簇因子(Clustering factor)

    聚簇因子是 Oracle 统计信息中在CBO优化器模式下用于计算cost的参数之一,决定了当前的SQL语句是否走索引,还是全表扫描以及是否作为嵌套连接外...

17110
来自专栏杨建荣的学习笔记

简单实用的sql小技巧(第一篇) (r3笔记第36天)

今天和大家简单分享几个实用的sql小技巧。还有一些还在整理中,会不断的分享出来。 有些其实也不算是sql的技巧,可能大家在写sql语句的时候没有意识到我们可以通...

31230
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第七篇(r2笔记99天)

在数据迁移完成之后,开始了例行的后期数据库维护,早上一来就发现了一个sql执行时间很长了。达到了37279秒。最后在改进调优之后执行速度在1分钟以内。 这个速度...

35880
来自专栏数据库新发现

关于dirty buffer

SQL> select VIEW_DEFINITION from v$fixed_view_definition where VIEW_NAME = 'GV$B...

10730

扫码关注云+社区

领取腾讯云代金券