您的位置: 首页  >  技术问答  >  执行FORALL时执行时间不同.. INSERT INTO .. VALUE

执行FORALL时执行时间不同.. INSERT INTO .. VALUE

分类: 技术问答 • 2022-04-02 15:50:59

执行FORALL时执行时间不同.. INSERT INTO .. VALUE

问题描述：

我们遇到FORALL .. INSERT INTO .. VALUES语句的执行时间不同。我们创建了一个模拟行为的测试。运行下面的脚本显示随机分布的执行时间在0.5到7-8秒之间。执行FORALL时执行时间不同.. INSERT INTO .. VALUE

脚本在运行SQL Developer的客户端上执行，并且Oracle数据库在同一网络上的HyperV服务器虚拟机上运行。在此VM上，分配的内存和硬盘大小看起来足够了。

什么可能是不同的执行时间的原因，我们有什么可以稳定（并最小化）执行时间？

编辑：相关的性能只是FORALL .. INSERT语句需要的时间 - 在脚本中用DBMS.PUT_LINE命令清楚地显示。创建测试数据的性能与我的问题无关！

下面是测试脚本：

DECLARE 
    SUBTYPE t_logmsg_rec IS logmsg%ROWTYPE; 
    TYPE t_logmsg_list IS TABLE OF t_logmsg_rec; 
    l_logmsg_list t_logmsg_list := t_logmsg_list(); 
    l_logmsg_rec t_logmsg_rec; 
    l_max_logmsg_key_id logmsg.KEY_ID%TYPE; 
BEGIN 
    SELECT NVL(MAX(KEY_ID),1) 
    INTO l_max_logmsg_key_id 
    FROM logmsg; 

    FOR i IN 1..10000 LOOP 
    l_max_logmsg_key_id := l_max_logmsg_key_id + 1; 
    l_logmsg_rec.key_id  := l_max_logmsg_key_id; 
    l_logmsg_rec.msg_id  := 1; 
    l_logmsg_rec.session_id := 666; 
    l_logmsg_rec.log_timestamp := current_timestamp; 
    l_logmsg_rec.log_pck  := 'perf_test'; 
    l_logmsg_rec.log_user  := 'fl'; 
    l_logmsg_rec.log_type  := 1; 
    l_logmsg_rec.log_msg  := 'test msg ' ||i; 

    l_logmsg_rec.log_apl  := 1; 

    l_logmsg_list.EXTEND; 
    l_logmsg_list(l_logmsg_list.COUNT) := l_logmsg_rec; 
    END LOOP; 

    dbms_output.put_line('Start: ' || current_timestamp); 

    FORALL l_idx IN 1 .. l_logmsg_list.COUNT 
     INSERT INTO logmsg VALUES l_logmsg_list (l_idx); 

    dbms_output.put_line('End: ' || current_timestamp); 
END; 
/

而且表的创建脚本：

CREATE TABLE "SCOTT"."LOGMSG" 
( "KEY_ID" NUMBER(38,0) NOT NULL ENABLE, 
"MSG_ID" NUMBER(38,0) NOT NULL ENABLE, 
"LOG_TYPE" NUMBER(3,0) NOT NULL ENABLE, 
"LOG_TIMESTAMP" TIMESTAMP (6) NOT NULL ENABLE, 
"LOG_USER" VARCHAR2(32 BYTE) NOT NULL ENABLE, 
"LOG_MSG" VARCHAR2(2000 BYTE), 
"LOG_APL" NUMBER(5,0), 
"LOG_ATT" "SYS"."XMLTYPE" , 
"LOG_PCK" VARCHAR2(100 BYTE), 
"SESSION_ID" NUMBER(38,0), 
CONSTRAINT "PK_LOGMSG" PRIMARY KEY ("KEY_ID") 
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
TABLESPACE "SCOTT_I_TABLESPACE" ENABLE 
) SEGMENT CREATION IMMEDIATE 
PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING 
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
TABLESPACE "SCOTT_TABLESPACE" 
XMLTYPE COLUMN "LOG_ATT" STORE AS BASICFILE CLOB (
TABLESPACE "SCOTT_TABLESPACE" DISABLE STORAGE IN ROW CHUNK 16384 RETENTION 
NOCACHE LOGGING 
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ; 

CREATE INDEX "SCOTT"."IX_LOGMSG_1" ON "SCOTT"."LOGMSG" ("MSG_ID") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
TABLESPACE "SCOTT_I_TABLESPACE" ; 

CREATE INDEX "SCOTT"."IX_LOGMSG_2" ON "SCOTT"."LOGMSG" ("SESSION_ID") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
TABLESPACE "SCOTT_I_TABLESPACE" ; 

CREATE INDEX "SCOTT"."IX_LOGMSG_3" ON "SCOTT"."LOGMSG" ("LOG_TIMESTAMP") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
TABLESPACE "SCOTT_I_TABLESPACE" ; 

CREATE INDEX "SCOTT"."IX_LOGMSG_C1" ON "SCOTT"."LOGMSG" ("LOG_MSG") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
TABLESPACE "SCOTT_I_TABLESPACE" ; 

CREATE INDEX "SCOTT"."IX_LOGMSG_C2" ON "SCOTT"."LOGMSG" ("LOG_PCK") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
TABLESPACE "SCOTT_I_TABLESPACE" ;

答

也许你可以尝试替代，你能避免在阵列中按行人口的行。我试图用一个批量收集操作来说明同一个例子。希望能帮助到你。

set sqlbl on; 
DECLARE 
    TYPE t_logmsg_tab IS TABLE OF log_msg%ROWTYPE; 
    lv_log_msg t_logmsg_tab; 
BEGIN 


--Since i can see only 10000 values are fetched so here we need not to use LIMIT clause but if 
--date volume is more then we need to put LIMIT clause to prevent memory overflow 

    SELECT NVL(MAX(KEY_ID) OVER(ORDER BY 1),1)+LEVEL, 
     '1' msg_id, 
     '666' session_id, 
     current_timestamp, 
     'perf_test' log_pck, 
     'f1' log_user, 
     1 log_type, 
     'test msg '||LEVEL, 
     '1' log_apl 
     BULK COLLECT INTO 
     lv_log_msg 
     FROM l_max_logmsg_key_id 
     CONNECT BY LEVEL < 10001; 

--Not required going for row by row processing will definitely take toll 
--So avoiding and performing a bulk Collect Operation 


-- SELECT NVL(MAX(KEY_ID),1) 
-- INTO l_max_logmsg_key_id 
-- FROM logmsg; 
-- 
-- FOR i IN 1..10000 LOOP 
-- l_max_logmsg_key_id := l_max_logmsg_key_id + 1; 
-- l_logmsg_rec.key_id  := l_max_logmsg_key_id; 
-- l_logmsg_rec.msg_id  := 1; 
-- l_logmsg_rec.session_id := 666; 
-- l_logmsg_rec.log_timestamp := current_timestamp; 
-- l_logmsg_rec.log_pck  := 'perf_test'; 
-- l_logmsg_rec.log_user  := 'fl'; 
-- l_logmsg_rec.log_type  := 1; 
-- l_logmsg_rec.log_msg  := 'test msg ' ||i; 
-- 
-- l_logmsg_rec.log_apl  := 1; 
-- 
-- l_logmsg_list.EXTEND; 
-- l_logmsg_list(l_logmsg_list.COUNT) := l_logmsg_rec; 
-- END LOOP; 

    IF lv_log_msg.EXISTS(1) THEN 

    dbms_output.put_line('Start: ' || current_timestamp); 

    FORALL l_idx IN lv_log_msg.FIRST .. lv_log_msg.LAST 
     INSERT INTO logmsg VALUES lv_log_msg (l_idx); 

    dbms_output.put_line('End: ' || current_timestamp); 

    END IF; 

END; 
/
+0

请检查我的编辑评论。 – flo

相关推荐