记一次分区表update调优过程

原始SQL:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263CREATE OR REPLACE PROCEDURE sp_upd_suppressed_emails(A_LIMIT_BULK IN PLS_INTEGER DEFAULT 2000,A_COMMIT_AFTER IN PLS_INTEGER DEFAULT 2000isv_operation Varchar2(20) := 'START';v_last         PLS_INTEGER;   v_start        PLS_INTEGER;   v_end          PLS_INTEGER;  l_row          PLS_INTEGER; CURSOR curs_contact IS SELECT id FROM contactWHERE active_ind = 2 and suppress_flag = 'Y' and bounce_flag = 'Y' and consumer_id in (select id from consumers where login_name IN ('ASPIRAFOCUS''CDFW''PSACSFSUSR') ) and  email is not null ORDER BY id;  TYPE contactId IS RECORD ( id contact.id%TYPE);  TYPE v_contactId_tbl IS TABLE OF contactId INDEX BY PLS_INTEGER; v_contact v_contactId_tbl;     BEGIN DBMS_OUTPUT.PUT_LINE ('start reading data..' );v_operation := 'BEFORE OPEN';OPEN curs_contact; LOOP v_operation := 'BEFORE FETCH';FETCH curs_contact BULK COLLECT INTO v_contact LIMIT A_LIMIT_BULK;v_operation := 'AFTER FETCH'; EXIT WHEN v_contact.COUNT = 0;  v_start := 1;   v_last := v_contact.COUNT;  l_row := 0;       LOOP           DBMS_OUTPUT.PUT_LINE ('LOOP 2 top ');EXIT WHEN v_start > v_last;v_end := LEAST (v_start + A_COMMIT_AFTER - 1, v_last);     DBMS_OUTPUT.PUT_LINE ('LOOP 2 v_start ' || v_start || ' end ' || v_end ); BEGIN              v_operation := 'UPDATE_LOAD';FORALL i IN v_start .. v_end UPDATE contact SET active_ind = 1, suppress_flag = 'N', bounce_flag = 'N'  WHERE id = v_contact (i).id;               END;DBMS_OUTPUT.PUT_LINE ('after forall start ' || v_start || ' end ' || v_end );COMMIT;v_start := v_end + 1;         END LOOP;DBMS_OUTPUT.PUT_LINE ('LOOP 2 bottom v_start ' || v_start || ' end ' || v_end );COMMIT;END LOOP;DBMS_OUTPUT.PUT_LINE ('stop reading data..' ); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Exception:Others-> v_operation = ' || v_operation || ', rowcount # ' || l_row || ' SQLCODE ' || SQLCODE || ' ERR_MSG = ' || SUBSTR(SQLERRM, 1, 500) );  END sp_upd_suppressed_emails;

原始SQL在执行过程中经过几小时未完成,查看sql统计信息:

Stat NameStatement TotalPer Execution% Snap Total
Elapsed Time (ms)2,997,435187,339.6853.56
CPU Time (ms)2,896,748181,046.7265.48
Executions16
Buffer Gets521,592,79132,599,549.4469.21
Disk Reads10,974685.881.12
Parse Calls10.060.00
Rows32,0002,000.00
User I/O Wait Time (ms)2,525
Cluster Wait Time (ms)1,146
Application Wait Time (ms)4
Concurrency Wait Time (ms)1
Invalidations0
Version Count1
Sharable Mem(KB)51

Back to Plan 1(PHV: 3689516641)

Execution Plan

IdOperationNameRowsBytesCost (%CPU)TimePstartPstop
0UPDATE STATEMENT16798 (100)
1   UPDATECONTACT
2     PARTITION RANGE ALL15116798 (1)00:00:0111048575
3       PARTITION HASH SINGLE15116798 (1)00:00:01KEYKEY
4         TABLE ACCESS FULLCONTACT15116798 (1)00:00:01

表数据量非常大,根据created_date时间字段分区,ID字段进行子hash分区,上面建立了基于ID字段的global hash分区索引。

从上面情况看出,原始SQL分批2000个ID进行批量update,但执行计划走的是TABLE ACCESS FULL, 并且进行了分区的PRATITION RANGE ALL操作,可以看出2000条记录的批量更新由于不能利用global hash分区索引进行检索,主要原因是由于hash分区适用于等值操作,对于范围或者批量操作,可能遍历很多hash分区,导致结果不如TABLE ACCESS FULL。

针对这种情况,根据业务逻辑,将批量条件从ID改成其他非global hash索引列,执行时间降低为5分钟。

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556CREATE OR REPLACE PROCEDURE sp_upd_suppressed_emails(A_LIMIT_BULK IN PLS_INTEGER DEFAULT 2000,A_COMMIT_AFTER IN PLS_INTEGER DEFAULT 2000isv_operation Varchar2(20) := 'START';v_last         PLS_INTEGER;v_start        PLS_INTEGER;v_end          PLS_INTEGER;l_row          PLS_INTEGER;CURSOR curs_contact IS SELECT distinct email FROM contactWHERE active_ind = 2 and suppress_flag = 'Y' and bounce_flag = 'Y'and consumer_id in (select id from consumers where login_name IN ('ASPIRAFOCUS''CDFW''PSACSFSUSR') )and  email is not null ORDER BY email;TYPE contactId IS RECORD ( email contact.email%TYPE);TYPE v_contactId_tbl IS TABLE OF contactId INDEX BY PLS_INTEGER;v_contact v_contactId_tbl;v_upd_date date ;BEGINDBMS_OUTPUT.PUT_LINE ('start reading data..' );v_operation := 'BEFORE OPEN';select sysdate into v_upd_date from dual;OPEN curs_contact;LOOPv_operation := 'BEFORE FETCH';FETCH curs_contact BULK COLLECT INTO v_contact LIMIT A_LIMIT_BULK;v_operation := 'AFTER FETCH';EXIT WHEN v_contact.COUNT = 0;v_start := 1;v_last := v_contact.COUNT;l_row := 0;LOOPDBMS_OUTPUT.PUT_LINE ('LOOP 2 top ');EXIT WHEN v_start > v_last;v_end := LEAST (v_start + A_COMMIT_AFTER - 1, v_last);DBMS_OUTPUT.PUT_LINE ('LOOP 2 v_start ' || v_start || ' end ' || v_end );BEGINv_operation := 'UPDATE_LOAD';FORALL i IN v_start .. v_endUPDATE contact SET active_ind = 1, suppress_flag = 'N', bounce_flag = 'N' , modified_date =  v_upd_dateWHERE email = v_contact (i).emailand   active_ind = 2 and suppress_flag = 'Y' and bounce_flag = 'Y'and consumer_id in (select id from consumers where login_name IN ('ASPIRAFOCUS''CDFW''PSACSFSUSR') )  ;END;DBMS_OUTPUT.PUT_LINE ('after forall start ' || v_start || ' end ' || v_end );COMMIT;v_start := v_end + 1;END LOOP;DBMS_OUTPUT.PUT_LINE ('LOOP 2 bottom v_start ' || v_start || ' end ' || v_end );COMMIT;END LOOP;DBMS_OUTPUT.PUT_LINE ('stop reading data..' );EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE ('Exception:Others-> v_operation = ' || v_operation || ', rowcount # ' || l_row || ' SQLCODE ' || SQLCODE || ' ERR_MSG = ' || SUBSTR(SQLERRM, 1, 500) );END sp_upd_suppressed_emails;

发表评论

电子邮件地址不会被公开。 必填项已用*标注