記一次分割槽表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 Name	Statement Total	Per 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

Execution Plan

Id	Operation	Name	Rows	Bytes	Cost (%CPU)	Time	Pstart	Pstop
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;