首页 运维干货运维技巧 - 活用临时表隔离冷热数据

运维技巧 - 活用临时表隔离冷热数据

运维派隶属马哥教育旗下专业运维社区,是国内成立最早的IT运维技术社区,欢迎关注公众号:yunweipai
领取学习更多免费Linux云计算、Python、Docker、K8s教程关注公众号:马哥linux运维

编辑手记: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工具的合理使用,加上一点点灵光一现那些看似解决不了的难题都可一一化解。

文章来自微信公众号:数据和云

本文链接:https://www.yunweipai.com/21836.html

网友评论comments

发表回复

您的电子邮箱地址不会被公开。

暂无评论

Copyright © 2012-2022 YUNWEIPAI.COM - 运维派 京ICP备16064699号-6
扫二维码
扫二维码
返回顶部