非常慢的插入语句

重现步骤：

create table house_t
(
POSTALCODE                  VARCHAR2(6),
IFNSFL                      VARCHAR2(4),
TERRIFNSFL                  VARCHAR2(4),
IFNSUL                      VARCHAR2(4),
TERRIFNSUL                  VARCHAR2(4),
OKATO                       VARCHAR2(11),
OKTMO                       VARCHAR2(11),
UPDATEDATE                  DATE,
HOUSENUM                    VARCHAR2(20),
ESTSTATUS                   NUMBER(5),
BUILDNUM                    VARCHAR2(10),
STRUCNUM                    VARCHAR2(10),
STRSTATUS                   NUMBER(5),
HOUSEID                     VARCHAR2(36),
HOUSEGUID                   VARCHAR2(36),
AOGUID                      VARCHAR2(36),
STARTDATE                   DATE,
ENDDATE                     DATE,
STATSTATUS                  NUMBER(5),
NORMDOC                     VARCHAR2(36),
COUNTER                     NUMBER(10),
CADNUM                      VARCHAR2(100),
DIVTYPE                     NUMBER(5)
);

样本数据：

insert into house_t (HOUSEID, HOUSEGUID, AOGUID)
with g as (select * from dual connect by level <= 1000)
select sys_guid(), sys_guid(), sys_guid() from g,g,g
where rownum <= 50000000;
commit;

SQL> select count(*) from house_t;

  COUNT(*)
----------
  50000000

CREATE UNIQUE INDEX HOUSE_T_IDX0 ON HOUSE_T(HOUSEID);
CREATE INDEX  HOUSE_T_IDX1 ON HOUSE_T (HOUSEGUID);
CREATE INDEX HOUSE_T_IDX2 ON HOUSE_T (AOGUID);

SQL> select segment_name, bytes/1024/1024 from user_segments;

SEGMENT_NAME                   BYTES/1024/1024
------------------------------ ---------------
HOUSE_T                                   6336
HOUSE_T_IDX0                              2368
HOUSE_T_IDX1                              2432
HOUSE_T_IDX2                              2432

现在模拟你的脚本：

SQL> alter session set tracefile_identifier=BALAZS1;
SQL> exec dbms_session.session_trace_enable(true, false);

declare
  TYPE T_HOUSE_T_ARRAY IS TABLE OF VARCHAR2(36);
  L_DATA T_HOUSE_T_ARRAY;
  CURSOR c IS SELECT sys_guid() from dual
    connect by level <= 50000 order by dbms_random.value;
BEGIN
  for i in 1..70
  loop
    begin
      OPEN c;
      LOOP
      FETCH c BULK COLLECT INTO L_DATA;
      FORALL i IN 1..L_DATA.COUNT
      INSERT INTO house_t (HOUSEID, HOUSEGUID, AOGUID)
        VALUES (L_DATA(i), L_DATA(i), L_DATA(i));
      EXIT WHEN c%NOTFOUND;
      END LOOP;
      CLOSE c;
  commit;
end;
  end loop;
END;
 21  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:58.97
SQL> exec dbms_session.session_trace_disable;

和 tkprof 输出：

SQL ID: 4h4byjv3yd590 Plan Hash: 0

INSERT INTO HOUSE_T (HOUSEID, HOUSEGUID, AOGUID)
VALUES
 (:B1 , :B2 , :B3 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     70     82.17     105.82      23693     359178   43342331     3500000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       71     82.17     105.82      23693     359178   43342331     3500000

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                     24243        0.01         11.82
  ...

并且数量巨大leaf node splits：

select sn.name, ss.value from v$statname sn join v$sesstat ss 
  on (sn.statistic# = ss.statistic#) 
where sid = sys_context('userenv', 'sid') 
  and name in ('leaf node splits', 'leaf node 90-10 splits', 
    'branch node splits', 'root node splits');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
leaf node splits                                                      82981
leaf node 90-10 splits                                                   18
branch node splits                                                      363
root node splits                                                          0

1) 我在这里使用 SSD，这就是为什么我的演示花费的时间要少得多，因为磁盘响应时间要短得多。您应该查看current块的数量，而不是花费在此上的时间。

2）我以随机顺序插入数据，这导致leaf block splits. leaf node 90-10 splits是由按顺序递增的顺序将数据插入到索引的最右边部分引起的。leaf node 90-10 splits可以简单地通过将新的索引块添加到索引的“最右边”部分来完成，然后将最高值复制到其中。有时它被非正式地称为99-1拆分而不是90-10. leaf block splits意味着在索引的“中间”添加新块，在几个块中复制和重新排列数据，这是更多的工作。

3) 性能方面，我发现使用 GUID 作为标识符是一个非常糟糕的主意。NUMBER与简单标识符相比，它们消耗的存储空间要多出数倍。

这是相同的，但有 NUMBER 个类型：

EGMENT_NAME                   BYTES/1024/1024
------------------------------ ---------------
HOUSE_T2                                  2432
HOUSE_T_IDX02                             1088
HOUSE_T_IDX12                             1088
HOUSE_T_IDX22                             1088

另外：Oracle Cost Based Optimizer 的限制（文档 ID 212809.1）

• 字符列直方图的局限性

直方图仅存储字符串的前 32 个字符（8.1.6 之前的 5 个字符，请参阅错误：598799）。如果在具有超过 31 个字符的数据且前 31 个字符相同的字符列上收集直方图，则列直方图可能无法正确反映这些列的基数，因为这些值都将被视为相同。数字数据也有类似的限制，在直方图端点中标准化为 15 位。

从 12c 开始，为字符串列上的直方图考虑的最大可能字符数从 32 个增加到 64 个。

这导致 GUID 列上的直方图出现一些严重不正确的基数估计错误。

这是当值以递增顺序插入时的行为方式（已删除order by dbms_random.value）：

INSERT INTO HOUSE_T (HOUSEID, HOUSEGUID, AOGUID)
VALUES
 (:B1 , :B2 , :B3 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     70     21.13      23.39       3266     167674    2496874     3500000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       71     21.13      23.39       3266     167674    2496874     3500000

如您所见，每行少于 1 个当前块。