一个十分诡异的异常！

Question

一个十分诡异的异常！昨天客户反映了一个异常，如下：
下面是一个很单纯的SQL，往一个备源档 S06020DCCP1插入数据，语法很简单：INSERT INTO S06020DCCP1...SELECT ....
但是诡异的事情出现了：
我后面的select 来源资料查询出只有6笔资料，但是执行insert into S06020DCCP1 ... SELECT ....
居然插入了4035笔资料，感到很奇怪！
我到客户正式区资料库模拟，确实情况如上。
更诡异的是：
居然一会又正常了，即插入6笔，一会又不正常了，即插入4035笔！
Insert - 4035 row(s), executed in 1.969 sec.
Total execution time 2 sec.   Insert - 6 row(s), executed in 0.187 sec.
Total execution time 0.203 sec.异常一直查到今天，未果！  不知道坛友们是否遇到过，或者有什么想法没？
总感觉这是在动态变化的。下面是该SQL语句：
INSERT INTO S06020DCCP1(CO,COABR,FCTCLASS,FCTCLASSNM,TGDSDAY,DSTPORT,DSTPORTNM,LINECLASS
                       ,SOURODERNO,RAMTXSNOYD,OPNRARGN,OPNRADAT,SPEC,SPECNM,SKU,MTCHCLRS
                       ,ODQTY,ITM,LVID,VER,SZNUMBER,RGNID
                       ,RGNNM,MTNO,MTNM,COLOR,UN
                       ,UQTY,XREM,JMPGASACO,SORTASACO,TXEMP,TXDAT,TXTM
                       ,RFLDONE,LISMPID, QTY,LISDP)
SELECT CO, COABR, FCTCLASS, FCTCLASSNM, TGDSDAY, DSTPORT, DSTPORTNM, LINECLASS, 
       SOURODERNO, RAMTXSNOYD, OPNRARGN,OPNRADAT, SPEC, SPECNM, SKU, MTCHCLRS, 
       ODQTY, ITM, LVID, VER, SZNUMBER, RGNID, 
       RGNNM, MTNO, MTNM, COLOR,UN, 
       UQTY, XREM,JMPGASACO, SORTASACO, TXEMP, TXDAT, TXTM, 
       RFLDONE, LISMPID, QTY,LISDP
FROM (
      SELECT DISTINCT dccp1.CO, dccp1.COABR, dccp1.FCTCLASS, dccp1.FCTCLASSNM, dccp1.TGDSDAY, dccp1.DSTPORT, dccp1.DSTPORTNM,dccp1.LINECLASS, 
                      dccp1.SOURODERNO, dccp1.RAMTXSNOYD, dccp1.OPNRARGN, dccp1.OPNRADAT, dccp1.SPEC, dccp1.SPECNM,dccp1.SKU, dccp1.MTCHCLRS, 
                      dccp1.ODQTY, dccp1.ITM, dccp1.LVID, dccp1.VER, dccp1.SZNUMBER, dccp1.RGNID,
                      dccp1.RGNNM,dca92.mtsid||dca92.colorid||dca92.misectscalesid mtno,'' mtnm, dca92.colorid COLOR, dccp1.UN,
                      dccp1.uqty * dca92.unuqty UQTY,dccp1.XREM, dccp1.JMPGASACO,dccp1.SORTASACO, dccp1.TXEMP, dccp1.TXDAT, dccp1.TXTM, 
                      dccp1.RFLDONE,dccp1.LisMpId, dccp1.QTY ,dccp1.LISDP
      FROM  dcp1 dccp1,t06020dca91 dca91,t06020dca92 dca92
      WHERE dca92.co=dca91.co AND dca92.fctclass=dca91.fctclass AND dca92.hmat=dca91.hmat AND dca91.hmat LIKE 'G%'
      AND dccp1.co=dca92.co AND dccp1.mtno=dca91.mtsid||dca91.colorid||dca91.misectscalesid 
      AND dccp1.co='3' AND dccp1.txemp='DSP'
    );
不好意思，代码编辑器有问题，CSDN无法编辑SQL格式了！

Answer 1

补充点更诡异的是：不同机子执行结果还不一样，我让几个同事也远程连接到客户正式区资料库执行相同的超作，
一个同事执行都是正常的，即查询6笔，插入也是6笔
一个同事查询6笔，但是insert 3605笔！
一个同事查询6笔，但是insert 4035笔！
。咋感觉有点不稳定呢 ？

Answer 2

不正确的那些，到底插入了什么数据呢，可以先初步分析一下错误数据
同时结合那几张表的定义，主要是主KEY的定义

Answer 3

 插入的数据就只有材料编号MTNO，颜色COLOR，用量UQTY这三个栏位不一样，其它字段都是一样的
 分析过这些数据，但是找不出关联性为什么插入了，我查询出只有6笔为什么插入不一样

Answer 4

[
材料编号MTNO，颜色COLOR，用量UQTY这三个栏位不一样
]是不是产生排列组合了？
各张表的主KEY是不是都在WHERE条件里关联了？
查询用的工具是什么？OB的话以前碰到过奇怪问题的
INSERT前的检索语句，在SQLPLUS里执行的话，结果是几条，或者还是一会儿正常，一会儿不正常

Answer 5

这个应该跟工具无关，客户端使用的是SQLTools 1.4.2
我现在在分析数据，一步一步从分析数据看看。

Answer 6

oracle默认为read commited,是不是在你查询后到insert执行期间有新数据插入?

Answer 7

这个不可能。在这期间没有新数据插入，也没有触发器，只是很单纯的SQL，我估计是表连接发生改变，导致笛卡尔乘积不同，也就是说Oracle采用了不同的执行计划，导致了选择了不同的方式的表连接顺序，产生了不同的结果集，但这个在insert的时候改变的，因为我每次selelct结果集都是同一个6笔
我再追查下呢

Answer 8

--现在我还真有点搞不懂了，确实执行计划不一样：--1、查询6笔，插入4306笔，执行计划分别是
PLAN_TABLE_OUTPUT
Plan hash value: 921494302------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     1 |  2130 |  1329   (2)| 00:00:16 |
|   1 |  VIEW                         |                |     1 |  2130 |  1329   (2)| 00:00:16 |
|   2 |   HASH UNIQUE                 |                |     1 |   300 |  1329   (2)| 00:00:16 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T06020DCA92    |     1 |    50 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS              |                |     1 |   300 |  1328   (2)| 00:00:16 |
|*  5 |      HASH JOIN                |                |     5 |  1250 |  1313   (2)| 00:00:16 |
|*  6 |       TABLE ACCESS FULL       | DCP1           |     1 |   205 |   111   (1)| 00:00:02 |
|*  7 |       TABLE ACCESS FULL       | T06020DCA91    | 21730 |   954K|  1201   (2)| 00:00:15 |
|*  8 |      INDEX RANGE SCAN         | PK_T06020DCA92 |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   5 - access("DCCP1"."MTNO"="DCA91"."MTSID"||"DCA91"."COLORID"||"DCA91"."MISECTSCALESID
              ")
   6 - filter("DCCP1"."CO"='3' AND "DCCP1"."TXEMP"='DSP')
   7 - filter("DCA91"."CO"='3' AND "DCA91"."HMAT" LIKE 'G%')
   8 - access("DCA92"."CO"='3' AND "DCA92"."FCTCLASS"="DCA91"."FCTCLASS" AND
              "DCA92"."HMAT"="DCA91"."HMAT")
       filter("DCA92"."HMAT" LIKE 'G%')
--insert into ...selelct ...   4306
PLAN_TABLE_OUTPUT
Plan hash value: 528790147------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT              |                |     1 |  2130 |  1329   (2)| 00:00:16 |
|   1 |  VIEW                         |                |     1 |  2130 |  1329   (2)| 00:00:16 |
|   2 |   SORT UNIQUE                 |                |     1 |   300 |  1329   (2)| 00:00:16 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T06020DCA92    |     1 |    50 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS              |                |     1 |   300 |  1328   (2)| 00:00:16 |
|*  5 |      HASH JOIN                |                |     5 |  1250 |  1313   (2)| 00:00:16 |
|*  6 |       TABLE ACCESS FULL       | DCP1           |     1 |   205 |   111   (1)| 00:00:02 |
|*  7 |       TABLE ACCESS FULL       | T06020DCA91    | 21730 |   954K|  1201   (2)| 00:00:15 |
|*  8 |      INDEX RANGE SCAN         | PK_T06020DCA92 |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   5 - access("DCCP1"."MTNO"="DCA91"."MTSID"||"DCA91"."COLORID"||"DCA91"."MISECTSCALESID
              ")
   6 - filter("DCCP1"."CO"='3' AND "DCCP1"."TXEMP"='DSP')
   7 - filter("DCA91"."CO"='3' AND "DCA91"."HMAT" LIKE 'G%')
   8 - access("DCA92"."CO"='3' AND "DCA92"."FCTCLASS"="DCA91"."FCTCLASS" AND
              "DCA92"."HMAT"="DCA91"."HMAT")
       filter("DCA92"."HMAT" LIKE 'G%')
--2、插入和查询都是6笔，执行计划：
--insert into ...selelct ...6
PLAN_TABLE_OUTPUT
Plan hash value: 3005576431
 
---------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT      |             |  3697 |  7690K|       |  5094   (2)| 00:01:02 |
|   1 |  VIEW                 |             |  3697 |  7690K|       |  5094   (2)| 00:01:02 |
|   2 |   SORT UNIQUE         |             |  3697 |  1144K|  2968K|  5094   (2)| 00:01:02 |
|*  3 |    HASH JOIN          |             |  3697 |  1144K|       |  4840   (2)| 00:00:59 |
|*  4 |     TABLE ACCESS FULL | DCP1        |     2 |   444 |       |   110   (0)| 00:00:02 |
|*  5 |     HASH JOIN         |             |  1774 |   164K|       |  4729   (2)| 00:00:57 |
|*  6 |      TABLE ACCESS FULL| T06020DCA91 | 21730 |   954K|       |  1201   (2)| 00:00:15 |
|*  7 |      TABLE ACCESS FULL| T06020DCA92 | 39012 |  1904K|       |  3527   (1)| 00:00:43 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("DCCP1"."CO"="DCA92"."CO" AND 
              "DCCP1"."MTNO"="DCA91"."MTSID"||"DCA91"."COLORID"||"DCA91"."MISECTSCALESID")
   4 - filter("DCCP1"."CO"='3' AND "DCCP1"."TXEMP"='DSP')
   5 - access("DCA92"."CO"="DCA91"."CO" AND "DCA92"."FCTCLASS"="DCA91"."FCTCLASS" 
              AND "DCA92"."HMAT"="DCA91"."HMAT")
   6 - filter("DCA91"."CO"='3' AND "DCA91"."HMAT" LIKE 'G%')
   7 - filter("DCA92"."CO"='3' AND "DCA92"."HMAT" LIKE 'G%')--selelct ....
PLAN_TABLE_OUTPUT
Plan hash value: 2477468782
 
--------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  3697 |  1144K|       |  5094   (2)| 00:01:02 |
|   1 |  HASH UNIQUE         |             |  3697 |  1144K|  2968K|  5094   (2)| 00:01:02 |
|*  2 |   HASH JOIN          |             |  3697 |  1144K|       |  4840   (2)| 00:00:59 |
|*  3 |    TABLE ACCESS FULL | DCP1        |     2 |   444 |       |   110   (0)| 00:00:02 |
|*  4 |    HASH JOIN         |             |  1774 |   164K|       |  4729   (2)| 00:00:57 |
|*  5 |     TABLE ACCESS FULL| T06020DCA91 | 21730 |   954K|       |  1201   (2)| 00:00:15 |
|*  6 |     TABLE ACCESS FULL| T06020DCA92 | 39012 |  1904K|       |  3527   (1)| 00:00:43 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DCCP1"."CO"="DCA92"."CO" AND 
              "DCCP1"."MTNO"="DCA91"."MTSID"||"DCA91"."COLORID"||"DCA91"."MISECTSCALESID")
   3 - filter("DCCP1"."CO"='3' AND "DCCP1"."TXEMP"='DSP')
   4 - access("DCA92"."CO"="DCA91"."CO" AND "DCA92"."FCTCLASS"="DCA91"."FCTCLASS" 
              AND "DCA92"."HMAT"="DCA91"."HMAT")
   5 - filter("DCA91"."CO"='3' AND "DCA91"."HMAT" LIKE 'G%')
   6 - filter("DCA92"."CO"='3' AND "DCA92"."HMAT" LIKE 'G%')

Answer 9

--几个表的数据一直式没变过，笔数都没变过：
SELECT  Count(*) FROM S06020DCCP1;   --115333
SELECT  Count(*) FROM dcp1 ;         --3
SELECT  Count(*) FROM t06020dca91 ;  --488174
SELECT  Count(*) FROM t06020dca92 ;  --1167802

Answer 10

--现在csdn格式OK了，重新用格式编辑下该SQL语句
INSERT INTO S06020DCCP1(CO,COABR,FCTCLASS,FCTCLASSNM,TGDSDAY,DSTPORT,DSTPORTNM,LINECLASS
                       ,SOURODERNO,RAMTXSNOYD,OPNRARGN,OPNRADAT,SPEC,SPECNM,SKU,MTCHCLRS
                       ,ODQTY,ITM,LVID,VER,SZNUMBER,RGNID
                       ,RGNNM,MTNO,MTNM,COLOR,UN
                       ,UQTY,XREM,JMPGASACO,SORTASACO,TXEMP,TXDAT,TXTM
                       ,RFLDONE,LISMPID, QTY,LISDP)
SELECT CO, COABR, FCTCLASS, FCTCLASSNM, TGDSDAY, DSTPORT, DSTPORTNM, LINECLASS, 
       SOURODERNO, RAMTXSNOYD, OPNRARGN,OPNRADAT, SPEC, SPECNM, SKU, MTCHCLRS, 
       ODQTY, ITM, LVID, VER, SZNUMBER, RGNID, 
       RGNNM, MTNO, MTNM, COLOR,UN, 
       UQTY, XREM,JMPGASACO, SORTASACO, TXEMP, TXDAT, TXTM, 
       RFLDONE, LISMPID, QTY,LISDP
FROM (
      SELECT DISTINCT dccp1.CO, dccp1.COABR, dccp1.FCTCLASS, dccp1.FCTCLASSNM, dccp1.TGDSDAY, dccp1.DSTPORT, dccp1.DSTPORTNM,dccp1.LINECLASS, 
                      dccp1.SOURODERNO, dccp1.RAMTXSNOYD, dccp1.OPNRARGN, dccp1.OPNRADAT, dccp1.SPEC, dccp1.SPECNM,dccp1.SKU, dccp1.MTCHCLRS, 
                      dccp1.ODQTY, dccp1.ITM, dccp1.LVID, dccp1.VER, dccp1.SZNUMBER, dccp1.RGNID,
                      dccp1.RGNNM,dca92.mtsid||dca92.colorid||dca92.misectscalesid mtno,'' mtnm, dca92.colorid COLOR, dccp1.UN,
                      dccp1.uqty * dca92.unuqty UQTY,dccp1.XREM, dccp1.JMPGASACO,dccp1.SORTASACO, dccp1.TXEMP, dccp1.TXDAT, dccp1.TXTM, 
                      dccp1.RFLDONE,dccp1.LisMpId, dccp1.QTY ,dccp1.LISDP
      FROM  dcp1 dccp1,t06020dca91 dca91,t06020dca92 dca92
      WHERE dca92.co=dca91.co AND dca92.fctclass=dca91.fctclass AND dca92.hmat=dca91.hmat AND dca91.hmat LIKE 'G%'
      AND dccp1.co=dca92.co AND dccp1.mtno=dca91.mtsid||dca91.colorid||dca91.misectscalesid 
      AND dccp1.co='3' AND dccp1.txemp='DSP'
    );--同志们，有没有啥子想法啊？？？

Answer 11

      SELECT DISTINCT ...
      FROM  dcp1 dccp1,t06020dca91 dca91,t06020dca92 dca92
      WHERE dca92.co=dca91.co AND dca92.fctclass=dca91.fctclass AND dca92.hmat=dca91.hmat AND dca91.hmat LIKE 'G%'
      AND dccp1.co=dca92.co AND dccp1.mtno=dca91.mtsid||dca91.colorid||dca91.misectscalesid 
      AND dccp1.co='3' AND dccp1.txemp='DSP'
和
      SELECT ...
      FROM  dcp1 dccp1,t06020dca91 dca91,t06020dca92 dca92
      WHERE dca92.co=dca91.co AND dca92.fctclass=dca91.fctclass AND dca92.hmat=dca91.hmat AND dca91.hmat LIKE 'G%'
      AND dccp1.co=dca92.co AND dccp1.mtno=dca91.mtsid||dca91.colorid||dca91.misectscalesid 
      AND dccp1.co='3' AND dccp1.txemp='DSP'
的检索结果分别是多少条？
...就是你那些检索字段

Answer 12

汗,如果出现这个问题,那就是bug了

Answer 13

后面那个select加不加distinct对结果集没影响，都是6笔数据
又查了一上午，没结果！

Answer 14

三张表的主KEY都联结起来了的话就麻烦了，
漏一个就可能造成执行计划不一样，结果就不一样的现象可以的话试试改一下检索SQL问，把[AND dca91.hmat LIKE 'G%']
放到最后试试看仔细查查，防止简单错误实在不行的话，正版ORACLE的话，只能问他们的技术支持了

Answer 15

估计是解发了一个BUG，否则无法解释这种差异。

Answer 16

我有点不相信是bug，如果是bug的话，不会有这么严重的错误，Oracle的老大们都不是吃闲饭的，至少此需求还是最根本的！
还有就是，总感觉有某种东西支配此情况发生，因为我从昨天到今天，一直在追查，从数据结果分析来说，并不是排列组合照成的
我之所以说感觉有某种东西支配此情况发生，是因为此情况发生时间是不定的，也就是说一会儿是查询6笔插入4306笔这种情况，
一会却是查询6笔插入6笔正常情况，相隔时间我真没找出规律，并且前提是所有来源表的数据都是没有变化的，这点就更让人迷茫了与此同时，我重新建了一张表test_20110223，结构和备源表S06020DCCP1一样，SQL不变的情况下模拟了哈，结论一样 ，两种情况都发生了，时间不定虽然我们公司Oracle都是正版的，至于问Oracle的技术支持，这个不是我的职责范围所在，估计上面的老大也不会选择这个，说是Oracle的bug来给客户解释
说不通的哎！
头痛了。

Answer 17

要不你就做个job.记录下当插入数据异常时各表的数量.
insert into 监控表
select 'results', count(*) from yourstatment
union all
select 'table1',count(*) from table1
....
union all
select 'tablen',count(*) from tablen

Answer 18

如果执行计划都能影响最终结果正确性的话那只能解释为bug了

Answer 19

FROM  dcp1 dccp1,t06020dca91 dca91,t06020dca92 dca92
      WHERE dca92.co=dca91.co AND dca92.fctclass=dca91.fctclass AND dca92.hmat=dca91.hmat AND dca91.hmat LIKE 'G%'
      AND dccp1.co=dca92.co AND dccp1.mtno=dca91.mtsid||dca91.colorid||dca91.misectscalesid 
      AND dccp1.co='3' AND dccp1.txemp='DSP'
建议先去掉表dcp1，试一下，看一下结果是多少。
再加上后再试一下，看一下结果是多少，感觉这个
dccp1.mtno=dca91.mtsid||dca91.colorid||dca91.misectscalesid 
dca91.mtsid、dca91.colorid、dca91.misectscalesid 是什么类型的？是不是带空格？
这样连接有些怪，要不改为使用其它方式？！

Answer 20

没有，这两天我天天都在跟踪，居然不会发生insert 4306笔了，我纳闷了~\(≧▽≦)/~
不管怎么执行，都是正常的了，奇怪。还好，客户喊我们再跟踪下，也没为难我们。