Mac De Oracle 2022年最後のエントリーは、MacネタというかDTMネタで :)
あれから2ヶ月ということでw その後に追加した曲も含めたランキング
5位
Number Of Loops - Club Edition / N + 1 Loops / 416回再生
4位
Memories Loops / N + 1 Loops / 418回再生
3位
Waterfall Loops - Dance Edition / N + 1 Loops / 455回再生
2位
Loops in Clouds / N + 1 Loops / 476回再生
1位
Rain Loops / N + 1 Loops / 799回再生
Previously on Mac De Oracle
SQL*Plus -Fastオプション / FAQでした。
今日は、結合排除のバリエーションをいくつか紹介しておこうと思います。
なんどか説明していた無駄に結合してないですよね？ の例は、参照整合性制約を頼りに結合排除が行われるものでした。
今日はそれに加えて、典型的な例を2つ紹介しておきたいと思います。（ここ” ”、試験に出ませんよ！w でないけど大切：）
一つ目は、結合列がそれぞれユニークキーかプライマリキーで一意で、1 : 0..1 で外部結合されるケース
結合の排除は、その結合を排除しても結果に影響しなことが自明な場合に発動するので、条件を満たしています。ただ、参照整合性制約のパターン同様に、SQL文を見ただけでは気づけないですよね。
あれ、実行計画にSQL文に記載されている表がない！？ で制約を見てみて、あ”〜〜〜〜理解。みたいなw
実行計画ではSQLモニターも含め、Join Eliminationしたことを明示的に示すコメント等はありません。結合が消えていることで気づくことが多いわけですw（よーく考えたら、その結合イラねーじゃんというわけですけどもw0
1* CREATE TABLE foo (id NUMBER PRIMARY KEY, note VARCHAR2(100))
表が作成されました。
1* CREATE TABLE bar (id NUMBER PRIMARY KEY, memo VARCHAR2(100))
表が作成されました。
1 SELECT
2 foo.id
3 , foo.note
4 FROM
5 foo
6 LEFT OUTER JOIN bar
7 ON
8* foo.id = bar.id
実行計画
----------------------------------------------------------
Plan hash value: 1245013993
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| FOO | 1 | 65 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
この手の制約による結合の排除は、前提になる制約が結合排除の条件からはずれると排除されなくなります。
たとえば、前述の例で、それぞれの表を主キーで 1:0..1 で外部結合していましたよね？
それが仕様変更で、片方の方の主キーが複合主キーになってしまい。1:* の外部結合になってしまうと。。。。結合排除できなくなります。排除した場合、クエリー結果に影響するから。
1 CREATE TABLE foo2 (
2 id NUMBER
3 , note VARCHAR2(100)
4 , PRIMARY KEY (id)
5* )
表が作成されました。
1 CREATE TABLE bar2 (
2 id NUMBER
3 , sq NUMBER NOT NULL
4 , memo VARCHAR2(100)
5 , PRIMARY KEY (id, sq)
6* )
表が作成されました。
1 SELECT
2 foo2.id
3 , foo2.note
4 FROM
5 foo2
6 LEFT OUTER JOIN bar2
7 ON
8* foo2.id = bar2.id
実行計画
----------------------------------------------------------
Plan hash value: 3679270243
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER| | 1 | 78 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| FOO2 | 1 | 65 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C009222 | 1 | 13 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FOO2"."ID"="BAR2"."ID"(+))
Previously on Mac De Oracle.
前回は、アドベントカレンダーのおまけのおまけwでした
今日は、そこで仕込んでおいたネタを使い、SQL*Plusも機能拡張されてたのすっかり忘れていた！ ので、
高Fetch圧症の話に絡めてSQL*Plusの-F[ast]オプション書いておこうと思います。
軽めですが。
Fetch回数削減に効果があるので、多数の行をFetchするような時は思い出すと良いですね。
Client/Server間のrount tripが減ることに繋がるわけで、そこが慢性病の原因なら少しでも楽になれたら良いと思いますし。
（ということで、Fetch Sizeも忘れないでね。という気持ちを込めて。）
年を跨いで, ”実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 / No.61 / ANSI JOINのおまけ”のおまけです.
前回の投稿から間隔が空いていたので, まずは, 簡単な復習から.
ANSI構文のON句の結合条件でORが利用されているという, スーパータイプ, サブタイプテーブルの実装崩れというか, 大人の事情に押し切られて負けた感じありありの半端な状態.
あ, そうだ, Oralceの外部結合だとOR使えないけど, ANSIなら使えるじゃん！
という流れを感じるSQL文を, Oracleのオプティマイザは, LATERALへの書き換え(VW_LAT_E87C3AAF)や, OR EXPANDの書き換え(VW_ORE_FDF394AE)を駆使して, 物凄い最適化を行っていました.
この例では, 外部表, 内部表の多重度は, 1:0..1. かつ, スーパータイプ, サブタイプでいうところの不完全なサブタイプ.
さらに, 内部表は, 単純にニコイチにしただけのようなサブタイプテーブルで外部表との結合列が2列（おそらく本来同一列に統合されていただろう. . と思われる）ある. 惜しい！という感じのモデル.
比較的軽度のモデリング障害ではあるので, このまま使うのであれば, LATERAL変換されるのを避けるような書き換え, 比較的単純な HASH JOIN なるようにすればそこそこ改善できそうな感じはしますよね（いわゆるTemp落ちはある程度発生する前提で）
なお, この例で AUTO TRACEでの実行時間と, SQL MONITORの実行時間（DB内部）に差異があることに気づいた方もいると思いますが. これ, クライアントがデータをFETCHしている時間ですね. 行数が多いので. SQL*Plusの場合, デフォルトのFETCH SIZEが15なので行数が多いと, FETCHの際, サーバーからの受信で時間がかかります.
（この症状は以前, 高フェッチ圧症として紹介したこともあるので, 覚えている方も多いのではないかと思います. この例では1行の行サイズも大きめかつ, 行数も多めにして SELECT * にしているのでそこそこ目立つ時間になるようにしています. これも別のエントリーでネタにするための仕込みではあるのですが, 今回の記事では気にしないでください. SQLモニターのサーバー内部での純粋な処理時間だけで, 書き換え前後での差を見て行きます！）
SCOTT@orclpdb1> @dayx
1 SELECT *
2 FROM
3 supertype st
4 LEFT OUTER JOIN nikoichi_mitaina_subtype nmst
5 ON
6 st.pkey = nmst.col1
7* OR st.pkey = nmst.col2
10001行が選択されました.
経過: 00:00:01.41
実行計画
----------------------------------------------------------
Plan hash value: 2133431102
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20002 | 20M| 40226 (1)| 00:00:02 |
| 1 | MERGE JOIN OUTER | | 20002 | 20M| 40226 (1)| 00:00:02 |
| 2 | TABLE ACCESS FULL | SUPERTYPE | 10001 | 4971K| 204 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 2 | 1082 | 40021 (1)| 00:00:02 |
| 4 | VIEW | VW_LAT_E87C3AAF | 2 | 1082 | 4 (0)| 00:00:01 |
| 5 | VIEW | VW_ORE_FDF394AE | 2 | 1082 | 4 (0)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| NIKOICHI_MITAINA_SUBTYPE | 1 | 1009 | 2 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | UK1 | 1 | | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| NIKOICHI_MITAINA_SUBTYPE | 1 | 1009 | 2 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | UK2 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("ST"."PKEY"="NMST"."COL1")
filter("NMST"."COL1" IS NOT NULL)
9 - filter(LNNVL("ST"."PKEY"="NMST"."COL1"))
10 - access("ST"."PKEY"="NMST"."COL2")
filter("NMST"."COL2" IS NOT NULL)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
32231 consistent gets
0 physical reads
0 redo size
15493776 bytes sent via SQL*Net to client (別エントリ向け)
7378 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client (別エントリ向け)
10001 sorts (memory)
0 sorts (disk)
10001 rows processed
1 SELECT /*+ MONITOR */ *
2 FROM
3 supertype st
4 LEFT OUTER JOIN nikoichi_mitaina_subtype nmst
5 ON
6 st.pkey = nmst.col1
7* OR st.pkey = nmst.col2
DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>NULL,TYPE=>'TEXT')
--------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
SQL Text
------------------------------
SELECT /*+ MONITOR */ * FROM supertype st LEFT OUTER JOIN nikoichi_mitaina_subtype nmst ON st.pkey = nmst.col1 OR st.pkey = nmst.col2
Global Information
------------------------------
Status : DONE (ALL ROWS)
...略...
Duration : 3s（別ネタ向け仕込み）
...略...
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.37 | 0.31 | 0.06 | 668 | 32231 |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=2133431102)
=====================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
=====================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 4 | +0 | 1 | 10001 | . | | |
| 1 | MERGE JOIN OUTER | | 20002 | 40226 | 4 | +0 | 1 | 10001 | . | | |
| 2 | TABLE ACCESS FULL | SUPERTYPE | 10001 | 204 | 4 | +0 | 1 | 10001 | . | | |
| 3 | BUFFER SORT | | 2 | 40021 | 4 | +0 | 10001 | 10000 | 2048 | | |
| 4 | VIEW | VW_LAT_E87C3AAF | 2 | 4 | 4 | +0 | 10001 | 10000 | . | | |
| 5 | VIEW | VW_ORE_FDF394AE | 2 | 4 | 4 | +0 | 10001 | 10000 | . | | |
| 6 | UNION-ALL | | | | 4 | +0 | 10001 | 10000 | . | | |
| 7 | TABLE ACCESS BY INDEX ROWID | NIKOICHI_MITAINA_SUBTYPE | 1 | 2 | 4 | +0 | 10001 | 5000 | . | | |
| 8 | INDEX UNIQUE SCAN | UK1 | 1 | 1 | 4 | +0 | 10001 | 5000 | . | | |
| 9 | TABLE ACCESS BY INDEX ROWID | NIKOICHI_MITAINA_SUBTYPE | 1 | 2 | 4 | +0 | 10001 | 5000 | . | | |
| 10 | INDEX UNIQUE SCAN | UK2 | 1 | 1 | 4 | +0 | 10001 | 5000 | . | | |
=====================================================================================================================================================================
では, 書き換えて, LATERAL変換を避け, HASH JOINになるようにしてみましょう. (WITH句を利用していますが, 再利用ではなく読みやすさ狙いです. Oracleもそれを理解できるのでインラインビューとして扱われます)
今回のようなデータモデル障害の場合は, 治療もシンプルで良いのですがw（例に取り上げるのがメンドクサイやつだと, 解説するのもメンドクサイし良いことないので）
現場どのようになっているかを理解する必要があります. この例では, col1列とcol2列は実は同一列で良いだろうということになるので, 以下のように書き換えれば, JOIN ON ... OR なんて現時点のオプティマイザでは, ほぼ危険な感じしかしない実行計画になるようなSQLへの書き換えも回避できるのではないでしょうか？
結果は見ての通り, 別エントリ向けの仕込みであるFETCH時間を除いたデータベース内部のみの処理時間は, 0.37sec から 0.15secと62%ほど改善しています（ただ, このデータ量で私の環境だとPGA内に収まっているのでTemp落ちの影響は見えないですね. オンメモリなら勝ちは確実ですが）
1 WITH
2 t1 AS
3 (
4 SELECT
5 pkey
6 , CASE
7 WHEN col2 IS NULL
8 THEN col1
9 ELSE col2
10 END AS join_key
11 ,description
12 FROM
13 nikoichi_mitaina_subtype
14 WHERE
15 col1 IS NOT NULL
16 OR col2 IS NOT NULL
17 )
18 SELECT *
19 FROM
20 supertype st
21 LEFT OUTER JOIN t1
22 ON
23* st.pkey = t1.join_key
10001行が選択されました.
経過: 00:00:01.28
実行計画
----------------------------------------------------------
Plan hash value: 2223315184
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10001 | 14M| | 1224 (1)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 10001 | 14M| 5096K| 1224 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| SUPERTYPE | 10001 | 4971K| | 204 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| NIKOICHI_MITAINA_SUBTYPE | 7500 | 7390K| | 410 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ST"."PKEY"=CASE WHEN (ROWID(+) IS NOT NULL) THEN CASE WHEN ("COL2"(+) IS
NULL) THEN "COL1"(+) ELSE "COL2"(+) END ELSE NULL END )
3 - filter("COL1"(+) IS NOT NULL OR "COL2"(+) IS NOT NULL)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
2748 consistent gets
0 physical reads
0 redo size
15483707 bytes sent via SQL*Net to client（別ネタ向け仕込み）
7378 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client（別ネタ向け仕込み）
0 sorts (memory)
0 sorts (disk)
10001 rows processed
1 WITH
2 t1 AS
3 (
4 SELECT
5 pkey
6 , CASE
7 WHEN col2 IS NULL
8 THEN col1
9 ELSE col2
10 END AS join_key
11 ,description
12 FROM
13 nikoichi_mitaina_subtype
14 WHERE
15 col1 IS NOT NULL
16 OR col2 IS NOT NULL
17 )
18 SELECT /*+ MONITOR */ *
19 FROM
20 supertype st
21 LEFT OUTER JOIN t1
22 ON
23* st.pkey = t1.join_key
DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>NULL,TYPE=>'TEXT')
----------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
SQL Text
------------------------------
WITH t1 AS ( SELECT pkey , CASE WHEN col2 IS NULL THEN col1 ELSE col2 END AS join_key ,description
FROM nikoichi_mitaina_subtype WHERE col1 IS NOT NULL OR col2 IS NOT NULL ) SELECT /*+ MONITOR */ *
FROM supertype st LEFT OUTER JOIN t1 ON st.pkey = t1.join_key
Global Information
------------------------------
Status : DONE (ALL ROWS)
...略...
Duration : 2s（別ネタ向け仕込み）
...略...
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.15 | 0.13 | 0.02 | 668 | 2748 |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=2223315184)
======================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
======================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 3 | +0 | 1 | 10001 | . | | |
| 1 | HASH JOIN OUTER | | 10001 | 1224 | 3 | +0 | 1 | 10001 | 7MB | | |
| 2 | TABLE ACCESS FULL | SUPERTYPE | 10001 | 204 | 1 | +0 | 1 | 10001 | . | | |
| 3 | TABLE ACCESS FULL | NIKOICHI_MITAINA_SUBTYPE | 7500 | 410 | 3 | +0 | 1 | 10000 | . | | |
======================================================================================================================================================
テレビ東京で放送された「復讐の未亡人」をNetflixで一気見した後に勢いで作ったGarageBandのApple LoopsとChopで打ち込みなしで作った16曲目w
ドラマの影響まんま受けたので、まあ、そんな感じになっております（Studio DisplayのスピーカやBOSE Sound Link IIとかイヤホンだとそうでもないけど、MBAのスピーカだとVOX系の音のエコーというか音が目立ちすぎるのに後で気づいた。いろんなスピーカー特性を気にしつつ、いい感じの音にするのって難しいなあぁと思ったりした）
GarageBandの画面の映像はこちら。
イントロのベルは、1音毎にチョップして、左右に振り分けるようにした。
明けまして、おめでとうございます。新年一発目は、Mac / GarageBandネタから。(SQLネタまた別の日にw）
年末ギリギリから一昨日までに色々作りたくなり、4曲公開してしまったw（短い年末年始でしたが、まあまあ楽しめたような気がします）
では、昨年末ギリギリに公開した曲から順に。
70s後半から80s前半ぐらいまでの雰囲気のディスコっぽいイメージで何か作りたくなり作った1曲。
まあ、そんな感じですw
2022/12/28 released. 80s Loops / N + 1 Loops
HOUSE風な何か、かつ、ドライブで無限ループ再生して聴けそうな何かを、狙ってたらこうなってしまった曲w
春になったら山坂道への行き帰りに使えればいいかなと。
2023/1/2 released. Haze Loops / N + 1 Loops
えーっと、ハードロックなのないなぁ。まだ。と思って作ってみた1曲。そんな雰囲気を味わってもらえれば:)
フリー映像素材とどう合わせるか、悩みまくった結果、こうなってしまったリックス。
2023/1/4 released. Hard Code Loops / N + 1 Loops
そして、寒すぎて、寒すぎ〜る〜とガクガクしているところへ、友人のFBが共有した昨年クリスマスのスキューバダイビング中のオーストラリアの画像を見て。
ん〜、なんか、まったりした日向で、メローな曲でもあったら気分変わるかと。思いつつ、で、Musicで無限ループ再生できるような構成にしてみた曲。
2023/1/7 released. Mellow Loops / N + 1 Loops
そろそろ、SoundCloudにアカウント作って遊んだ方が良いのかなぁ。と思ったり。
では、また :)
3ヶ月前から、GarageBandのAppleLoopsとそれをチョップするだけで作り上げるDTMみたいなことにハマってましてw （打ち込みとかMIDIを利用した録音もなし）
YouTubeで N + 1 Loops と言う性能に関わっている方ならピクっとするような名で、12曲（テスト含めるともっとありますが）リリースしました。（帰ってきた！ 実行計画は、SQL文のレントゲン写真だ！ Oracle Database (全部俺） Advent Calendar 2022のネタに苦しんでいる最中にw）
と言うことで、2022年の締めくくりは、リリースした曲の中から再生回数だけのTop5を選んで締めくくりたいと思いますw
ちなみに、GarageBand進化しすぎてて、すげーなと言う話は、GrageBandの進化はすごいね（多分、周回遅れwを参考にしてください。
では早速！（2022/12/31 9:30時点結果です）
第５位
Loops in Clouds / N + 1 Loops : 2022/11/21 released 再生回数: 323
これ、スラップベースで何か作りたいと思って、AppleLoopsを探し回りやっと見つけた〜！ベースラインをチョップしまくって作ったやつです。自分でもお気に入りではありますが、GarageBandは使いこなせてなくて、結構苦しんだ曲でもありました。
ギターは、Nile Rodgers風のがいいなーと思ってたのですが、まあこんな感じにおさまりましたw
久々の、Macネタです
最近、GarageBandで曲を作った後、iMovieでフリー映像コンテンツと合わせYouTubeで公開しているのですが、クロマキー合成できることを知った（何周遅れだろうw）
では、試してみましょう。
いくつか試したところ白黒のもできることに気づく！
使い方は簡単で、背景になる動画の上部にグリーン、ブルー、ブラックの動画を置く。ベースとなる動画と繋がるリンクが現れるのでわかりやすいはず。
その後、赤丸で囲ったドロップダウンメニューから、「ブルー/グリーン」を選択すれば、グリーンとブルー背景の動画は簡単に合成できる。
ブラック背景の動画はそれだけではうまく合成できず、横に並んでいる「クリーンアップ」から消しゴムアイコンを選択して動画の背景になる部分をクリックするとブラック背景の動画もいい感じで合成してくれる。簡単！！
さて, 恒例のアドベントカレンダーのおまけw
Day25で, 性能面で気になる部分のコメントしていた点を思い出してください. INDEX UNIQUE SCANを繰り返している点. 問題にならないなら良いのですが, （データ件数がそれ以上増加しなければ, 安定した処理時間にはなるわけですが....)
データ量次第の危さを感じますとコメントしていた点を思い出してください.
では, 私が, 懸念しているINDEX UNIQUE SCANの回数をSQLモニターという名の内視鏡的な方法で診ていきましょう.
VW_LAT_E87C3AAFのLATERALビューに変換されている操作以降がSUPERTYPE表のヒット件数分繰り返されています. この部分こそ, "データ量次第の危さ”と言った理由です.
Previously on Mac De Oracle...
Day 24は, SQL MACROにフォーカスをあてました. SEMI JOINだろうと思ってた方々 m(_ _)m 元々今日のネタの伏線をはろうとしていたので, いずれにしても, SEMI JOINではなかったのですがw
と, いうことで Day 25！
ついに, アドベントカレンダー 実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022も千秋楽です
最終日ですので, 大人の事情に金縛りに会いながらも, 頑張って作ってしまったと思われる, 稀に, よくみるタイプのモデルを, それぞれのバージョンのOracleオプティマイザが, どう最適化しようと苦労しているのか, 実行計画というレントゲンを通し, 生暖か, いや, 熱い眼差しでワイン片手に, 観察しつつ, アドベントカレンダー 実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022を締めくくりたいと思います. （オチはないかもw）
当医院に担ぎ込まれてきたSQL文を問診してみると, こんな感じに見えました. （スーパータイプ, サブタイプテーブルを大人の事情で無理強いされて出来上がってしまったような危うい雰囲気であります. 本題はモデルの良し悪しではないので, これ以上, ツッコまないよ）
ということで, 稀によく見るタイプの患者さんを図に起こしてみました. ん〜, かなり複雑な事情がありそうですね！
参考までに, スーパータイプ, サブタイプの概念モデルの実装方法についてはいくつかのパターンがあります. サブタイプがニコイチになっている割に, 結合キーが２個というところが大人の事情を強く感じますが.
（スーパータイプ, サブタイプよくわからんという方は, 斜め読みするならスーパータイプ／サブタイプのテーブルへの実装 / hmatsu47が良いかなと思います）
他に, 外傷がないか SQL文 を診ておきましょう, SQL文はこんなでした. ANSI構文でなければ, こうはならないですよね. Oracleの方言では結合条件のORは書けないので, UNIONで書くことになるのですが, Oracleの方言でこれを書こうとすると割と大変です. UNIONで書いた方が割と無難な実行計画に最適化されやすいというのもありますが, さて, ANSI構文ではどうなりますか. .
（SQLは雰囲気が伝わるように作ってありますw）
SELECT *
FROM
supertype st
LEFT OUTER JOIN nikoichi_mitaina_subtype nmst
ON
st.pkey = nmst.col1
OR st.pkey = nmst.col2
;
では, 実行計画という名のレントゲンを11gR1, 11gR2, 12cR1, 12cR2, 18c, 19c, 21cで, 最適化の違いを診ていきましょう. なお, 隠しパラメータ含めパラメータはデフォルト設定です.
データの準備
SCOTT@orclpdb1> @day25-0
1* DROP TABLE supertype
表が削除されました.
経過: 00:00:00.05
1 CREATE TABLE supertype
2 (
3 pkey NUMBER PRIMARY KEY
4 , attr1 NUMBER NOT NULL
5 , attr2 NUMBER NOT NULL
6 , note VARCHAR2(500)
7* )
表が作成されました.
経過: 00:00:00.03
1* DROP TABLE nikoichi_mitaina_subtype
表が削除されました.
経過: 00:00:00.05
1 CREATE TABLE nikoichi_mitaina_subtype
2 (
3 pkey NUMBER PRIMARY KEY
4 , col1 NUMBER
5 , col2 NUMBER
6 , description VARCHAR2(1000)
7 , CONSTRAINT uk1 unique (col1) USING INDEX
8 , CONSTRAINT uk2 unique (col2) USING INDEX
9* )
表が作成されました.
経過: 00:00:00.03
1 BEGIN
2 FOR i IN 1..10000 LOOP
3 INSERT INTO supertype VALUES(i,0,0,LPAD(i,500,'*'));
4 INSERT INTO nikoichi_mitaina_subtype VALUES(
5 i
6 , CASE WHEN MOD(i,2) = 0 THEN i ELSE null END
7 , CASE WHEN MOD(i,2) = 1 THEN i ELSE null END
8 , LPAD(i,1000,'*')
9 );
10 IF MOD(i,100) = 0 THEN COMMIT; END IF;
11 END LOOP;
12 INSERT INTO supertype VALUES(10001,0,0,null);
13 COMMIT;
14 DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT',tabname=>'SUPERTYPE',no_invalidate=>false,cascade=>true);
15 DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT',tabname=>'NIKOICHI_MITAINA_SUBTYPE',no_invalidate=>false,cascade=>true);
16* END;
PL/SQLプロシージャが正常に完了しました.
経過: 00:00:02.83
現時点でリリースされている最新版, 21cから順に, 11gR2まで遡って診ていきます
Previously on Mac De Oracle...
Day 23は, ANTI JOINにフォーカスをあてました. そうくれば, 次は, SEMI JOINだろう？ と思ったあなた！ ハズレですw
では, Day 24 クリスマスイブの窓を開けましょう！
実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 9 / No.44 / COLLECTION ITERATOR PICKLER FETCH
で, パイプラインテーブルファンクションだと, ファンクションが実行されていることしかExplain等で見る事はできなくて, COLLECTION ITERATOR PICKLER FETCH だけなんだよね, と言うお話をしました.
1 SELECT
2 *
3 FROM
4* day9_pkg.list_2_latest_sales(2, 20, 5745)
PROD_ID CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- -------- ---------- ---------- ------------- -----------
20 5745 01-12-31 2 999 1 628.89
実行計画
----------------------------------------------------------
Plan hash value: 457385954
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| LIST_2_LATEST_SALES | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
レントゲン屋さん的にはw 辛いよね. と. 間接的に見るしかないわけで. 少々面倒, 対応を意識していないと難しいですし.
今日は, クリスマスイブですし, Oracleさんもどんどん新機能提供してくれてて, それファンクションじゃないの？ いえ, いえ. ？？ 今日は, SQLマクロ(SQM)の実行計画という名のレントゲンです.
SQLマクロ(SQM)を作ります. パイプラインテーブルファンクションと似てますけども, SQL_MACROです.
SCOTT@orclpdb1> @day24
1* DROP FUNCTION sq_macro_sample
ファンクションが削除されました.
1 CREATE FUNCTION sq_macro_sample
2 (
3 p_first_rows NUMBER
4 , p_table_name DBMS_TF.TABLE_T
5 )
6 RETURN VARCHAR2
7 SQL_MACRO
8 IS
9 BEGIN
10 RETURN
11 'SELECT
12 *
13 FROM
14 p_table_name
15 FETCH FIRST sq_macro_sample.p_first_rows ROWS ONLY'
16 ;
17* END;
ファンクションが作成されました.
Day 22は, Subquery Unnestingを取り上げました. 最近は積極的にUnnestingしているように思うのですが, 皆さんはどう思いますか？
では, Day 23の窓を開けましょう！
今日は, 昨日話題にした, Subquery Unnesting の発動も必要ですが ANTI JOIN の実行計画という名のレントゲンから, いくつか特徴のある物をピックアップ（全バリエーション取れなかった言い訳w）.
ANTI JOIN（結合されなかった主問い合わせの対象行を返す）もJOINのナカーマなであるわけで, Nested Loop Join, Hash Join, Merge joinがあり, さらに, NULLを考慮する必要のある結合列がないか, 一つか, それ以上かで, Null-Awareなし, NA(Null-Aware), SNA(Single Null-Aware) でOperation列に表示されるOperation名にいくつかのバリエーションがあります.
とはいえ, ANTI JOINかNULLを意識する必要があるかないか大きな分類で, JOIN方式は通常のJOINと同じ種類があるのはご存知の通り.
NOT EXISTS演算子や, NOT IN条件かつSubquery Unnestingが発動していると, 直感的にイメージできるようになってればいいかもしれないですね. かつ, NULLを意識してるなどもOperation名から見切れるとなお.
たった1文のOperationにそこまで情報が詰め込まれているんですよね.
参考）
Oracle Database 21c SQL Tuning Guide / 9.3.4 Antijoins
いつもと同じように 21c で確認します.
SCOTT@orclpdb1> select banner from v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
ヒントまで使ってなんとか, NESTED LOOPS ANTI が取れました（なかなか言うこと聞いてくれなかったw）
結合列でNULLを意識する必要のない場合に現れます.
HR@orclpdb1> @day23-2
1 SELECT
2 /*+
3 NO_INDEX(departments DEPT_ID_PK)
4 */
5 department_id
6 , department_name
7 FROM
8 departments
9 WHERE
10 department_id NOT IN
11 (
12 SELECT
13 /*+
14 NL_AJ
15 */
16 department_id
17 FROM
18 employees
19 WHERE
20 department_id IS NOT NULL
21* )
DEPARTMENT_ID DEPARTMENT_NAME
------------- ----------------------
120 Treasury
130 Corporate Tax
140 Control And Credit
...略...
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll
16行が選択されました.
実行計画
----------------------------------------------------------
Plan hash value: 3082375452
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 323 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 17 | 323 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 41 | 123 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
filter("DEPARTMENT_ID" IS NOT NULL)
Previously on Mac De Oracle...
Day 21は, INLIST ITERATOR と Sub Query と STATISTICS COLLECTORを取り上げました. 古くからあるOperationを最適化する比較的新しい機能ですね. とはいえ, 現場で見たことない！ なんていうのは多そうではあります.
では, Day 22の窓を開けましょう！
昨日の記事でちょっとだけ話題にした, Subquery Unnesting なので, Subquery Unnestingの代表的な3例を診ておきましょう.
いつもと同じように 21c で確認します.
SCOTT@orclpdb1> select banner from v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
まず, これも通常は, SQLを書き換えないと対応タイプですが, Oracleは結合に書き換えてくれます. 大量にデータを返すのに, スカラー副問い合わせが使われていて辛かった経験って一度ぐらいはありそうですね.
（私も過去一度だけこいつのチューニングしたことがありますが, 当時はスカラー副問い合わせをUnnestingできなかったので, SQLをOUTER JOINに書き換えてもらいました！）
以下の例では, スカラー副問い合わせが, OUTER JOINかつ, このケースではMERGE JOINに書き換えられています.
SCOTT@orclpdb1> @day22
1 SELECT
2 deptno
3 ,dname
4 ,(
5 SELECT
6 MAX(sal)
7 FROM
8 emp
9 WHERE
10 emp.deptno=dept.deptno
11 ) AS max_sal
12 FROM
13 dept
14 ORDER BY
15* deptno
DEPTNO DNAME MAX_SAL
---------- ------------------------------------------ ----------
10 ACCOUNTING 5000
20 RESEARCH 3000
30 SALES 2850
40 OPERATIONS
実行計画
----------------------------------------------------------
Plan hash value: 2834279049
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 116 | 7 (29)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 4 | 116 | 7 (29)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 3 | 48 | 5 (40)| 00:00:01 |
| 5 | VIEW | VW_SSQ_1 | 3 | 48 | 4 (25)| 00:00:01 |
| 6 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 12 | 84 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ITEM_1"(+)="DEPT"."DEPTNO")
filter("ITEM_1"(+)="DEPT"."DEPTNO")
では, Scalar Subquery Unnestingされなかった場合はどう見えるでしょうか？（これが, SELECTリストで利用されたScalar subqueryの実行計画です. NO_UNNESTヒントで抑止します.
スカラーサブクエリーが実行計画の最初に現れるのが特徴です（Oracleの場合）
以下の例では, dept表の行数分, Nested Loop Joinのように, emp表へのアクセスが繰り返し実行されます. なので, 行数が多い場合は, HASH JOINなどに書き換えた方が効率が良いわけです. Scalar Subquery UnnestingはそれをOracleが内部的に実施してくれている便利な機能なんですよ.
1 SELECT
2 deptno
3 ,dname
4 ,(
5 SELECT
6 /*+
7 NO_UNNEST
8 */
9 MAX(sal)
10 FROM
11 emp
12 WHERE
13 emp.deptno=dept.deptno
14 ) AS max_sal
15 FROM
16 dept
17 ORDER BY
18* deptno
DEPTNO DNAME MAX_SAL
---------- ------------------------------------------ ----------
10 ACCOUNTING 5000
20 RESEARCH 3000
30 SALES 2850
40 OPERATIONS
実行計画
----------------------------------------------------------
Plan hash value: 1283604845
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 52 | 8 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 4 | 28 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IX_EMP | 4 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."DEPTNO"=:B1)
Previously on Mac De Oracle...
Day 20は, DISTINCT Eliminationを取り上げました. 古くから実装されているElimination機能ですが, 知ってる方いましたかね？w 古すぎでしょうかw. とはいえ, この恩恵を得ている方も実は多いかもしれませんよ. クソデカクエリー追いきれてないかもしれないですし.
では, Day 21の窓を開けましょう！
久々に, 実行計画は, SQL文のレントゲン写真だ！ っぽく, 実行計画に現れるOperationを楽しんで診ていきましょう！
いつもと同じように 21c で確認します.
SCOTT@orclpdb1> select banner from v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
今日のテーマはIN条件とサブクエリー. IN条件といえば, 昔, 悩ませ過ぎは及ばざるがごとし #1という記事を書いてました. （そのころはまだIT業界に居なかった？ 方も多そうですが）
IN条件, 索引を使って少量のデータをアクセスするには, 以下の実行計画にあるように, INLIST ITERATORで繰り返しアクセスするは問題ないわけですが, 大量にあるとかなり性能面で影響が出ます. （悩ませ過ぎは及ばざるがごとし #1などは, ハードパース時間に影響がでたケースです）
SCOTT@orclpdb1> @day21
1 SELECT *
2 FROM
3 emp
4 WHERE
5* empno IN (7369,7499)
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
実行計画
----------------------------------------------------------
Plan hash value: 2355049923
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 78 | 2 (0)| 00:00:01 |
| 1 | IINLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 78 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 2 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPNO"=7369 OR "EMPNO"=7499)
Previously on Mac De Oracle...
Day 19は, かなり地味にデビューしていた機能ですが、変なところで目立った Group by Elimination にフォーカスを当てました。この手のEliminationは、実行計画という名のレントゲンからは見えなくなってしまう。何か悪さをしているときの調査は難易度高めです。基本的に操作が行われなくなるわけですが、例としてお見せしたような単純なSQLなら別ですが、クソデカクエリーだと、キッツイですよねw 結果不正って。
と言うことで、Day 20の窓を開けましょう！
今日は、 DISTINCT Eliminationです。またか！ と。そういうEliminationもある。ということ知っておいた方が良いと思うので、eliminationネタの最後として、ちょっと飽きてきた感じはありますが取り上げてみました。この機能の提供も古く、機能が提供されていなかった時代を知る人の方が少ないかもしれませんw
ちなみに、今回も該当機能をを局所的に無効化する例を書いていますが、ヒントが提供されていない最適化もあるので、そう言う場合は、隠しパラメータからそれらしいのを探してして、検証して効果の有無を確かめると良いと思います。MOSに書かれてないケースも多いのでBlogなどから情報を集めたりして、最終的には動作確認。もし不具合などと関連しているようであればサポートへ問い合わせても対応してくれるでしょう。コミュニティーにこれどうよ？と投げてみるのもありだと思います。Jonathanもネタもとは、コミュニティーでのやりとりだったりすることも多く、調べてみたら、そうだった！という記事も多く、本当に助かった！って経験は何度もあります！！！。
parameterはこんなあたりから見つけると楽ですよん。
Difference of Initialization Parameters between 19c (19.3.0.0.0) and 21c (21.3.0.0.0) - including hidden params
Difference of Initialization Parameters between 19c (19.3.0.0.0) and 21c (21.3.0.0.0)
いつもと同じように 21c で確認します.
SCOTT@orclpdb1> select banner from v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
前回と同じデータを使います。（こんな使いまわせるテストデータを作るスクリプト用意しておくと何かと便利ですよ！）
SCOTT@orclpdb1> @day20
1* DROP TABLE business_day_calendar
表が削除されました。
1 CREATE TABLE business_day_calendar AS
2 WITH
3 FUNCTION get_num_of_dates
4 RETURN NUMBER IS
5 l_dummy_date DATE;
6 --
7 eORA01839 exception;
8 pragma exception_init(eORA01839, -1839);
9 BEGIN
10 -- validate leap year
11 l_dummy_date := TO_DATE(TO_CHAR(SYSDATE, 'YYYY') || '0229', 'YYYYMMDD');
12 RETURN 366;
13 EXCEPTION
14 WHEN eORA01839 THEN
15 RETURN 365;
16 END;
17 SELECT
18 TO_DATE(TRUNC(SYSDATE,'YYYY') + level - 1) AS business_date
19 , CASE
20 WHEN TO_CHAR(
21 TO_DATE(TRUNC(SYSDATE,'YYYY') + level - 1)
22 , 'DY'
23 , 'NLS_DATE_LANGUAGE=AMERICAN'
24 ) IN ('SUN','WED')
25 THEN '1'
26 ELSE '0'
27 END AS is_holiday
28 FROM
29 dual
30 CONNECT BY
31* level <= get_num_of_dates
表が作成されました。
1 ALTER TABLE business_day_calendar
2 ADD CONSTRAINT pk_business_day_calendar PRIMARY KEY
3 (
4 business_date
5 )
6* USING INDEX
表が変更されました。
はい、主キー列にDISTINCTを使ってますが、無駄ですよね！（いきなり本題w）
最近のコメント