帰ってきた！ 標準はあるにはあるが癖の多いSQL #21 - Table Value Constructer（TVC）- ハードパース時間とメモリ消費量 / BONUS TRACK Tweet
https://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2025/12/post-d04e78.htmlのおまけです！
前回は、マニュアルでは言及されたりしていますが、TVCで多くの行を生成するのは複数の問題を引き起こしそう。。。
Oracle Databaseでは生成行数上限があるのですが、なにか異様に時間がかかってました。どのあたりだろう？。とか
MySQL/PostgreSQLは行数こそ制限されてないようですが、メモリ消費には影響しそうだよなぁ。
というあたり気になりますよね。今日はその辺りをざっくりと確認しておこうという。
マニュアルでも言及されてるし、TVCで大量行生成しねーーーだろーーーっ。
とも思うわけですが、世の中広いので、油断禁物ww
それやっちゃうと、実際にどうなりそうかって、肌感覚で知ってたほうが良いだろうという意図もあり。
まずは、
前回利用したSQLをCOUNT(1)に書き換えたものを利用します
Oracle Databaseの例(MySQLではRVCを利用する点以外違いはありません）
e.g.
sql_oracle_65534.sql
SELECT COUNT(1) FROM ( VALUES
(1)
,(2)
,(3)
,(4)
...略...
,(65530)
,(65531)
,(65532)
,(65533)
,(65534)
) t1 ( id )
/
環境はいつものとおり、arm64向け Oracle Database/MySQL/PostgreSQL環境をVirtualBox for macOS / Apple Siliconにて
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
[master@arm64-oraclelinux8u10 ~]$ cat /etc/oracle-release
Oracle Linux Server release 8.10
[master@arm64-oraclelinux8u10 ~]$ uname -r
5.15.0-313.189.5.3.el8uek.aarch64
以降, 変化確認のために実行時間も記録しておきます
PostgreSQL 17.6だと、29ms程度。
[postgres@Oracle-Linux-8u10-arm64-2 ~]$ psql -U scott -d perftestdb -h localhost
Password for user scott:
psql (17.6)
Type "help" for help.
perftestdb=> \timing
Timing is on.
perftestdb=> select version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 17.6 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-26), 64-bit
(1 row)
Time: 1.848 ms
perftestdb=>
perftestdb=> \i sql_postgresql_65534.sql
count
-------
65534
(1 row)
Time: 28.617 ms
MySQL 8.4.7 だと 60ms程度のようですね。
[master@Oracle-Linux-8u10-arm64-2 ~]$ mysql -u root -D perftestdb -p -h localhost
Enter password:
...略...
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.4.7 |
+-----------+
1 row in set (0.00 sec)
mysql>
mysql> \. sql_mysql_65534.sql
+----------+
| COUNT(1) |
+----------+
| 65534 |
+----------+
1 row in set (0.06 sec)
さて、今日の真打w Oracle Database、
前回のエントリで気づいたかもしれませんが、Oracle DatabaseのTVCどうやらハードパースにものすごく時間がかかっている雰囲気。
かといってソフトパースでも17秒ぐらいなので決して速くはないのですが、ハードパース時間がすごいですね。
[oracle@arm64-oraclelinux8u10 ~]$ sqlplus scott@localhost:1521/freepdb1
...略...
SCOTT@localhost:1521/freepdb1> select banner_full from v$version;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
-- ハードパースだと..
SCOTT@localhost:1521/freepdb1> @sql_oracle_65534.sql
COUNT(1)
----------
65534
経過: 00:50:43.88
-- ソフトパースだと...
SCOTT@localhost:1521/freepdb1> @sql_oracle_65534.sql
COUNT(1)
----------
65534
経過: 00:00:17.99
ということで、Oracle Database。ハードハース時間が長いのですが、もう少し掘り下げて覗いてみようと思います。
10046トレース（久々w）でログをとって追ってみます。
SCOTT@localhost:1521/freepdb1> alter session set tracefile_identifier='10046_tvc';
セッションが変更されました。
SCOTT@localhost:1521/freepdb1> alter session set statistics_level=all;
セッションが変更されました。
SCOTT@localhost:1521/freepdb1> alter session set max_dump_file_size = unlimited;
セッションが変更されました。
SCOTT@localhost:1521/freepdb1> alter system flush shared_pool;
システムが変更されました。
SCOTT@localhost:1521/freepdb1> alter session set events '10046 trace name context forever,level 12';
セッションが変更されました。
SCOTT@localhost:1521/freepdb1> @sql_oracle_65534
COUNT(1)
----------
65534
経過: 00:43:01.31
SCOTT@localhost:1521/freepdb1> alter session set events '10046 trace name context off';
セッションが変更されました。
[oracle@arm64-oraclelinux8u10 trace]$ ls -l *10046_tvc*
-rw-r-----. 1 oracle oinstall 1289063 Dec 25 20:36 FREE_ora_5169_10046_tvc.trc
-rw-r-----. 1 oracle oinstall 18615 Dec 25 20:36 FREE_ora_5169_10046_tvc.trm
[oracle@arm64-oraclelinux8u10 trace]$ tkprof FREE_ora_5169_10046_tvc.trc FREE_ora_5169_10046_tvc.trc.txt explain=scott/tiger@localhost:1521/freepdb1 sys=yes waits=yes aggregate=no
...略...
該当箇所を見ると、やはり！
ハードパース時間がほとんどですね。 時間の単位は秒なので、43分ほどであることと、ほぼCPU時間に等しいことも見えますね。
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 2524.81 2551.05 0 60 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 2 0.03 0.03 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2524.86 2551.10 0 60 0 1
実行計画は以前みたものと同じで、VALUES SCANになっています。
待機イベントをみると、前回topコマンドで気になっていたメモリー関連の待機イベントでの待機回数が非常に多くなっています。しかも、PGA内のCGA/UGA使っているようにみえますね。CGAなんて久々に見ました。（2007年のネタを思い出しますw - Mac De Oracle なんですが、Windows(32bit)でのOracleな話 #3）TVCで行を生成すると、PGA、CGAが拡大しその影響でUGAも増加、専用サーバーなのでその流れでPGAも拡大している様子が想像できますよね。23ai FREEだとメモリ制限もきついので、もう少しメモリを消費させれば、PGAのLIMITや23ai FREEのメモリ制限などに抵触する可能性はありますよね。。。それは後ほど試します！
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 134 (SCOTT)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=0 pr=0 pw=0 time=36096 us starts=1 direct read=0 direct write=0)
65534 65534 65534 VIEW (cr=0 pr=0 pw=0 time=34577 us starts=1 direct read=0 direct write=0 cost=131076 size=0 card=65534)
65534 65534 65534 VALUES SCAN (cr=0 pr=0 pw=0 time=30701 us starts=1 direct read=0 direct write=0 cost=131076 size=0 card=65534)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Allocate CGA memory from OS 488 0.00 0.00
Allocate PGA memory from OS 6 0.00 0.00
Free private memory to OS 22 0.02 0.03
Allocate UGA memory from OS 216 0.00 0.00
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 9.11 9.11
********************************************************************************
比較のためにソフトパースの場合は以下のとおり。
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.03 0.03 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.03 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 134 (SCOTT)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=0 pr=0 pw=0 time=36156 us starts=1 direct read=0 direct write=0)
65534 65534 65534 VIEW (cr=0 pr=0 pw=0 time=34656 us starts=1 direct read=0 direct write=0 cost=131076 size=0 card=65534)
65534 65534 65534 VALUES SCAN (cr=0 pr=0 pw=0 time=30708 us starts=1 direct read=0 direct write=0 cost=131076 size=0 card=65534)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Allocate UGA memory from OS 38 0.00 0.00
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 26.66 26.66
********************************************************************************
おまけ。PGA/UGA/CGAに関する懐かしいエントリ。（知ってる方いるかなw）
ソートに関する検証 その２ / InsightTechnology 旧ブログ
PGA/UGAサイズの変化 / 23ai FREEのメモリー制限は2GBである前提は頭の片隅に置いておく必要はあるが、
いまのところ制限内に収まっているようなのでこのながれのまま、サイズの変化を見ておきましょう。
ハードパースさせつつ試しています。
SCOTT@localhost:1521/freepdb1> alter system flush shared_pool;
システムが変更されました。
SCOTT@localhost:1521/freepdb1> @show_mystats.sql
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
40 CPU used by this session 1
40 logical read bytes from cache 4243456
40 no work - consistent read gets 281
40 physical read IO requests 9
40 physical read bytes 106496
40 physical read total IO requests 9
40 physical read total bytes 106496
40 physical reads 13
40 physical reads cache 13
40 redo synch writes 1
40 redo write info find 1
40 session logical reads 518
40 session pga memory 2764448
40 session pga memory max 2829984
40 session uga memory 791336
40 session uga memory max 791384
40 sorts (memory) 41
17行が選択されました。
経過: 00:00:00.02
SCOTT@localhost:1521/freepdb1> @sql_oracle_65534
COUNT(1)
----------
65534
経過: 00:49:53.83
SCOTT@localhost:1521/freepdb1> @show_mystats.sql
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
40 CPU used by this session 273602
40 CPU used when call started 273602
40 logical read bytes from cache 4243456
40 no work - consistent read gets 281
40 physical read IO requests 9
40 physical read bytes 106496
40 physical read total IO requests 9
40 physical read total bytes 106496
40 physical reads 13
40 physical reads cache 13
40 redo synch writes 1
40 redo write info find 1
40 session logical reads 518
40 session pga memory 12053496
40 session pga memory max 1600383992
40 session uga memory 10680464
40 session uga memory max 40035216
40 sorts (memory) 42
18行が選択されました。
統計値差を確認！
PGAが1.5Gほど！！！！！！！に拡張！この辺りは 10046とレースの待機イベントにも現れていたCGAが占めていそうですね。UGAよりも。。。
(統計情報の詳細は、Database Reference E.2 Statistics Descriptions参照のこと)
単純な数値型1列で、65534行をTVCで生成しましたが、こんなにPGAを消費しちゃうんんですね。驚き！
PGAも無制限に利用できるわけではないので、TVCで大量に行データを生成するとPGAの制限にあたってエラーになるだろうなぁ。というのは容易に想像できます。
SCOTT@localhost:1521/freepdb1> @list_diff 2
STAT_NAME STAT_VALUE UNIT
---------------------------------------- ---------- -----
CPU used by this session 2736.01 sec
CPU used when call started 2736.02 sec
physical read total IO requests 9 times
session pga memory 8.86 MB
session pga memory max 1523.55 MB
session uga memory 9.43 MB
session uga memory max 37.43 MB
sorts (memory) 1 times
ついでなので、
数値型1行で65534行から半減させつつ16行まで、どの程度のPGAが消費されるか計測してグラフにしてみました。
TVCによる大量の行生成はやめた方が良いですよね。まじで。
PGAサイズの増加に合わせハードパース時間もとんでもないことになりますし。。。ご利用は計画的に！ という感じです。
1列の行数だけ仕様上限だと発生まだ余裕は多少あるので、列数を多くし、1行で1ブロック(8K)程度になるようなイメージで作ってみました。PGA_AGGREGATE_LIMITでエラーになるか、
もしくは、23ai FREEのメモリ制限に先に当たってエラーになるか。。。どちらかでエラーになるはず！！！
ということで、8列かつ1行/ブロックになるような行サイズで、65534行をTVCで生成してCOUNT()するSQLを生成。(@make_tvc_sql.sql は後半に載せてあります)
SCOTT@localhost:1521/freepdb1> @make_tvc_sql.sql 65534 oracle
SELECT count(1) FROM ( VALUES
(1,
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1',
...略...
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1')
...略...
) t1 ( id, col1, col2, col3, col4, col5, col6, col7, col8 );
では実行！！！、コケると思いますよw 絶対！！！！！
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
に接続されました。
SCOTT@localhost:1521/freepdb1> @show_mystats.sql
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
203 CPU used by this session 1
203 CPU used when call started 1
203 logical read bytes from cache 3334144
203 no work - consistent read gets 215
203 physical read IO requests 16
203 physical read bytes 131072
203 physical read total IO requests 16
203 physical read total bytes 131072
203 physical reads 16
203 physical reads cache 16
203 redo synch writes 1
203 redo write info find 1
203 session logical reads 407
203 session pga memory 2698912
203 session pga memory max 2698912
203 session uga memory 791312
203 session uga memory max 791312
203 sorts (memory) 37
18行が選択されました。
経過: 00:00:00.02
SCOTT@localhost:1521/freepdb1> @sql_oracle_65534
SELECT count(1) FROM ( VALUES
*
行1でエラーが発生しました。:
ORA-00028: セッションは終了しました ヘルプ:
https://docs.oracle.com/error-help/db/ora-00028/
経過: 00:05:51.08
ね！ 狙い通りにエラー発生！！！！！w
ORA-00028エラーは、副産物なので根本原因をログから確認してみましょう！
以下トレースファイルより。
23ai FREEなのでそもそも利用可能なメモリサイズ上限はあるのですが、この例では、PGA_AGGREGATE_LIMITに抵触してエラーとなったようですね。まあ、想像通りの結果なのですがw
$ORACLE_BASE/diag/rdbms/free/FREE/incident/incdir_134953/FREE_ora_4958_i134953.trc
...略...
...略...
=======================================
PRIVATE MEMORY SUMMARY FOR THIS PROCESS
---------------------------------------
******************************************************
PRIVATE HEAP SUMMARY DUMP
1842 MB total:
1795 MB commented, 523 KB permanent
47 MB free (0 KB in empty extents),
877 MB, 2 heaps: "callheap " 14 MB free held
480 MB, 1 heap: "Alloc environm " 16 MB free held
480 MB, 2 chunks: "kgh stack " 16 MB free held
...略...
Summary of subheaps at depth 2
1326 MB total:
40 MB commented, 1286 MB permanent
408 KB free (0 KB in empty extents),
signalling ORA-4036 interrupt
...略...
Incident 134953 created, dump file: /opt/oracle/diag/rdbms/free/FREE/incident/incdir_134953/FREE_ora_4958_i134953.trc
ORA-04036: インスタンスまたはPDBにより使用されるPGAメモリーがPGA_AGGREGATE_LIMITを超えています。
TVCで生成した行数のみだけではなく、列数や列サイズもPGA消費に影響するすることを意味しています！！！
とにかく、マニュアルに記載されているように、大量の行を生成するのは避けるのが吉という癖の強い機能なので、ご利用は計画的にw
ついでなので、行数上限の制約は無いPostgreSQLとMySQLのメモリ消費量をざっくりみておきました。
これらもメモリ消費は大きくなるので、TVCによる大量の行生成はさけたほうがよいでしょうね。（ほかの方法はあるわけですし）
PostgreSQLでOracle Databaseで実行したSQLと同じ文を実行してみると。。。
[postgres@Oracle-Linux-8u10-arm64-2 ~]$ psql -U scott -d perftestdb -h localhost
Password for user scott:
psql (17.6)
Type "help" for help.
perftestdb=> \i sql_postgresql_65534.sql
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=983.01..983.02 rows=1 width=8) (actual time=34.131..34.132 rows=1 loops=1)
Output: count(1)
-> Values Scan on "*VALUES*" (cost=0.00..819.18 rows=65534 width=0) (actual time=0.003..31.264 rows=65534 loops=1)
Output: "*VALUES*".column1, "*VALUES*".column2, "*VALUES*".column3, "*VALUES*".column4, "*VALUES*".column5,
"*VALUES*".column6, "*VALUES*".column7, "*VALUES*".column8, "*VALUES*".column9
Planning:
Buffers: shared hit=3
Memory: used=602113kB allocated=607745kB
Planning Time: 2697.696 ms
Execution Time: 34.330 ms
(9 rows)
以下のパラメータを設定、再起動してログ出力にてざっくりとと、max resident sizeを見てみた。
EXECUTOR STATISTICSは、2.9GBぐらいまで増加してますね。
log_statement_stats = off
log_parser_stats = on
log_planner_stats = on
log_executor_stats = on
にして再起動！
2025-12-25 20:10:25.291 JST [8356] LOG: PARSER STATISTICS
2025-12-25 20:10:25.291 JST [8356] DETAIL: ! system usage stats:
! 1.042480 s user, 0.067788 s system, 1.112459 s elapsed
! [1.105611 s user, 0.125358 s system total]
! 1505652 kB max resident size
! 2416/0 [2416/376] filesystem blocks in/out
! 2/35500 [176/36935] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 1/1 [10/1] voluntary/involuntary context switches
2025-12-25 20:10:25.291 JST [8356] STATEMENT: SELECT count(1) FROM ( VALUES
...略...
) t1 ( id, col1, col2, col3, col4, col5, col6, col7, col8 );
2025-12-25 20:10:27.072 JST [8356] LOG: PARSE ANALYSIS STATISTICS
2025-12-25 20:10:27.072 JST [8356] DETAIL: ! system usage stats:
! 0.271937 s user, 0.031995 s system, 0.309900 s elapsed
! [2.689273 s user, 0.260475 s system total]
! 1621520 kB max resident size
! 608/0 [3024/376] filesystem blocks in/out
! 44/38352 [220/79769] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 19/0 [936/3] voluntary/involuntary context switches
2025-12-25 20:10:27.072 JST [8356] STATEMENT: SELECT count(1) FROM ( VALUES
...略...
) t1 ( id, col1, col2, col3, col4, col5, col6, col7, col8 );
2025-12-25 20:10:28.571 JST [8356] LOG: REWRITER STATISTICS
2025-12-25 20:10:28.571 JST [8356] DETAIL: ! system usage stats:
! 0.000005 s user, 0.000001 s system, 0.000003 s elapsed
! [4.003534 s user, 0.396938 s system total]
! 2098660 kB max resident size
! 0/0 [3024/376] filesystem blocks in/out
! 0/0 [220/84364] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [1781/6] voluntary/involuntary context switches
2025-12-25 20:10:28.571 JST [8356] STATEMENT: SELECT count(1) FROM ( VALUES
...略...
) t1 ( id, col1, col2, col3, col4, col5, col6, col7, col8 );
2025-12-25 20:10:32.469 JST [8356] LOG: PLANNER STATISTICS
2025-12-25 20:10:32.469 JST [8356] DETAIL: ! system usage stats:
! 2.298387 s user, 0.046537 s system, 2.349685 s elapsed
! [7.677502 s user, 0.547417 s system total]
! 2220484 kB max resident size
! 80/0 [3104/376] filesystem blocks in/out
! 4/38971 [224/127833] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 2/4 [3274/11] voluntary/involuntary context switches
2025-12-25 20:10:32.469 JST [8356] STATEMENT: SELECT count(1) FROM ( VALUES
...略...
) t1 ( id, col1, col2, col3, col4, col5, col6, col7, col8 );
2025-12-25 20:10:34.034 JST [8356] LOG: EXECUTOR STATISTICS
2025-12-25 20:10:34.034 JST [8356] DETAIL: ! system usage stats:
! 0.040565 s user, 0.000000 s system, 0.040742 s elapsed
! [9.047784 s user, 0.654338 s system total]
! 2699340 kB max resident size
! 0/0 [3136/376] filesystem blocks in/out
! 0/9 [228/132686] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [5130/13] voluntary/involuntary context switches
2025-12-25 20:10:34.034 JST [8356] STATEMENT: SELECT count(1) FROM ( VALUES
...略...
) t1 ( id, col1, col2, col3, col4, col5, col6, col7, col8 );
最後は、MySQL
ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytesってエラーになったので
max_allowed_packetパラメータを64MBから768MBへ大きく設定しなおして無理やりエラーを回避して確認！
MySQLでもかなりメモリ消費しちゃってますね。。
[master@Oracle-Linux-8u10-arm64-2 ~]$ mysql -u root -D perftestdb -p -h localhost
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.4.7 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> \. sql_mysql_65534.sql
ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes
No connection. Trying to reconnect...
Connection id: 9
Current database: perftestdb
...略...
Current database: perftestdb
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+
1 row in set (0.01 sec)
...略...
[master@Oracle-Linux-8u10-arm64-2 ~]$ sudo vi /etc/my.cnf
[master@Oracle-Linux-8u10-arm64-2 ~]$ sudo service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[master@Oracle-Linux-8u10-arm64-2 ~]$ mysql -u scott -D perftestdb -p -h localhost
Enter password:
...略...
-- 768MBに
mysql> show variables like 'max_allowed_packet';
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| max_allowed_packet | 805306368 |
+--------------------+-----------+
1 row in set (0.00 sec)
mysql>
3GBぐらいまで消費しちゃってますね。
mysql> SELECT * from performance_schema.users WHERE USER='scott';
+-------+---------------------+-------------------+-------------------------------+--------------------------+
| USER | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS | MAX_SESSION_CONTROLLED_MEMORY | MAX_SESSION_TOTAL_MEMORY |
+-------+---------------------+-------------------+-------------------------------+--------------------------+
| scott | 1 | 1 | 647288 | 1398335 |
+-------+---------------------+-------------------+-------------------------------+--------------------------+
1 row in set (0.00 sec)
mysql> \. sql_mysql_65534.sql
+----------+
| COUNT(*) |
+----------+
| 65534 |
+----------+
1 row in set (3.68 sec)
mysql> SELECT * from performance_schema.users WHERE USER='scott';
+-------+---------------------+-------------------+-------------------------------+--------------------------+
| USER | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS | MAX_SESSION_CONTROLLED_MEMORY | MAX_SESSION_TOTAL_MEMORY |
+-------+---------------------+-------------------+-------------------------------+--------------------------+
| scott | 1 | 1 | 2404782200 | 2893645114 |
+-------+---------------------+-------------------+-------------------------------+--------------------------+
1 row in set (0.00 sec)
mysql> SELECT MAX_TOTAL_MEMORY from performance_schema.events_statements_history WHERE SQL_TEXT LIKE 'SELECT COUNT(*)%';
Empty set (0.00 sec)
mysql> \. sql_mysql_65534.sql
+----------+
| COUNT(*) |
+----------+
| 65534 |
+----------+
1 row in set (3.93 sec)
mysql> SELECT MAX_TOTAL_MEMORY from performance_schema.events_statements_history WHERE SQL_TEXT LIKE 'SELECT COUNT(*)%';
+------------------+
| MAX_TOTAL_MEMORY |
+------------------+
| 2893680900 |
+------------------+
1 row in set (0.00 sec)
では、Advent Calendarも終わり、今年の残すところあとわずか。
みなさま、よいお年をお迎えください。
make_tvc_sql.sql
1行に複数列を持たせかつ、1行1ブロック程度になるような行サイズとなるTVCクエリを生成するスクリプト
set feed off
set timi off
set head off
set termout off
set veri off
set trimspool on
col col1 for a20
col col2 for a20
col col3 for a20
col col4 for a20
col col5 for a20
col col6 for a20
col col7 for a20
col col8 for a20
set linesize 400
set pagesize 1000
SET SERVEROUTPUT ON
spool sql_&2._&1..sql
DECLARE
c_max_rows CONSTANT NUMBER := &1;
c_rvc_text_mysql CONSTANT CHAR(3) := 'ROW';
c_type_mysql CONSTANT CHAR(5) := 'MYSQL';
c_type CONSTANT VARCHAR2(10) := UPPER('&2');
BEGIN
DBMS_OUTPUT.PUT_LINE('SELECT COUNT(*) FROM ( VALUES');
FOR i IN 1..c_max_rows LOOP
DBMS_OUTPUT.PUT_LINE(
CASE WHEN i > 1 THEN ',' END
|| CASE WHEN c_type = c_type_mysql THEN c_rvc_text_mysql END
|| '(' || TO_CHAR(i)
|| ', ''' || LPAD(TO_CHAR(i),1000,'x') || ''''
|| ', ''' || LPAD(TO_CHAR(i),1000,'x') || ''''
|| ', ''' || LPAD(TO_CHAR(i),1000,'x') || ''''
|| ', ''' || LPAD(TO_CHAR(i),1000,'x') || ''''
|| ', ''' || LPAD(TO_CHAR(i),1000,'x') || ''''
|| ', ''' || LPAD(TO_CHAR(i),1000,'x') || ''''
|| ', ''' || LPAD(TO_CHAR(i),1000,'x') || ''''
|| ', ''' || LPAD(TO_CHAR(i),373,'x') || ''')'
);
END LOOP;
DBMS_OUTPUT.PUT_LINE(') t1 ( id, col1, col2, col3, col4, col5, col6, col7, col8 );');
END;
/
spool off
SET SERVEROUTPUT OFF
UNDEFINE 1
UNDEFINE 2
set head on
set termout on
set feed on
set veri on
set timi on
set trimspool off
関連エントリー
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル！
・標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日？
・標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る＞＜
・標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠（有名なやつ）
・標準はあるにはあるが癖の多いSQL 全部俺 #11 デュエル、じゃなくて、デュアル
・標準はあるにはあるが癖の多いSQL 全部俺 #12 文字[列]探すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #13 あると便利ですが意外となかったり
・標準はあるにはあるが癖の多いSQL 全部俺 #14 連番の集合を返すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #15 SQL command line client
・標準はあるにはあるが癖の多いSQL 全部俺 #16 SQLのレントゲンを撮る方法
・標準はあるにはあるが癖の多いSQL 全部俺 #17 その空白は許されないのか？
・標準はあるにはあるが癖の多いSQL 全部俺 #18 (+)の外部結合は方言
・標準はあるにはあるが癖の多いSQL 全部俺 #19 帰ってきた、部分文字列の扱いでも癖w
・標準はあるにはあるが癖の多いSQL 全部俺 #20 結果セットを単一列に連結するにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #21 演算結果にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #22 集合演算にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #23 複数行INSERTにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #24 乱数作るにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #25 SQL de Fractalsにも癖がある:)
・標準はあるにはあるが癖の多いSQL 全部俺 おまけ SQL de 湯婆婆やるにも癖がでるw
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #1 SQL de ROT13 やるにも癖が出るw
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #4 Optimizer Traceの取得でも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #7 - Hash Joinの実行計画にも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #8 - Hash Joinさせるにも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #9、BOOLEAN型にも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る（後編）
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る（後編）の おまけ - SQL*PlusのautotraceでSQL Analysis Reportが出力される！ (23ai〜)
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #11 - 引用符にも癖がでるし、NULLのソート構文にも癖がある！（前編）
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #12 - 引用符にも癖がでるし、NULLのソート構文にも癖がある！（後編）ー 列エイリアスの扱いにも癖がある！
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #13 - コメント書くにも癖がある
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #14 - コメントを書く位置にも癖がでる (SQL Clientにも癖がある)
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #15 - 実行計画でスカラー副問合せの見せ方にも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #16 - FROM句のインラインビューのエイリアスにもクセがある（必須だったり、任意だったり）
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #17 - ANY_VALUE() ってなかなかいいじゃん、癖無さそう！
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #18 - t_alias と c_alias にも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #19 - c_alias の癖（おまけ）
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #20 - Table Value Constructer （TVC）
最近のコメント