Oracle Database - Multi Row INSERT、バインド変数を使うとリテラル値を使う場合では見える景色が変わるんだよね #最終回 - ぐるぐるしちゃう影響
Previously on Mac De Oracle
Oracle Database - Multi Row INSERT、バインド変数を使うとリテラル値を使う場合では見える景色が変わるんだよね #4 - The SQL was transformed!
前回は、Multi row Insertをリモート表へインサートするとSQL transformの影響で、
DUAL表アクセスがオーバーヘッドとなり Multi row Insertのメリットが削がれてしまう（現時点の仕様では）ということを確認しました！
偶々リモート表に実行したから気づけたわけですがw。あの仕様に気付けたのはラッキーというべきかw
ということで、脇道にそれまくったこのシリーズも、やっと最終回です！
リモート表を使ってぐるぐるしてネットワークラウンドトリップを乗せる必要はなくて、
それが自然に乗るAPサーバーとDBサーバー間の状況を作ればよいだけなので、
最終回は素直にw
JavaからOracle Databaseへアクセスしローカル表に対してぐるぐるしちゃいながら、
Single row insert を繰り返すぐるぐる系と、
Multi row insert を利用して、ゆるやかに、ぐーるぐーるするタイプで 100,000 行を登録してみようと思いますw
N+1問題の類とネットワークラウンドトリップとネットワークレイテンシーと、コミット間隔などパラメータは多いですが、だいたい 100 - 1000 行程度付近前後にリーズナブルなポイントが現れていますよね。。。
(ちなにみSQL*Netのパラメータ等はデフォのままです。また、リモート表ではないので、OPEN_CURSORSもデフォルトのままの 300 で問題ありません。参考まで)
バインド変数利用と、どの程度の単位でまとめてインサートするか、コミットの間隔など沢山のパラメータがあるので、そららの様子をみながら表を見てもらうと面白いと思います。
なお、いつものように後半にログと利用したコードなどをまとめて載せています。
（今回は、生成AIのGeminiくんにサクッと書いてもらいましたw）
Multi Row Insertで、100 - 1000行程度まとめるとメモリにもCPUにも優しくなりますね。単純に、1行毎ぐるぐるすると無駄が多くなるのは一目瞭然だと思います。
一応、ログは以下のような感じ。
Client -> Datatabase - Single row Insert / commit間隔の調整
いわゆる、普通のぐるぐる系ですw
[oracle@arm64-oraclelinux8u10 ~]$ java -classpath ./:$CLASSPATH Oracle23aiDynamicBulkLoad 100000 1 1; ./post_process.sh
...略...
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
に接続されました。
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
212 CPU used by this session 4
212 CPU used when call started 1
212 SQL*Net roundtrips to/from client 2
...略...
212 execute count 621
...略...
212 parse count (hard) 100
212 parse count (total) 144
212 parse time cpu 2
212 parse time elapsed 2
...略...
212 session pga memory 3337208
212 session pga memory max 5189280
212 session uga memory 1904696
212 session uga memory max 3119464
...略...
ロード開始: 総計 100000 行 (チャンクサイズ: 1 コミット間隔: 1)
完了！ 総時間: 43.42 秒
...略...
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
212 CPU used by this session 694
212 CPU used when call started 694
...略...
212 SQL*Net roundtrips to/from client 200002
...略...
212 execute count 100669
...略...
212 parse count (hard) 106
212 parse count (total) 100149
212 parse time cpu 4
212 parse time elapsed 23
...略...
212 session pga memory 3402744
212 session pga memory max 5189280
212 session uga memory 1904696
212 session uga memory max 3119464
...略...
212 user commits 100000
...略...
COUNT(1)
----------
100000
SEGMENT_NAME MB
------------------------------ ----------
MROWS_INS_TAB 45
表が切り捨てられました。
[oracle@arm64-oraclelinux8u10 ~]$ java -classpath ./:$CLASSPATH Oracle23aiDynamicBulkLoad 100000 1 10; ./post_process.sh
...略...
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
176 CPU used by this session 4
176 CPU used when call started 1
176 SQL*Net roundtrips to/from client 2
...略...
176 execute count 417
...略...
176 parse count (hard) 78
176 parse count (total) 126
176 parse time cpu 5
176 parse time elapsed 5
...略...
176 session pga memory 3026592
176 session pga memory max 5123744
176 session uga memory 1773632
176 session uga memory max 3053928
...略...
ロード開始: 総計 100000 行 (チャンクサイズ: 1 コミット間隔: 10)
完了！ 総時間: 43.25 秒
...略...
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
176 CPU used by this session 705
176 CPU used when call started 705
...略...
176 SQL*Net roundtrips to/from client 200002
...略...
176 execute count 100441
...略...
176 parse count (hard) 81
176 parse count (total) 100129
176 parse time cpu 9
176 parse time elapsed 21
...略...
176 session pga memory 3026592
176 session pga memory max 5123744
176 session uga memory 1773632
176 session uga memory max 3053928
...略...
176 user commits 100000
...略...
COUNT(1)
----------
100000
...略...
[oracle@arm64-oraclelinux8u10 ~]$ java -classpath ./:$CLASSPATH Oracle23aiDynamicBulkLoad 100000 1 100; ./post_process.sh
...略...
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
44 CPU used by this session 5
44 CPU used when call started 1
44 SQL*Net roundtrips to/from client 2
...略...
44 execute count 621
...略...
44 parse count (hard) 100
44 parse count (total) 144
44 parse time cpu 4
44 parse time elapsed 6
...略...
44 session logical reads 2518
44 session pga memory 3206136
44 session pga memory max 5123744
44 session uga memory 1904800
44 session uga memory max 3054064
...略...
ロード開始: 総計 100000 行 (チャンクサイズ: 1 コミット間隔: 100)
完了！ 総時間: 43.68 秒
...略...
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
44 CPU used by this session 723
44 CPU used when call started 723
...略...
44 SQL*Net roundtrips to/from client 200002
...略...
44 execute count 100669
...略...
44 parse count (hard) 106
44 parse count (total) 100149
44 parse time cpu 8
44 parse time elapsed 28
...略...
44 session pga memory 3271672
44 session pga memory max 5123744
44 session uga memory 1904800
44 session uga memory max 3054064
...略...
44 user commits 100000
...略...
COUNT(1)
----------
100000
...略...
[oracle@arm64-oraclelinux8u10 ~]$ java -classpath ./:$CLASSPATH Oracle23aiDynamicBulkLoad 100000 1 1000; ./post_process.sh
...略...
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
39 CPU used by this session 4
39 CPU used when call started 1
39 SQL*Net roundtrips to/from client 2
...略...
39 execute count 621
...略...
39 parse count (hard) 100
39 parse count (total) 144
39 parse time cpu 3
39 parse time elapsed 1
...略...
39 session pga memory 3206136
39 session pga memory max 5123744
39 session uga memory 1904800
39 session uga memory max 3054064
...略...
ロード開始: 総計 100000 行 (チャンクサイズ: 1 コミット間隔: 1000)
完了！ 総時間: 42.41 秒
...略...
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
39 CPU used by this session 698
39 CPU used when call started 698
...略...
39 SQL*Net roundtrips to/from client 200002
...略...
39 execute count 100669
...略...
39 parse count (hard) 106
39 parse count (total) 100149
39 parse time cpu 10
39 parse time elapsed 23
...略...
39 session pga memory 3271672
39 session pga memory max 5123744
39 session uga memory 1904800
39 session uga memory max 3054064
...略...
39 user commits 100000
...略...
COUNT(1)
----------
100000
...略...
[oracle@arm64-oraclelinux8u10 ~]$ java -classpath ./:$CLASSPATH Oracle23aiDynamicBulkLoad 100000 1 10000; ./post_process.sh
...略...
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
176 CPU used by this session 3
176 CPU used when call started 1
176 SQL*Net roundtrips to/from client 2
...略...
176 execute count 621
...略...
176 parse count (hard) 100
176 parse count (total) 144
176 parse time cpu 1
176 parse time elapsed 3
...略...
176 session pga memory 3206136
176 session pga memory max 5123744
176 session uga memory 1904800
176 session uga memory max 3054064
...略...
ロード開始: 総計 100000 行 (チャンクサイズ: 1 コミット間隔: 10000)
完了！ 総時間: 41.98 秒
...略...
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
176 CPU used by this session 673
176 CPU used when call started 673
...略...
176 SQL*Net roundtrips to/from client 200002
...略...
176 execute count 100669
...略...
176 parse count (hard) 106
176 parse count (total) 100149
176 parse time cpu 6
176 parse time elapsed 22
...略...
176 session pga memory 3271672
176 session pga memory max 5123744
176 session uga memory 1904800
176 session uga memory max 3054064
...略...
176 user commits 100000
...略...
COUNT(1)
----------
100000
...略...
[oracle@arm64-oraclelinux8u10 ~]$ java -classpath ./:$CLASSPATH Oracle23aiDynamicBulkLoad 100000 1 100000; ./post_process.sh
...略...
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
176 CPU used by this session 5
176 CPU used when call started 1
176 SQL*Net roundtrips to/from client 2
...略...
176 execute count 621
...略...
176 parse count (hard) 100
176 parse count (total) 144
176 parse time cpu 1
176 parse time elapsed 3
...略...
176 session pga memory 3206136
176 session pga memory max 5123744
176 session uga memory 1904800
176 session uga memory max 3054064
...略...
ロード開始: 総計 100000 行 (チャンクサイズ: 1 コミット間隔: 100000)
完了！ 総時間: 41.62 秒
...略...
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
176 CPU used by this session 698
176 CPU used when call started 698
...略...
176 SQL*Net roundtrips to/from client 200002
...略...
176 execute count 100669
...略...
176 parse count (hard) 106
176 parse count (total) 100149
176 parse time cpu 4
176 parse time elapsed 26
...略...
176 session pga memory 3271672
176 session pga memory max 5123744
176 session uga memory 1904800
176 session uga memory max 3054064
...略...
176 user commits 100000
...略...
COUNT(1)
----------
100000
...略...
Client -> Datatabase - Multi row Insert / バルクロード行数調整
Multi row Insertなので繰り返し実行ではありますが、ぐるぐる というより、ぐーーーーる、ぐーーーーる系な感じw です。( N+1だと ぐるぐる、ぐーーーる、ぐーーるの違いを表現できなーーーいw )
[oracle@arm64-oraclelinux8u10 ~]$ java -classpath ./:$CLASSPATH Oracle23aiDynamicBulkLoad 100000 10 1; ./post_process.sh
...略...
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
38 CPU used by this session 4
38 CPU used when call started 1
38 SQL*Net roundtrips to/from client 2
...略...
38 execute count 621
...略...
38 parse count (hard) 100
38 parse count (total) 144
38 parse time cpu 2
38 parse time elapsed 5
...略...
38 session pga memory 3206136
38 session pga memory max 5123744
38 session uga memory 1904800
38 session uga memory max 3054064
...略...
ロード開始: 総計 100000 行 (チャンクサイズ: 10 コミット間隔: 1)
完了！ 総時間: 5.44 秒
...略...
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
38 CPU used by this session 98
38 CPU used when call started 98
38 SQL*Net roundtrips to/from client 20002
...略...
38 execute count 10719
...略...
38 parse count (hard) 107
38 parse count (total) 10154
38 parse time cpu 5
38 parse time elapsed 10
...略...
38 session pga memory 3337208
38 session pga memory max 5123744
38 session uga memory 1970280
38 session uga memory max 3054064
...略...
38 user commits 10000
...略...
COUNT(1)
----------
100000
...略...
[oracle@arm64-oraclelinux8u10 ~]$ java -classpath ./:$CLASSPATH Oracle23aiDynamicBulkLoad 100000 100 1; ./post_process.sh
...略...
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
38 CPU used by this session 4
38 CPU used when call started 1
38 SQL*Net roundtrips to/from client 2
...略...
38 execute count 621
...略...
38 parse count (hard) 100
38 parse count (total) 144
38 parse time cpu 2
38 parse time elapsed 2
...略...
38 session pga memory 3206136
38 session pga memory max 5123744
38 session uga memory 1904800
38 session uga memory max 3054064
...略...
ロード開始: 総計 100000 行 (チャンクサイズ: 100 コミット間隔: 1)
完了！ 総時間: 1.34 秒
...略...
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
38 CPU used by this session 35
38 CPU used when call started 35
38 SQL*Net roundtrips to/from client 2002
...略...
38 execute count 1719
...略...
38 parse count (hard) 107
38 parse count (total) 1154
38 parse time cpu 2
38 parse time elapsed 2
...略...
38 session pga memory 3795960
38 session pga memory max 7400440
38 session uga memory 2101240
38 session uga memory max 3054064
...略...
38 user commits 1000
...略...
COUNT(1)
----------
100000
...略...
[oracle@arm64-oraclelinux8u10 ~]$ java -classpath ./:$CLASSPATH Oracle23aiDynamicBulkLoad 100000 1000 1; ./post_process.sh
...略...
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
38 CPU used by this session 5
38 CPU used when call started 1
38 SQL*Net roundtrips to/from client 2
...略...
38 execute count 621
...略...
38 parse count (hard) 100
38 parse count (total) 144
38 parse time cpu 3
38 parse time elapsed 3
...略...
38 session pga memory 3206136
38 session pga memory max 5123744
38 session uga memory 1904800
38 session uga memory max 3054064
...略...
ロード開始: 総計 100000 行 (チャンクサイズ: 1000 コミット間隔: 1)
完了！ 総時間: 1.21 秒
...略...
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
38 CPU used by this session 63
38 CPU used when call started 63
38 SQL*Net roundtrips to/from client 202
...略...
38 execute count 819
...略...
38 parse count (hard) 107
38 parse count (total) 254
38 parse time cpu 9
38 parse time elapsed 11
...略...
38 session pga memory 3533816
38 session pga memory max 34925560
38 session uga memory 2232200
38 session uga memory max 5702640
...略...
38 user commits 100
...略...
COUNT(1)
----------
100000
...略...
[oracle@arm64-oraclelinux8u10 ~]$ java -classpath ./:$CLASSPATH Oracle23aiDynamicBulkLoad 100000 10000 1; ./post_process.sh
...略...
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
19 CPU used by this session 5
19 CPU used when call started 2
19 SQL*Net roundtrips to/from client 2
...略...
19 execute count 559
...略...
19 parse count (hard) 100
19 parse count (total) 142
19 parse time cpu 5
19 parse time elapsed 3
...略...
19 session pga memory 3271672
19 session pga memory max 5123744
19 session uga memory 1899488
19 session uga memory max 3053984
...略...
ロード開始: 総計 100000 行 (チャンクサイズ: 10000 コミット間隔: 1)
完了！ 総時間: 33.74 秒
...略...
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
19 CPU used by this session 3334
19 CPU used when call started 3334
19 SQL*Net roundtrips to/from client 22
...略...
19 execute count 667
...略...
19 parse count (hard) 107
19 parse count (total) 162
19 parse time cpu 728
19 parse time elapsed 730
...略...
19 session pga memory 6286328
19 session pga memory max 286518264
19 session uga memory 5043712
19 session uga memory max 31643696
...略...
19 user commits 10
...略...
COUNT(1)
----------
100000
...略...
ふーーーっ。
完！
では、また、別のネタでお会いしましょう :)
テスト環境の情報
macOS Apple SiliconのVirtualBox
oracle@Mac ~ % ./print_env.sh
*** mac info. ***
Model Name: MacBook Air
Chip: Apple M2
Total Number of Cores: 8 (4 performance and 4 efficiency)
Memory: 24 GB
*** macOS ver. ***
ProductName: macOS
ProductVersion: 26.2
BuildVersion: 25C56
*** VirtualBox ver. ***
7.2.4r170995
VMのOS、および、Java
[oracle@arm64-oraclelinux8u10 ~]$ java -version
openjdk version "11.0.25" 2024-10-15 LTS
OpenJDK Runtime Environment (Red_Hat-11.0.25.0.9-1.0.1) (build 11.0.25+9-LTS)
OpenJDK 64-Bit Server VM (Red_Hat-11.0.25.0.9-1.0.1) (build 11.0.25+9-LTS, mixed mode, sharing)
[oracle@arm64-oraclelinux8u10 ~]$ uname -rpo
5.15.0-313.189.5.3.el8uek.aarch64 aarch64 GNU/Linux
[oracle@arm64-oraclelinux8u10 ~]$ cat /etc/os-release
NAME="Oracle Linux Server"
VERSION="8.10"
...略...
ソース
[oracle@arm64-oraclelinux8u10 ~]$ cat show_mrows_ins_tab_size.sql
select segment_name,bytes/1024/1024 as "MB" from dba_segments where owner='SCOTT' and segment_name = upper('mrows_ins_tab')
/
[oracle@arm64-oraclelinux8u10 ~]$ cat post_process.sh
sqlplus system/hogehoge@localhost:1521/freepdb1 @post_process
[oracle@arm64-oraclelinux8u10 ~]$ cat post_process.sql
SELECT COUNT(1) FROM scott.mrows_ins_tab
/
@show_mrows_ins_tab_size
truncate table scott.mrows_ins_tab
/
exit
[oracle@arm64-oraclelinux8u10 ~]$ cat show_mystats.sh
sqlplus system/hogehoge@localhost:1521/freepdb1 @show_mystats2 scott
[oracle@arm64-oraclelinux8u10 ~]$ cat show_mystats2.sql
set veri off
SELECT
s.sid,
n.name,
s.value
FROM
v$sesstat s
INNER JOIN v$statname n
ON
s.statistic# = n.statistic#
AND s.sid = (SELECT sid FROM v$session WHERE username = UPPER('&1'))
WHERE
s.value > 0
AND (
n.name LIKE '%memory%'
OR n.name LIKE '%CPU%'
OR n.name LIKE '%I/O%'
OR n.name LIKE '%write%'
OR n.name LIKE '%read%'
OR n.name LIKE 'redo%'
OR n.name LIKE 'SQL*Net%'
OR n.name LIKE '%commit%'
OR n.name LIKE 'execute count'
OR n.name LIKE 'parse%'
)
ORDER BY
n.name;
UNDEFINE 1
set veri on
exit
Geminiくんに書いてもらったJavaのコードw
[oracle@arm64-oraclelinux8u10 ~]$ cat Oracle23aiDynamicBulkLoad.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.io.IOException;
import java.io.InputStream;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.util.Collections;
public class Oracle23aiDynamicBulkLoad {
private static final String URL = "jdbc:oracle:thin:@localhost:1521/freepdb1";
private static final String USER = "scott";
private static final String PASSWORD = "hogehoge";
public static void main(String[] args) {
// インサートする行数、デフォルト値(10万行)
int totalRows = (args.length > 0) ? Integer.parseInt(args[0]) : 100000;
// 1回あたりの同時インサート行数、デフォルト100行
int chunkSize = (args.length > 1) ? Integer.parseInt(args[1]) : 100;
// commit interval, デフォルト1行
int commitInterval = (args.length > 2) ? Integer.parseInt(args[2]) : 1;
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
conn.setAutoCommit(false);
showSessionStats();
System.out.println("ロード開始: 総計 " + totalRows + " 行 (チャンクサイズ: " + chunkSize + " コミット間隔: " + commitInterval + ")");
long startTime = System.currentTimeMillis();
for (int i = 0; i < totalRows; i += chunkSize) {
int currentBatchSize = Math.min(chunkSize, totalRows - i);
executeMultiRowInsert(conn, i, currentBatchSize);
if (chunkSize == 1 && (i % commitInterval) == 0) {
conn.commit();
} else {
conn.commit();
}
}
long endTime = System.currentTimeMillis();
System.out.printf("完了！ 総時間: %.2f 秒%n", (endTime - startTime) / 1000.0);
showSessionStats();
conn.disconnect();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void showSessionStats() {
try {
ProcessBuilder pb = new ProcessBuilder("/bin/bash", "-c", "/home/oracle/show_mystats.sh");
Process process = pb.start();
// 結果の取得
InputStream is = process.getInputStream();
InputStreamReader isr = new InputStreamReader(is);
BufferedReader br = new BufferedReader(isr);
String line;
while ((line = br.readLine()) != null) {
System.out.println(line);
}
// 終了コードを取得
int exitCode = process.waitFor();
System.out.println("Exited with code: " + exitCode);
} catch (IOException | InterruptedException e) {
e.printStackTrace();
}
}
private static String lpad(String original, int length, String padChar) {
if (original.length() >= length) return original;
return padChar.repeat(length - original.length()) + original;
}
private static void executeMultiRowInsert(Connection conn, int offset, int rowCount) throws SQLException {
String rowPlaceholder = "(?, ?)";
String allPlaceholders = String.join(", ", Collections.nCopies(rowCount, rowPlaceholder));
String sql = "INSERT /* MONITOR */ INTO mrows_ins_tab (id, col8) VALUES " + allPlaceholders;
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
for (int i = 0; i < rowCount; i++) {
int id = offset + i + 1;
int baseIdx = i * 2;
String col8 = lpad(String.valueOf(id), 373, "x");
pstmt.setInt(baseIdx + 1, id);
pstmt.setString(baseIdx + 2, col8);
}
pstmt.executeUpdate();
}
}
}
関連エントリ
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #20 - Table Value Constructer （TVC）
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #21 - Table Value Constructer（TVC）- ハードパース時間とメモリ消費量 / BONUS TRACK
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #22 - Multi Row INSERT
・Oracle Database - Multi Row INSERT、バインド変数を使うと、リテラル値を使う場合では見える景色が変わるんだよね #1 - バグなのか現時点の仕様なのか?
・Oracle Database - Multi Row INSERT、バインド変数を使うとリテラル値を使う場合では見える景色が変わるんだよね #2
・Oracle Database - Multi Row INSERT、バインド変数を使うとリテラル値を使う場合では見える景色が変わるんだよね #3 - ローカル表とリモート表での挙動の差異？！
・Oracle Database - Multi Row INSERT、バインド変数を使うとリテラル値を使う場合では見える景色が変わるんだよね #4 - The SQL was transformed!
最近のコメント