insert中启用错误日志的问题及分析(r2第10天)

在平时的工作中,有时候需要insert一批数据,这些数据可能是临时表,外部表,普通表,子查询等形式,类似下面的格式 insert into xxxx (select xxxxx from xxx where xxxxx); 如果其中有冗余数据的时候,整个Insert会自动rollback,一条数据也插不进去,错误类似下面的形式。 insert /*+ append */into mo1_memo select *from MO1_MEMO_EXT_92; * ERROR at line 1: ORA-00001: unique constraint (MIG_TEST.MO1_MEMO_PK) violated 可能我们想保证正常数据能够插入,对于违反约束等的数据稍后处理,这个是用错误日志就是一个很好的选择。 首先就是创建错误日志,可以使用提供的包来创建,也可以手动创建。 这里我需要用到表含有lob字段,创建错误日志的时候有下面的错误。 EXEC DBMS_ERRLOG.create_error_log(dml_table_name => 'MO1_MEMO') BEGIN DBMS_ERRLOG.create_error_log(dml_table_name => 'MO1_MEMO'); END; ERROR at line 1: ORA-20069: Unsupported column type(s) found: MEMO_SYSTEM_TEXT_C ORA-06512: at "SYS.DBMS_ERRLOG", line 235 ORA-06512: at line 1 不过想想也是合理的。不过问题还是要解决的。 可以看看创建错误日志的包,oracle已经考虑到了,我们可以忽略这种不支持的类型,当然还可以指定错误日志的名字。 SQL> desc dbms_errlog PROCEDURE CREATE_ERROR_LOG Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- DML_TABLE_NAME VARCHAR2 IN ERR_LOG_TABLE_NAME VARCHAR2 IN DEFAULT ERR_LOG_TABLE_OWNER VARCHAR2 IN DEFAULT ERR_LOG_TABLE_SPACE VARCHAR2 IN DEFAULT SKIP_UNSUPPORTED BOOLEAN IN DEFAULT 我们创建错误日志 SQL> EXEC DBMS_ERRLOG.create_error_log(dml_table_name => 'MO1_MEMO',SKIP_UNSUPPORTED=>true,ERR_LOG_TABLE_NAME=>'MO1_MEMO_ERROR'); PL/SQL procedure successfully completed. Elapsed: 00:00:00.03 尝试插入有冗余的数据, SQL> insert /*+ append */into mo1_memo select *from MO1_MEMO_EXT_92 LOG ERRORS INTO MO1_MEMO_ERROR('test_unique') REJECT LIMIT UNLIMITED 2 / insert /*+ append */into mo1_memo select *from MO1_MEMO_EXT_92 LOG ERRORS INTO MO1_MEMO_ERROR('test_unique') REJECT LIMIT UNLIMITED * ERROR at line 1: ORA-00001: unique constraint (MIG_TEST.MO1_MEMO_PK) violated Elapsed: 00:00:17.32 直接抛了错误,看来错误日志没有正确启用。 查看错误日志,里面也是空的。

SQL> SELECT *FROM MO1_MEMO_ERROR; --no rows 反复尝试,最后发现是Hint的原因,去掉Hint 就没有问题了。 SQL> insert into mo1_memo select *from MO1_MEMO_EXT_92 LOG ERRORS INTO MO1_MEMO_ERROR('test_unique') REJECT LIMIT UNLIMITED; 99 rows created. Elapsed: 00:04:04.83

1* select count(*)from mo1_memo_error SQL> / COUNT(*) ---------- 907544 错误日志里面有详细的数据信息。但是如果足够细心查看执行时间的话,就会发现如果不使用append,性能就会差很多。 下面是一个简单的测试, 如果不使用append的时候,插入80万左右的数据在1分钟左右,如果使用了append就只需要大概13秒左右。 还有上面的测试结果,如果80万记录中99%左右的数据有冗余,插入错误日志就需要大概4分钟的样子 SQL> insert into mo1_memo select * from mo1_memo_ext_99 LOG ERRORS INTO MO1_MEMO_ERROR REJECT LIMIT UNLIMITED; 877245 rows created. Elapsed: 00:01:12.91 SQL> insert into mo1_memo select * from mo1_memo_ext_98; 753637 rows created. Elapsed: 00:01:04.75 SQL> rollback; Rollback complete. Elapsed: 00:00:56.35 SQL> insert /*+append*/ into mo1_memo select *from mo1_memo_ext_98; 753637 rows created. Elapsed: 00:00:13.20 所以启用错误日志可以根据大家的需求来选择,有利有弊。

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

原文发表时间:2014-06-21

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏idba

死锁案例之四

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

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

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

生产环境中有大量的sql语句在运行,尽管有awr,ash做数据的收集统计,但是dba的调优工作大多数情况都是在问题已经发生后做排查的,有些sql语句可能执行的时...

2704
来自专栏乐沙弥的世界

PL/SQL-->UTL_FILE包的使用介绍

    在PL/SQL中,UTL_FILE包提供了文本文件输入和输出互功能。也就是说我们可以通过该包实现从操作系统级别来实现文件读取输入或者是写入到操作系统文件...

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

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

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

3185
来自专栏SpringBoot 核心技术

第七章:使用QueryDSL与SpringDataJPA实现子查询

1391
来自专栏沃趣科技

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

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

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

一个普通数据库用户所能查到的"意料之外"的信息(r2笔记98天)

有时候限于工作环境的情况,大多数开发人员只得到了一个权限收到限制的数据库用户。 可能你都不知道你所拥有的数据库用户都能查到哪些你想象不到的数据库信息,其实你知道...

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

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

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

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

一个SQL性能问题的优化探索(二)(r11笔记第38天)

继续前几天的一个案例一个SQL性能问题的优化探索(一)(r11笔记第33天) 如下的SQL语句存在索引字段CARD_NO,但是执行的时候却走了全表扫描,因为这是...

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

关于dual表的破坏性测试(r3笔记第60天)

关于dual表的破坏性测试,既然是破坏性测试,就需要确定这个测试仅限于测试或者个人学习所用,可能有些sql看似极为简单,但是一旦运行就会导致整个业务系统崩溃。 ...

37813

扫码关注云+社区