与VARCHAR2相比,将'TinyString'
字符串(仅是示例字符串)插入行中存储的CLOB时,我遇到了显着的性能下降。我的理解是,在启用STORAGE IN ROW的情况下将比插入VARCHAR2的慢15倍。
看下面的代码:
我有很多表,每个表都附有一个COMPOUND TRIGGER,类似于下面的表:
CREATE OR REPLACE TRIGGER mdhl_basic_trigger_compound
FOR INSERT OR UPDATE OR DELETE ON target_table
COMPOUND TRIGGER TYPE EVENTS_HIST IS TABLE OF log_table%ROWTYPE INDEX BY PLS_INTEGER;
coll_events_hist EVENTS_HIST;
ctr PLS_INTEGER := 0;
my_bgroup VARCHAR2(3);
BEFORE EACH ROW IS
BEGIN
IF INSERTING OR UPDATING THEN
my_bgroup := :NEW.BGROUP;
ELSE
my_bgroup := :OLD.BGROUP;
END IF;
ctr := ctr + 1;
coll_events_hist(ctr).BGROUP := my_bgroup;
coll_events_hist(ctr).TABLE_NAME := 'BASIC_MDHL';
coll_events_hist(ctr).EVENT_TS := current_timestamp;
coll_events_hist(ctr).EVENT_RAW := 'TinyString';
END BEFORE EACH ROW;
AFTER STATEMENT IS
BEGIN
FORALL counter IN 1 .. coll_events_hist.count()
INSERT INTO log_table VALUES coll_events_hist(counter);
END AFTER STATEMENT;
END mdhl_basic_trigger_compound;
对
target_table
进行任何操作时,上述触发器都会将coll_events_hist
类型填充的数据存储到log_table
中,该数据通过以下方式定义:CREATE TABLE "USERNAME"."LOG_TABLE"
( "BGROUP" VARCHAR2(3) NOT NULL ENABLE,
"TABLE_NAME" VARCHAR2(255) NOT NULL ENABLE,
"EVENT_TS" TIMESTAMP (7) DEFAULT current_timestamp,
"EVENT_RAW" CLOB
)
SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
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 "USERS"
LOB ("EVENT_RAW") STORE AS BASICFILE "EV_RAW_SEG"(
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 16384 PCTVERSION 5
CACHE
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))
我的设置是:
Windows 7 SP1,
甲骨文11g
*我的基准测试过程在每次迭代中迭代10次以更新target_table上的21k行。
最佳答案
在您的情况下,“tinystring”总是
在FORALL部分中,浪费的时间会浪费在查找已创建的所有临时吊顶上。
您会发现在每行部分插入会获得更好的性能:
例如在我的测试系统上,您的lob触发器是:
SQL> insert into target_Table select 'ABC' from dual connect by level <= 10000;
10000 rows created.
Elapsed: 00:00:10.49
与触发为:
SQL> CREATE OR REPLACE TRIGGER mdhl_basic_trigger
2 before INSERT OR UPDATE OR DELETE ON target_table for each row
3 declare
4
5 my_bgroup VARCHAR2(3);
6
7 v_timer2 number := 0;
8 v_timer number;
9 BEGIN
10
11 IF INSERTING OR UPDATING THEN
12 my_bgroup := :NEW.BGROUP;
13 ELSE
14 my_bgroup := :OLD.BGROUP;
15 END IF;
16
17 INSERT INTO log_table VALUES(my_bgroup, 'BASIC_MDHL', current_timestamp, 'TinyString');
18
19 END mdhl_basic_trigger;
20 /
SQL> insert into target_Table select 'ABC' from dual connect by level <= 10000;
10000 rows created.
Elapsed: 00:00:01.18
如果您知道您的字符串始终小于32k,那么如果将触发器创建为以下内容,则可以让所有人保持速度提升:
SQL> CREATE OR REPLACE TRIGGER mdhl_basic_trigger_compound
2 FOR INSERT OR UPDATE OR DELETE ON target_table
3
4 COMPOUND TRIGGER
5
6 type events_rec is record (BGROUP VARCHAR2(3),
7 TABLE_NAME VARCHAR2(255) ,
8 EVENT_TS TIMESTAMP (7),
9 EVENT_RAW varchar2(32767));
10 TYPE EVENTS_HIST IS TABLE OF events_rec INDEX BY PLS_INTEGER;
11 coll_events_hist EVENTS_HIST;
12 ctr PLS_INTEGER := 0;
13 my_bgroup VARCHAR2(3);
14
15 v_timer2 number := 0;
16 v_timer number;
17 BEFORE EACH ROW IS
18 BEGIN
19
20 IF INSERTING OR UPDATING THEN
21 my_bgroup := :NEW.BGROUP;
22 ELSE
23 my_bgroup := :OLD.BGROUP;
24 END IF;
25
26 ctr := ctr + 1;
27 coll_events_hist(ctr).BGROUP := my_bgroup;
28 coll_events_hist(ctr).TABLE_NAME := 'BASIC_MDHL';
29 coll_events_hist(ctr).EVENT_TS := current_timestamp;
30 coll_events_hist(ctr).EVENT_RAW := 'TinyString';
31
32 END BEFORE EACH ROW;
33
34 AFTER STATEMENT IS
35 BEGIN
36 v_timer := dbms_utility.get_time;
37 FORALL counter IN 1 .. coll_events_hist.count()
38 INSERT INTO log_table VALUES coll_events_hist(counter);
39 v_timer2 := v_timer2 + (dbms_utility.get_time - v_timer);
40 dbms_output.put_line(v_timer2/100);
41 END AFTER STATEMENT;
42 END mdhl_basic_trigger_compound;
43 /
SQL> insert into target_Table select 'ABC' from dual connect by level <= 10000;
10000 rows created.
Elapsed: 00:00:00.39
即将lob操作推迟到插入为止。