《運(yùn)維技巧 – 活用臨時(shí)表隔離冷熱數(shù)據(jù)》要點(diǎn)：
本文介紹了運(yùn)維技巧 – 活用臨時(shí)表隔離冷熱數(shù)據(jù)，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。
編輯手記：Oracle給了我們很多工具,在日常數(shù)據(jù)庫(kù)管理中活用這些工具方可發(fā)揮最大效能.
作者簡(jiǎn)介：
張洪濤 ?富士康 DBA
在數(shù)據(jù)庫(kù)監(jiān)控過(guò)程中發(fā)現(xiàn)考勤數(shù)據(jù)庫(kù)上Employees_ControlData存儲(chǔ)過(guò)程執(zhí)行時(shí)間需20分鐘.這個(gè)存儲(chǔ)過(guò)程邏輯很簡(jiǎn)單,就是打開(kāi)一個(gè)游標(biāo),做LOOP循環(huán),再刪除重復(fù)數(shù)據(jù),結(jié)構(gòu)如下:
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行,刪除重復(fù)數(shù)據(jù)
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萬(wàn)多筆數(shù)據(jù),LOOP也會(huì)執(zhí)行4萬(wàn)多次.AWR報(bào)告與GridControl監(jiān)控都顯示效能瓶頸在LOOP循環(huán)中六處SQL,再看一下LOOP循環(huán)中六處問(wèn)題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都查詢(xún)了EMPLOYEE_CONTROL考勤信息表.此表已有近兩億筆數(shù)據(jù),根據(jù)INOROUT_TIME字段進(jìn)行分區(qū),并對(duì)相關(guān)字段建立了索引.
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考勤信息表至少需保留一年數(shù)據(jù)備查.六條SQL已加INOROUT_TIME >SYSDATE-3條件,執(zhí)行計(jì)劃中可進(jìn)行分區(qū)裁剪,刪減數(shù)據(jù)這條路行不通.
WHERE中的條件也正確使用了索引,似乎所有常規(guī)優(yōu)化方法都已用上,如何才能進(jìn)一步提升存儲(chǔ)過(guò)程LOOP循環(huán)執(zhí)行速度?
我們?cè)俜治鲞@六條SQL,在WHERE條件中都出現(xiàn)了對(duì)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’
存儲(chǔ)過(guò)程實(shí)際要讀取的只有一天的數(shù)據(jù),這部分?jǐn)?shù)據(jù)一般只有10萬(wàn)筆為熱點(diǎn)數(shù)據(jù).如果我們先把此部分?jǐn)?shù)據(jù)單獨(dú)讀出,在LOOP循環(huán)中就可只讀取臨時(shí)表內(nèi)容,避免4萬(wàn)次讀取有兩億筆數(shù)據(jù)的EMPLOYEE_CONTROL考勤資料表.
依此思路,我們先建立一個(gè)臨時(shí)表,并為臨時(shí)表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);
再在存儲(chǔ)過(guò)程頭部將符合條件的數(shù)據(jù)取出：
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’);
這樣就可改寫(xiě)消耗資源的六條SQL查詢(xún)臨時(shí)表.以第14行SQL為例,需改寫(xiě)為：
–Line14行
SELECT COUNT (*)
INTO tmpVar
FROM cpytgl.EMPLOYEE_CONTROL_TEMP
WHERE EMP_NO = USERID.WORKNO AND TYPE = ‘F’ AND ROWNUM< 6;
LOOP循環(huán)中六條SQL改為查詢(xún)10萬(wàn)筆記錄的臨時(shí)表后,存儲(chǔ)過(guò)程只需1分鐘即可跑完.相較之前20分鐘運(yùn)行時(shí)間有大幅度提升.
此例核心為使用臨時(shí)表隔離冷熱數(shù)據(jù).DBA一次調(diào)優(yōu)不一定能想出最佳方法,通過(guò)對(duì)應(yīng)用的不斷深入觀察,以及Oracle工具的合理使用,加上一點(diǎn)點(diǎn)靈光一現(xiàn)那些看似解決不了的難題都可一一化解.
文章來(lái)自微信公眾號(hào)：數(shù)據(jù)和云
轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/2205.html
