« 2023年8月 | トップページ | 2023年10月 »

2023年9月28日 (木)

explain plan for文で、rebuild 後の索引サイズも見積れる! / FAQ

Jonathan Lewisが、索引rebuild後のサイズ見積もりで昔からある方法を紹介していた. そういえば、私が以前紹介していた方法で見積もれるよね!
ということで、簡単な例で紹介しておきますね。

この方法、通常は、create indexの時にしか使えないのですが、結局のところ、alter index ...rebuildも、create indexもindexを作成することには違いはないので、rebuilddでもcreateでも最終的な索引サイズには同じだよね。という単純な発想です。どうせ、ballpark figureなわけで、厳密性不要なわけですし、リーズナブルだと思います。

参考
explain plan文 De 索引サイズ見積 / FAQ


では、試してみましょう!


索引の無い表を作ります!

  1  CREATE TABLE foobar
2 (
3 key_code CHAR(10) NOT NULL
4* )

表が作成されました。

データを登録します。この例では 10万行登録してあります。(単純なぐるぐる方式ですが、大した量ではないので気にしないでくださいw)

 1  BEGIN
2 FOR i IN 1..100000 LOOP
3 INSERT INTO foobar VALUES(TO_CHAR(i,'FM0000000009'));
4 IF MOD(i,100) = 0
5 THEN
6 COMMIT;
7 END IF;
8 END LOOP;
9* END;

PL/SQLプロシージャが正常に完了しました。


統計情報を取得します。

  1  BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'FOOBAR', cascade=>true, no_invalidate=>false);
3* END;

PL/SQLプロシージャが正常に完了しました。


まずは、create index文で作成される索引サイズの見積もりを行います。(見積もりだけなので実際には作成されません!)
3145KBとの見積もりです

  1* EXPLAIN PLAN FOR CREATE INDEX ix_foobar ON foobar(key_code)

解析されました。

@?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 4144366834

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 100K| 1074K| 143 (1)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IX_FOOBAR | | | | |
| 2 | SORT CREATE INDEX | | 100K| 1074K| | |
| 3 | TABLE ACCESS FULL | FOOBAR | 100K| 1074K| 69 (2)| 00:00:01 |
------------------------------------------------------------------------------------

Note
-----
- estimated index size: 3145K bytes

14行が選択されました。


では、見積もりとの乖離を確認するために実際に索引を作成して、セグメントサイズを確認してみましょう。
3072KBとなりました。見積もりは、3145KBでした。大きな差はないですよね。これで十分でしょう。

  1* CREATE INDEX ix_foobar ON foobar(key_code)

索引が作成されました。


統計情報を取得して、セグメントサイズを確認します。

  1  BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'FOOBAR', cascade=>true, no_invalidate=>false);
3* END;

PL/SQLプロシージャが正常に完了しました。

1* select segment_name,segment_type,sum(bytes)/1024 AS "KB" from user_segments where segment_name = 'IX_FOOBAR' group by segment_name,segment_type

SEGMENT_NAME SEGMENT_TYPE KB
------------------------------ ------------------------------------------------------ ----------
IX_FOOBAR INDEX 3072

次に rebuild したサイズを見積もるため、50%程度のデータを削除します。


1* DELETE FROM foobar WHERE key_code BETWEEN '0000000000' AND '0000050000'

50000行が削除されました。

1* COMMIT

コミットが完了しました。


統計を取得します。

  1  BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'FOOBAR', cascade=>true, no_invalidate=>false);
3* END;

PL/SQLプロシージャが正常に完了しました。


50%削除しましたが、rebuild前なのでサイズはcreate index時と同じですよね。

  1* select segment_name,segment_type,sum(bytes)/1024 AS "KB" from user_segments where segment_name = 'IX_FOOBAR' group by segment_name,segment_type

SEGMENT_NAME SEGMENT_TYPE KB
------------------------------ ------------------------------------------------------ ----------
IX_FOOBAR INDEX 3072


この状態で、alter index .... rebuild文ではなく、create index文でサイズを見積もります。
2097KBになると見積もられました。

  1* EXPLAIN PLAN FOR CREATE INDEX ix_foobar ON foobar(key_code)

解析されました。

@?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 4144366834

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 50000 | 537K| 80 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IX_FOOBAR | | | | |
| 2 | SORT CREATE INDEX | | 50000 | 537K| | |
| 3 | TABLE ACCESS FULL | FOOBAR | 50000 | 537K| 43 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Note
-----
- estimated index size: 2097K bytes

14行が選択されました。


alter index ... rebuildは、expla plan forでは索引サイズを見積もれないことも、念の為に確認しておきましょう。
見積もられないですよね。間違いなく!

  1* EXPLAIN PLAN FOR ALTER INDEX ix_foobar REBUILD

解析されました。

@?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 4144366834

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 50000 | 537K| 43 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IX_FOOBAR | | | | |
| 2 | SORT CREATE INDEX | | 50000 | 537K| | |
| 3 | TABLE ACCESS FULL | FOOBAR | 50000 | 537K| 43 (0)| 00:00:01 |
------------------------------------------------------------------------------------

10行が選択されました。


最後に、実際に alter index ... rebuildしてみるとサイズはどうなるでしょうか! 確認してみましょう!
はい!  2048KBとreuildしてコンパクトになりました!。 見積もりサイズは、2097KB でした。この程度の誤差は問題にもならないでしょう。

  1* ALTER INDEX ix_foobar REBUILD

索引が変更されました。

1* select segment_name,segment_type,sum(bytes)/1024 AS "KB" from user_segments where segment_name = 'IX_FOOBAR' group by segment_name,segment_type

SEGMENT_NAME SEGMENT_TYPE KB
------------------------------ ------------------------------------------------------ ----------
IX_FOOBAR INDEX 2048

今回使用したSQLスクリプト

[oracle@localhost ~]$ cat estimate_rebuild_index_size.sql
DROP TABLE foobar PURGE
.
l
/

CREATE TABLE foobar
(
key_code CHAR(10) NOT NULL
)
.
l
/

BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO foobar VALUES(TO_CHAR(i,'FM0000000009'));
IF MOD(i,100) = 0
THEN
COMMIT;
END IF;
END LOOP;
END;
.
l
/

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'FOOBAR', cascade=>true, no_invalidate=>false);
END;
.
l
/


EXPLAIN PLAN FOR CREATE INDEX ix_foobar ON foobar(key_code)
.
l
/
@?/rdbms/admin/utlxpls

CREATE INDEX ix_foobar ON foobar(key_code)
.
l
/

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'FOOBAR', cascade=>true, no_invalidate=>false);
END;
.
l
/

select segment_name,segment_type,sum(bytes)/1024 AS "KB" from user_segments where segment_name = 'IX_FOOBAR' group by segment_name,segment_type
.
l
/

DELETE FROM foobar WHERE key_code BETWEEN '0000000000' AND '0000050000'
.
l
/

COMMIT
.
l
/

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'FOOBAR', cascade=>true, no_invalidate=>false);
END;
.
l
/

select segment_name,segment_type,sum(bytes)/1024 AS "KB" from user_segments where segment_name = 'IX_FOOBAR' group by segment_name,segment_type
.
l
/

EXPLAIN PLAN FOR CREATE INDEX ix_foobar ON foobar(key_code)
.
l
/
@?/rdbms/admin/utlxpls

EXPLAIN PLAN FOR CREATE INDEX ix_foobar ON foobar(key_code)
.
l
/
@?/rdbms/admin/utlxpls

EXPLAIN PLAN FOR ALTER INDEX ix_foobar REBUILD
.
l
/
@?/rdbms/admin/utlxpls

ALTER INDEX ix_foobar REBUILD
.
l
/

select segment_name,segment_type,sum(bytes)/1024 AS "KB" from user_segments where segment_name = 'IX_FOOBAR' group by segment_name,segment_type
.
l
/

DROP TABLE foobar PURGE
.
l
/


ちょっと、涼しくなったと思ったら、残暑がキツイ。

では、また。


| | | コメント (0)

2023年9月23日 (土)

帰ってきた! 標準はあるにはあるが癖の多いSQL #4 Optimizer Traceの取得でも癖がでる

Previously on Mac De Oracle
前回は、MySQLのHash Join を取り上げました。MySQL 8.0.32では NLJに使えるINDEXが存在していても、Hash Joinをヒントで強制することができる!(オプティマイザが選択することがある!)
(MySQLの生い立ちを考えると、Hash Joinとか言われると、MySQLもHash Joinが必要な時代になったのか〜。と遠くを見る自分がいるw)

ということで、今回は、前々回とりあげた、
悩ませ過ぎは及ばざるがごとし (MySQL 8.0.32編)で思い出したネタで、オプティマイザがパースに悩むというか、考えすぎている姿を、時間ではなく、オプティマイザトレースのサイズから見える化して比較!!! してみたいと思います。


実は、
10億年以上前に、Oracle Databaseで同じことやってましたw

MySQLへ話を戻すと、
MySQLのオプティマイザトレースは、Oracle Databaseのそれとは少々ことなり、パラメータで指定されたメモリー上にJSON形式で記録され、INFORMATION_SCHEMA経由で問い合わせて確認します。
パラメータで指定されたメモリーサイズを超えるトレースは切り捨てられてしまいます。Oracle Databaseのオプティマイザトレースとは異なる注意点ですね。

また、面白い特徴もあります。
オプティマイザトレースの出力サイズを事前に予測することはできないわけですが、メモリー内に記録しきれず切り捨てたバイト数をレポートしてくれます。
その機能を利用し、切り捨てたれたトレースのバイト数とトレースを記録するために指定したメモリーのバイト数の合計からトレースのサイズを確認することはできます! 面白い仕組みを提供してくれてますよね。

余談
Oracle/MySQLは、オプティマイザトレースを明示的に取得できますが、PostgreSQLって、オプティマイザトレースだけを取得するOracleの10053トレースMySQLのoptimizer_trace=onに類似する方法はなかったはず。
(DB側の機能の一部としてはないという認識なので、もし勘違いしていたら、ツッコミ🙇よろしくお願いします)



細けーことはこれぐらいにして、比較してみましょう。

まず、前回もネタにした、パース時間のながーーーーーーい、クエリー。
explainだけで、29秒 です!!  11! の組み合わせをみているわけですから、デフォルト設定のMySQL、ものすごーく考えてますよね。
OracleとかPostgreSQLならとっくに諦めている数です。

参考)
検証で利用しているMySQLの表及び索引定義とSQLスクリプトは以下ブログ参照のこと。
帰ってきた! 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る


mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32 |
+-----------+
1 row in set (0.00 sec)


mysql> \. test1.sql
Database changed
+----------------------------------------------------------------------------------------------------------------------+
| PLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=7163.00 rows=10240)
-> Nested loop inner join (cost=3579.00 rows=5120)
-> Nested loop inner join (cost=1787.00 rows=2560)
-> Nested loop inner join (cost=891.00 rows=1280)
-> Nested loop inner join (cost=443.00 rows=640)
-> Nested loop inner join (cost=219.00 rows=320)
-> Nested loop inner join (cost=107.00 rows=160)
-> Nested loop inner join (cost=51.00 rows=80)
-> Nested loop inner join (cost=23.00 rows=40)
-> Nested loop inner join (cost=9.00 rows=20)
-> Index scan on master using ix_master (cost=2.00 rows=10)
-> Index lookup on detail using PRIMARY (id=`master`.id) (cost=0.52 rows=2)
-> Index lookup on t2 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t3 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t4 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t5 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t6 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t7 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t8 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t9 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t10 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
|
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (29.05 sec)

MySQLでオプティマイザトレースを取得するには、optimizer_trace を on 、optimizer_trace_max_mem_size に オプティマイザトレース記録に必要なメモリーサイズを指定します。
その後、explain文でオプティマイザトレースを取得します。なお、今回は、explainでパース部分だけをトレースして比較します。
https://dev.mysql.com/doc/refman/8.0/en/information-schema-optimizer-trace-table.html


optimizer_trace_max_mem_size を 100 bytesにして、確実に切り捨てが起きるように設定します。

mysql> show variables like 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name | Value |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+
1 row in set (0.18 sec)

mysql> SET optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'optimizer_trace';
+-----------------+-------------------------+
| Variable_name | Value |
+-----------------+-------------------------+
| optimizer_trace | enabled=on,one_line=off |
+-----------------+-------------------------+
1 row in set (0.00 sec)

mysql> show variables like 'optimizer_trace_max_mem_size';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| optimizer_trace_max_mem_size | 1048576 |
+------------------------------+---------+
1 row in set (0.00 sec)

mysql> SET optimizer_trace_max_mem_size = 100;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'optimizer_trace_max_mem_size';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| optimizer_trace_max_mem_size | 100 |
+------------------------------+-------+
1 row in set (0.00 sec)

オプティマイザトレースの準備ができたので、explainしてみましょう!

mysql> \. test1.sql
Database changed
+----------------------------------------------------------------------------------------------------------------------+
| PLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=7163.00 rows=10240)
-> Nested loop inner join (cost=3579.00 rows=5120)
-> Nested loop inner join (cost=1787.00 rows=2560)
-> Nested loop inner join (cost=891.00 rows=1280)
-> Nested loop inner join (cost=443.00 rows=640)
-> Nested loop inner join (cost=219.00 rows=320)
-> Nested loop inner join (cost=107.00 rows=160)
-> Nested loop inner join (cost=51.00 rows=80)
-> Nested loop inner join (cost=23.00 rows=40)
-> Nested loop inner join (cost=9.00 rows=20)
-> Index scan on master using ix_master (cost=2.00 rows=10)
-> Index lookup on detail using PRIMARY (id=`master`.id) (cost=0.52 rows=2)
-> Index lookup on t2 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t3 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t4 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t5 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t6 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t7 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t8 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t9 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t10 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
|
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (7 min 3.67 sec)

次に、INFORMATION_SCHEMA.OPTIMIZER_TRACEからオプティマイザトレースを取得してみます。

2147483647 bytes (切り捨てられたトレースのサイズ)+ 100 bytes (optimizer_trace_max_mem_sizeパラメータに指定したサイズ)= 2,147,483,747 bytes およそ 2GB です

mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
*************************** 1. row ***************************
QUERY: explain format=tree
select
*
from
master inner join detail
on master.id = detail.id
inner join detail t2
on
t2.id = detail.id
inner join detail t3
on
t3.id = t2.id
inner join detail t4
on
t4.id = t3.id
inner join detail t5
on
t5.id = t4.id
inner join detail t6
on
t6.id = t5.id
inner join detail t7
on
t7.id = t6.id
inner join detail t8
on
t8.id = t7.id
inner join detail t9
on
t9.id = t8.id
inner join detail t10
on
t10.id = t9.id
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `master`.`id` AS `id`,`master`.`dummya` AS `dummya`,`detail`.`id` AS `id`,`detail`.`subid` AS `subid`,`detail`.`dummya` AS `dummya`,`t2`.`id` AS `id`,
`t2`.`subid` AS `subid`,`t2`.`dummya` AS `dummya`,`t3`.`id` AS `id`,`t3`.`subid` AS `subid`,`t3`.`dummya` AS `dummya`,`t4`.`id` AS `id`,`t4`.`subid` AS `subid`,`t4`.`dummya` AS `dummya`,
`t5`.`id` AS `id`,`t5`.`subid` AS `subid`,`t5`.`dummya` AS `dummya`,`t6`.`id` AS `id`,`t6`.`subid` AS `subid`,`t6`.`dummya` AS `dummya`,`t7`.`id` AS `id`,`t7`.`subid` AS `subid`,
`t7`.`dummya` AS `dummya`,`t8`.`id` AS `id`,`t8`.`subid` AS `subid`,`t8`.`dummya` AS `dummya`,`t9`.`id` AS `id`,`t9`.`subid` AS `subid`,`t9`.`dummya` AS `dummya`,`t10`.`id` AS `id`,
`t10`.`subid` AS `subid`,`t10`.`dummya` AS `dummya` from ((((((((((`master` join `detail` on((`master`.`id` = `detail`.`id`))) join `detail` `t2` on((`t2`.`id` = `detail`.`id`)))
join `detail` `t3` on((`t3`.`id` = `t2`.`id`))) join `detail` `t4` on((`t4`.`id` = `t3`.`id`))) join `detail` `t5` on((`t5`.`id` = `t4`.`id`))) join `detail` `t6` on((`t6`.`id` = `t5`.`id`)))
join `detail` `t7` on((`t7`.`id` = `t6`.`id`))) join `detail` `t8` on((`t8`.`id` = `t7`.`id`))) join `detail` `t9` on((`t9`.`id` = `t8`.`id`))) join `detail` `t10` on((`t10`.`id` = `t9`.`id`)))"
},
{
"transformations_to_nested_joins": {
"transformations": [
"JOIN_condition_to_WHERE",
"parenthesis_removal"
],
"expanded_query": "/* select#1 */ select `master`.`id` AS `id`,`master`.`dummya` AS `dummya`,`detail`.`id` AS `id`,`detail`.`subid` AS `subid`,`detail`.`dummya` AS `dummya`,
`t2`.`id` AS `id`,`t2`.`subid` AS `subid`,`t2`.`dummya` AS `dummya`,`t3`.`id` AS `id`,`t3`.`subid` AS `subid`,`t3`.`dummya` AS `dummya`,`t4`.`id` AS `id`,`t4`.`subid` AS `subid`,
`t4`.`dummya` AS `dummya`,`t5`.`id` AS `id`,`t5`.`subid` AS `subid`,`t5`.`dummya` AS `dummya`,`t6`.`id` AS `id`,`t6`.`subid` AS `subid`,`t6`.`dummya` AS `dummya`,`t7`.`id` AS `id`,
`t7`.`subid` AS `subid`,`t7`.`dummya` AS `dummya`,`t8`.`id` AS `id`,`t8`.`subid` AS `subid`,`t8`.`dummya` AS `dummya`,`t9`.`id` AS `id`,`t9`.`subid` AS `subid`,
`t9`.`dummya` AS `dummya`,`t10`.`id` AS `id`,`t10`.`subid` AS `subid`,`t10`.`dummya` AS `dummya` from `master` join `detail` join `detail` `t2` join `detail` `t3`
join `detail` `t4` join `detail` `t5` join `detail` `t6` join `detail` `t7` join `detail` `t8` join `detail` `t9` join `detail` `t10`
where ((`t10`.`id` = `t9`.`id`) and (`t9`.`id` = `t8`.`id`) and (`t8`.`id` = `t7`.`id`) and (`t7`.`id` = `t6`.`id`) and (`t6`.`id` = `t5`.`id`) and (`t5`.`id` = `t4`.`id`)
and (`t4`.`id` = `t3`.`id`) and (`t3`.`id` = `t2`.`id`) and (`t2`.`id` = `detail`.`id`) and (`master`.`id` = `detail`.`id`))"
}
}
]
}
},


....中略....

},
"condition_filtering_pct": 100,
"rows_for_plan": 10240,
"cost_for_plan": 7163,
"pruned_by_cost": true
}

MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 2147483647
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.26 sec)

mysql>


次に、考え過ぎているオプティマイザに、
JOIN_ORDERヒントを利用し、結合順を 考えるな! 感じろ! 作戦でオプティマイザに考えさせないように。。。パースも速いし、オプティマイザトレースのJSONのサイズも小さい!

mysql> \. test1.sql
Database changed
+----------------------------------------------------------------------------------------------------------------------+
| PLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=7163.00 rows=10240)
-> Nested loop inner join (cost=3579.00 rows=5120)
-> Nested loop inner join (cost=1787.00 rows=2560)
-> Nested loop inner join (cost=891.00 rows=1280)
-> Nested loop inner join (cost=443.00 rows=640)
-> Nested loop inner join (cost=219.00 rows=320)
-> Nested loop inner join (cost=107.00 rows=160)
-> Nested loop inner join (cost=51.00 rows=80)
-> Nested loop inner join (cost=23.00 rows=40)
-> Nested loop inner join (cost=9.00 rows=20)
-> Index scan on master using ix_master (cost=2.00 rows=10)
-> Index lookup on detail using PRIMARY (id=`master`.id) (cost=0.52 rows=2)
-> Index lookup on t2 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t3 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t4 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t5 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t6 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t7 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t8 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t9 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t10 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
|
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


オプティマイザトレース結果のJSONサイズは、

57379 bytes (切り捨てられたトレースのバイト数) + 100 bytes(optimizer_trace_max_mem_sizeパラメータに指定したサイズ) = 57,479 bytes 、約 56 KBとなりました。

2GB vs 56KB

オプティマイザトレースのJSONサイズからオプティマイザの仕事量を覗いてみるのも面白いですよね。(俺だけか喜んでるのw)

mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
*************************** 1. row ***************************
QUERY: explain format=tree
select
/*+
JOIN_ORDER(master,detail,t2,t3,t4,t5,t6,t7,t8,t9,t10)
*/
*
from
master inner join detail
on master.id = detail.id
inner join detail t2
on
t2.id = detail.id
inner join detail t3
on
t3.id = t2.id
inner join detail t4
on
t4.id = t3.id
inner join detail t5
on
t5.id = t4.id
inner join detail t6
on
t6.id = t5.id
inner join detail t7
on
t7.id = t6.id
inner join detail t8
on
t8.id = t7.id
inner join detail t9
on
t9.id = t8.id
inner join detail t10
on
t10.id = t9.id
TRACE:
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 57379
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

ということで、 MySQLのオプティマイザトレースはメモリー中に記録され、メモリーに記録できないトレースは切り捨てられる。
また、切り捨てられたサイズ+オプティマイザトレース向けメモリーサイズからトレースを完全に取得するためのメモリーサイズを確認できる。
ただし、必要がメモリーの空き次第ではありそうですね。ということで、Oracle Databaseの 10053トレースとはちょっと違う注意点もあるな。

という知見を得た :)

Oracle Database の 10053トレースでのトレースによる考えている仕事量の見える化同様に、MySQLのオプティマイザトレースでも、オプティマイザが考えすぎていると様子をトレースサイズで見える化してみるという検証はここまで。
MySQLのオプティマイザトレースをなんとなく眺めて、なぜ、その判断をしたのだなどを追うのはまた別の機会に;)


10月も近いのに、muggy な気候の続く、東京より。

ではまた。





関連エントリー
悩ませ過ぎは及ばざるがごとし (MySQL 8.0.32編)
悩ませ過ぎは及ばざるがごとし #7 - おまけ



標準はあるにはあるが癖の多い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 オプティマイザの結合順評価テーブル数上限にも癖が出る

| | | コメント (0)

2023年9月 7日 (木)

MySQL 8.0.32では NLJに使えるINDEXが存在していても、Hash Joinをヒントで強制することができる!(オプティマイザが選択することがある!)

Previously on Mac De Oracle
前回は悩ませ過ぎは及ばざるがごとし (MySQL 8.0.32編)で、パース時間が長い場合、ヒントを使うことでパース時間を短縮させることが可能なケースもあるというという話でした。Oracleでも同様のケースがあることもお話ししました。が、その中で、MySQL 8.0.32 ではオプティマイザが、NLJで利用するINDEXが存在していても、HJを選択することがあることに気づいた!(どうやら、該当表がバッファープールに乗っているとNLJを選択しやすくなる傾向もありそう)という、予告編までw

ということで、今日は、MySQL 8.0.32 では、NLJに利用するINDEXが存在していても、オプティマイザは、HJを選択することもあるし、ヒントによってHJを矯正できるのか? ということを確認してみました。


これまでの、MySQL 8.0.x台のHash Joinの適用範囲および挙動は以下のブログで取り上げられています。(非常に参考になりました。ありがたいですね。本当に ;)

MySQL 8.0.18のHASH JOINを試した(tom__bo’s Blog)
https://tombo2.hatenablog.com/entry/2019/10/14/212100

MySQL 8.0.20 のハッシュジョイン(Hash Join)を INDEX があるテーブルで試してみる(Qiita @hmatsu47)
https://qiita.com/hmatsu47/items/e473a3e566b910d61f5b

MySQL 8.0.20 でHASH JOINが効くケースが拡大した (mita2 database life)
https://mita2db.hateblo.jp/entry/2020/05/03/174101

と、NLJ可能な索引があるとなぜか、HJにならなかった。ついでに、HASH_JOINというヒントは、8.0.18でしか効果がない!
https://dev.mysql.com/doc/refman/8.0/ja/optimizer-hints.html#optimizer-hints-table-level


MySQL 8.0 Manual
https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html

"8.2.1.4 Hash Join Optimization By default, MySQL (8.0.18 and later) employs hash joins whenever possible. It is possible to control whether hash joins are employed using one of the BNL and NO_BNL optimizer hints, or by setting block_nested_loop=on or block_nested_loop=off as part of the setting for the optimizer_switch server system variable.

Note
MySQL 8.0.18 supported setting a hash_join flag in optimizer_switch, as well as the optimizer hints HASH_JOIN and NO_HASH_JOIN. In MySQL 8.0.19 and later, none of these have any effect any longer.

Beginning with MySQL 8.0.18, MySQL employs a hash join for any query for which each join has an equi-join condition, and in which there are no indexes that can be applied to any join conditions, such as this one:"


ななな、なーーーん、だってーーーーーーっ!。


だが、しかし、8.0.32 になるとマニュアルの記載では特に見かけなかったが、NLJに使える索引がある場合でも等価結合でHash Joinになるではありませんか。みなさん!

まじでーーーというか、実際、そうなるもん!

以下、前回のブログ参照のこと。
悩ませ過ぎは及ばざるがごとし (MySQL 8.0.32編) (Mac De Oracle)
https://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2023/09/post-bee50f.html


ただし、 HASH_JOINヒントは使えないので、どのようにヒントを書けば Hash Joinを強制できるのだろう? 8.0.32編 ということで、試してみた!

結論から言ってしまうと

MySQL 8.0.32 では NLJに使えるINDEXが存在していても、Hash Joinを強制することができる! (いつから変わったかは不明だが、8.0.32では可能!)
MySQL 8.0.32 では NLJに使えるINDEXが存在していても、Hash Joinを強制することができる! (いつから変わったかは不明だが、8.0.32では可能!)


詳細は、以下の検証を。比較的気楽にヒントでHJを強制できるようです(だたしちょっと分かりずらいね。Oracleとかと比べると)

表定義等は前々回のエントリー参照のこと。帰ってきた! 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る

起動直後、バッファキャッシュが空だと(未検証)Hash Joinが選択されるので、まずは、オプティマイザが Hash Join (等価結合でNLJに利用できる索引が存在する状況でも)が選択されている(前回のブログのおさらい)

[master@localhost ~]$ sudo service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[master@localhost ~]$ mysql -u scott -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.32 Source distribution

Copyright (c) 2000, 2023, 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> select version();
+-----------+
| version() |
+-----------+
| 8.0.32 |
+-----------+
1 row in set (0.00 sec)

mysql>
mysql> \. test1.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
+----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t10.id = `master`.id) (cost=20489.43 rows=10240)
-> Table scan on t10 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t9.id = `master`.id) (cost=10238.74 rows=5120)
-> Table scan on t9 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t8.id = `master`.id) (cost=5113.38 rows=2560)
-> Table scan on t8 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t7.id = `master`.id) (cost=2550.45 rows=1280)
-> Table scan on t7 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t6.id = `master`.id) (cost=1268.60 rows=640)
-> Table scan on t6 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t5.id = `master`.id) (cost=627.24 rows=320)
-> Table scan on t5 (cost=0.01 rows=20)
-> Hash
-> Inner hash join (t4.id = `master`.id) (cost=306.09 rows=160)
-> Table scan on t4 (cost=0.02 rows=20)
-> Hash
-> Inner hash join (t3.id = `master`.id) (cost=145.03 rows=80)
-> Table scan on t3 (cost=0.03 rows=20)
-> Hash
-> Inner hash join (t2.id = `master`.id) (cost=64.01 rows=40)
-> Table scan on t2 (cost=0.06 rows=20)
-> Hash
-> Inner hash join (detail.id = `master`.id) (cost=23.00 rows=20)
-> Table scan on detail (cost=0.12 rows=20)
-> Hash
-> Index scan on master using ix_master (cost=2.00 rows=10)
|
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

関連する表をアクセスしてバッファープールに乗った状態にすると。オプティマイザは、INDEXを利用したNLJを選択するようになった。(前回のブログのおさらい)

mysql> 
mysql> select * from master;
+----+--------+
| id | dummya |
+----+--------+
| 1 | 1 |
| 10 | 10 |

....中略....

| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
+----+--------+
10 rows in set (0.01 sec)

mysql> select * from detail;
+----+-------+--------+
| id | subid | dummya |
+----+-------+--------+
| 1 | 1 | 11 |
| 1 | 2 | 12 |
| 2 | 1 | 21 |
| 2 | 2 | 22 |

....中略....

| 9 | 1 | 91 |
| 9 | 2 | 92 |
| 10 | 1 | 101 |
| 10 | 2 | 102 |
+----+-------+--------+
20 rows in set (0.01 sec)

mysql> \. test1.sql
Database changed
+----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=7163.00 rows=10240)
-> Nested loop inner join (cost=3579.00 rows=5120)
-> Nested loop inner join (cost=1787.00 rows=2560)
-> Nested loop inner join (cost=891.00 rows=1280)
-> Nested loop inner join (cost=443.00 rows=640)
-> Nested loop inner join (cost=219.00 rows=320)
-> Nested loop inner join (cost=107.00 rows=160)
-> Nested loop inner join (cost=51.00 rows=80)
-> Nested loop inner join (cost=23.00 rows=40)
-> Nested loop inner join (cost=9.00 rows=20)
-> Index scan on master using ix_master (cost=2.00 rows=10)
-> Index lookup on detail using PRIMARY (id=`master`.id) (cost=0.52 rows=2)
-> Index lookup on t2 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t3 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t4 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t5 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t6 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t7 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t8 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t9 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t10 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
|
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


オプティマイザがNLJを選択するようになった状況で、Hash Join となるヒントを追加(ヒント等は後半のスクリプト test1_hj.sql を参照のこと)すると、NLJで利用可能なINDEXが存在していたとしても、HJを強制できる。(8.0.20ごろまでは、HJは選択されなかったようなので、8.0.21から8.0.32の間でHJの適用範囲がさらに拡大されたのでしょうね。想像ですけども)

mysql> 
mysql> \. test1_hj.sql
Database changed
+----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t10.id = `master`.id) (cost=20468.86 rows=10240)
-> Table scan on t10 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t9.id = `master`.id) (cost=10226.19 rows=5120)
-> Table scan on t9 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t8.id = `master`.id) (cost=5104.85 rows=2560)
-> Table scan on t8 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t7.id = `master`.id) (cost=2544.11 rows=1280)
-> Table scan on t7 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t6.id = `master`.id) (cost=1263.65 rows=640)
-> Table scan on t6 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t5.id = `master`.id) (cost=623.31 rows=320)
-> Table scan on t5 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t4.id = `master`.id) (cost=303.02 rows=160)
-> Table scan on t4 (cost=0.01 rows=20)
-> Hash
-> Inner hash join (t3.id = `master`.id) (cost=142.76 rows=80)
-> Table scan on t3 (cost=0.01 rows=20)
-> Hash
-> Inner hash join (t2.id = `master`.id) (cost=62.50 rows=40)
-> Table scan on t2 (cost=0.02 rows=20)
-> Hash
-> Inner hash join (detail.id = `master`.id) (cost=22.25 rows=20)
-> Table scan on detail (cost=0.05 rows=20)
-> Hash
-> Table scan on master (cost=2.00 rows=10)
|
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)


ついでなので、NLJになるようにがっつりヒントで固めてみた場合はどうなるか? 想定通り、NLJに倒せますね。(前回のブログのおさらい)

mysql> 
mysql> \. test1_nlj.sql
Database changed
+----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=7173.00 rows=10240)
-> Nested loop inner join (cost=3589.00 rows=5120)
-> Nested loop inner join (cost=1797.00 rows=2560)
-> Nested loop inner join (cost=901.00 rows=1280)
-> Nested loop inner join (cost=453.00 rows=640)
-> Nested loop inner join (cost=229.00 rows=320)
-> Nested loop inner join (cost=117.00 rows=160)
-> Nested loop inner join (cost=61.00 rows=80)
-> Nested loop inner join (cost=33.00 rows=40)
-> Nested loop inner join (cost=19.00 rows=20)
-> Index scan on master using ix_master (cost=12.00 rows=10)
-> Index lookup on detail using PRIMARY (id=`master`.id) (cost=0.52 rows=2)
-> Index lookup on t2 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t3 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t4 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t5 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t6 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t7 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t8 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t9 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t10 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
|
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>


検証で利用した 表と索引定義は前回のブログ参照のこと。

以下、今回検証で利用したスクリプト

オリジナル。結合順だけ JOIN_ORDERで制御。オプティマイザが、NLJ/HJをその時の気分で(嘘w)、その時の状況で判断して、NLJ/HJのいずれかを選択する(ことがわかっている 8.0.32 では)

[master@localhost ~]$ cat test1.sql
use perftestdb;


explain format=tree
select
/*+
JOIN_ORDER(master,detail,t2,t3,t4,t5,t6,t7,t8,t9,t10)
*/
*
from
master inner join detail
on master.id = detail.id
inner join detail t2
on
t2.id = detail.id
inner join detail t3
on
t3.id = t2.id
inner join detail t4
on
t4.id = t3.id
inner join detail t5
on
t5.id = t4.id
inner join detail t6
on
t6.id = t5.id
inner join detail t7
on
t7.id = t6.id
inner join detail t8
on
t8.id = t7.id
inner join detail t9
on
t9.id = t8.id
inner join detail t10
on
t10.id = t9.id
;


JOIN_ORDER/NO_INDEX/NO_JOIN_INDEXヒントを利用して、結合順と索引スキャンの抑止、そして、NLJで利用する索引の利用を抑止することで HJ になるようにしたスクリプト

[master@localhost ~]$ cat test1_hj.sql
use perftestdb;


explain format=tree
select
/*+
JOIN_ORDER(master,detail,t2,t3,t4,t5,t6,t7,t8,t9,t10)
NO_INDEX(master ix_master)
NO_JOIN_INDEX(detail primary)
NO_JOIN_INDEX(t2 primary)
NO_JOIN_INDEX(t3 primary)
NO_JOIN_INDEX(t4 primary)
NO_JOIN_INDEX(t5 primary)
NO_JOIN_INDEX(t6 primary)
NO_JOIN_INDEX(t7 primary)
NO_JOIN_INDEX(t8 primary)
NO_JOIN_INDEX(t9 primary)
NO_JOIN_INDEX(t10 primary)
*/
*
from
master inner join detail
on master.id = detail.id
inner join detail t2
on
t2.id = detail.id
inner join detail t3
on
t3.id = t2.id
inner join detail t4
on
t4.id = t3.id
inner join detail t5
on
t5.id = t4.id
inner join detail t6
on
t6.id = t5.id
inner join detail t7
on
t7.id = t6.id
inner join detail t8
on
t8.id = t7.id
inner join detail t9
on
t9.id = t8.id
inner join detail t10
on
t10.id = t9.id
;


最後に、前回も利用した NLJ に倒すためのガチガチヒントバージョンのスクリプト

[master@localhost ~]$ cat test1_nlj.sql
use perftestdb;


explain format=tree
select
/*+
JOIN_ORDER(master,detail,t2,t3,t4,t5,t6,t7,t8,t9,t10)
INDEX(master ix_master)
JOIN_INDEX(detail primary)
JOIN_INDEX(t2 primary)
JOIN_INDEX(t3 primary)
JOIN_INDEX(t4 primary)
JOIN_INDEX(t5 primary)
JOIN_INDEX(t6 primary)
JOIN_INDEX(t7 primary)
JOIN_INDEX(t8 primary)
JOIN_INDEX(t9 primary)
JOIN_INDEX(t10 primary)
*/
*
from
master inner join detail
on master.id = detail.id
inner join detail t2
on
t2.id = detail.id
inner join detail t3
on
t3.id = t2.id
inner join detail t4
on
t4.id = t3.id
inner join detail t5
on
t5.id = t4.id
inner join detail t6
on
t6.id = t5.id
inner join detail t7
on
t7.id = t6.id
inner join detail t8
on
t8.id = t7.id
inner join detail t9
on
t9.id = t8.id
inner join detail t10
on
t10.id = t9.id
;

ということで、まだまだまだ、蒸し暑いし、台風直撃しそうな、東京からお送りしました。台風に注意しつつ週末を過ごすしかなさそう。

では、また。





悩ませ過ぎは及ばざるがごとし (MySQL 8.0.32編)

| | | コメント (0)

2023年9月 3日 (日)

悩ませ過ぎは及ばざるがごとし (MySQL 8.0.32編)

Mac De OracleでMySQLとかPostgreSQLネタやるのって、2006年ごろの、異機種間接続サービス(Oracle Generic Connectivity)以来のような感覚になりますなw
あの頃は、

MySQL4.0.25 (MacOSX 10.4 Tiger)
MySQL4.0.26 (WindowsXP Pro)
MySQL4.1.13a (MacOSX 10.4 Tiger Server付属のもの)
PostgreSQL7.4.9 (MacOSX 10.4 Tiger)
Oracle10g R1 EE 10.1.0.3 for MacOSX

で遊んでた頃なので、みんな、それぞれ、バージョンが育ったりしてますよねwwww

では本題

Previously on Mac De Oracle
前回は、帰ってきた! 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る でした。

MySQLで結合数が多いとオプティマイザが考え過ぎてパース時間が伸びるケースとシステムパラメータでそれを制御する例でした。前回のエントリーでもちょっと書きましたが、似たようなケースで、Oracleではオプティマイザにそもそも頭を使わせないでパース時間を短縮させちゃうなんて、荒技でもな、テクニックがあります。それは単純、ヒントを指定してオプティマイザに該当部分で頭を使わせないようにするw

 

という方法です。

 

悩ませ過ぎは及ばざるがごとし #7 - おまけ

 

上記の例は、結合ではなくINリストとヒントでオプティマイザに考えさせないという方法です。
(なお、Oracleのヒントの指定方法は長年の研究によるちょっと特徴のあるヒントの指定方法ですがw 大丈夫ですよ。ちゃんと機能しますのでw 最近、OracleのSQLヒントレポートが出力できるようになったので、その辺り気にする人もいるのですけどもね。引用先のOracle SQLヒントの利用方法は、パース時間短縮専用ということではなく、一般的に私がよく使う方法でもあります。その辺のネタは次回にでもw)

 

では、MySQLでも効果があるのか、試してみましょう! というのが今日のお題。

 

MySQL 8.0.32で、パースに時間を要するケースの再現。optimizer_search_depthパラメータはデフォルトのまま

 

MySQL : optimizer_search_depth optimizer_search_depth


mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32 |
+-----------+
1 row in set (0.00 sec)
>

 


mysql> show variables like 'optimizer_search_depth';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| optimizer_search_depth | 62 |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> \. test1.sql
Database changed
+----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=7162.25 rows=10240)
-> Nested loop inner join (cost=3578.25 rows=5120)
-> Nested loop inner join (cost=1786.25 rows=2560)
-> Nested loop inner join (cost=890.25 rows=1280)
-> Nested loop inner join (cost=442.25 rows=640)
-> Nested loop inner join (cost=218.25 rows=320)
-> Nested loop inner join (cost=106.25 rows=160)
-> Nested loop inner join (cost=50.25 rows=80)
-> Nested loop inner join (cost=22.25 rows=40)
-> Nested loop inner join (cost=8.25 rows=20)
-> Index scan on master using ix_master (cost=1.25 rows=10)
-> Index lookup on detail using PRIMARY (id=`master`.id) (cost=0.52 rows=2)
-> Index lookup on t2 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t3 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t4 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t5 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t6 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t7 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t8 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t9 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t10 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
|
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (21.37 sec)

 

パースに 21.37 sec 要していますね。

というところまでは、前回のエントリーと同じですが、次は、パラメータで調整するのではなく、結合順を考えさせないためにヒントで結合順を指定みましょう。どうなりますかね。。。楽しみです。

 

JOIN_ORDERヒントで結合順を全て指定します。OracleのLEADINGヒントに似てますよね。


[master@localhost ~]$ cat test1.sql
use perftestdb;


explain format=tree
select
/*+
JOIN_ORDER(master,detail,t2,t3,t4,t5,t6,t7,t8,t9,t10)
*/
*
from
master inner join detail
on master.id = detail.id
inner join detail t2
on
t2.id = detail.id
inner join detail t3
on
t3.id = t2.id
inner join detail t4
on
t4.id = t3.id
inner join detail t5
on
t5.id = t4.id
inner join detail t6
on
t6.id = t5.id
inner join detail t7
on
t7.id = t6.id
inner join detail t8
on
t8.id = t7.id
inner join detail t9
on
t9.id = t8.id
inner join detail t10
on
t10.id = t9.id
;

 

MySQLを起動して。。。試してみます。


[master@localhost ~]$ sudo service mysqld start
[sudo] master のパスワード:
Redirecting to /bin/systemctl start mysqld.service
[master@localhost ~]$ mysql -u scott -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.32 Source distribution

Copyright (c) 2000, 2023, 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> \. test1.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
+----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t10.id = `master`.id) (cost=20489.43 rows=10240)
-> Table scan on t10 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t9.id = `master`.id) (cost=10238.74 rows=5120)
-> Table scan on t9 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t8.id = `master`.id) (cost=5113.38 rows=2560)
-> Table scan on t8 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t7.id = `master`.id) (cost=2550.45 rows=1280)
-> Table scan on t7 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t6.id = `master`.id) (cost=1268.60 rows=640)
-> Table scan on t6 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t5.id = `master`.id) (cost=627.24 rows=320)
-> Table scan on t5 (cost=0.01 rows=20)
-> Hash
-> Inner hash join (t4.id = `master`.id) (cost=306.09 rows=160)
-> Table scan on t4 (cost=0.02 rows=20)
-> Hash
-> Inner hash join (t3.id = `master`.id) (cost=145.03 rows=80)
-> Table scan on t3 (cost=0.03 rows=20)
-> Hash
-> Inner hash join (t2.id = `master`.id) (cost=64.01 rows=40)
-> Table scan on t2 (cost=0.06 rows=20)
-> Hash
-> Inner hash join (detail.id = `master`.id) (cost=23.00 rows=20)
-> Table scan on detail (cost=0.12 rows=20)
-> Hash
-> Index scan on master using ix_master (cost=2.00 rows=10)
|
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

パースめちゃめちゃ早くなりました! w オプティマイザよ、考えるな! 感じろ! いや、でも考えて! オプティマイザ!w

ただ、妙なのは、見積もりだからでしょうか?????? 等価結合なのにHash Joinになってます! MySQLってOracleとかPostgreSQLっぽくHash Join行ったりするんでしたっけ?????? 謎。。。。。

統計情報をとってあります。(前回の通りですが。。。。。。)

 

ちょっと気になるところがあります。前々回もそうだったので、MySQL起動直後だけ、該当表を一度もアクセスしていない場合には、なぜか、Hash Joinになるというのが再現しています!!!!!! なになになにこれ。。。。

 

簡単な実験、analyzeとかしませんよ。単純に、利用する表を全表走査してみますね。そのあとで、実行計画がどうなるかみてみましょう。ヒントは結合順の指定だけですよ。


mysql> 
mysql>
mysql> select * from master;
+----+--------+
| id | dummya |
+----+--------+
| 1 | 1 |
| 10 | 10 |

...中略...

| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
+----+--------+
10 rows in set (0.00 sec)

mysql> select * from detail;
+----+-------+--------+
| id | subid | dummya |
+----+-------+--------+
| 1 | 1 | 11 |
| 1 | 2 | 12 |
| 2 | 1 | 21 |
| 2 | 2 | 22 |
| 3 | 1 | 31 |

...中略...

| 8 | 2 | 82 |
| 9 | 1 | 91 |
| 9 | 2 | 92 |
| 10 | 1 | 101 |
| 10 | 2 | 102 |
+----+-------+--------+
20 rows in set (0.01 sec)

 

どうなるか変化するかどうか、乞うご期待!!!!!

 

きたーーーーーー。NLJに変化しました。。。。。MySQL的に、現時点ではこの実行計画の方が自然なのでは????? 教えて、マイエスキューエルの人!

 

本題である、パース時間は見事に早くなってますよね!!! 結合順を考えなくて済むわけですから、早いに決まってます!

考えるな! 感じろ! (でも考えて、オプティマイザ! 大切なので2度目の雄叫びw)


mysql> \. test1.sql
Database changed
+----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=7163.00 rows=10240)
-> Nested loop inner join (cost=3579.00 rows=5120)
-> Nested loop inner join (cost=1787.00 rows=2560)
-> Nested loop inner join (cost=891.00 rows=1280)
-> Nested loop inner join (cost=443.00 rows=640)
-> Nested loop inner join (cost=219.00 rows=320)
-> Nested loop inner join (cost=107.00 rows=160)
-> Nested loop inner join (cost=51.00 rows=80)
-> Nested loop inner join (cost=23.00 rows=40)
-> Nested loop inner join (cost=9.00 rows=20)
-> Index scan on master using ix_master (cost=2.00 rows=10)
-> Index lookup on detail using PRIMARY (id=`master`.id) (cost=0.52 rows=2)
-> Index lookup on t2 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t3 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t4 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t5 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t6 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t7 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t8 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t9 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t10 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
|
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

MySQL 8.0.32、色々と楽しませてくれますねー。。 Oracle 7.0の挙動と戯れてたときを思い出しますねぇ〜。



 

おまけ、念の為ヒントバリバリで固めてみましたw 結果は同じです。 NLJになってくれてます


[master@localhost ~]$ cat test1.sql
use perftestdb;


explain format=tree
select
/*+
JOIN_ORDER(master,detail,t2,t3,t4,t5,t6,t7,t8,t9,t10)
INDEX(master ix_master)
JOIN_INDEX(detail primary)
JOIN_INDEX(t2 primary)
JOIN_INDEX(t3 primary)
JOIN_INDEX(t4 primary)
JOIN_INDEX(t5 primary)
JOIN_INDEX(t6 primary)
JOIN_INDEX(t7 primary)
JOIN_INDEX(t8 primary)
JOIN_INDEX(t9 primary)
JOIN_INDEX(t10 primary)
*/
*
from
master inner join detail
on master.id = detail.id
inner join detail t2
on
t2.id = detail.id
inner join detail t3
on
t3.id = t2.id
inner join detail t4
on
t4.id = t3.id
inner join detail t5
on
t5.id = t4.id
inner join detail t6
on
t6.id = t5.id
inner join detail t7
on
t7.id = t6.id
inner join detail t8
on
t8.id = t7.id
inner join detail t9
on
t9.id = t8.id
inner join detail t10
on
t10.id = t9.id
;

 

 


mysql> \. test1.sql
Database changed
+----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=22518.00 rows=10240)
-> Nested loop inner join (cost=11254.00 rows=5120)
-> Nested loop inner join (cost=5622.00 rows=2560)
-> Nested loop inner join (cost=2806.00 rows=1280)
-> Nested loop inner join (cost=1398.00 rows=640)
-> Nested loop inner join (cost=694.00 rows=320)
-> Nested loop inner join (cost=342.00 rows=160)
-> Nested loop inner join (cost=166.00 rows=80)
-> Nested loop inner join (cost=78.00 rows=40)
-> Nested loop inner join (cost=34.00 rows=20)
-> Index scan on master using ix_master (cost=12.00 rows=10)
-> Index lookup on detail using PRIMARY (id=`master`.id) (cost=2.02 rows=2)
-> Index lookup on t2 using PRIMARY (id=`master`.id) (cost=2.01 rows=2)
-> Index lookup on t3 using PRIMARY (id=`master`.id) (cost=2.00 rows=2)
-> Index lookup on t4 using PRIMARY (id=`master`.id) (cost=2.00 rows=2)
-> Index lookup on t5 using PRIMARY (id=`master`.id) (cost=2.00 rows=2)
-> Index lookup on t6 using PRIMARY (id=`master`.id) (cost=2.00 rows=2)
-> Index lookup on t7 using PRIMARY (id=`master`.id) (cost=2.00 rows=2)
-> Index lookup on t8 using PRIMARY (id=`master`.id) (cost=2.00 rows=2)
-> Index lookup on t9 using PRIMARY (id=`master`.id) (cost=2.00 rows=2)
-> Index lookup on t10 using PRIMARY (id=`master`.id) (cost=2.00 rows=2)
|
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

なんか、Oracle7.0とか、8iとかのドキドキ感があるMySQL 8.0.x と遊ぶのもいいのですが、こればかりやってもいられない。Oraclerですし。PostgreSQLもありますしw ネタは色々湧いてきそうではありまする。。;)

では、また。

 



関連エントリー
帰ってきた! 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る
悩ませ過ぎは及ばざるがごとし #7 - おまけ


yoku0825さんより、MySQL 8.0.xではoptimizerがバッファーブールに乗ってるデータを意識するようになったとの情報が寄せられました。
そこは見逃していた! とはいえ、以前よりHash Joinの適用範囲がさらに拡大?しているような挙動もあり、色々ドキドキしますね。
とはいえ、マニュアルによれば、HASH_JOINは、 (MySQL 8.0.18 のみ。MySQL 8.0.19 以降では無効です) との記載があり現状制御しにくいですね。索引を使えなくしたら倒れてくれるだろうか。。。別途試してみよう(9/5追記)

https://twitter.com/yoku0825/status/1698140599722110976


| | | コメント (0)

2023年9月 2日 (土)

帰ってきた! 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る

Previously on Mac De Oracle前回の癖はw
帰ってきた! 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出る
でした。
実は、今日の癖は、その検証時に気づいた、MySQLで結合表数が多いと、パース時間長くなってね? というか長いよね! という点。

オプティマイザは、皆さんの難しい(いや、難しい必要はないですが)SQLを無駄なく、そのSQLに見合った最短の時間で結果を返そうと努力して実行計画を立てています。これを解析フェーズとかパース時間とか、ハードパース時間とかいくつかのバリエーションで表現していたりします。
今回は、中をとってw パース時間としますね。SQL文の実行計画を立てる、オプティマイザがあれやこれや考えて実行計画を立て終えるまでの時間のことです。

先日の検証で、10表結合したのですが、MySQLでは、20秒以上要していました。実行計画でオプティマイザ考えすぎて帰ってこない。SQL文の実行ではなく実行計画を立てるところで時間がかかるケースはいくつか有名なのがありますが。
そうならないように各データベースは閾値を設けています。今回は、結合順評価テーブル数上限 ってやつです。結合する表の順序の組み合わせをどこまで検討するかということなのですが、これが、票数が増えると鰻登りに増加します。
今回のケースだと、1011表なので、10!11!ですね。2表をを繰り返し結合していてもしっかり考えてくれちゃいます。

ただ、何も考えずにオプティマイザに時間を与えていると、パース時間だけでとんでもない時間になったり、性能要件に見合わないといったケースが出てきます。なので、適当なところで手を打って、オプティマイザに考えさせ過ぎないようしています。
その閾値のデフォルト値が、MySQLとOracle/PostgreSQLとでは結構違うみたい。というところが今回の癖!

ちなみに、Oracleの例ですが、結合以外にINリストに大量にリテラルがあり、かつ考慮する必要のある索引も多いケースでは、悩ませ過ぎは及ばざるがごとし #7 - おまけなんてことも起こりますw オプティマイザを悩ませすぎると大変なので、そんな時は、SQLヒントなどを使って、オプティマイザに考えさせないという治療で回避するのが特効薬ですw

余談はこれぐらいにして、本題へ。

 

Note:
(MySQL/PostgreSQL/Oracle 23c freeそれぞれ、同一Virtualbox VMを利用して検証しているため、CPUリソース等の差異はありません)

最初は、ネタの発端になったMySQL 8.0.32から


mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32 |
+-----------+
1 row in set (0.00 sec)

mysql> show tables;
+----------------------+
| Tables_in_perftestdb |
+----------------------+
| detail |
| master |
+----------------------+
2 rows in set (0.00 sec)

mysql> desc detail;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| subid | int | NO | PRI | NULL | |
| dummya | varchar(10) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show indexes from detail;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| detail | 0 | PRIMARY | 1 | id | A | 10 | NULL | NULL | | BTREE | | | YES | NULL |
| detail | 0 | PRIMARY | 2 | subid | A | 20 | NULL | NULL | | BTREE | | | YES | NULL |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.02 sec)

mysql> desc master;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| dummya | varchar(10) | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show indexes from master;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| master | 0 | PRIMARY | 1 | id | A | 10 | NULL | NULL | | BTREE | | | YES | NULL |
| master | 1 | ix_master | 1 | dummya | A | 10 | NULL | NULL | YES | BTREE | | | YES | NULL |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.02 sec)

mysql> analyze table master;
+-------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| perftestdb.master | analyze | status | OK |
+-------------------+---------+----------+----------+
1 row in set (0.04 sec)

mysql> analyze table detail;
+-------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| perftestdb.detail | analyze | status | OK |
+-------------------+---------+----------+----------+
1 row in set (0.02 sec)

mysql>

 

 

表定義とデータは以前のエントリーも参考に
MySQL 8.0.32 / explain analyze 実行途中でキャンセルできるみたいだけど、キャンセルしたら、Actual Plan、途中まで出るの?

 

では、

MySQL 8.0.32で、パースに時間を要するケースの再現。optimizer_search_depthパラメータはデフォルトのまま

 

MySQL : optimizer_search_depth optimizer_search_depth


mysql> show variables like 'optimizer_search_depth';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| optimizer_search_depth | 62 |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> \. test1.sql
Database changed
+----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=7162.25 rows=10240)
-> Nested loop inner join (cost=3578.25 rows=5120)
-> Nested loop inner join (cost=1786.25 rows=2560)
-> Nested loop inner join (cost=890.25 rows=1280)
-> Nested loop inner join (cost=442.25 rows=640)
-> Nested loop inner join (cost=218.25 rows=320)
-> Nested loop inner join (cost=106.25 rows=160)
-> Nested loop inner join (cost=50.25 rows=80)
-> Nested loop inner join (cost=22.25 rows=40)
-> Nested loop inner join (cost=8.25 rows=20)
-> Index scan on master using ix_master (cost=1.25 rows=10)
-> Index lookup on detail using PRIMARY (id=`master`.id) (cost=0.52 rows=2)
-> Index lookup on t2 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t3 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t4 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t5 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t6 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t7 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t8 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t9 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t10 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
|
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (21.37 sec)

パースに 21.37 sec 要していますね。 62!まで結合順を探査する設定ですから、このケースだと、10!まで行っているということなのでしょうね。

では、Oracleなどで設けられてる閾値と同等の 6 にせってしてみた場合のパース時間はどうでしょうね。このケースで実行計画を間違うことはない(単純なので)ですが、複雑な結合だと、実行計画をミスする率は多くなりそうですけども、Oracleとかだとその程度で使ってますし。。この程度でも良いのかもしれません。時間かかってもいいよというケースも当然あるので、非機能要件次第ではありますけどね。


mysql> set optimizer_search_depth = 6;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'optimizer_search_depth';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| optimizer_search_depth | 6 |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> \. test1.sql
Database changed
+----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=7162.25 rows=10240)
-> Nested loop inner join (cost=3578.25 rows=5120)
-> Nested loop inner join (cost=1786.25 rows=2560)
-> Nested loop inner join (cost=890.25 rows=1280)
-> Nested loop inner join (cost=442.25 rows=640)
-> Nested loop inner join (cost=218.25 rows=320)
-> Nested loop inner join (cost=106.25 rows=160)
-> Nested loop inner join (cost=50.25 rows=80)
-> Nested loop inner join (cost=22.25 rows=40)
-> Nested loop inner join (cost=8.25 rows=20)
-> Index scan on master using ix_master (cost=1.25 rows=10)
-> Index lookup on detail using PRIMARY (id=`master`.id) (cost=0.52 rows=2)
-> Index lookup on t2 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t3 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t4 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t5 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t6 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t7 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t8 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t9 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t10 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
|
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.27 sec)

はい! パース時間は、一気短縮され、 0.27 sec になりました。Oracleのハードパースでもこれぐらいなこんな程度だとは思います。

 

次に、PostgreSQLで確認してみましょ。 PostgreSQLの場合は、Oracleに近かったはず。表定義とデータ量は同じです。

PostgreSQLでは、結合順序評価上限は以下join_collapse_limitパラメータで制御するようですね。あまり気にしてなかったので脳のしわが一つ増えた! defaultは、8 でOracleより多少、多いというところですね。
join_collapse_limit

 


perftestdb=> select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit
(1 行)

perftestdb=> show join_collapse_limit;
join_collapse_limit
---------------------
8
(1 行)

perftestdb=> \d+ master
テーブル"public.master"
列 | タイプ | 照合順序 | Null 値を許容 | デフォルト | ストレージ | 統計目標 | 説明
--------+-----------------------+----------+---------------+------------+------------+----------+------
id | integer | | not null | | plain | |
dummya | character varying(10) | | | | extended | |
インデックス:
"master_pkey" PRIMARY KEY, btree (id)
アクセスメソッド: heap

perftestdb=> \d+ detail
テーブル"public.detail"
列 | タイプ | 照合順序 | Null 値を許容 | デフォルト | ストレージ | 統計目標 | 説明
--------+-----------------------+----------+---------------+------------+------------+----------+------
id | integer | | not null | | plain | |
subid | integer | | not null | | plain | |
dummya | character varying(10) | | | | extended | |
インデックス:
"pk_detail" PRIMARY KEY, btree (id, subid)
アクセスメソッド: heap

perftestdb=> analyze verbose master;
INFO: analyzing "public.master"
INFO: "master": scanned 1 of 1 pages, containing 10 live rows and 0 dead rows; 10 rows in sample, 10 estimated total rows
ANALYZE
perftestdb=> analyze verbose detail;
INFO: analyzing "public.detail"
INFO: "detail": scanned 1 of 1 pages, containing 20 live rows and 0 dead rows; 20 rows in sample, 20 estimated total rows
ANALYZE
perftestdb=>

 

では試してみましょう。


perftestdb=> \timing on
タイミングは on です。
perftestdb=>
perftestdb=>
perftestdb=> \i ./test1_pg.sql
QUERY PLAN
-----------------------------------------------------------------------------------------------
Hash Join (cost=19.53..156.40 rows=10240 width=116)
Hash Cond: (master.id = t8.id)
-> Hash Join (cost=12.93..31.40 rows=1280 width=83)
Hash Cond: (master.id = detail.id)
-> Hash Join (cost=6.33..10.00 rows=160 width=50)
Hash Cond: (master.id = t4.id)
-> Hash Join (cost=2.67..4.45 rows=40 width=28)
Hash Cond: (master.id = t7.id)
-> Hash Join (cost=1.23..2.50 rows=20 width=17)
Hash Cond: (t6.id = master.id)
-> Seq Scan on detail t6 (cost=0.00..1.20 rows=20 width=11)
-> Hash (cost=1.10..1.10 rows=10 width=6)
-> Seq Scan on master (cost=0.00..1.10 rows=10 width=6)
-> Hash (cost=1.20..1.20 rows=20 width=11)
-> Seq Scan on detail t7 (cost=0.00..1.20 rows=20 width=11)
-> Hash (cost=3.15..3.15 rows=40 width=22)
-> Hash Join (cost=1.45..3.15 rows=40 width=22)
Hash Cond: (t4.id = t5.id)
-> Seq Scan on detail t4 (cost=0.00..1.20 rows=20 width=11)
-> Hash (cost=1.20..1.20 rows=20 width=11)
-> Seq Scan on detail t5 (cost=0.00..1.20 rows=20 width=11)
-> Hash (cost=5.60..5.60 rows=80 width=33)
-> Hash Join (cost=2.90..5.60 rows=80 width=33)
Hash Cond: (detail.id = t3.id)
-> Hash Join (cost=1.45..3.15 rows=40 width=22)
Hash Cond: (detail.id = t2.id)
-> Seq Scan on detail (cost=0.00..1.20 rows=20 width=11)
-> Hash (cost=1.20..1.20 rows=20 width=11)
-> Seq Scan on detail t2 (cost=0.00..1.20 rows=20 width=11)
-> Hash (cost=1.20..1.20 rows=20 width=11)
-> Seq Scan on detail t3 (cost=0.00..1.20 rows=20 width=11)
-> Hash (cost=5.60..5.60 rows=80 width=33)
-> Hash Join (cost=2.90..5.60 rows=80 width=33)
Hash Cond: (t8.id = t10.id)
-> Hash Join (cost=1.45..3.15 rows=40 width=22)
Hash Cond: (t8.id = t9.id)
-> Seq Scan on detail t8 (cost=0.00..1.20 rows=20 width=11)
-> Hash (cost=1.20..1.20 rows=20 width=11)
-> Seq Scan on detail t9 (cost=0.00..1.20 rows=20 width=11)
-> Hash (cost=1.20..1.20 rows=20 width=11)
-> Seq Scan on detail t10 (cost=0.00..1.20 rows=20 width=11)
(41 行)

時間: 29.117 ミリ秒
perftestdb=>

 

MySQLで上限を制限した時間と似たような時間でパースが行われていますね。ただ、HASH JOINが選択されていますがw この辺りも、MySQLの生い立ちと、PostgreSQL、そしてOracleの違いとして現れてきます。MySQL 8のHash Joinって、MySQL 8.0.20 でHASH JOINが効くケースが拡大したという元々、Block Nested Loopの置き換えとしての意味が強かったという生い立ちが影響しているようにも見えます。なので、等価結合のINNER JOIN(今回の例)の場合、MySQL 8.0.32でもNLJが選択されているということのようですね。似てるようで似てないRDBMSの世界。まだまだ、私も学びが必要ですね。ちょっと曲のあるMySQLのHash Joinの発動条件。。いずれPostgreSQLやOracleっぽくHash Joinをカジュアルに使えちゃう日が来るのだろうか。。。

 

最後に真打w Oracle Database

 

今回は、23c Freeで試します。ただし、前回のエントリーにあるように、23c freeでは、"_optimizer_max_permutations" = 300と少なく設定されています。
今回の確認では、正式リリースされれば、通常の 2000 になるだろう。という想定で、 2000 にしてハードパース時間を確認しておきます。


SCOTT@freepdb1> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

 

デフォルトの設定はいかですが、.sqlスクリプト内で、 _optimizer_max_permutations を 2000 に設定して実行します!


parameter name                   parameter value
-------------------------------- ------------------------------
_optimizer_max_permutations 300
_optimizer_search_limit 5


SCOTT@freepdb1> desc master
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
DUMMYA VARCHAR2(10)

SCOTT@freepdb1> desc detail
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
SUBID NOT NULL NUMBER
DUMMYA VARCHAR2(10)

SCOTT@freepdb1> select table_name,index_name,column_name from user_ind_columns where table_name in ('MASTER','DETAIL') order by table_name,index_name,column_position;

TABLE_NAME INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
DETAIL PK_DETAIL ID
DETAIL PK_DETAIL SUBID
MASTER SYS_C008265 ID

SCOTT@freepdb1> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'MASTER',cascade=>true,no_invalidate=>false);

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:01.08
SCOTT@freepdb1> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'DETAIL',cascade=>true,no_invalidate=>false);

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:00.13

では、試してみましょう!


SCOTT@freepdb1> @test1_ora

セッションが変更されました。

経過: 00:00:00.01

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_optimizer_max_permutations integer 2000
1* alter system flush shared_pool

システムが変更されました。

経過: 00:00:00.68

解析されました。

経過: 00:00:00.27

 

一般的なOracleの_optimizer_max_permutationsパラメータの設定で、 0.27 sec となりました. こんなもんでしょうね。- this is an adaptive plan となっているので、ACTUAL PLANを見ないと確定できないケースですが、多分
Hash joinが選ばれていることでしょうw(なので確認まではしませんが)

 

ついでなので、実行計画(見積もり)を見ておきましょう。 hash joinになってますね。一部、SORT MERGE JOINになってますが、


SCOTT@freepdb1> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 3620033460

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10240 | 950K| 33 (4)| 00:00:01 |
|* 1 | HASH JOIN | | 10240 | 950K| 33 (4)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DETAIL | 20 | 180 | 3 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 5120 | 430K| 30 (4)| 00:00:01 |
| 4 | TABLE ACCESS FULL | DETAIL | 20 | 180 | 3 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 2560 | 192K| 27 (4)| 00:00:01 |
| 6 | TABLE ACCESS FULL | DETAIL | 20 | 180 | 3 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 1280 | 87040 | 24 (5)| 00:00:01 |
| 8 | TABLE ACCESS FULL | DETAIL | 20 | 180 | 3 (0)| 00:00:01 |
|* 9 | HASH JOIN | | 640 | 37760 | 21 (5)| 00:00:01 |
| 10 | TABLE ACCESS FULL | DETAIL | 20 | 180 | 3 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 320 | 16000 | 18 (6)| 00:00:01 |
| 12 | TABLE ACCESS FULL | DETAIL | 20 | 180 | 3 (0)| 00:00:01 |
|* 13 | HASH JOIN | | 160 | 6560 | 15 (7)| 00:00:01 |
|* 14 | HASH JOIN | | 80 | 2560 | 12 (9)| 00:00:01 |
|* 15 | HASH JOIN | | 40 | 920 | 9 (12)| 00:00:01 |
| 16 | MERGE JOIN | | 20 | 280 | 6 (17)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID| MASTER | 10 | 50 | 2 (0)| 00:00:01 |
| 18 | INDEX FULL SCAN | SYS_C008265 | 10 | | 1 (0)| 00:00:01 |
|* 19 | SORT JOIN | | 20 | 180 | 4 (25)| 00:00:01 |
| 20 | TABLE ACCESS FULL | DETAIL | 20 | 180 | 3 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | DETAIL | 20 | 180 | 3 (0)| 00:00:01 |
| 22 | TABLE ACCESS FULL | DETAIL | 20 | 180 | 3 (0)| 00:00:01 |
| 23 | TABLE ACCESS FULL | DETAIL | 20 | 180 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------


Note
-----
- this is an adaptive plan

174行が選択されました。

経過: 00:00:00.71

MySQL/PostgreSQL/Oracleそれぞれ、ハードパースの結合順評価テーブル数上限にも癖というか違いがあって、かつ、それが、パース時間の差になってみたり。癖が多いですよね。それぞれの特徴というか。
ついでに、実行計画、MySQLのHash Joinの発動ケース。現状OracleやPostgreSQLのようには使えないので、その辺りは、認識しておいた方が良いですよね。癖として ;)

8月末に東北に居たのですが、例年だと朝晩は涼しくて、過ごしやすいのに、今年は、ダメですね。東京と同じです。農作物への影響が気になりますね。寒暖の差が美味しさに影響するのもありますからね。。。

 

では、また。

 

次回は、OracleのSQLヒントネタでもしようかと思ってます。Oracleのヒントレポートが出力されるようになって、そこ気になる、なぜ。みたいな方も多かったり、それにより無用な議論があったりと聞いているのでw 一応書いておこうかなと。

 



MySQL/PostgreSQL/Oracleで利用したスクリプト(テーブル、索引定義およびデータ取得と統計取得コマンドは本文参照のこと)

MySQL


[master@localhost ~]$ cat test1.sql
use perftestdb;


explain
select *
from
master inner join detail
on master.id = detail.id
inner join detail t2
on
t2.id = detail.id
inner join detail t3
on
t3.id = t2.id
inner join detail t4
on
t4.id = t3.id
inner join detail t5
on
t5.id = t4.id
inner join detail t6
on
t6.id = t5.id
inner join detail t7
on
t7.id = t6.id
inner join detail t8
on
t8.id = t7.id
inner join detail t9
on
t9.id = t8.id
inner join detail t10
on
t10.id = t9.id
;

 

PostgreSQL


[master@localhost ~]$ cat test1_pg.sql
explain
select *
from
master inner join detail
on master.id = detail.id
inner join detail t2
on
t2.id = detail.id
inner join detail t3
on
t3.id = t2.id
inner join detail t4
on
t4.id = t3.id
inner join detail t5
on
t5.id = t4.id
inner join detail t6
on
t6.id = t5.id
inner join detail t7
on
t7.id = t6.id
inner join detail t8
on
t8.id = t7.id
inner join detail t9
on
t9.id = t8.id
inner join detail t10
on
t10.id = t9.id
;

 

Oracle


[master@localhost ~]$ cat test1_ora.sql
alter session set "_optimizer_max_permutations"=2000;
show parameter "_optimizer_max_permutations"

alter system flush shared_pool
.
l
/


explain plan for
select *
from
master inner join detail
on master.id = detail.id
inner join detail t2
on
t2.id = detail.id
inner join detail t3
on
t3.id = t2.id
inner join detail t4
on
t4.id = t3.id
inner join detail t5
on
t5.id = t4.id
inner join detail t6
on
t6.id = t5.id
inner join detail t7
on
t7.id = t6.id
inner join detail t8
on
t8.id = t7.id
inner join detail t9
on
t9.id = t8.id
inner join detail t10
on
t10.id = t9.id
;

 



関連エントリー
Oracle Database 23c Free Developer Releaseの”_optimizer_max_permutations” parameterの設定値について

 


標準はあるにはあるが癖の多い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取得中のキャンセルでも癖が出る

| | | コメント (0)

DTM/GarageBand (N + 1 Loops ) : 8月にリリースした曲

ということで、最近のルーティーンw

DTM/GarageBandでリリースした曲、いつもより多めでしたwww


Jangly Loops / N + 1 Loops - 2023/08/05

ジャングル的なイメージのリズム作りたくて試行錯誤してて何となくできちゃったやつですねw EDMはEDMですけど、最初にイメージしてたのとは違うというよくある結果にw




Savannah Loops / N + 1 Loops - 2023/08/10

ジャングル的なイメージがまだ残っててw、さらに何となくループを置いていたら前のよりいい感じのジャングル感というかサバンナ感になりましたw




Nested Loops - Ver 2 / N + 1 Loops - 2023/08/13

以前公開してた Nested Loops / N + 1 Loopsにかなり手を入れて作り直した?w Version.2. アジャイルDTMなので、気になってるところは残ったまま公開しつつ、後で手を入れて公開し直すのもありということで。




Fireworks Loops / N + 1 Loops - 2023/08/17

ファンク的なところに戻ってきて、夏は花火よな〜と思いながら、スネアの単音にバーーーんって感じのスペース感追加w




Sunset Loops / N + 1 Loops - 2023/08/23

これのベースラインというかリズムセクション作ってる途中でこれを元にDub Version作れそうって思いつつ、夕陽的なイメージにして先に仕上げた曲




Sunny Rain Loops Ver 3 - "Dub Version of Sunset Loops" / N + 1 Loops - 2023/08/31

これ、Version.3なので、3回公開し直してますw ベースラインのチョップし直しがメインなのですが、Ver.1/2もMakingのPlaylistには限定公開で貼り付けてるので、気になる方は聞いてみくださいw 気にならんかもしれないですがw




8月は本業もそれなりに忙しかったのに、なんでこんなに曲作ってるんだ、俺。

と思いつつ、9月になっていたのに気づいたので、やっぱり、それなりに忙しいんだな。w

| | | コメント (0)

2023年9月 1日 (金)

Oracle Database 23c Free Developer Releaseの”_optimizer_max_permutations” parameterの設定値について

Previously on Mac De Oracle..
前回のお話は、帰ってきた! 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出るでした。Oracle/PostgreSQL/MySQLそれぞれ、再帰問合せで試してみましたが、興味深い違いが出ました。思わず、PostgreSQLのソースコード読み始めてしまいましたwwww

ということで、本日は、その流れで気づいてOracle 23c Freeのひみつ!


たまたま気づいたのですけど、 Oracle Database 23c Free Developer Release で、
under scored parameterの "_optimizer_max_permutations" って、 300なんですね。随分少ない設定にされていました。

これ

5! (120) < 6! (720) ということを意味するので、6表以上の結合では、通常のOracleより実行計画をミスりやすいということを意味しています。
(なぜなのでしょう、Free Developer Releaseだからではないか? というコメントももらいましたが、そうなのですかねぇ。でもそうかもしれないw。理由はなぜなのかわからんので何とも言えないですね)
なので、多数の結合を伴うSQLの検証には注意した方が良いですね。もしくは、通常の 2000 ぐらいまであげて試すとかしておいた方が良いかもね。
FreeのDeveloper Releaseだから 300 になっているのか確認できる資料は見つからなかったけど。


念の為、調べてみるとやはり、23c Free Developer Releaseのみ少なく設定されていました。

SYS@free> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

parameter name parameter value
-------------------------------- ------------------------------
_optimizer_max_permutations 300
_optimizer_search_limit 5



BANNER
----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

parameter name parameter value
------------------------------ ------------------------------
_optimizer_max_permutations 2000
_optimizer_search_limit 5


BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

parameter name parameter value
------------------------------ ------------------------------
optimizer_max_permutations 2000
_optimizer_search_limit 5


BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

parameter name parameter value
------------------------------ ------------------------------
_optimizer_max_permutations 2000
_optimizer_search_limit 5


BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

parameter name parameter value
------------------------------ ------------------------------
_optimizer_max_permutations 2000
_optimizer_search_limit 5


Oracle 23c の正式版がリリースされたら皆さんも、2000になっているか確認しましょうね!(多分、2000になっていると思うけどw)

東京の8月が毎日真夏日だったなんで、話題。来年は毎日、猛暑日じゃなければ良いのですけども。。。
残暑厳しい東京からお送りしました。

では、また。

| | | コメント (0)