集控项目问题:由于ERP系统的Oracle数据库产生的日志量过多(就一个表在5.13号凌晨1个小时内就有2.6个亿的更新日志),导致罗马平台同步数据采集超时。所以需要对这块做专题优化。
修改逻辑:通过分析对应的程序逻辑,发现INSERT日志和UPDATE日志都是针对XYG_ALFR_INV_ALLO_GT和XYG_ALFR_CUST_BALANCE_TEMP表。而这2个表是GLOBAL TEMP的临时表。要减少日志的产生,可以直接用Oracle内存表的逻辑重构修改。
总结来说,就是,将 XYG_ALFR_INV_ALLO_GT 和 XYG_ALFR_CUST_BALANCE_TEMP 改为内存表的形式。
本文同时也是Oracle内存表应用于业务系统优化的最佳实践。
一、问题/主题
集控项目问题:由于ERP系统的Oracle数据库产生的日志量过多(就一个表在5.13号凌晨1个小时内就有2.6个亿的更新日志),导致罗马平台同步数据采集超时。所以需要对这块做专题优化。
备注:
上面数据的统计语句:
select table_name,operation,count(operation) from V$LOGMNR_CONTENTS
where table_name =''
group by table_name,operation
order by 3 desc
二、分析及处理过程
就看DML日志最多的3个表,其中2个表是XYG开头的。就是XYG_ALFR_CUST_BALANCE_TEMP和XYG_ALFR_INV_ALLO_GT,它们都是ERP应收模块的计算客户余额范围内的应收交易明细用。
核心的逻辑数据处理就是这个包:XYG_ALFR_REPORT_PKG
通过简单的分析,其实这2个表都是会话级别的临时表,其实是完全没必要产生日志的。经过验证,临时表用APPEND+NOLOGING来INSERT数据,是可以减少INSERT的日志,不过会增加COMMIT的脚本;另外,也无法减少UPDATE产生的日志。
其实主要的日志量还是在UPDATE,如下图所示:
经过讨论,要优化日志产生,现在有3个方案:
1 罗马平台在抽取日志的时候,能不能区分不抽临时表的日志。
2 修改PKG逻辑,将Temp表改为内存表,不过需要验证和测试,不一定可行。
3 修改业务逻辑,增加客户化核销逻辑。这个工作量很大,需要定方案和大量的开发。
第一个方案目前不可行。第三个方案需要进行业务改造,风险大,而且时间周期长。
最稳妥的是第二个方案,所以最终也选择了第二个方案。
简而言之,就是:将 XYG_ALFR_INV_ALLO_GT 和 XYG_ALFR_CUST_BALANCE_TEMP 改为内存表的形式。
优化的方案确定了,那如何修改为内存表?
首先了解啥是内存表。
内存表概念
1、PL/SQL表类似于C语言中的数组。如果要声明一个PL/SQL表,要先定义该表类型,然后在声明属于该类型的变量。
2、理论上,数据库数据空间有多大,我们的内存表就可以存储多大的数据,就是说他和我们的物理表是相同的,我们可以把物理表的数据完全拷贝到内存表中。
3、PL/SQL表的元素没有必要按照特定的次序排列,因为他们不是象数组那样连续存储在内存中,元素可以按照任意键值进行插入。
4、PL/SQL表的键值(KEY)没有必要是顺序的。表所占用的内存并不依赖于键所使用的数值。
(备注:上面的解析来源自http://www.itpub.net/forum.php?mod=viewthread&tid=1707128)
上面我们可以大概了解了一下内存表的用法。其实网上的解析对于我们实际的应用还是差异蛮大的。
最核心的是要解决一个问题:内存表的数据源存储在哪里?
以上面的itpub的作为例子。
如上图所示,数据源是存在emp表里面:他的逻辑实际上是从emp取数出来放在内存表。每次都要查询emp表才可以得到内存表的数据。效率实际上是很低。只能解决查询数据之后的复杂逻辑处理的效率问题,而无法解决数据的存储问题。
而我们本次的优化目标是需要想办法将整个XYG_ALFR_INV_ALLO_GT表和XYG_ALFR_CUST_BALANCE_TEMP表都用内存表的方式来改写(数据需要直接存在内存里面,而且是可以随时查询,按需进行增删改)。减少这2个表格的频繁DML操作带来的日志量的暴增。
所以,要实现优化的效果,目前我的做法是用Oracle Package的包级变量来存储内存表的数据,实现数据直接存储在内存里面。而且是session级别的。
具体实现步骤:
1、首先,我们需要定义记录以及表的Type类型。
CREATE OR REPLACE TYPE APPS.XYG_ALFR_INV_ALLO_REC AS OBJECT ( LEGAL_ENTITY_ID NUMBER, ORG_ID NUMBER, PARTY_ID NUMBER, CUST_ACCOUNT_ID NUMBER, ….太多,省略了 MINUS_AMOUNT NUMBER ); CREATE OR REPLACE TYPE APPS.XYG_ALFR_INV_ALLO_TBL AS TABLE OF XYG_ALFR_INV_ALLO_REC; |
2、接着,定义包级变量,存储session级别的数据。
就是:L_INV_ALLO_TBL
3、然后,封装对这个内存表(包级变量)的初始化/新增/更新/查询等的方法。
初始化:
新增:
更新:
删除:
整个内存表的查询:
SQL查询结果样例:
4、基本的测试:
例如新增内存表数据的测试
DECLARE L_INV_ALLO_REC XYG_ALFR_INV_ALLO_REC := XYG_ALFR_REPORT_PKG.GET_INV_ALLO_REC_INIT(); BEGIN XYG_ALFR_REPORT_PKG.MEMORY_TBL_INIT(); ---- L_INV_ALLO_REC.LEGAL_ENTITY_ID := 88888; L_INV_ALLO_REC.PAYMENT_SCHEDULE_ID := -1; XYG_ALFR_REPORT_PKG.INSERT_INV_ALLO_TBL(L_INV_ALLO_REC); --- L_INV_ALLO_REC.LEGAL_ENTITY_ID := 888999; L_INV_ALLO_REC.PAYMENT_SCHEDULE_ID := -2; XYG_ALFR_REPORT_PKG.INSERT_INV_ALLO_TBL(L_INV_ALLO_REC); END; |
查询:
从上面的脚本很容易能看出,数据已经实现直接存储在内存里面。而且,对数据的所有新增/删除/更新等操作,都是在内存表的PLSQL赋值操作,完全没SQL的DML操作。无任何的数据库日志产生。能达到优化的效果。
特别说明:
1、关于内存表下标定位的处理逻辑。
细心的朋友可能会发现一个点:
在新增内存表数据的时候,用到了这个(XYG_DBA_PKG.INSERT_VC2MAP_TBL)。
而在更新/删除的时候,用到了这个获取内存表下标的逻辑:
L_TBL_IDX :=
TO_NUMBER(XYG_DBA_PKG.G_VARCHAR2_MAP_TBL(L_INV_ALLO_TBL_CODE||'|'||P_BATCH_ID||'|'||P_PAYMENT_SCHEDULE_ID));
为什么如此复杂?直接用下标行不行?
其实是这样子的:
内存表实际上是一个数组,所有数组的唯一定位只有靠下标。而下标只能是1,2,3..这样子的整数,是一个无意义的递增整数序列。
而实际上我们在更新或者删除数据的时候,只知道业务的核心ID字段,而是无法直接知道是哪个下标的。这样子就涉及到一个很困难的问题:如何根据业务的核心ID字段来快速定位对应的内存表的下标?
一个很笨的办法是,用遍历数组的办法。当出现匹配业务ID字段的时候,获取到对应的下标并就跳出循环。效率很低很低。
另外一个很笨的办法是,因为内存表是能转换为管道表的,管道表能直接SELECT查询的,然后通过SELECT查询的办法能快速定位要更新的数组的下标。这个办法有一个内存表频繁转换到管道表的额外消耗,实际上也是不可取的方法。
所以,我最后用的办法是,再用另外一个内存表(而且是Map类型的内存表),下标Key是字符串(就是核心业务ID字段),值value是对应这个核心业务ID字段的内存表的下标。
这样子,只需要知道核心业务ID字段,就可以通过这个Map内存表获取内存表的下标,就能快速定位要更新/删除的数据了。
目前性能最好的方式了。
如下图所示:
这样子就可以完美实现一个效果:根据业务ID字段能快速定位要更新的内存表的行,进行更新/删除的动作。
2、如何直接通过SQL的SELECT语句,快速将数据INSERT到内存表里面去。
这个问题其实是一个开发技巧。
如果是包的Type变量,实际上直接用SELECT栏位+ BULK COLLECT INTO即可。
但是,我们现在用的是数据库级别的Object的Type变量,包括REC和TABLE的。这种如何批量获取?
网上了解了一下,原来语法是这样子的:
以XYG_ALFR_INV_ALLO_REC+ XYG_ALFR_INV_ALLO_TBL作为例子。
定义变量:
L_INTER_INV_ALLO_TBL XYG_ALFR_INV_ALLO_TBL := XYG_ALFR_INV_ALLO_TBL();
然后(注意格式,SELECT对应的是REC类型):
SELECT XYG_ALFR_INV_ALLO_REC(对应的SELECT字段,必须要和REC的类型和数量都一致)
BULK COLLECT INTO L_INTER_INV_ALLO_TBL
FROM XXXXX
WHERE XXXXX
就这个格式就行。
当然,要塞到我们的内存表,还需要做一次的遍历,因为INSERT做了封装的。
FOR I IN 1..L_INTER_INV_ALLO_TBL.COUNT LOOP
XYG_ALFR_REPORT_PKG.INSERT_INV_ALLO_TBL(L_INTER_INV_ALLO_TBL(I));
END LOOP;
(完整样例请看:XYG_ALFR_REPORT_PKG.GET_CUST_AR_DETAIL)
三、总结
通过阅读本文,应该要了解Oracle内存表的开发实现方法。最好按照自己的理解写一个实际的例子。核心的逻辑还是要用Package的变量来实现内存表数据的存储。然后,对外封装各种新增/删除/更新/查询的方法,方便处理内存表的数据,实现对应的业务逻辑。