Oracle如何最大化批量插入的性能《第二季》

西区O记重案实录 2021-05-12

3164

如何最大化批量插入的性能？

DBA常见问题

这是DBA日常经常会问道的问题，之前曾经详细讨论过数据库里面的办法，数据库里面的方法如下：

数据库套路

优化itl，pct，no logging，append 提升3%（如蚊子咬，whatever）

开启parallel dml 提升5~10%

使用batch commit 提升100%

使用batch execution 提升150%

适当采用hash partition 提升100%

数据预排序data pre-ordered 提升50%

应用修改为并行，并结合分区，提升1000%

16年的分享，曾经详细讨论，请参考菜单-优化专家

某运营商的大型工单排重，通过修改应用为并行，HASH分区等，提高了近10倍的性能。

最近遇到一例协助客户进行最大化数据插入量的修改，只是这次同时进行应用代码的修改，细节分享如下：

客户的需求比工单排重还简单，只需要将远程数据库的某一表批量的select过来，插入目标表即可，目标表上无索引。数据量大的表可达几百万行以上。

客户翻译小表的执行性能尚可，大表的性能会逐渐下降到非常之差。通过观测，插入了300万行数据，会在前20万行，非常快，之后逐渐下降，最终个会使用接近60分钟。

应用select 和 insert的伪代码如下

conn1= ConnectionFactory.getSourceConnection();

PrepareStatment= conn1. prepareStatement(“select **** from ****”);

将远程数据库的执行结果放在本地的list中（内存）

ResultSet rset = stmt.executeQuery(sql);

while (rset.next()) {

datalist.add(rest.get(obj));

}

准备目标数据库的连接和语句

conn2= ConnectionFactory.getTargetConnection();

String insertsql= "inset into ….(?????)";

PreparedStatement stmt2;

stmt2 = this.repository_conn.prepareStatement(insertsql);

循环插入

for (int j = 0; j < asmlist.size(); j++) {

stmat2.addBatch(asmlist.get(j));

if(j/5000=0){

stmt2.executeBatch(); 批量执行

conn2.commit; 批量提交

}

通过以上伪代码，可以看出，用户的代码已经考虑到常规的优化因素。

将远程数据全部放到本地内存后，再进行插入，优点是一次完成select，一次进行insert，避免进行多次交互。缺点就是对本地内存的占用较大，需要本地服务器有足够内存，避免发生swap，或者虚拟机memory leak。

2. 插入的时候，已经是批量执行，和批量提交，这一步提升比逐行执行，已经快了很多倍以上了。

在测试过程中发现了，两个问题，部分select 完成时间极长，甚至最终报错snap too old。经过检查后发现，这部分select在sqlplus远程连接，运行很快，能够在几秒后开始返回数据，但在JDBC中运行，则数分钟都每响应，或者rset 在fetch100多行后，又再次hang住。等待事件观测为：

SQL*NET message to client

偶尔发现长时间在

PGA memory operation

这里其实就是一个经常有DBA问的难点，为什么sqlplus运行快，但在程序中就运行不出来。

优化查询性能

SQL*NET message to client这些网络等待事件，是数据库中非常常见的等待事件，服务端发送数据到客户端以后，客户端需要返回ack到服务器端确认。在新版本数据库中，已经罕见有相关bug出现。而官方的优化办法，一般是调整网络参数，tns中的SUD size等。

常见解决方案

1. SDU size

Remember that 'SQL*net message to client' is normally not a network issue, as the throughput is based on the setting for the environment network setting for TCP packet The first session is sent the contents of the SDU buffer which is written to TCP buffer then the session waits for the 'SQL*net message to client' event. The wait is associated with the following factors:

Oracle SDU size
Amount of data returned to the client

Client’s TNSNAMES.ORA:

TEST =
(DESCRIPTION =
(SDU=8192)
(TDU=8192) <- 8.0 TDU position
(ADDRESS =(PROTOCOL = TCP)(HOST = bill.johndoe.com)(PORT = 1521))
(CONNECT_DATA = (SID = V920)))

LISTENER.ORA:

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
        (SDU = 8192)         <- Setting SDU to 8192 for this SID
        (TDU = 8192)         <- 8.0 TDU position
        (SID_NAME = V920)
        (ORACLE_HOME = /oracle/product/9.2.0)))

2. Arraysize

set arraysize 1000

3. TCP

调整操作系统的TCP相关参数，请参加各个平台的最佳实践文档。

其实用户的生产系统已经运行了数年，从未发生过NET配置导致的问题，而且大部分采集都能成功，只有个别SQL出问题，所以网络这方面优化的可能性较小。SQL*NET message to client很可能会误导优化方向，浪费大量的时间。通过检查客户的SQL:

客户的SQL

select ***

from table1,

join view2 on ...

join view3 on ...

left join table2

SQL是表和视图进行join，联想到查询转换。本着老师傅调优，先猜后分析的最快优化法则，立刻禁用了查询转换

/*+no_query_transformation/

SQL立刻恢复正常，在JDBC中能够快速完成查询。no_query_transformation关闭了所有类型的查询转换，调整面太大。

查询转换的类型：

Aggregate Subquery Elimination
Common subexpression elimination
Complex View Merging
Filter predicate push-down in a view
Group by placement
Intersect conversion into join
Join Elimination
Join predicate push down
Materialized view rewrite
Minus conversion into anti-join
Native Full Outer Join
OR expansion
Order by Elimination
Outer to inner join conversion
Predicate move-around
Star transformation
Subquery unnesting

IN EXISTS -> SEMI JOIN
NOT IN NOT EXISTS -> ANTI JOIN
Null-Aware and Single Null-Aware ANTI JOIN

Transitive Predicates

所以再利用hint，进行更细的测试发现，利用OPT hint设置_simple_view_merge=false即可解决该问题。这样select的问题获得解决。

优化插入性能

下面开始分析插入慢的问题，首先JVM内存足够，服务器内存没有发生swap。这种插入的性能是很差的，内存级的操作，居然几百万行，要几十分钟。而在数据库层面，几乎看不到activesession的存在，对数据库的压力微乎其微，也就是瓶颈是在应用层。用户迫切需要，能不能让数据库忙起来？可以。

于是着手修改伪代码如下:

上文所示，原有插入是线性循环datalist进行batch的加载。

现在，将datalist分解为若干个子list（可配置的并行度，几个并行就对应几个子list）

计算出offset对数据进行简单切分，伪代码如下

int offset = allrow pdegree; --pdegree可以设置到配置文件中，动态调整

for (int v = 0; v< pdegree; v++) {

int start = v * offset;

int end = (v + 1) * offset;

if (v == pdegree - 1) {

end = allrow;

}

LinkedList c1 = newLinkedList<>(datalist.subList(start, end));

--这里有个要点，切分的list必须是在内存中新分配的对象，而不是指向原有list的指针，否则会报同步错误！！！

再将insert的代码修改为线程 InsertWorker，这样执行的时候，即为拆分list，每个InsertWorker 持有一个子list，在后台执行，互相独立。

启动insetWorker

InsertWorker p = new InsertWorker();

p.setList(c1);

p.dbid = this.dbid;

p.workid=work.getWORKID().intValue();

p.dbname = this.dbname;

p.workerid = v;

p.start();

Insert Worker的伪代码，即是前文执行insert的部门，只是加了了线程支持。

继承Thread

InsertWorker() {

conn =ConnectionFactory.getReposConnection();

}

LinkedList insertlist;

public void setList(LinkedListc1) {

this.insertlist = c1;

}

@Override 重写方法

public void run() {

执行insert

}

修改完毕以后，并行度5的情况，插入由60分钟，提升到了5分钟，修改到12并行度，提升到了1分钟内完成。

至此解决，原有的code执行时间为3~5小时，并伴有snap too old的报错，优化后为10分钟左右。

优点，insert workder可以自定义并行度，切分datalist，最大化性能，完全可控制。

缺点，用户感觉插入库，有时候压力过大，因为线程在提交运行后，是在后台执行的，高峰期可能有上百个线程在后台进行批量插入。这个时候，建议用户对代码再次修改，将insertWorker 加入一个 ThreadList，再控制ThreadList中的同时active的thread数量，比如最大为10，这样就可以完美控制对数据库的性能冲击。