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

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

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-07-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏沃趣科技

SQL优化案例-从执行计划定位SQL问题(三)

当SQL出现问题,能从执行计划中快速的定位哪部分出现问题很重要,SQL文本如下(为保证客户隐私,已经将注释和文字部分去掉):

1416
来自专栏乐沙弥的世界

函数使得索引列失效

      在索引列上使用函数使得索引失效的是常见的索引失效原因之一,因此尽可能的避免在索引列上使用函数。尽管可以使用基于函数的索引来 解决索引失效的问题,但...

703
来自专栏乐沙弥的世界

Oracle 性能相关常用脚本(SQL)

在缺乏的可视化工具来监控数据库性能的情形下,常用的脚本就派上用场了,下面提供几个关于Oracle性能相关的脚本供大家参考。以下脚本均在Oracle 10g测试通...

852
来自专栏IT技术精选文摘

数据库SQL性能优化(一)

substr(hbs_bh,1,4)=’5400’,优化处理:hbs_bh like ‘5400%’ trunc(sk_rq)=trunc(sysdate), ...

1846
来自专栏杨建荣的学习笔记

关于索引扫描的极速调优实战(第一篇) (r3笔记第81天)

一般在生产环境中,如果某个查询中涉及一个大表,走索引扫描是显然是最值得推荐的方式,但是索引扫描有unique index scan, range scan,sk...

2204
来自专栏杨建荣的学习笔记

通过pl/sql来格式化sql(r4笔记第63天)

在之前的一篇博文中分享了通过java来格式化sql,http://blog.itpub.net/23718752/viewspace-1444910/ 今天突然...

3154
来自专栏码匠的流水账

聊聊spring for kafka的AckMode

本文主要讲述一下spring for kafka的consumer在spring.kafka.consumer.enable-auto-commit是false...

342
来自专栏james大数据架构

SQL常用数据库结构升级语句

修改视图 --SQL Server 2005 GO IF EXISTS (SELECT * FROM sys.views WHERE object_id ...

1729
来自专栏杨建荣的学习笔记

生产系统调优之_敢于质疑(90天)

接着昨天的那个问题来说。有个sql语句在做了统计信息收集之后,速度有了一定的提升,从5秒的响应降低到了2秒。但是和预期还是有一定 的差距,按照80条查询请求在短...

2457
来自专栏WindCoder

where in与join 查询

Oracle:当前所用版本中,限制in中的参数不能超过 1000个。当超出时会被报错"ORA-01795异常(where in超过1000)的解决"。

200

扫描关注云+社区