记一次全局分区索引update调优

原始SQL:

CREATE OR REPLACE PROCEDURE sp_upd_suppressed_emails
(
  A_LIMIT_BULK IN PLS_INTEGER DEFAULT 2000,
  A_COMMIT_AFTER IN PLS_INTEGER DEFAULT 2000
) is

  v_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 contact
  WHERE 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,435 187,339.68 53.56
CPU Time (ms) 2,896,748 181,046.72 65.48
Executions 16    
Buffer Gets 521,592,791 32,599,549.44 69.21
Disk Reads 10,974 685.88 1.12
Parse Calls 1 0.06 0.00
Rows 32,000 2,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    
Invalidations 0    
Version Count 1    
Sharable Mem(KB) 51    

Back to Plan 1(PHV: 3689516641)

 

Execution Plan

IdOperationNameRowsBytesCost (%CPU)TimePstartPstop
0 UPDATE STATEMENT       16798 (100)      
1    UPDATE CONTACT            
2      PARTITION RANGE ALL   1 51 16798 (1) 00:00:01 1 1048575
3        PARTITION HASH SINGLE   1 51 16798 (1) 00:00:01 KEY KEY
4          TABLE ACCESS FULL CONTACT 1 51 16798 (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分钟。

​CREATE OR REPLACE PROCEDURE sp_upd_suppressed_emails
(
A_LIMIT_BULK IN PLS_INTEGER DEFAULT 2000,
A_COMMIT_AFTER IN PLS_INTEGER DEFAULT 2000
) is

v_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 contact
WHERE 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 ;

BEGIN

DBMS_OUTPUT.PUT_LINE ('start reading data..' );
v_operation := 'BEFORE OPEN';

select sysdate into v_upd_date from dual;


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' , modified_date = v_upd_date
WHERE email = v_contact (i).email
and 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..' );

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;
posted @ 2019-10-15 15:16  caljosiju  阅读(375)  评论(0编辑  收藏  举报