とあるJargon絡みのおはなし
久々のDatabaseネタですw
先日、ぐるぐる系がほんとうに嫌いなんだなぁ、というアトモスフィアを感じる会話に交ぜてもらいww、
ちょっと思い出したことがあったので書いてみようと思います。
（悪いとか良いとかそういうくくりの話に振るつもりはないので、どの方法をどう使うか、よーく考えて判断しましょうね、ぐらいの気持ちで書いいてますw）
世の中には、意図的にぐるぐる系バッチだけ、index unique scanや、access by rowidだけになることを狙い、統計情報も意図的に設定（e.g. https://docs.oracle.com/cd/F82042_01/arpls/DBMS_STATS.html#GUID-F993ADFE-85A9-4939-84D1-CC7A6E5C56D2 など）したり、あたまを振り絞って考え抜かれた ぐるぐる系 の世界があったります。
待機イベントは、DB cpu, db file sequential readが主体で。（私は、やりたいとは思わないけどw）
ところで、
ぐるぐる系ってのは、いわゆるN+1問題に類する処理。
フレームワーク起因でそうなりやすいやつだけでなく、意図的にぐるぐるしちゃうというのもすべて含めたもの。基本的にネガティブな意味で使われていますw。
そんなJargonが存在する現場では、的確に意図が伝わるってのがメリットだったり。。。
N+1がさぁ〜より、ぐるぐるがさぁ〜ってほうが日本語の脳みそにはイメージしやすくてグっと来るw 方は多い感じはしますw。現場でうまれたJargonですのでw 英語3文字略語よりわかりやすくて良い(個人差ありw)
もう一つ、 ぐるぐる系の反対語のJargonって知ってます？
答え: ガツン系
ここまでの用語解説部分はツッコミいれるところではないので、かるくスルーしてくださいませ。 前説ですから。 m(_ _)m
（ちょっと脱線しすぎた感じはするけどもwwww）
ということで（どう言うことだ！w）、ながーーーーーーい前置きはこれぐらいにして本題へ。
ぐるぐる系とガツン系の予習をしていただいたところでww
ちょっとしたサンプルを紹介し、どちらを選ぶかは、アナタしだいです。という意味で、打ちっぱなしというか投げっぱなしの話を書いておこう思います。
今回の例ではストアドプロシージャ化してあります。
ストアドプロシージャでぐるぐるしちゃった場合、ネットワークレイテンシーの影響は受けないのですが繰り返し部分のオーバーヘッド有無の差異は見えるはずです。
SQL文の繰り返しもそうですが、それ以外のオーバーヘッドも積もると山になるわけで。
なお、SQL文がものすごく軽いくて速い、かつ、バッチ専用の別サーバーで実行される場合は繰り返し実行されるロジックだけでなく、サーバー間のネットワークレイテンシーの影響が見過ごせないぐらい（回避不能でもありますが）目立ってくることも思い出してくださいね。
以下の図をイメージしておくと危険な香りを感じ取れるのではないか思います。（特にクラウド上では）
パートの処理時間は記載の時間程度かかるった場合のイメージで書いています。処理時間の延び具合も実際の処理だったり環境でも異なるのであくまで、そんなイメージにしてあります。
普通のぐるぐる系のイメージ（オンプレミスで隣接しているような環境だネットワークレイテンシーは短いのは言うまでもないですが）
普通のぐるぐる系がネットワークレイテンシーの遅い環境に乗せられてしまった場合のイメージ（オンプレミス環境で熟成されたぐるぐる系がクラウドに乗ったらこうなったなんて話は、稀によく聞きますw。しかも自分たちではどうしようもないですよね。こればかりは。）
普通のグルグル系のがネットワークレイテンシーの遅い環境に乗せられてしまった上に、実行計画がぶれて大変なことになってしまったイメージ
最後に、がツン系にして回避したイメージ
この辺り大切なので、試験にでるよ！（ないないw
利用したリリースは以下の通り。
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
差分マージを例ネタに、ぐるぐる系とガツン系の例、そして、ぐるぐる系のループ内で実行されるSQL文の実行計画が最悪の状態でぐるぐるしている例を載せておきますね。
準備
以下ディレクトリにcsvファイルを作成してあります。連番で100000行のデータが入っています。
[oracle@localhost ~]$ ll /home/oracle/rowbyrow_or_batch/*.csv
-rw-r--r--. 1 oracle oinstall 588894 1月 16 14:39 /home/oracle/rowbyrow_or_batch/data.csv
[oracle@localhost ~]$ cat /home/oracle/rowbyrow_or_batch/data.csv
1
2
3
4
...中略...
99996
99997
99998
99999
100000
[oracle@localhost ~]$
差分データを外部表で提供するためにディレクトリオブジェクト、対応するディレクトリを作成します。
SCOTT@orclpdb1> create directory rowbyrow_or_batch as '/home/oracle/rowbyrow_or_batch';
ディレクトリが作成されました。
経過: 00:00:00.24
SCOTT@orclpdb1> select directory_name, directory_path from dba_directories where directory_name = upper('rowbyrow_or_batch');
DIRECTORY_NAME DIRECTORY_PATH
------------------------------ -------------------------------------------------
ROWBYROW_OR_BATCH /home/oracle/rowbyrow_or_batch
経過: 00:00:00.09
SCOTT@orclpdb1>
次に外部表を定義します。この時点では該当ディレクトリにcsvファイルは実在しなくても問題ありませんが、実行前には対応するファイルシステムのディレクトとcsvファイルを作成、配置しておく必要はあります。
（外部表作成時にそれらの存在は確認されていないだけです）
SCOTT@orclpdb1> DROP TABLE csv_data PURGE;
SCOTT@orclpdb1> CREATE TABLE csv_data
(
id NUMBER NOT NULL
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY rowbyrow_or_batch
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
(
id
)
)
LOCATION (
'data.csv'
)
);
表が作成されました。
経過: 00:00:00.03
SCOTT@orclpdb1> desc csv_data
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
SCOTT@orclpdb1> select min(id),max(id),count(1) from csv_data;
MIN(ID) MAX(ID) COUNT(1)
---------- ---------- ----------
1 100000 100000
SCOTT@orclpdb1> select count(distinct id) from csv_data;
COUNT(DISTINCTID)
-----------------
100000
差分データをマージする表を作成してデータを登録しておきます。（統計情報取得も忘れずに）
SCOTT@orclpdb1> CREATE TABLE tmp
(
ID NUMBER NOT NULL
, CONSTRAINT pk_tmp PRIMARY KEY (id) USING INDEX
);
表が作成されました。
経過: 00:00:00.17
SCOTT@orclpdb1> BEGIN
FOR i in 50001..200000 LOOP
INSERT INTO tmp VALUES(i);
IF MOD(i,100) = 0 THEN COMMIT; END IF;
END LOOP;
END;
/
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:08.57
SCOTT@orclpdb1> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT',tabname=>'TMP',cascade=>true, no_invalidate=>false);
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:01.86
SCOTT@orclpdb1> select min(id),max(id),count(1) from tmp;
MIN(ID) MAX(ID) COUNT(1)
---------- ---------- ----------
50001 200000 150000
経過: 00:00:00.03
はい！ 準備完了。
上記表へ、csvファイルというか外部表との差分を追加する処理をぐるぐる系と、ガツン系。
そして、おまけで、荒れ狂う実行計画のうるぐるぐる系（ヒントを使って最悪の実行計画にしてるだけですが）w 実行してみましょう。
特に落ちはないですが、打ちっぱなしというか、投げぱなしにしますので、
みなさん、遠ーーーくを見ながら、あーだーこーだビール片手のおつまみにでもして考えてみるのも良いか、と。。
まず、ぐるぐる系から。
SCOTT@orclpdb1> select min(id),max(id),count(id) from tmp;
MIN(ID) MAX(ID) COUNT(ID)
---------- ---------- ----------
50001 200000 150000
経過: 00:00:00.01
SCOTT@orclpdb1> exec rowbyrow_proc;
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:04.69
SCOTT@orclpdb1> select min(id),max(id),count(id) from tmp;
MIN(ID) MAX(ID) COUNT(ID)
---------- ---------- ----------
1 200000 200000
経過: 00:00:00.01
次は、ガツン系で。
SCOTT@orclpdb1> select min(id),max(id),count(id) from tmp;
MIN(ID) MAX(ID) COUNT(ID)
---------- ---------- ----------
50001 200000 150000
経過: 00:00:00.01
SCOTT@orclpdb1> exec allrows_batch_proc;
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:00.76
SCOTT@orclpdb1> select min(id),max(id),count(id) from tmp;
MIN(ID) MAX(ID) COUNT(ID)
---------- ---------- ----------
1 200000 200000
最後に、おまけ。 荒れ狂うぐるぐる系w。（繰り返し実行されるSQL文の実行計画が最悪という設定)
こうなっちゃた経験のある方も少ないないはず。荒れ狂わないように最良の実行計画に固定しちゃう予防策を取っておく方が安全ではあります。
SCOTT@orclpdb1> select min(id),max(id),count(id) from tmp;
MIN(ID) MAX(ID) COUNT(ID)
---------- ---------- ----------
50001 200000 150000
経過: 00:00:00.18
SCOTT@orclpdb1> exec badboy_rowbyrow_proc;
PL/SQLプロシージャが正常に完了しました。
経過: 00:28:02.30
SCOTT@orclpdb1> select min(id),max(id),count(id) from tmp;
MIN(ID) MAX(ID) COUNT(ID)
---------- ---------- ----------
1 200000 200000
経過: 00:00:00.31
それぞれの実行計画も載せておきますね。
ぐるぐる系のrowbyrow_procプロシージャで実行したSQL文
(実際に3つの方法で確認してますが、monitor/gather_plan_statisticsヒントつけちゃってるのに、autotraceの結果だけのせているのでヒントが気になるかたは外しても問題ないです。多少処理時間は速くなるはずですが。)
MERGE
/*+
monitor
gather_plan_statistics
index(tmp pk_tmp)
*/
INTO tmp
USING (
SELECT
:l_id AS id
FROM
dual
) diff_data
ON
( tmp.id = diff_data.id )
WHEN NOT MATCHED THEN
INSERT VALUES
(
diff_data.id
)
;
SCOTT@orclpdb1> l
1 MERGE
2 /*+
3 monitor
4 gather_plan_statistics
5 index(tmp pk_tmp)
6 */
7 INTO tmp
8 USING (
9 SELECT
10 :l_id AS id
11 FROM
12 dual
13 ) diff_data
14 ON
15 ( tmp.id = diff_data.id )
16 WHEN NOT MATCHED THEN
17 INSERT VALUES
18 (
19 diff_data.id
20* )
SCOTT@orclpdb1> /
1行がマージされました。
経過: 00:00:00.02
実行計画
----------------------------------------------------------
Plan hash value: 1857829212
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | 23 | 3 (0)| 00:00:01 |
| 1 | MERGE | TMP | | | | |
| 2 | VIEW | | | | | |
| 3 | NESTED LOOPS OUTER| | 1 | 23 | 3 (0)| 00:00:01 |
| 4 | VIEW | | 1 | 18 | 3 (0)| 00:00:01 |
| 5 | FAST DUAL | | 1 | | 3 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN| PK_TMP | 1 | 5 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("TMP"."ID"(+)=TO_NUMBER("DIFF_DATA"."ID"))
統計
----------------------------------------------------------
1 recursive calls
28 db block gets
3 consistent gets
3 physical reads
2432 redo size
204 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
ガツン系（allrows_batch_procプロシージャで実行）
MERGE
/*+
monitor
gather_plan_statistics
index_ffs(tmp pk_tmp)
*/
INTO tmp
USING (
SELECT
id
FROM
csv_data
) diff_data
ON ( tmp.id = diff_data.id )
WHEN NOT MATCHED THEN
INSERT VALUES
(
diff_data.id
)
;
SCOTT@orclpdb1>
1 MERGE
2 /*+
3 monitor
4 gather_plan_statistics
5 index_ffs(tmp pk_tmp)
6 */
7 INTO tmp
8 USING (
9 SELECT
10 id
11 FROM
12 csv_data
13 ) diff_data
14 ON ( tmp.id = diff_data.id )
15 WHEN NOT MATCHED THEN
16 INSERT VALUES
17 (
18 diff_data.id
19* )
SCOTT@orclpdb1> /
50000行がマージされました。
経過: 00:00:00.60
実行計画
----------------------------------------------------------
Plan hash value: 1601203627
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 100K| 1757K| 289 (15)| 00:00:01 |
| 1 | MERGE | TMP | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN OUTER | | 100K| 976K| 289 (15)| 00:00:01 |
|* 4 | EXTERNAL TABLE ACCESS FULL| CSV_DATA | 100K| 488K| 57 (20)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN | PK_TMP | 150K| 732K| 214 (6)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TMP"."ID"(+)="ID")
4 - filter(SYS_OP_XTNN("CSV_DATA"."ID"))
統計
----------------------------------------------------------
44 recursive calls
101323 db block gets
593 consistent gets
0 physical reads
12691800 redo size
204 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50000 rows processed
荒れ狂うぐるぐる系badboy_rowbyrow_procプロシージャで実行したSQL文
計測回数が少ないので参考程度ですが、荒れ狂ってない場合の同一SQLと比較して、4倍も遅い状況。（一回当たりは 0.08秒でも積み上がると笑えないですね）
MERGE
/*+
monitor
gather_plan_statistics
index_ffs(tmp pk_tmp)
*/
INTO tmp
USING (
SELECT
:l_id AS id
FROM
dual
) diff_data
ON
( tmp.id = diff_data.id )
WHEN NOT MATCHED THEN
INSERT VALUES
(
diff_data.id
)
;
SCOTT@orclpdb1> l
1 MERGE
2 /*+
3 monitor
4 gather_plan_statistics
5 index_ffs(tmp pk_tmp)
6 */
7 INTO tmp
8 USING (
9 SELECT
10 :l_id AS id
11 FROM
12 dual
13 ) diff_data
14 ON
15 ( tmp.id = diff_data.id )
16 WHEN NOT MATCHED THEN
17 INSERT VALUES
18 (
19 diff_data.id
20* )
SCOTT@orclpdb1> /
1行がマージされました。
経過: 00:00:00.08
実行計画
----------------------------------------------------------
Plan hash value: 1739044970
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | 23 | 226 (10)| 00:00:01 |
| 1 | MERGE | TMP | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN OUTER | | 1 | 23 | 226 (10)| 00:00:01 |
| 4 | VIEW | | 1 | 18 | 3 (0)| 00:00:01 |
| 5 | FAST DUAL | | 1 | | 3 (0)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN| PK_TMP | 150K| 732K| 214 (6)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TMP"."ID"(+)=TO_NUMBER("DIFF_DATA"."ID"))
統計
----------------------------------------------------------
1 recursive calls
8 db block gets
503 consistent gets
494 physical reads
780 redo size
204 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
本日利用したコードは以下の通り（csvファイルを作成したコードは略）
create directory rowbyrow_or_batch as '/home/oracle/rowbyrow_or_batch';
select directory_name, directory_path from dba_directories where directory_name = upper('rowbyrow_or_batch');
DROP TABLE csv_data PURGE;
CREATE TABLE csv_data
(
id NUMBER NOT NULL
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY rowbyrow_or_batch
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
(
id
)
)
LOCATION (
'data.csv'
)
);
DROP TABLE tmp PURGE;
CREATE TABLE tmp
(
ID NUMBER NOT NULL
, CONSTRAINT pk_tmp PRIMARY KEY (id) USING INDEX
);
BEGIN
FOR i in 50001..200000 LOOP
INSERT INTO tmp VALUES(i);
IF MOD(i,100) = 0 THEN COMMIT; END IF;
END LOOP;
END;
/
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT',tabname=>'TMP',cascade=>true, no_invalidate=>false);
CREATE OR REPLACE PROCEDURE allrows_batch_proc AS
BEGIN
MERGE
/*+
monitor
gather_plan_statistics
index_ffs(tmp pk_tmp)
*/
INTO tmp
USING (
SELECT
id
FROM
csv_data
) diff_data
ON ( tmp.id = diff_data.id )
WHEN NOT MATCHED THEN
INSERT VALUES
(
diff_data.id
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/
CREATE OR REPLACE PROCEDURE rowbyrow_proc AS
C_DIR CONSTANT VARCHAR2(30) := 'ROWBYROW_OR_BATCH';
C_FILE_NAME CONSTANT VARCHAR2(30) := 'data.csv';
C_BUFFERSIZE CONSTANT BINARY_INTEGER := 32767;
C_OPENMODE CONSTANT VARCHAR2(2) := 'r';
fileHandle UTL_FILE.FILE_TYPE;
line VARCHAR2(32767);
l_id NUMBER;
rowcount NUMBER;
BEGIN
fileHandle := UTL_FILE.FOPEN(C_DIR, C_FILE_NAME, C_OPENMODE, C_BUFFERSIZE);
BEGIN
LOOP
UTL_FILE.GET_LINE(fileHandle, line, C_BUFFERSIZE);
l_id := TO_NUMBER(line);
MERGE
/*+
monitor
gather_plan_statistics
index(tmp pk_tmp)
*/
INTO tmp
USING (
SELECT
l_id AS id
FROM
dual
) diff_data
ON
( tmp.id = diff_data.id )
WHEN NOT MATCHED THEN
INSERT VALUES
(
diff_data.id
);
rowcount := rowcount + 1;
IF MOD(rowcount, 100) = 0 THEN
COMMIT;
NULL;
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN /* EOF */
NULL;
END;
COMMIT;
UTL_FILE.FCLOSE(fileHandle);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
IF UTL_FILE.IS_OPEN(fileHandle) THEN
UTL_FILE.FCLOSE(fileHandle);
END IF;
RAISE;
END;
/
CREATE OR REPLACE PROCEDURE badboy_rowbyrow_proc AS
C_DIR CONSTANT VARCHAR2(30) := 'ROWBYROW_OR_BATCH';
C_FILE_NAME CONSTANT VARCHAR2(30) := 'data.csv';
C_BUFFERSIZE CONSTANT BINARY_INTEGER := 32767;
C_OPENMODE CONSTANT VARCHAR2(2) := 'r';
fileHandle UTL_FILE.FILE_TYPE;
line VARCHAR2(32767);
l_id NUMBER;
rowcount NUMBER;
BEGIN
fileHandle := UTL_FILE.FOPEN(C_DIR, C_FILE_NAME, C_OPENMODE, C_BUFFERSIZE);
BEGIN
LOOP
UTL_FILE.GET_LINE(fileHandle, line, C_BUFFERSIZE);
l_id := TO_NUMBER(line);
MERGE
/*+
monitor
gather_plan_statistics
index_ffs(tmp pk_tmp)
*/
INTO tmp
USING (
SELECT
l_id AS id
FROM
dual
) diff_data
ON
( tmp.id = diff_data.id )
WHEN NOT MATCHED THEN
INSERT VALUES
(
diff_data.id
);
rowcount := rowcount + 1;
IF MOD(rowcount, 100) = 0 THEN
COMMIT;
NULL;
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN /* EOF */
NULL;
END;
COMMIT;
UTL_FILE.FCLOSE(fileHandle);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
IF UTL_FILE.IS_OPEN(fileHandle) THEN
UTL_FILE.FCLOSE(fileHandle);
END IF;
RAISE;
END;
/
Enjoy SQL!
今年は、桜吹雪を2回も見ることができたのですが、おそらく、20年ぶりw
では、また。
最近のコメント