Oracle数据库优化之内存表的使用说明

集控项目问题：由于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的变量来实现内存表数据的存储。然后，对外封装各种新增/删除/更新/查询的方法，方便处理内存表的数据，实现对应的业务逻辑。