《運維技巧 – 活用臨時表隔離冷熱數據》要點：
本文介紹了運維技巧 – 活用臨時表隔離冷熱數據，希望對您有用。如果有疑問，可以聯系我們。
編輯手記：Oracle給了我們很多工具,在日常數據庫管理中活用這些工具方可發揮最大效能.
作者簡介：
張洪濤 ?富士康 DBA
在數據庫監控過程中發現考勤數據庫上Employees_ControlData存儲過程執行時間需20分鐘.這個存儲過程邏輯很簡單,就是打開一個游標,做LOOP循環,再刪除重復數據,結構如下:
CREATE OR REPLACE PROCEDURE Employees_ControlData
IS
tmpVar??? NUMBER(6);
tmpVar1?? NUMBER(6);
tmpVar2?? NUMBER (6);
tmpVar3?? NUMBER(6);
CURSOR?EMP_NO
IS
SELECT?WORKNO FROM?ZZ_EMPLOYEES;
BEGIN
–LINE 12行
FOR USERID INEMP_NO
LOOP
……
END LOOP;
–Line128行,刪除重復數據
Delete ….? ;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
— Consider logging the error and then re-raise
RAISE;
END Employees_Controldata;
ZZ_EMPLOYEES有4萬多筆數據,LOOP也會執行4萬多次.AWR報告與GridControl監控都顯示效能瓶頸在LOOP循環中六處SQL,再看一下LOOP循環中六處問題SQL：
–Line14行
SELECT COUNT(*)
INTO tmpVar
FROM CPYTGL.EMPLOYEE_CONTROL
WHERE???? EMP_NO = USERID.WORKNO
ANDTO_CHAR(INOROUT_TIME,’YYYY/MM/DD’)=
TO_CHAR(SYSDATE-2,’YYYY/MM/DD’)
AND TYPE=’F’
AND INOROUT_TIME >SYSDATE-3
AND?ROWNUM<6;
–Line38行
INSERT INTOEMPLOYEE_CONTROL_EXCEPTION
SELECT DISTINCT *
FROM EMPLOYEE_CONTROL
WHERE???? EMP_NO = USERID.WORKNO
AND TO_CHAR(INOROUT_TIME,’YYYY/MM/DD’)=
TO_CHAR(SYSDATE-2,’YYYY/MM/DD’)
AND TYPE=’F’
AND INOROUT_TIME >SYSDATE-3;
–Line49行
INSERT INTOEMPLOYEE_CONTROL_EXCEPTION
SELECT DISTINCT *
FROM EMPLOYEE_CONTROL
WHERE EMP_NO = USERID.WORKNO
AND TO_CHAR(INOROUT_TIME,’YYYY/MM/DD’)=
TO_CHAR(SYSDATE-2,’YYYY/MM/DD’)
AND TYPE=’F’
AND INOROUT_TIME >SYSDATE-3
AND INOROUT_TIME NOTIN
(SELECT INOROUT_TIME
FROM EMPLOYEE_CONTROL_EXCEPTION
WHERE EMP_NO = USERID.WORKNO
AND TO_CHAR(INOROUT_TIME,
‘YYYY/MM/DD’)=
TO_CHAR(SYSDATE-2,
‘YYYY/MM/DD’)
AND TYPE=’F’
AND INOROUT_TIME >SYSDATE-3);
–Line72行
SELECT COUNT(*)
INTO tmpVar1
FROM CPYTGL.EMPLOYEE_CONTROL
WHERE???? EMP_NO = USERID.WORKNO
ANDTO_CHAR(INOROUT_TIME,’YYYY/MM/DD’)=
TO_CHAR(SYSDATE-2,’YYYY/MM/DD’)
AND TYPE=’L’
AND INOROUT_TIME >SYSDATE-3
AND ROWNUM<6;
–Line82行
INSERT INTO?EMPLOYEE_CONTROL_EXCEPTION
SELECT DISTINCT *
FROM EMPLOYEE_CONTROL
WHERE???? EMP_NO = USERID.WORKNO
AND TO_CHAR(INOROUT_TIME,’YYYY/MM/DD’)=
TO_CHAR(SYSDATE-2,’YYYY/MM/DD’)
AND TYPE=’L’
AND INOROUT_TIME >SYSDATE-3;
–Line87行
INSERT INTO?EMPLOYEE_CONTROL_EXCEPTION
SELECT?DISTINCT *
FROM EMPLOYEE_CONTROL
WHERE???? EMP_NO = USERID.WORKNO
AND TO_CHAR(INOROUT_TIME,’YYYY/MM/DD’)=
TO_CHAR(SYSDATE-2,’YYYY/MM/DD’)
AND TYPE=’L’
AND INOROUT_TIME >SYSDATE-3
AND INOROUT_TIME NOTIN(SELECTINOROUT_TIME
FROM?EMPLOYEE_CONTROL_EXCEPTION
WHERE???? EMP_NO = USERID.WORKNO
AND TO_CHAR(INOROUT_TIME,
‘YYYY/MM/DD’)=
TO_CHAR(SYSDATE-2,
‘YYYY/MM/DD’)
AND TYPE=’L’
AND INOROUT_TIME >SYSDATE-3);
這六處SQL都查詢了EMPLOYEE_CONTROL考勤信息表.此表已有近兩億筆數據,根據INOROUT_TIME字段進行分區,并對相關字段建立了索引.
CREATE INDEX?EMPLOYEE_CONTROL_EMPNOINOROUT ON EMPLOYEE_CONTROL
(TO_CHAR(“INOROUT_TIME”,’YYYY/MM/DD’),EMP_NO) LOCAL;
SQL> SELECT column_name
2???FROM dba_part_key_columns
3??WHERE name = ‘EMPLOYEE_CONTROL’;
COLUMN_NAME
——————————————————————————–
INOROUT_TIME
SQL> SELECT num_rows
2???FROM dba_tables
3??WHERE table_name = ‘EMPLOYEE_CONTROL’;
NUM_ROWS
———-
193585044
EMPLOYEE_CONTROL考勤信息表至少需保留一年數據備查.六條SQL已加INOROUT_TIME >SYSDATE-3條件,執行計劃中可進行分區裁剪,刪減數據這條路行不通.
WHERE中的條件也正確使用了索引,似乎所有常規優化方法都已用上,如何才能進一步提升存儲過程LOOP循環執行速度?
我們再分析這六條SQL,在WHERE條件中都出現了對EMPLOYEE_CONTROL表以下限定條件：
TO_CHAR(INOROUT_TIME,’YYYY/MM/DD’)=
TO_CHAR(SYSDATE-2,’YYYY/MM/DD’)
AND INOROUT_TIME >SYSDATE-3
AND TYPE=’L’
AND TYPE=’F’
存儲過程實際要讀取的只有一天的數據,這部分數據一般只有10萬筆為熱點數據.如果我們先把此部分數據單獨讀出,在LOOP循環中就可只讀取臨時表內容,避免4萬次讀取有兩億筆數據的EMPLOYEE_CONTROL考勤資料表.
依此思路,我們先建立一個臨時表,并為臨時表EMP_NO字段添加索引：
CREATE GLOBAL TEMPORARY TABLE CPYTGL.EMPLOYEE_CONTROL_TEMP
ON COMMIT DELETE ROWS
AS
SELECT *
FROMCPYTGL.EMPLOYEE_CONTROL
WHERE 1 = 0;
CREATE INDEX CPYTGL.EMPLOYEE_CONTROL_TEMP_NOONCPYTGL.EMPLOYEE_CONTROL_TEMP
(EMP_NO);
再在存儲過程頭部將符合條件的數據取出：
INSERT INTO cpytgl.EMPLOYEE_CONTROL_TEMP
SELECT*
FROM CPYTGL.EMPLOYEE_CONTROL
WHERE???? TO_CHAR (INOROUT_TIME, ‘YYYY/MM/DD’) =
TO_CHAR(SYSDATE – 2, ‘YYYY/MM/DD’)
AND?INOROUT_TIME > SYSDATE- 3
AND TYPE IN(‘F’, ‘L’);
這樣就可改寫消耗資源的六條SQL查詢臨時表.以第14行SQL為例,需改寫為：
–Line14行
SELECT COUNT (*)
INTO tmpVar
FROM cpytgl.EMPLOYEE_CONTROL_TEMP
WHERE EMP_NO = USERID.WORKNO AND TYPE = ‘F’ AND ROWNUM< 6;
LOOP循環中六條SQL改為查詢10萬筆記錄的臨時表后,存儲過程只需1分鐘即可跑完.相較之前20分鐘運行時間有大幅度提升.
此例核心為使用臨時表隔離冷熱數據.DBA一次調優不一定能想出最佳方法,通過對應用的不斷深入觀察,以及Oracle工具的合理使用,加上一點點靈光一現那些看似解決不了的難題都可一一化解.
文章來自微信公眾號：數據和云
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/2205.html
