2022年4月11日 (月)

実行計画は、SQL文のレントゲン写真だ! No.35 - 似て非なるもの USE_CONCAT と OR_EXPANDヒントとパラレルクエリー

Previously on Mac De Oracle
前回は、その前のエントリーの流れから、標準はあるにはあるが癖の多いSQL - #27 LNNVL is 何? と思った方向けでちょいと脱線してました。
今日は、話を元に戻しますw

USE_CANTATとOR_EXPAND、レントゲン(実行計画)をみて、どこがどう違うのかは理解できたのではないかと思います。ではなぜ、今後使うとしたら、OR_EXPANDなのかは、USE_CONCATとより言うことを聞いてくれやすいという他にもう一つあるのですが、それは何かわかりますか?
大人の事情で、しばらく関わりが薄かった時期(w にこのヒントの効果を知ったのですが、もう一つのメリットまでは知らなかったんですよw。 斜め読みだけしてると取りこぼしちゃいますねw

答えはパラレルクエリーにした場合の違い。

OR_EXPANDによる書き換えとUNION-ALLへの内部的な書き換えの効果で、パラレルクエリーとの相性が良くなっているんですよね。

早速、レントゲンをみてみましょう :)
(あ、書き忘れてましたが、Oracle Database 21cを使ってます)

USE_CONCATを使ってCONCATENATION(Id=1のoperation)を強制してかつパラレルクエリーにしています。PX COORDINATOR が Id=2とId=9に現れているのでUNIONの各SELECT文はシリアルに実行されているようですね。この挙動は変わってなさそうです。

SCOTT@orclpdb1> r
1 select
2 /*+
3 parallel(4)
4 use_concat
5 */
6 *
7 from
8 tab311
9 where
10 unique_id= 1
11* or sub_item_code = '0001000000'

経過: 00:00:00.44

実行計画
----------------------------------------------------------
Plan hash value: 1305058436

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 1076 | 8 (0)| 00:00:01 | | | |
| 1 | CONCATENATION | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ20001 | 2 | 538 | 4 (0)| 00:00:01 | Q2,01 | P->S | QC (RAND) |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 2 | 538 | 4 (0)| 00:00:01 | Q2,01 | PCWP | |
| 5 | BUFFER SORT | | | | | | Q2,01 | PCWC | |
| 6 | PX RECEIVE | | 1 | | 3 (0)| 00:00:01 | Q2,01 | PCWP | |
| 7 | PX SEND HASH (BLOCK ADDRESS) | :TQ20000 | 1 | | 3 (0)| 00:00:01 | | S->P | HASH (BLOCK|
|* 8 | INDEX RANGE SCAN | TAB311_IX_SUB_ITEM_CODE | 1 | | 3 (0)| 00:00:01 | | | |
| 9 | PX COORDINATOR | | | | | | | | |
| 10 | PX SEND QC (RANDOM) | :TQ10001 | 2 | 538 | 4 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 11 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 2 | 538 | 4 (0)| 00:00:01 | Q1,01 | PCWP | |
| 12 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 13 | PX RECEIVE | | 1 | | 3 (0)| 00:00:01 | Q1,01 | PCWP | |
| 14 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 1 | | 3 (0)| 00:00:01 | | S->P | HASH (BLOCK|
|* 15 | INDEX RANGE SCAN | TAB311_PK | 1 | | 3 (0)| 00:00:01 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

8 - access("SUB_ITEM_CODE"='0001000000')
15 - access("UNIQUE_ID"=1)
filter(LNNVL("SUB_ITEM_CODE"='0001000000'))

Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO (SYSTEM))
- Degree of Parallelism is 4 because of hint

OR_EXPANDでU内部的にUNION-ALLに書き換えてパラレルクエrーにすると。。。。。。おーーーーー。違う!!! Id=1にあるPX COORDINATOR だけになってますね。各SELECT文もパラレル化されているようです。:)
結構違いますね。やはり、使うなら、USE_CANTATよりOR_EXPANDのようが良さそうですね。これで思い出した! ORDERED と LEADINGヒントのような感じですかねー。同じ機能を持つ後発ヒントの方が色々と使い勝手が良くなってることって意外に多いです!

SCOTT@orclpdb1> r
1 select
2 /*+
3 parallel(4)
4 or_expand
5 */
6 *
7 from
8 tab311
9 where
10 unique_id= 1
11* or sub_item_code = '0001000000'

経過: 00:00:00.14

実行計画
----------------------------------------------------------
Plan hash value: 3317360125

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 1160 | 8 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 4 | 1160 | 8 (0)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
| 3 | BUFFER SORT | | 4 | 1160 | | | Q1,02 | PCWP | |
| 4 | VIEW | VW_ORE_5F0E22D2 | 4 | 1160 | 8 (0)| 00:00:01 | Q1,02 | PCWP | |
| 5 | UNION-ALL | | | | | | Q1,02 | PCWP | |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 2 | 538 | 4 (0)| 00:00:01 | Q1,02 | PCWP | |
| 7 | BUFFER SORT | | | | | | Q1,02 | PCWC | |
| 8 | PX RECEIVE | | 1 | | 3 (0)| 00:00:01 | Q1,02 | PCWP | |
| 9 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 1 | | 3 (0)| 00:00:01 | Q1,00 | S->P | HASH (BLOCK|
| 10 | PX SELECTOR | | | | | | Q1,00 | SCWC | |
|* 11 | INDEX RANGE SCAN | TAB311_PK | 1 | | 3 (0)| 00:00:01 | Q1,00 | SCWP | |
|* 12 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 2 | 538 | 4 (0)| 00:00:01 | Q1,02 | PCWP | |
| 13 | BUFFER SORT | | | | | | Q1,02 | PCWC | |
| 14 | PX RECEIVE | | 1 | | 3 (0)| 00:00:01 | Q1,02 | PCWP | |
| 15 | PX SEND HASH (BLOCK ADDRESS) | :TQ10001 | 1 | | 3 (0)| 00:00:01 | Q1,01 | S->P | HASH (BLOCK|
| 16 | PX SELECTOR | | | | | | Q1,01 | SCWC | |
|* 17 | INDEX RANGE SCAN | TAB311_IX_SUB_ITEM_CODE | 1 | | 3 (0)| 00:00:01 | Q1,01 | SCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

11 - access("UNIQUE_ID"=1)
12 - filter(LNNVL("UNIQUE_ID"=1))
17 - access("SUB_ITEM_CODE"='0001000000')

Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO (SYSTEM))
- Degree of Parallelism is 4 because of hint


前々回手動でunionに書き換えたSQLをパラレルにするとどうなるだろう。。。
ほう。

select
/*+
parallel(4)
*/
*
from
tab311
where
unique_id = 1
union
select
*
from
tab311
where
sub_item_code = '0001000000';

実行計画
----------------------------------------------------------
Plan hash value: 3983264199

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1345 | 11 (19)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 5 | 1345 | 11 (19)| 00:00:01 | Q1,03 | P->S | QC (RAND) |
| 3 | HASH UNIQUE | | 5 | 1345 | 11 (19)| 00:00:01 | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 5 | 1345 | 11 (19)| 00:00:01 | Q1,03 | PCWP | |
| 5 | PX SEND HASH | :TQ10002 | 5 | 1345 | 11 (19)| 00:00:01 | Q1,02 | P->P | HASH |
| 6 | HASH UNIQUE | | 5 | 1345 | 11 (19)| 00:00:01 | Q1,02 | PCWP | |
| 7 | UNION-ALL | | | | | | Q1,02 | PCWP | |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 3 | 807 | 4 (0)| 00:00:01 | Q1,02 | PCWP | |
| 9 | PX RECEIVE | | 3 | | 3 (0)| 00:00:01 | Q1,02 | PCWP | |
| 10 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 3 | | 3 (0)| 00:00:01 | Q1,00 | S->P | HASH (BLOCK|
| 11 | PX SELECTOR | | | | | | Q1,00 | SCWC | |
|* 12 | INDEX RANGE SCAN | TAB311_PK | 3 | | 3 (0)| 00:00:01 | Q1,00 | SCWP | |
| 13 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 2 | 538 | 5 (0)| 00:00:01 | Q1,02 | PCWP | |
| 14 | PX RECEIVE | | 2 | | 3 (0)| 00:00:01 | Q1,02 | PCWP | |
| 15 | PX SEND HASH (BLOCK ADDRESS) | :TQ10001 | 2 | | 3 (0)| 00:00:01 | Q1,01 | S->P | HASH (BLOCK|
| 16 | PX SELECTOR | | | | | | Q1,01 | SCWC | |
|* 17 | INDEX RANGE SCAN | TAB311_IX_SUB_ITEM_CODE | 2 | | 3 (0)| 00:00:01 | Q1,01 | SCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

12 - access("UNIQUE_ID"=1)
17 - access("SUB_ITEM_CODE"='0001000000')

Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO (SYSTEM))
- Degree of Parallelism is 4 because of hint

同じく、前々回手動でunion all + フィルタ条件追加に書き換えたSQLをパラレルにするとどうなるだろう。。。
おおおおおーーーーーーっと。これはCONCATENATIONの実行計画にそっくりですね。CONCATENATIONの部分がUNION-ALLになっている程度の違い。2つのPX COORDINATOR がある点も共通しています。。。むむ。

このSQLをOR_EXPANDの実行計画と同じようにするには......あ! あれだ!

select
/*+
parallel(4)
*/
*
from
tab311
where
unique_id = 1
union all
select
*
from
tab311
where
sub_item_code = '0001000000'
and LNNVL(unique_id=1);

実行計画
----------------------------------------------------------
Plan hash value: 1844591072

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 1076 | 9 (0)| 00:00:01 | | | |
| 1 | UNION-ALL | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 3 | 807 | 4 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 3 | 807 | 4 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 6 | PX RECEIVE | | 3 | | 3 (0)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 3 | | 3 (0)| 00:00:01 | Q1,00 | S->P | HASH (BLOCK|
| 8 | PX SELECTOR | | | | | | Q1,00 | SCWC | |
|* 9 | INDEX RANGE SCAN | TAB311_PK | 3 | | 3 (0)| 00:00:01 | Q1,00 | SCWP | |
| 10 | PX COORDINATOR | | | | | | | | |
| 11 | PX SEND QC (RANDOM) | :TQ20001 | 1 | 269 | 5 (0)| 00:00:01 | Q2,01 | P->S | QC (RAND) |
|* 12 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 1 | 269 | 5 (0)| 00:00:01 | Q2,01 | PCWP | |
| 13 | BUFFER SORT | | | | | | Q2,01 | PCWC | |
| 14 | PX RECEIVE | | 2 | | 3 (0)| 00:00:01 | Q2,01 | PCWP | |
| 15 | PX SEND HASH (BLOCK ADDRESS) | :TQ20000 | 2 | | 3 (0)| 00:00:01 | Q2,00 | S->P | HASH (BLOCK|
| 16 | PX SELECTOR | | | | | | Q2,00 | SCWC | |
|* 17 | INDEX RANGE SCAN | TAB311_IX_SUB_ITEM_CODE | 2 | | 3 (0)| 00:00:01 | Q2,00 | SCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

9 - access("UNIQUE_ID"=1)
12 - filter(LNNVL("UNIQUE_ID"=1))
17 - access("SUB_ITEM_CODE"='0001000000')

Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO (SYSTEM))
- Degree of Parallelism is 4 because of hint


ということで、
前々回手動でunion all + フィルタ条件追加に書き換えたSQLを単純にパラレルクエリーにしてもイマイチだったので、OR_EXPANDのような実行計画にするために、インラインビューにしてみました!!! どうでしょう? OR_EXPANDの実行計画と同じようになりました。
ポイントは、前々回のOR_EXPANDの実行計画中に現れるインラインビュー VW_ORE_5F0E22D2 です。内部的にインラインビューを追加してるんですよね! OR_EXPANDのUNION ALL書き換え。
インラインビュー化したことで、Id=4にビューが登場しています。OR_EXPANDでは、VW_ORE_* と名付けられるOR_EXPANDトランスフォームにより追加されるインラインビューと同じ役割を持っていますが、内部的に書き換えられて追加されるインラインビューとは異なり動的に名称が付加されません。

インラインビューが決めて! というか、意外と忘れがちなので注意しないとね。

select
/*+
parallel(4)
*/
*
from
(
select
*
from
tab311
where
unique_id = 1
union all
select
*
from
tab311
where
sub_item_code = '0001000000'
and LNNVL(unique_id=1)
);

経過: 00:00:00.03

実行計画
----------------------------------------------------------
Plan hash value: 3706965944

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 1160 | 9 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 4 | 1160 | 9 (0)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
| 3 | BUFFER SORT | | 4 | 1160 | | | Q1,02 | PCWP | |
| 4 | VIEW | | 4 | 1160 | 9 (0)| 00:00:01 | Q1,02 | PCWP | |
| 5 | UNION-ALL | | | | | | Q1,02 | PCWP | |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 3 | 807 | 4 (0)| 00:00:01 | Q1,02 | PCWP | |
| 7 | BUFFER SORT | | | | | | Q1,02 | PCWC | |
| 8 | PX RECEIVE | | 3 | | 3 (0)| 00:00:01 | Q1,02 | PCWP | |
| 9 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 3 | | 3 (0)| 00:00:01 | Q1,00 | S->P | HASH (BLOCK|
| 10 | PX SELECTOR | | | | | | Q1,00 | SCWC | |
|* 11 | INDEX RANGE SCAN | TAB311_PK | 3 | | 3 (0)| 00:00:01 | Q1,00 | SCWP | |
|* 12 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 1 | 269 | 5 (0)| 00:00:01 | Q1,02 | PCWP | |
| 13 | BUFFER SORT | | | | | | Q1,02 | PCWC | |
| 14 | PX RECEIVE | | 2 | | 3 (0)| 00:00:01 | Q1,02 | PCWP | |
| 15 | PX SEND HASH (BLOCK ADDRESS) | :TQ10001 | 2 | | 3 (0)| 00:00:01 | Q1,01 | S->P | HASH (BLOCK|
| 16 | PX SELECTOR | | | | | | Q1,01 | SCWC | |
|* 17 | INDEX RANGE SCAN | TAB311_IX_SUB_ITEM_CODE | 2 | | 3 (0)| 00:00:01 | Q1,01 | SCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

11 - access("UNIQUE_ID"=1)
12 - filter(LNNVL("UNIQUE_ID"=1))
17 - access("SUB_ITEM_CODE"='0001000000')

Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO (SYSTEM))
- Degree of Parallelism is 4 because of hint

4月はじめだと言うのに、夏日とか、北の方面の友人からは31度だとか、最近の異常気象ほんとに農家泣かせな感じ。最近は天気予想が細かい範囲ででるので以前より対応しやすいのかもしれないけど。
こんな、陽気だとぶらりと湘南あたりからリモートワークしたいw

ではまた。






Related article on Mac De Oracle
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 1 / TABLE FULL SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 2 / INDEX UNIQUE SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 3 / INDEX RANGE SCAN, Index Only Scan
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 4 / INDEX RANGE SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 5 / INDEX RANGE SCAN, INLIST ITERATOR
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 6 / INDEX FAST SCAN, Index Only Scan
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 7 / INDEX FULL SCAN,Index Only Scan
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 8 / INDEX SKIP SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 9 / TABLE ACCESS INMEMORY FULL
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 10 / NESTED LOOP JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 11 / MERGE JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 12 / HASH JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 13 / HASH JOIN OUTER
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 14 / HASH JOIN FULL OUTER
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 15 / PX, TABLE ACCESS FULL
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 16 / CONCATENATION
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 17 / SORT UNIQUE, UNION-ALL = UNION
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 18 / UNION-ALL
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 19 / INTERSECTION
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 20 / MINUS
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 21 / WINDOW NOSORT STOPKEY
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 22 / COUNT STOPKEY
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 23 / HASH JOIN - LEFT-DEEP JOIN vs RIGHT-DEEP JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 24 / CONNECT BY NO FILTERING WITH START-WITH
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 25 / UNION ALL (RECURSIVE WITH) DEPTH FIRST, RECURSIVE WITH PUMP
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#1 / STAR TRANSFORM, VECTOR TRANSFORM (DWH向け)
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#2 / MERGE (UPSERT)
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#3 / RDFView
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#4 / INDEX FULL SCAN (MIN/MAX) - Index Only Scan
・実行計画は、SQL文のレントゲン写真だ! No.30 - LOAD TABLE CONVENTIONAL vs. LOAD AS SELECT
・実行計画は、SQL文のレントゲン写真だ! No.31 - TEMP TABLE TRANSFORMATION LOAD AS SELECT (CURSOR DURATION MEMORY)
・実行計画は、SQL文のレントゲン写真だ! No.32 - EXTERNAL TABLE ACCESS FULL / INMEMORY FULL
・実行計画は、SQL文のレントゲン写真だ! No.33 - BITMAP CONVERSION TO ROWIDS
・実行計画は、SQL文のレントゲン写真だ! No.34 - 似て非なるもの USE_CONCAT と OR_EXPAND ヒント と 手書きSQLのレントゲンの見分け方

| | コメント (0)

2022年4月 9日 (土)

実行計画は、SQL文のレントゲン写真だ! No.34 - 似て非なるもの USE_CONCAT と OR_EXPAND ヒント と 手書きSQLのレントゲンの見分け方

Previously on Mac De Oracle
前回のレントゲンは、BITMAP CONVERSION TO ROWIDSでした。複数の索引を同時に使うという昔からあるオペレーションでした。

今回は単にレントゲンを見ていくだけではなく、同じ問い合わせ結果(よくある間違いなどもいれてありますw)になるものの微妙に違うレントゲンをみつつ、元のSQL文、それに今回ヒントになにが使われているか、見ていきたいと思います。

これが前回のエントリで使ったSQL分です. 問い合わせ結果と実行計画(前回のエントリで取り上げたBITMAP CONVERSIONです。この問い合わせ結果と実行計画という名のレントゲンをよーーーーーーーーーく、覚えておいてくださいね。

いくつかのレントゲンを使って、これなーーーーーーーんだ? wみたいなw

これが原型なので、覚えておいてください。

select
*
from
tab311
where
unique_id= 1
or sub_item_code = '0001000000';

UNIQUE_ID SUB_ITEM_CODE FOO IS_DELETE
---------- ------------------------------ -------------------------------------------------- ----------
1 0000000002 ************************************************** 0
**************************************************
**************************************************
**************************************************
*************************************************1

1 0001000001 fooooooooooooo1 0
2 0001000000 fooooooo2 0
1 0001000000 2**** 0

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1345 | 8 (13)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 5 | 1345 | 8 (13)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| 5 | SORT ORDER BY | | | | | |
|* 6 | INDEX RANGE SCAN | TAB311_PK | | | 3 (0)| 00:00:01 |
| 7 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 8 | INDEX RANGE SCAN | TAB311_IX_SUB_ITEM_CODE | | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("UNIQUE_ID"=1)
filter("UNIQUE_ID"=1)
8 - access("SUB_ITEM_CODE"='0001000000')

統計
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1248 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed

 

前回のエントリでも少し書いたのですが、CONCATENATION がレントゲンに現れるときは、BITMAP CONVERTなど効率が悪いOR条件の実行計画を改善するため、OR条件部分を分離排除し、2つの索引それぞれを有効に利用させるためのヒントによるチューニングで行なった場合が多いです。オプティマイザが選択するケースもありますが。

ヒントで強制的にすることもありますが、ヒントが効かないケースは多も多いのは事実です。理由は内部的に2つのクエリーに分解しているわけですが、それぞれで利用する索引のアクセス効率が悪いオプティマイザに見えている場合にはヒントが効かない場合が多いように思います。
なんとなーーーくざっくりなイメージですが、UNIONのような形に内部的に書き換えていると思うとわかりやすいかもしれないですね。。UNIONとでてないのでUNIONのようなものとしかかけないのですがW
とにかく、CONCATENATIONを見つけたら USE_CONCAT ヒントでチューニングされてるね!
と脊髄反応できるようになっているとよいですね!

 UNIQUE_ID SUB_ITEM_CODE                  FOO                                                 IS_DELETE
---------- ------------------------------ -------------------------------------------------- ----------
2 0001000000 fooooooo2 0
1 0001000000 2**** 0
1 0000000002 ************************************************** 0
**************************************************
**************************************************
**************************************************
*************************************************1

1 0001000001 fooooooooooooo1 0

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1345 | 11 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 4 | 1076 | 7 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TAB311_IX_SUB_ITEM_CODE | 4 | | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 1 | 269 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | TAB311_PK | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("SUB_ITEM_CODE"='0001000000')
5 - access("UNIQUE_ID"=1)
filter(LNNVL("SUB_ITEM_CODE"='0001000000'))

統計
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
1228 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

 

上記のレントゲンの元はこれです。USE_CONCATヒント使われてますよね。このケースではオプティマイザは言うことを聞いてくれたようですね。w

select
/*+
use_concat
*/
*
from
tab311
where
unique_id= 1
or sub_item_code = '0001000000';

 

では、オプティマイザが言うことを聞いてくれなかった場合、Oracle Database 12c R1まではどうやって、治療していたか。知りたく無いですか?
USE_CONCATでUNIONのように内部的書き換えてくれると、SQLへはヒントの追加だけで済むので、同値検証等も不要で患者さんの痛みは少なくてすむわけですが、先にも買いたように必ず効くわけでもないという、ちょっと癖のあるヒントなんです。で、12c R1までは、しかたないの、SQL構文変更という中程度の難易度の手術(SQL書き換えw)が必要でした。

 

先ほと、UNIONのようにと書きましたが、まさに、それで、UNIONまたは、UNION ALLに書き換えてしまうという手術ですw

 

どちらでやってもよいのですが、重複データの排除がどれだけの負荷になるかというところかなと思います。重複排除するデータ量が多いのであれば UNION にしてHASH UNIQUEによる重複行排除の方がよいかもしれませんし、少量なら UNION ALLでフィルタリングによる重複行排除のほうがよいかもしれません。HASH UNIQUEにしてもPGA不足でTEMP落ちしてしまうようなことがあるのならフィルタリングのほうがよさそうですし、その時の状況次第かと思います。

 

では、ずは、UNION を使った書き換えから。

 

ソートしていないので並びが変わってますが、あえてソートしていません。Id=2のUNION-ALLとId=1にHASH UNIQUEというoperationがありますが、これが現在のUNIONの典型的なoperationです。HASH UNIQUEがなかったころは、SORT UNIQUEだったわけですが、その影響でデータがソートされていたので、諸々勘違いしてデフォルトでソートされるんだー、みたいな勘違いしている方も一定数存在していた時期があり、HASH UNIQUEがなって順序通りになってない! と勝手にザワザワしていたこともありましたね。それ知ってる方々はOracleにながーーーーいこと関わっている方だと思いますw 注意しましょうね。思った通りの並びにしたい場合は、ちゃんとORDER BY句でソートしましょうね。(これ言いたかっただけw)
あと、最近は、UNIONをパラレル実行できるようになったので、その場合も、順序はバラバラになります。シリアルに実行している場合は上位にあるクエリから処理されるのでその順序で行が戻されていましたが、パラレルだと何が来るかはその時々ですね。

 

脇道にそれましたが、手書きでSQLを UNION に書き換えた場合のレントゲンはこんな感じです。UNION-ALLのオペレーションの後に重複行排除のUNIQUE操作が必ず入るので覚えやすいと思います。

 UNIQUE_ID SUB_ITEM_CODE                  FOO                                                 IS_DELETE
---------- ------------------------------ -------------------------------------------------- ----------
1 0000000002 ************************************************** 0
**************************************************
**************************************************
**************************************************
*************************************************1

1 0001000000 2**** 0
1 0001000001 fooooooooooooo1 0
2 0001000000 fooooooo2 0

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1345 | 12 (9)| 00:00:01 |
| 1 | HASH UNIQUE | | 5 | 1345 | 12 (9)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 1 | 269 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TAB311_PK | 1 | | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 4 | 1076 | 7 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | TAB311_IX_SUB_ITEM_CODE | 4 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("UNIQUE_ID"=1)
6 - access("SUB_ITEM_CODE"='0001000000')

統計
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
1225 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

 

もともとOR条件でしたが、それぞれの索引を有効に活用させるため、2つの文に分解し、それらを UNIONしています。UNIONで重複排除も行なっているわけです。

select
*
from
tab311
where
unique_id = 1
union
select
*
from
tab311
where
sub_item_code = '0001000000';

 

 

次に、UNION ALLへ手動で書き換えた場合はどうなるかレントゲンを見てみましょう。

 

結果も正しいです。レントゲンで見える UNION と UNION-ALLとの違いは、重複行排除のUNIQUEオペレーションが無いところです。Id=1にあるUNION-ALL だけで、 HASH UNIQUEがありません。
これ大丈夫なのでしょうか? 重複行を排除するオペレーションがないなんで、たまため結果が正しいだけでしょうか???

 

実は、実行計画に現れない違いが述語部分にあります。 Predicate Information (identified by operation id):セクションに 4 - filter(LNNVL("UNIQUE_ID"=1)) とあるのに気づきましたか?

 

4は、実行計画の Id = 4を示しています。これは Id = 4の TAB311のアクセス時に、"UNIQUE_ID"=1 であれば falseとして該当行をフィルタリングして捨てていることを意味しています。
つまり、UNIONで HASH UNIQUEを行なっていた重複行を排除と同様の効果をえるフィルター条件なんです。この条件にで、 Id = 2 と Id = 3で取得されたUNIQUE_ID=1の行を捨てています

 UNIQUE_ID SUB_ITEM_CODE                  FOO                                                 IS_DELETE
---------- ------------------------------ -------------------------------------------------- ----------
1 0000000002 ************************************************** 0
**************************************************
**************************************************
**************************************************
*************************************************1

1 0001000000 2**** 0
1 0001000001 fooooooooooooo1 0
2 0001000000 fooooooo2 0

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1345 | 11 (0)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 1 | 269 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TAB311_PK | 1 | | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 4 | 1076 | 7 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | TAB311_IX_SUB_ITEM_CODE | 4 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("UNIQUE_ID"=1)
4 - filter(LNNVL("UNIQUE_ID"=1))
5 - access("SUB_ITEM_CODE"='0001000000')

統計
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1225 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

 

UNION ALLでの書き換えは、以下のようなSQL文でした。and LNNVL(unique_id=1)という条件追加がポイントです。

select
*
from
tab311
where
unique_id = 1
union all
select
*
from
tab311
where
sub_item_code = '0001000000'
and LNNVL(unique_id=1);

 

では、つい忘れそうな、フィルター条件追加をわすれてUNION ALLにしてしまった場合はどうなるでしょう? もう想像できますよねw

 

SQLの違いからみてみましょう。 and LNNVL(unique_id=1) と言う条件が無いこと以外前述のSQLと同じです。

select
*
from
tab311
where
unique_id = 1
union all
select
*
from
tab311
where
sub_item_code = '0001000000';

 

実行してみると。。。。。あららららら、ちゃっしゃいましたな感じの結果ですねw Predicate Information (identified by operation id): には重複排除のフィルター条件は見当たりません。(当然ですね。書き忘れているわけですから)

UNIQUE_ID = 1 でもあり、SUB_ITEM_CODE = '0001000000' である行が2回リストされています。ざんねーーーん。注意しましょうね。

 UNIQUE_ID SUB_ITEM_CODE                  FOO                                                 IS_DELETE
---------- ------------------------------ -------------------------------------------------- ----------
1 0000000002 ************************************************** 0
**************************************************
**************************************************
**************************************************
*************************************************1

1 0001000000 2**** 0
1 0001000001 fooooooooooooo1 0
2 0001000000 fooooooo2 0
1 0001000000 2**** 0


----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1345 | 11 (0)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 1 | 269 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TAB311_PK | 1 | | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 4 | 1076 | 7 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | TAB311_IX_SUB_ITEM_CODE | 4 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("UNIQUE_ID"=1)
5 - access("SUB_ITEM_CODE"='0001000000')

統計
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1239 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed

 

よーーーーし、バグになるのが怖いから、常に UNION だーーーと安全策に流れそうですが、性能面ではフィルタリングと HASH UNIQUE、または、SORT UNIQUEかということであれば、データ量次第でどちらが良いか判断した方がよいだろうと。個人的には思っています。フィルターしたほうが有利なケースも当然ありますし、しないほうが良い場合もあるでしょう。

最後に、今日のタイトルにも書いた NO_EXPAND これ少々気難しい、USE_CONCATの後継として 12c R2 で登場したヒントです。内部の書き換えもそれまでのCONCATENATIONではなく、UNION ALLとフィルタリングにより重複行排除が行われるように、内部的に書き換えられるのが特徴です。なんでこれもっと早く実装してくれなかったんですかね? 強強ですね。USE_CONCATの気まぐれ感は消えてる感じがします。

事実、某所で、USE_CONCAT効かなくて、しぬーーーーーみたいな状況で、たまたま 12c R2だったので、NO_EXPAND で回避したーーーーなんてこともありました。そういことで、USE_EXPANDをUSE_CONCATの代わりにUSE_EXPANDを使うことをおすすめしますw (それ以外にもメリットも多いですし、それはまた、次回にでも)

select
/*+
or_expand
*/
*
from
tab311
where
unique_id= 1
or sub_item_code = '0001000000';

UNIQUE_ID SUB_ITEM_CODE FOO IS_DELETE
---------- ------------------------------ -------------------------------------------------- ----------
1 0000000002 ************************************************** 0
**************************************************
**************************************************
**************************************************
*************************************************1

1 0001000000 2**** 0
1 0001000001 fooooooooooooo1 0
2 0001000000 fooooooo2 0

 

レントゲンを見ると、手書きで書いた UNION ALLへの書き換えと微妙に違うの気づきますか? これまで紹介してきた手書きでの書き換えとヒントに夜書き換えは4つありますが、それぞれ実行計画に特徴があるんです。(もしかしたら将来は区別しにくくなるかもしれませんが、現状は区別できます!!!

OR_EXPANヒントでUNION ALL変換した場合 Id = 1にあるような、インラインビューがは登場します。VW_ORE_5F0E22D2 とオプティマイザが動的に名称をつけますが、ポイントは VW_ORE_* というprefixが作ろころですね。VWはびゅー。OREは、OR_Expand の大文字部分みたいですねw (そのうち内部的に生成されるインラインビュー名もまとめて紹介したいですね。すでに誰かやってそうな気もしますがw)

実行計画
----------------------------------------------------------
Plan hash value: 3148130991

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1450 | 11 (0)| 00:00:01 |
| 1 | VIEW | VW_ORE_5F0E22D2 | 5 | 1450 | 11 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 1 | 269 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TAB311_PK | 1 | | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 4 | 1076 | 7 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | TAB311_IX_SUB_ITEM_CODE | 4 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("UNIQUE_ID"=1)
5 - filter(LNNVL("UNIQUE_ID"=1))
6 - access("SUB_ITEM_CODE"='0001000000')

統計
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1225 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

 

長くなったので、まとめです。
ヒントによる OR条件のUNIONのような書き換えや、UNION ALLへの書き換え、または、人が UNION や UNION ALLへ手書きで書き換えたSQLのレントゲン、それぞれに特徴があり、4つとも、レントゲンから元のSQLがイメージできるんですよ!!!

USE_CONCATによる書き換え Oracle 8i 8.1以降〜

select
/*+
use_concat
*/
*
from
tab311
where
unique_id= 1
or sub_item_code = '0001000000';

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1345 | 11 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 4 | 1076 | 7 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TAB311_IX_SUB_ITEM_CODE | 4 | | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 1 | 269 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | TAB311_PK | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("SUB_ITEM_CODE"='0001000000')
5 - access("UNIQUE_ID"=1)
filter(LNNVL("SUB_ITEM_CODE"='0001000000'))

 

OR_EXPANDによる書き換え Oracle 12cR2以降〜

select
/*+
or_expand
*/
*
from
tab311
where
unique_id= 1
or sub_item_code = '0001000000';

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1450 | 11 (0)| 00:00:01 |
| 1 | VIEW | VW_ORE_5F0E22D2 | 5 | 1450 | 11 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 1 | 269 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TAB311_PK | 1 | | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 4 | 1076 | 7 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | TAB311_IX_SUB_ITEM_CODE | 4 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("UNIQUE_ID"=1)
5 - filter(LNNVL("UNIQUE_ID"=1))
6 - access("SUB_ITEM_CODE"='0001000000')

 

手動書き換え UNION

select
*
from
tab311
where
unique_id = 1
union
select
*
from
tab311
where
sub_item_code = '0001000000';

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1345 | 12 (9)| 00:00:01 |
| 1 | HASH UNIQUE | | 5 | 1345 | 12 (9)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 1 | 269 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TAB311_PK | 1 | | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 4 | 1076 | 7 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | TAB311_IX_SUB_ITEM_CODE | 4 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("UNIQUE_ID"=1)
6 - access("SUB_ITEM_CODE"='0001000000')

 

手動書き換え UNION ALL + 重複排除フィルター条件追加

select
*
from
tab311
where
unique_id = 1
union all
select
*
from
tab311
where
sub_item_code = '0001000000'
and LNNVL(unique_id=1);

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1345 | 11 (0)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 1 | 269 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TAB311_PK | 1 | | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 4 | 1076 | 7 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | TAB311_IX_SUB_ITEM_CODE | 4 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("UNIQUE_ID"=1)
4 - filter(LNNVL("UNIQUE_ID"=1))
5 - access("SUB_ITEM_CODE"='0001000000')

 

 

レントゲンから諸々読み取るスキルは大切だと思っているので、みんなもレントゲンというなの実行計画は読んでみるといいよーーーっ。おすすめ。

 

では、次回へつづく

 

 



Related article on Mac De Oracle
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 1 / TABLE FULL SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 2 / INDEX UNIQUE SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 3 / INDEX RANGE SCAN, Index Only Scan
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 4 / INDEX RANGE SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 5 / INDEX RANGE SCAN, INLIST ITERATOR
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 6 / INDEX FAST SCAN, Index Only Scan
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 7 / INDEX FULL SCAN,Index Only Scan
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 8 / INDEX SKIP SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 9 / TABLE ACCESS INMEMORY FULL
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 10 / NESTED LOOP JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 11 / MERGE JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 12 / HASH JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 13 / HASH JOIN OUTER
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 14 / HASH JOIN FULL OUTER
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 15 / PX, TABLE ACCESS FULL
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 16 / CONCATENATION
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 17 / SORT UNIQUE, UNION-ALL = UNION
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 18 / UNION-ALL
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 19 / INTERSECTION
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 20 / MINUS
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 21 / WINDOW NOSORT STOPKEY
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 22 / COUNT STOPKEY
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 23 / HASH JOIN - LEFT-DEEP JOIN vs RIGHT-DEEP JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 24 / CONNECT BY NO FILTERING WITH START-WITH
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 25 / UNION ALL (RECURSIVE WITH) DEPTH FIRST, RECURSIVE WITH PUMP
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#1 / STAR TRANSFORM, VECTOR TRANSFORM (DWH向け)
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#2 / MERGE (UPSERT)
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#3 / RDFView
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#4 / INDEX FULL SCAN (MIN/MAX) - Index Only Scan
・実行計画は、SQL文のレントゲン写真だ! No.30 - LOAD TABLE CONVENTIONAL vs. LOAD AS SELECT
・実行計画は、SQL文のレントゲン写真だ! No.31 - TEMP TABLE TRANSFORMATION LOAD AS SELECT (CURSOR DURATION MEMORY)
・実行計画は、SQL文のレントゲン写真だ! No.32 - EXTERNAL TABLE ACCESS FULL / INMEMORY FULL
・実行計画は、SQL文のレントゲン写真だ! No.33 - BITMAP CONVERSION TO ROWIDS

| | コメント (0)

2022年4月 7日 (木)

実行計画は、SQL文のレントゲン写真だ! No.33 - BITMAP CONVERSION TO ROWIDS


Previously on Mac De Oracle
前回は外部表特有のoperationであるEXTERNAL TABLE ACCESS FULL / INMEMORY FULL のレントゲンでした。

今日は、昔からあるBITMAP CONVERSION TO ROWIDSを見てみたいと思います。

SQLチューニング・ガイド 8.4.2 ビットマップのROWIDへの変換
SQLチューニング・ガイド 8.4.2 ビットマップのROWIDへの変換 / 21c


このオペレーションは、複数の索引からbitmapを生成しその結果のrowidを用いて表をアクセスするところにあります。通常一つの索引が利用されますが、この場合は複数の索引が利用されるところが特徴です。
ただ、bitmapに変換コストより、unionに書き換えたり(内部的な書き換えも含む)したほうが効率が良かったりします。なので意外と嫌われてたりw なので、STAR TRANSFORM などで見るぐららいで、結構それ以外の方向へチューニングされているケースのほうが多いかもしれません。でもこれで問題なければそのままでも問題はないわけですが。

あ、そういえば、以前、CONCATENATIONのレントゲンを紹介していましたね。
ちょうどよいので、CONCATENATIONのレントゲン撮影時と同じ表とSQL文を使って BITMAP CONVERSION TO ROWIDS のレントゲンを見てみましょう :)

SCOTT@orclpdb1> desc tab311
名前 NULL? 型
----------------------------------------- -------- ----------------------------
UNIQUE_ID NOT NULL NUMBER(10)
SUB_ITEM_CODE NOT NULL CHAR(10)
FOO NOT NULL VARCHAR2(500)
IS_DELETE NOT NULL NUMBER(1)

SCOTT@orclpdb1> select count(1) from tab311

COUNT(1)
----------
2000000

経過: 00:00:00.09

実行計画を見てわかると思いますが、 2つの索引(TAB311_PK, TAB311_IX_SUB_ITEM_CODE)のROWIDからBITMAPを作り(Id=3,7)、それを BITMAP OR (SQL文の7行目 Id=3)した結果をROWIDへ変換(Id=2)、複数のROWIDをまとめ、IOリクエストを少なくするための ROWID BATCHED(Id=1)で表(TAB311)をアクセスしていことが読み取れます。
ROWIDでアクセスするので、基本的に少量の行にアクセスする場合には有利ではあります。ただ、BITMAPへの変換コスト次第というところではあるわけです。なので、BITMAPの変換のないタイプのトランスフォームを狙ったHINTを利用したり、SQL文自体を書き換えたりするケースは少なくありません。意外に嫌いな方が多くてw 大抵チューニングされてしまい、あまり見かけることはないかもしれませんw 

SCOTT@orclpdb1> r
1 select
2 *
3 from
4 tab311
5 where
6 unique_id= 1
7* or sub_item_code = '0001000000'

経過: 00:00:00.01

実行計画
----------------------------------------------------------
Plan hash value: 1263461875

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1345 | 8 (13)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 5 | 1345 | 8 (13)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| 5 | SORT ORDER BY | | | | | |
|* 6 | INDEX RANGE SCAN | TAB311_PK | | | 3 (0)| 00:00:01 |
| 7 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 8 | INDEX RANGE SCAN | TAB311_IX_SUB_ITEM_CODE | | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("UNIQUE_ID"=1)
filter("UNIQUE_ID"=1)
8 - access("SUB_ITEM_CODE"='0001000000')

統計
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1103 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

ちなみに、CONCATENATIONのエントリーを見ていただくのがよいとは思いますが、これも比較的古くからある、CONCATENATIONを使ったSQL変換のレントゲンも改めて載せておきます。
(USE_CONCATヒントで強制しています。みなさん、知っているとは思いますが、NO_EXPANDヒントが逆のヒントです)

実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 Day 17 / CONCATENATION
UNION のような実行計画ですが、UNIONとは出てませんw これはまた別の機会に。ただ、ほぼ同等の意味で、OR条件でそれぞれに最適な索引を使うことでindex range scanやindex unique scanを効かせて高速にアクセスしようとしています。
BITMAPとの相互変換などが無い分、安定して早いケースは経験的にも多いのは確かです。どちらを選ぶかはやはり、登録されているデータの傾向と検索条件次第ではあります。ただ一般的BITMAP変換を避ける傾向が強いのは確かではありますね。

SCOTT@orclpdb1> r
1 select
2 /*+
3 use_concat
4 */
5 *
6 from
7 tab311
8 where
9 unique_id= 1
10* or sub_item_code = '0001000000'

経過: 00:00:00.00

実行計画
----------------------------------------------------------
Plan hash value: 1344230703

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1345 | 11 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 4 | 1076 | 7 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TAB311_IX_SUB_ITEM_CODE | 4 | | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB311 | 1 | 269 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | TAB311_PK | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("SUB_ITEM_CODE"='0001000000')
5 - access("UNIQUE_ID"=1)
filter(LNNVL("SUB_ITEM_CODE"='0001000000'))


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1091 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

GWも間近だ。そろそろ予定考えないとな。その前にACEのKPIはクリアしておかないと。追い込み追い込みw


ということで、次回へつづく。






Related article on Mac De Oracle
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 1 / TABLE FULL SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 2 / INDEX UNIQUE SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 3 / INDEX RANGE SCAN, Index Only Scan
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 4 / INDEX RANGE SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 5 / INDEX RANGE SCAN, INLIST ITERATOR
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 6 / INDEX FAST SCAN, Index Only Scan
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 7 / INDEX FULL SCAN,Index Only Scan
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 8 / INDEX SKIP SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 9 / TABLE ACCESS INMEMORY FULL
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 10 / NESTED LOOP JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 11 / MERGE JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 12 / HASH JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 13 / HASH JOIN OUTER
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 14 / HASH JOIN FULL OUTER
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 15 / PX, TABLE ACCESS FULL
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 16 / CONCATENATION
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 17 / SORT UNIQUE, UNION-ALL = UNION
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 18 / UNION-ALL
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 19 / INTERSECTION
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 20 / MINUS
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 21 / WINDOW NOSORT STOPKEY
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 22 / COUNT STOPKEY
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 23 / HASH JOIN - LEFT-DEEP JOIN vs RIGHT-DEEP JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 24 / CONNECT BY NO FILTERING WITH START-WITH
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 25 / UNION ALL (RECURSIVE WITH) DEPTH FIRST, RECURSIVE WITH PUMP
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#1 / STAR TRANSFORM, VECTOR TRANSFORM (DWH向け)
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#2 / MERGE (UPSERT)
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#3 / RDFView
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#4 / INDEX FULL SCAN (MIN/MAX) - Index Only Scan
・実行計画は、SQL文のレントゲン写真だ! No.30 - LOAD TABLE CONVENTIONAL vs. LOAD AS SELECT
・実行計画は、SQL文のレントゲン写真だ! No.31 - TEMP TABLE TRANSFORMATION LOAD AS SELECT (CURSOR DURATION MEMORY)
・実行計画は、SQL文のレントゲン写真だ! No.32 - EXTERNAL TABLE ACCESS FULL / INMEMORY FULL

| | コメント (0)

2022年4月 1日 (金)

実行計画は、SQL文のレントゲン写真だ! No.32 - EXTERNAL TABLE ACCESS FULL / INMEMORY FULL

Previously on Mac De Oracle
前回は、ルーティーンとなったw、19cと20cのパラメータ差分チェックでした。

今日は、元の路線に戻り、「実行計画は、SQL文のレントゲン写真だ!」シリーズです :)

前回のパラメータ差分チェックで、外部表を利用していたので、18c以降で変更された In-Memory External Tables とそれまでの External Tableのレントゲンを見ておこうと思います。

利用するのはOaracle Database 21cですが、In-Memory External Tablesは、18c以降であれば使える機能なので使えるはず!


12cまでのnon In-Memory External Tablesなころのレントゲンからです。

外部表は EXTERNAL TABLE ACCESS FULL というオペレーションになっています。CSVファイルを全て読み込んでいることを表ています。ここ大切なので、覚えておきましょう。

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 2727K| | 629 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 8168 | 2727K| 2984K| 629 (1)| 00:00:01 |
| 2 | VIEW | VW_FOJ_0 | 8168 | 2727K| | 30 (4)| 00:00:01 |
|* 3 | HASH JOIN FULL OUTER | | 8168 | 2727K| | 30 (4)| 00:00:01 |
| 4 | VIEW | | 838 | 139K| | 1 (100)| 00:00:01 |
|* 5 | FIXED TABLE FULL | X$KSPPI | 838 | 58660 | | 1 (100)| 00:00:01 |
| 6 | EXTERNAL TABLE ACCESS FULL| KSPPI_11_1_0_7_0 | 8168 | 1363K| | 29 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

外部表をフルスキャンしている。実際にはcsvファイルをまるっと読んで、ですよねーー。という感じ。外部表を利用されたことのある方であれば、ふむふむというところだと思います。

では21cの環境に切り替えて、前回Difference of Initialization Parameters between 19c (19.3.0.0.0) and 21c (21.3.0.0.0) - including hidden paramsを行った sysユーザーに接続して違いを見ていきましょう。

まずは、インメモリーを使わない状態で見てみます。(rpmでインストールしてconfigureしただけの21cデフォルトの状態。。のはずw。 こちらではカスタマイズしてないので)

ビルド表とプローブ表が12cR1の実行計画と逆になってますが、まあ気にしないw
外部表は、12cR1と同様に EXTERNAL TABLE ACCESS FULL ですね。

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 Version 21.3.0.0.0

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5997 | 2002K| | 457 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 5997 | 2002K| 2192K| 457 (1)| 00:00:01 |
| 2 | VIEW | VW_FOJ_0 | 5997 | 2002K| | 15 (7)| 00:00:01 |
|* 3 | HASH JOIN FULL OUTER | | 5997 | 1399K| | 15 (7)| 00:00:01 |
| 4 | EXTERNAL TABLE ACCESS FULL| KSPPI_19_3_0_0_0 | 5412 | 359K| | 14 (0)| 00:00:01 |
| 5 | VIEW | | 5997 | 1001K| | 1 (100)| 00:00:01 |
|* 6 | FIXED TABLE FULL | X$KSPPI | 5997 | 415K| | 1 (100)| 00:00:01 |
----------------------------------------------------------------------------------------------------------


では、外部表をインメモリー対応に作り変えます。
INMEMORY句を追加してる箇所がポイントですね。

DROP TABLE ksppi_19_3_0_0_0;
CREATE TABLE ksppi_19_3_0_0_0 (
ksppinm VARCHAR2(80)
,ksppdesc VARCHAR2(255)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tab
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
(
ksppinm
,ksppdesc
)
)
LOCATION (
'19.3.0.0.0.ksppi.csv'
)
)
INMEMORY MEMCOMPRESS FOR CAPACITY
;


おおお?? ビルド表とプローブ表が入れ替わりましたが、外部表は、EXTERNAL TABLE ACCESS FULL のままですね。
なにか設定し忘れているようです。

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 102K| 33M| | 7807 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 102K| 33M| 36M| 7807 (1)| 00:00:01 |
| 2 | VIEW | VW_FOJ_0 | 102K| 33M| | 341 (1)| 00:00:01 |
|* 3 | HASH JOIN FULL OUTER | | 102K| 33M| | 341 (1)| 00:00:01 |
| 4 | VIEW | | 5997 | 1001K| | 1 (100)| 00:00:01 |
|* 5 | FIXED TABLE FULL | X$KSPPI | 5997 | 415K| | 1 (100)| 00:00:01 |
| 6 | EXTERNAL TABLE ACCESS FULL| KSPPI_19_3_0_0_0 | 102K| 16M| | 341 (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

ん、外部表自体のインメモリー定義は問題なさそうですね。

SYS@ORCLCDB> r
1 select table_name, inmemory, inmemory_compression
2* from user_tables where EXTERNAL = 'YES'

TABLE_NAME INMEMORY INMEMORY_COMPRESSION
------------------------------ ------------------------ ---------------------------------------------------
OPATCH_XML_INV DISABLED
KSPPI_19_3_0_0_0 ENABLED FOR CAPACITY LOW


ポピュレーションされてないのか?

SYS@ORCLCDB> select SEGMENT_NAME,INMEMORY_SIZE,BYTES_NOT_POPULATED,POPULATE_STATUS,IS_EXTERNAL from v$im_segments;

レコードが選択されませんでした。


あ”〜〜っ! Oracle ACEのKPI入力期限まであと3ヶ月ある。あせるなwwww 大丈夫だw(謎

では、手動で。

SYS@ORCLCDB> exec dbms_inmemory.populate('SYS','KSPPI_19_3_0_0_0');

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

SYS@ORCLCDB> select SEGMENT_NAME,INMEMORY_SIZE,BYTES_NOT_POPULATED,POPULATE_STATUS,IS_EXTERNAL from v$im_segments;

レコードが選択されませんでした。


ん? なんで?
あれか!

SYS@ORCLCDB> show parameter inmemory_size

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
inmemory_size big integer 0


やはり、だよね〜w デフォルトのままだもの。そりゃそうだ。

では、変更しましょう。inmemory_sizeは、最低 100MBは指定しないといけないので、今回は最低サイズ(十分だと思うので)に。

SYS@ORCLCDB> create pfile from spfile;

ファイルが作成されました。

SYS@ORCLCDB> alter system set inmemory_size = 100m scope=spfile;

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

SYS@ORCLCDB> shutdown immediate
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
SYS@ORCLCDB> startup
ORACLEインスタンスが起動しました。

Total System Global Area 5586811432 bytes
Fixed Size 9697832 bytes
Variable Size 956301312 bytes
Database Buffers 4496293888 bytes
Redo Buffers 7077888 bytes
In-Memory Area 117440512 bytes
データベースがマウントされました。
データベースがオープンされました。
SYS@ORCLCDB> show parameter inmemory_size

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
inmemory_size big integer 112M


よし!! これで大丈夫なはず。

SYS@ORCLCDB> select SEGMENT_NAME,INMEMORY_SIZE,BYTES_NOT_POPULATED,POPULATE_STATUS,IS_EXTERNAL from v$im_segments;

レコードが選択されませんでした。

SYS@ORCLCDB> exec dbms_inmemory.populate('SYS','KSPPI_19_3_0_0_0');

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

SYS@ORCLCDB> select SEGMENT_NAME,INMEMORY_SIZE,BYTES_NOT_POPULATED,POPULATE_STATUS,IS_EXTERNAL from v$im_segments;

SEGMENT_NAME INMEMORY_SIZE BYTES_NOT_POPULATED POPULATE_STATUS IS_EXTERNAL
------------------------------ ------------- ------------------- --------------------------------------- ---------------
KSPPI_19_3_0_0_0 1179648 0 COMPLETED TRUE

では、こんどこそ!!

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 102K| 33M| | 7807 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 102K| 33M| 36M| 7807 (1)| 00:00:01 |
| 2 | VIEW | VW_FOJ_0 | 102K| 33M| | 341 (1)| 00:00:01 |
|* 3 | HASH JOIN FULL OUTER | | 102K| 33M| | 341 (1)| 00:00:01 |
| 4 | VIEW | | 5997 | 1001K| | 1 (100)| 00:00:01 |
|* 5 | FIXED TABLE FULL | X$KSPPI | 5997 | 415K| | 1 (100)| 00:00:01 |
| 6 | EXTERNAL TABLE ACCESS FULL| KSPPI_19_3_0_0_0 | 102K| 16M| | 341 (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

あれ〜〜〜〜〜〜〜っ。 もう一つ忘れてた。query_rewrite_integrity.

Database Reference 2.294 QUERY_REWRITE_INTEGRITY
https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/QUERY_REWRITE_INTEGRITY.html

SYS@ORCLCDB> alter session set query_rewrite_integrity=stale_tolerated;

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


外部表のオペレーションが EXTERNAL TABLE ACCESS INMEMORY FULL に変化しました。

-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 102K| 33M| | 7807 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 102K| 33M| 36M| 7807 (1)| 00:00:01 |
| 2 | VIEW | VW_FOJ_0 | 102K| 33M| | 341 (1)| 00:00:01 |
|* 3 | HASH JOIN FULL OUTER | | 102K| 33M| | 341 (1)| 00:00:01 |
| 4 | VIEW | | 5997 | 1001K| | 1 (100)| 00:00:01 |
|* 5 | FIXED TABLE FULL | X$KSPPI | 5997 | 415K| | 1 (100)| 00:00:01 |
| 6 | EXTERNAL TABLE ACCESS INMEMORY FULL| KSPPI_19_3_0_0_0 | 102K| 16M| | 341 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------


まとめ

18cで登場した In-Memory External Tables のレントゲンは以下の通り。
EXTERNAL TABLE ACCESS INMEMORY FULL というオペレーションがポイントです。
また、INMEMORY_SIZEパラメータの設定や、QUERY_REWRITE_INTEGRITYパラメータをstale_toleratedにする必要あります。忘れがち?!


21cでの non in-memory external tables と in-memory external tables のレントゲンをSQL MONITORの実行計画を。

non in-memory external tables : EXTERNAL TABLE ACCESS FULL

Global Stats
=====================================================================================================
| Elapsed | Cpu | IO | PL/SQL | Other | Fetch | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes |
=====================================================================================================
| 0.06 | 0.05 | 0.00 | 0.00 | 0.00 | 406 | 121 | 1 | 512KB | 16 | 683 |
=====================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=1973289935)
========================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (%) | (# samples) |
========================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +1 | 1 | 6066 | | | | | . | | |
| 1 | SORT ORDER BY | | 102K | 7807 | 1 | +1 | 1 | 6066 | | | | | 676KB | | |
| 2 | VIEW | VW_FOJ_0 | 102K | 341 | 1 | +1 | 1 | 6066 | | | | | . | | |
| 3 | HASH JOIN FULL OUTER | | 102K | 341 | 1 | +1 | 1 | 6066 | | | | | 2MB | | |
| 4 | VIEW | | 5997 | 1 | 1 | +1 | 1 | 5997 | | | | | . | | |
| 5 | FIXED TABLE FULL | X$KSPPI | 5997 | 1 | 1 | +1 | 1 | 5997 | | | | | . | | |
| 6 | EXTERNAL TABLE ACCESS FULL | KSPPI_19_3_0_0_0 | 102K | 341 | 1 | +1 | 1 | 5412 | 1 | 512KB | 16 | | . | | |
========================================================================================================================================================================================

in-memory external tables : EXTERNAL TABLE ACCESS INMEMORY FULL

Global Stats
======================================================================================
| Elapsed | Cpu | IO | PL/SQL | Other | Fetch | Buffer | Write | Write |
| Time(s) | Time(s) | Waits(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
======================================================================================
| 0.04 | 0.04 | 0.00 | 0.00 | 0.00 | 406 | 48 | 16 | 683 |
======================================================================================

SQL Plan Monitoring Details (Plan Hash Value=1973289935)
==================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Write | Write | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
==================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 2 | +0 | 1 | 6066 | | | . | | |
| 1 | SORT ORDER BY | | 102K | 7807 | 2 | +0 | 1 | 6066 | | | 676KB | | |
| 2 | VIEW | VW_FOJ_0 | 102K | 341 | 1 | +0 | 1 | 6066 | | | . | | |
| 3 | HASH JOIN FULL OUTER | | 102K | 341 | 1 | +0 | 1 | 6066 | | | 3MB | | |
| 4 | VIEW | | 5997 | 1 | 1 | +0 | 1 | 5997 | | | . | | |
| 5 | FIXED TABLE FULL | X$KSPPI | 5997 | 1 | 1 | +0 | 1 | 5997 | | | . | | |
| 6 | EXTERNAL TABLE ACCESS INMEMORY FULL | KSPPI_19_3_0_0_0 | 102K | 341 | 2 | +0 | 1 | 5412 | 16 | | . | | |
==================================================================================================================================================================================

レントゲンは大切 !!!! :)


東京は一気に葉桜になりつつある。。。ではまた。





Related article on Mac De Oracle
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 1 / TABLE FULL SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 2 / INDEX UNIQUE SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 3 / INDEX RANGE SCAN, Index Only Scan
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 4 / INDEX RANGE SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 5 / INDEX RANGE SCAN, INLIST ITERATOR
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 6 / INDEX FAST SCAN, Index Only Scan
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 7 / INDEX FULL SCAN,Index Only Scan
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 8 / INDEX SKIP SCAN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 9 / TABLE ACCESS INMEMORY FULL
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 10 / NESTED LOOP JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 11 / MERGE JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 12 / HASH JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 13 / HASH JOIN OUTER
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 14 / HASH JOIN FULL OUTER
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 15 / PX, TABLE ACCESS FULL
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 16 / CONCATENATION
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 17 / SORT UNIQUE, UNION-ALL = UNION
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 18 / UNION-ALL
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 19 / INTERSECTION
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 20 / MINUS
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 21 / WINDOW NOSORT STOPKEY
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 22 / COUNT STOPKEY
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 23 / HASH JOIN - LEFT-DEEP JOIN vs RIGHT-DEEP JOIN
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 24 / CONNECT BY NO FILTERING WITH START-WITH
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 25 / UNION ALL (RECURSIVE WITH) DEPTH FIRST, RECURSIVE WITH PUMP
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#1 / STAR TRANSFORM, VECTOR TRANSFORM (DWH向け)
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#2 / MERGE (UPSERT)
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#3 / RDFView
・実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#4 / INDEX FULL SCAN (MIN/MAX) - Index Only Scan
実行計画は、SQL文のレントゲン写真だ! No.30 - LOAD TABLE CONVENTIONAL vs. LOAD AS SELECT
実行計画は、SQL文のレントゲン写真だ! No.31 - TEMP TABLE TRANSFORMATION LOAD AS SELECT (CURSOR DURATION MEMORY)

| | コメント (0)

2022年3月21日 (月)

Oracle vagrant-projects

今日は約3ヶ月振りにVIrtualBoxの起動やら、Oracle VMの起動でハマって、このあたりの環境どうしようかなあぁ

と思いつつ 21cとかもあるしなぁと考えていたら。横目でみつつも、大人の事情でスルーしていたやつが結構育ってて来てたので、これやるのもいいかなぁと。まだ軽く調べ始めたところだが、来月早々にメインマシン変えるからそのあとな。

 

ひとまず、メモ 

Oracle Vagrant-projects

https://github.com/oracle/vagrant-projects

 

20220321-203926

| | コメント (0)

2019年9月30日 (月)

なぜ、そこに、LONG型があるんだ / FAQ

all/dba/user_tab_columns

https://docs.oracle.com/cd/E82638_01/refrn/ALL_TAB_COLUMNS.html#GUID-F218205C-7D76-4A83-8691-BFD2AD372B63

これらのビューは、列の属性関連の情報を持つビューです。
たまに、便利なびゅーではあるのですが、これらのビューをアクセスする使うスクリプトというかPL/SQLでコード書くこともあるのですが、一箇所だけ、使いにくいところがあります。

 

どこかわかります?

下位互換のためだろうと思われるのですが、一般には推奨されていない LONG型の列 が残っています。

ご存知だとは思いますが、一般的なガイドだと、CLOBの利用が推奨されています。
下位互換のためだから仕方ないのだとは思うのですが。

LONG型といえば、とにかく制約が多くて、文字列操作を行うにもめんどくさいわけで、実際に利用したい状況になると、うううううっとなることしばしば。

LONG型

で、普段どうやって、その面倒くさいところを回避しているかといえば、CLOBに変換してしまうことがが多いです。
CLOBにしてしまえば、沢山の制約から解放されますしね :)

以下のような感じで。


SCOTT> l
1 CREATE TABLE my_dba_tab_columns
2 AS
3 SELECT
4 owner
5 ,table_name
6 ,column_name
7 ,data_type
8 ,data_type_mod
9 ,data_type_owner
10 ,data_length
11 ,data_precision
12 ,data_scale
13 ,nullable
14 ,column_id
15 ,TO_CLOB(default_length) AS default_length
16 ,num_distinct
17 ,low_value
18 ,high_value
19 ,density
20 ,num_nulls
21 ,num_buckets
22 ,last_analyzed
23 ,sample_size
24 ,character_set_name
25 ,char_col_decl_length
26 ,global_stats
27 ,user_stats
28 ,avg_col_len
29 ,char_length
30 ,char_used
31 ,v80_fmt_image
32 ,data_upgraded
33 ,histogram
34 ,default_on_null
35 ,identity_column
36 ,sensitive_column
37 ,evaluation_edition
38 ,unusable_before
39 ,unusable_beginning
40 ,collation
41 FROM
42* dba_tab_columns
SCOTT> /

Table created.

SCOTT> desc dba_tab_columns
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(128)
TABLE_NAME NOT NULL VARCHAR2(128)
COLUMN_NAME NOT NULL VARCHAR2(128)
DATA_TYPE VARCHAR2(128)
DATA_TYPE_MOD VARCHAR2(3)
DATA_TYPE_OWNER VARCHAR2(128)
DATA_LENGTH NOT NULL NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
COLUMN_ID NUMBER
DEFAULT_LENGTH NUMBER
DATA_DEFAULT LONG
NUM_DISTINCT NUMBER
LOW_VALUE RAW(2000)
HIGH_VALUE RAW(2000)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
CHAR_COL_DECL_LENGTH NUMBER
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
AVG_COL_LEN NUMBER
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)
V80_FMT_IMAGE VARCHAR2(3)
DATA_UPGRADED VARCHAR2(3)
HISTOGRAM VARCHAR2(15)
DEFAULT_ON_NULL VARCHAR2(3)
IDENTITY_COLUMN VARCHAR2(3)
SENSITIVE_COLUMN VARCHAR2(3)
EVALUATION_EDITION VARCHAR2(128)
UNUSABLE_BEFORE VARCHAR2(128)
UNUSABLE_BEGINNING VARCHAR2(128)
COLLATION VARCHAR2(100)

SCOTT> desc my_dba_tab_columns
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(128)
TABLE_NAME NOT NULL VARCHAR2(128)
COLUMN_NAME NOT NULL VARCHAR2(128)
DATA_TYPE VARCHAR2(128)
DATA_TYPE_MOD VARCHAR2(3)
DATA_TYPE_OWNER VARCHAR2(128)
DATA_LENGTH NOT NULL NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
COLUMN_ID NUMBER
DEFAULT_LENGTH CLOB
NUM_DISTINCT NUMBER
LOW_VALUE RAW(2000)
HIGH_VALUE RAW(2000)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
CHAR_COL_DECL_LENGTH NUMBER
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
AVG_COL_LEN NUMBER
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)
V80_FMT_IMAGE VARCHAR2(3)
DATA_UPGRADED VARCHAR2(3)
HISTOGRAM VARCHAR2(15)
DEFAULT_ON_NULL VARCHAR2(3)
IDENTITY_COLUMN VARCHAR2(3)
SENSITIVE_COLUMN VARCHAR2(3)
EVALUATION_EDITION VARCHAR2(128)
UNUSABLE_BEFORE VARCHAR2(128)
UNUSABLE_BEGINNING VARCHAR2(128)
COLLATION VARCHAR2(100)

 


db tech showcase 2019もおわり、今年も残すところ 3ヶ月あまり。一年早い. そして。
来週は、開催時期を秋に変更してから2回目の多摩川花火大会。天気がよいといいのですが:)

ではまた。

| | コメント (0)

2019年8月25日 (日)

FAQ / PL/SQL PACKAGEでパプリックスコープを持つ定数をSQL文中で利用するには...

かなーり、ご無沙汰しておりました。(本業でいっぱいいっぱいで、という言い訳はこれぐらいにしてw) 偶に聞かれることがあるので、FAQネタから。 パッケージでパブリックなスコープを持つ定数は無名PL/SQLブロックやパッケージ、プロシージャ、ファンクションでしか参照できないんですよねー 例えば、DBMS_CRYPTOパッケージでHASHファンクションを利用してSH-256を作成したいなーと思って、

39.4 DBMS_CRYPTOのアルゴリズム
https://docs.oracle.com/cd/F19136_01/arpls/DBMS_CRYPTO.html#GUID-CE3CF17D-E781-47CB-AEE7-19A9B2BCD3EC
DBMS_CRYPTO.HASH()は関数なのでSQL文から呼びたーい、と以下のような使い方をすると...

SQL> SELECT DBMS_CRYPTO.HASH(TO_CLOB('hoge'), DBMS_CRYPTO.HASH_SH2569) AS "SH-256" FROM dual;
SELECT DBMS_CRYPTO.HASH(TO_CLOB('hoge'), DBMS_CRYPTO.HASH_SH2569) AS "SH-256" FROM dual
*
ERROR at line 1:
ORA-00904: "DBMS_CRYPTO"."HASH_SH2569": invalid identifier


SQL>
SQL> select DBMS_CRYPTO.HASH_SH256 from dual;
select DBMS_CRYPTO.HASH_SH256 from dual
*
ERROR at line 1:
ORA-06553: PLS-221: 'HASH_SH256' is not a procedure or is undefined
見事にエラーとなるわけです。 DBMS_CRYPTO.HASH_SH256は、パッケージファンクションではないので...利用可能なのはPL/SQLでのみ。
SQL> set serveroutput on
SQL>
¥SQL>
SQL> begin
2 dbms_output.put_line('DBMS_CRYPTO.HASH_SH256 : ' || DBMS_CRYPTO.HASH_SH256);
3 end;
4 /
DBMS_CRYPTO.HASH_SH256 : 4

PL/SQL procedure successfully completed.
SQL文で活用する為には、ファンクションでラップする必要があります。 以下のように。
SQL> l
1 CREATE OR REPLACE FUNCTION get_hash_sh256_type
2 RETURN NUMBER
3 AS
4 BEGIN
5 RETURN DBMS_CRYPTO.HASH_SH256;
6* END;
SQL> /

Function created.

SQL>
冒頭でエラーとなっていたSQL文をDBMS_CRYPTO.HASH_SH256を返すファンクションを使うように書き換えると、 はい、できました。
SQL> l
1 SELECT
2 DBMS_CRYPTO.HASH(
3 TO_CLOB('hoge')
4 , get_hash_sh256_type()
5 ) AS "SH-256"
6 FROM
7* dual
SQL> /

SH-256
--------------------------------------------------------------------------------
ECB666D778725EC97307044D642BF4D160AABB76F56C0069C71EA25B1E926825

SQL>


露店の焼きそばと焼き鳥を食べつつ、晩夏の夏祭りと、涼しい朝晩の気温で熟睡可能な山形より。 では、では。

| | コメント (0)

2019年3月22日 (金)

ORA_HASH()を使ってリストパーティションにハッシュパーティションのような均一配分を

ハッシュパーティションってリストパーティションみたいにパーティション狙い撃ちできて、かつ、ハッシュパーティションみたいに、データをパーティション間で均一化できないのかなぁ
というずいぶん昔の話を思い出して、そう言えば書いてないかもしれない。いつの話だよってぐらい昔の話だけどw
どうやったかというと、
ハッシュキーにふさわしい値をもつ列を決める(一意キーとか主キー列が理想、カーディナリティの低い、分布に偏りのあるデータを持つ列は使わない)

で、ハッシュキーが決まったら、話は早くて、ORA_HASH()関数で取得できるハッシュ値を利用したリストパーティションを作成するだけ。
ゴニョゴニュ言わなくても、SQLとPL/SQLのコードを見ていただければ、理解していただけるかと。

ORCL@SCOTT> l
1 CREATE TABLE list_p_tab
2 (
3 id_code VARCHAR2(10) NOT NULL
4 , foo VARCHAR2(30)
5 , id_code_hash_value NUMBER(2) NOT NULL
6 )
7 PARTITION BY LIST (id_code_hash_value)
8 (
9 PARTITION list_p_tab_p1 VALUES(0)
10 ,PARTITION list_p_tab_p2 VALUES(1)
11 ,PARTITION list_p_tab_p3 VALUES(2)
12 ,PARTITION list_p_tab_p4 VALUES(3)
13* )
ORCL@SCOTT> /

Table created.

ORCL@SCOTT> ALTER TABLE list_p_tab ADD CONSTRAINT gpk_list_p_tab PRIMARY KEY(id_code) USING INDEX GLOBAL;

Table altered.

ORCL@SCOTT> l
1 DECLARE
2 TYPE id_code_t IS TABLE OF list_p_tab.id_code%TYPE INDEX BY PLS_INTEGER;
3 TYPE foo_t IS TABLE OF list_p_tab.foo%TYPE INDEX BY PLS_INTEGER;
4 id_codes id_code_t;
5 foos foo_t;
6 k PLS_INTEGER := 1;
7 BEGIN
8 FOR i IN 1..400000 LOOP
9 id_codes(k) := TO_CHAR(i,'fm0000000009');
10 foos(k) := i;
11 k := k + 1;
12 IF k > 1000 THEN
13 FORALL j in 1..k-1
14 INSERT INTO list_p_tab VALUES(id_codes(j), foos(j), ORA_HASH(id_codes(j),3));
15 COMMIT;
16 k := 1;
17 END IF;
18 END LOOP;
19* END;
ORCL@SCOTT> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.28

ORCL@SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'LIST_P_TAB',granularity=>'ALL',cascade=>true,no_invalidate=>false,degree=>4);

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.51

ORCL@SCOTT> r
1 select
2 table_name
3 ,partition_name
4 ,num_rows
5 from
6 user_tab_partitions
7 where
8 table_name = 'LIST_P_TAB'
9 order by
10* 1,2

TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
LIST_P_TAB LIST_P_TAB_P1 99901
LIST_P_TAB LIST_P_TAB_P2 100194
LIST_P_TAB LIST_P_TAB_P3 100056
LIST_P_TAB LIST_P_TAB_P4 99849

ORCL@SCOTT> select id_code_hash_value,count(1) from list_p_tab group by id_code_hash_value order by 1;

ID_CODE_HASH_VALUE COUNT(1)
------------------ ----------
0 99901
1 100194
2 100056
3 99849

Elapsed: 00:00:00.06

ORCL@SCOTT> explain plan for
2 select
3 *
4 from
5 list_p_tab
6 where
7 id_code_hash_value = 1;

Explained.

Elapsed: 00:00:00.10
ORCL@SCOTT> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2143708561

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 2054K| 275 (1)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE| | 100K| 2054K| 275 (1)| 00:00:01 | 2 | 2 |
| 2 | TABLE ACCESS FULL | LIST_P_TAB | 100K| 2054K| 275 (1)| 00:00:01 | 2 | 2 |
----------------------------------------------------------------------------------------------------

グローバル索引を作成してあるので、パーティション関係ない検索は主キー索引経由でも可。

ORCL@SCOTT> explain plan for select * from list_p_tab where id_code = '00004000000';

Explained.

Elapsed: 00:00:00.02
ORCL@SCOTT> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 4132161764

---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 3 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| LIST_P_TAB | 1 | 21 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX UNIQUE SCAN | GPK_LIST_P_TAB | 1 | | 2 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID_CODE"='00004000000')

| | コメント (0)

2019年3月21日 (木)

in-memory関連の謎パラメータ 18c

12cR2と18cのin-memory関連の隠しパラメータ以外の違い。
Database Reference Release 18
inmemory_optimized_arithmeticってパラメータ、SIMD向け最適化っぽい(デフォがDESABLEDみたいだが)
inmemory_prefer_xmem_memcompress と inmemory_prefer_xmem_priority inmemory_xmem_sizeこれらのパラメータ、隠しパラメータじゃないのにundocumented なのはなぜ?
もしかして、xmem って略語、Exadataの資料でヒットした。。。Exadata オンリー? なやつかな?
BANNER_LEGACY                                                                   BANNER                                                                          
------------------------------------------------------------------------------- -------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

ORCL@SYS> show parameter memory orcl12c@SYS> show parameter memory

NAME TYPE VALUE NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ ------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0 hi_shared_memory_address integer 0
inmemory_adg_enabled boolean TRUE inmemory_adg_enabled boolean TRUE
inmemory_automatic_level string OFF
inmemory_clause_default string inmemory_clause_default string
inmemory_expressions_usage string ENABLE inmemory_expressions_usage string ENABLE
inmemory_force string DEFAULT inmemory_force string DEFAULT
inmemory_max_populate_servers integer 0 inmemory_max_populate_servers integer 0
inmemory_optimized_arithmetic string DISABLE
inmemory_prefer_xmem_memcompress string
inmemory_prefer_xmem_priority string
inmemory_query string ENABLE inmemory_query string ENABLE
inmemory_size big integer 0 inmemory_size big integer 0
inmemory_trickle_repopulate_servers_ integer 1 inmemory_trickle_repopulate_servers_ integer 1
percent percent
inmemory_virtual_columns string MANUAL inmemory_virtual_columns string MANUAL
inmemory_xmem_size big integer 0
memory_max_target big integer 0 memory_max_target big integer 0
memory_target big integer 0 memory_target big integer 0
optimizer_inmemory_aware boolean TRUE optimizer_inmemory_aware boolean TRUE
shared_memory_address integer 0 shared_memory_address integer 0

| | コメント (0)

ハッシュパーティションでのデータの偏り / FAQ

ぼやき漫才みたいな感じですが、Oracleに限らず、ハッシュパーティションでパーティション間のデータを均一にしたいなら、ユニークな値かそれに準ずる列を選ぶべきなわけですが、なにを間違ってしまったのか、稀ではありますが、少々残念なことになっていすることもあります。
とは言え、早めに気づけば影響も小さくて済むわけで:)


というわけで、今回はそんなおはなし。

id_code列の値はユニーク
ORCL@SCOTT> select count(*),count(distinct id_code) from org;

COUNT(*) COUNT(DISTINCTID_CODE)
---------- ----------------------
400000 400000
type列の値は、一意性がなくカーディナリティーも低い、かつ、大きな偏りがある。。
ORCL@SCOTT> select type,count(1) from org group by type;

type COUNT(1)
---------- ----------
1 60000
2 60000
9 60000
0 220000
ハッシュパーティションを選択する主な理由は、パーティションへのデータの均一分散なので、列の値がユニークな列をパーティションキーとしてパーティション化することが多いわけですが、。。
以下は、ハッシュキーに一意な値を持つ列を選択した場合の例
ORCL@SCOTT> r
1 create table hash_p_tab
2 partition by hash(id_code)
3 (
4 partition hash_p_tab_p1
5 ,partition hash_p_tab_p2
6 ,partition hash_p_tab_p3
7 ,partition hash_p_tab_p4
8 )
9 as select
10 id_code
11 ,foo
12 ,type
13 from
14* org

Table created.

ORCL@SCOTT> alter table hash_p_tab add constraint gpk_hash_p_tab primary key(id_code) using index global;

Table altered.

ORCL@SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'hash_p_tab',cascade=>true,no_invalidate=>false,granularity=>'ALL');


ORCL@SCOTT> select table_name,partition_name,num_rows from user_tab_partitions order by 1,2;

TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
HASH_P_TAB HASH_P_TAB_P1 99901
HASH_P_TAB HASH_P_TAB_P2 100194
HASH_P_TAB HASH_P_TAB_P3 100056
HASH_P_TAB HASH_P_TAB_P4 99849


しかし、値の分布に偏りのあるカーディナリティの低い列を選んで残念なことになっているケースも稀にあったりします。

なぜ、ハッシュキーにこの列を選んだんだ! みたいな。。。

そんな時は、設計した人に聞くしかないです。何がやりたかったのかを。。。私に聞かれてもハッシュキーの選択をミスったんですよねーたぶん、としか言えないので。
ORCL@SCOTT> r
1 create table hash_p_tab_skew
2 partition by hash(type)
3 (
4 partition hash_p_tab_skew_p1
5 ,partition hash_p_tab_skew_p2
6 ,partition hash_p_tab_skew_p3
7 ,partition hash_p_tab_skew_p4
8 )
9 as select
10 id_code
11 ,foo
12 ,type
13 from
14* org

Table created.

ORCL@SCOTT> alter table hash_p_tab_skew add constraint gpk_hash_p_tab_skew primary key(id_code) using index global;

Table altered.

ORCL@SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'hash_p_tab_skew',cascade=>true,no_invalidate=>false,granularity=>'ALL');

PL/SQL procedure successfully completed.

ORCL@SCOTT> select table_name,partition_name,num_rows from user_tab_partitions where table_name = upper('hash_p_tab_skew') order by 1,2

TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
HASH_P_TAB_SKEW HASH_P_TAB_SKEW_P1 0
HASH_P_TAB_SKEW HASH_P_TAB_SKEW_P2 280000
HASH_P_TAB_SKEW HASH_P_TAB_SKEW_P3 60000
HASH_P_TAB_SKEW HASH_P_TAB_SKEW_P4 60000
で、データを均一にパーティションに分散させることをなんとなーく、イメージしつつ、上記のようなミスをしてしまうと、パラレルクエリーなどで最もデータの多いパーティションの処理時間に引っ張られて想定より処理時間が長くなるなんてことも。。。あるわけで
例がイケてないけど(気にしないでw)、パラレルサーバーのスレーブ間でbuffer gets部分を見ていただくと偏りはわかりやすいはず。(赤字部分):)
偏りあり
orcl2@SCOTT> @sql_skew
1 select
2 /*+
3 monitor
4 parallel(4)
5 */
6 count(1)
7 from
8 hash_p_tab_skew t01
9 inner join hash_p_tab_skew2 t02
10 on
11 t01.id_code = t02.id_code
12* and t01.type = t02.type


Parallel Execution Details (DOP=4 , Servers Allocated=4)
==========================================================================================
| Name | Type | Server# | Elapsed | Cpu | Other | Buffer | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Gets | (sample #) |
==========================================================================================
| PX Coordinator | QC | | 0.02 | 0.00 | 0.02 | 72 | |
| p000 | Set 1 | 1 | 0.18 | 0.17 | 0.01 | 1940 | |
| p001 | Set 1 | 2 | 0.05 | 0.05 | 0.00 | 448 | |
| p002 | Set 1 | 3 | 0.05 | 0.05 | 0.00 | 448 | |
| p003 | Set 1 | 4 | 0.00 | 0.00 | 0.00 | | |
==========================================================================================

偏りの悪影響のイメージはこんな感じ
20190321-165450
20190321-165622

偏りなし
orcl2@SCOTT> @sql_noskew
1 select
2 /*+
3 monitor
4 parallel(4)
5 */
6 count(1)
7 from
8 hash_p_tab t01
9 inner join hash_p_tab2 t02
10 on
11 t01.id_code = t02.id_code
12* and t01.type = t02.type


Parallel Execution Details (DOP=4 , Servers Allocated=4)
==========================================================================================
| Name | Type | Server# | Elapsed | Cpu | Other | Buffer | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Gets | (sample #) |
==========================================================================================
| PX Coordinator | QC | | 0.01 | 0.00 | 0.00 | 96 | |
| p000 | Set 1 | 1 | 0.08 | 0.08 | | 714 | |
| p001 | Set 1 | 2 | 0.08 | 0.08 | 0.00 | 714 | |
| p002 | Set 1 | 3 | 0.08 | 0.07 | 0.01 | 714 | |
| p003 | Set 1 | 4 | 0.08 | 0.08 | | 712 | |
==========================================================================================
パーティション毎にぞれぞれ得手不得手があります。そこんとこを把握したうえで、有効活用したいものですよね。(パーティションもいろいろ進化してきて便利になってきたわけですが、その分わかりにくいところも増えてきて理解するのに大変だったり 18cのも差分は把握しといたほうがいいかw...:)

| | コメント (0)

Join Elimination(結合の排除)と 参照整合性制約 / FAQ

偶に聞かれることがあるので、再び、Join Elimination(結合の排除)について
まずは、以下のSQL文を。
order表とcustomers表をinner joinしている単純な文ですが、重要なのは、実行計画の方!


order表とcustomers表をinner joinしているのに、order表だけ(この場合、order表の主キー索引だけのIndex Only Scanになっていますが)で、customes表を結合していせん。

理由は単純で、以下のSQL文では、customsers表の結合が不要なだけなんです。なぜかわかりますか?
以前、浅瀬でジャブジャブしていたセッション資料にヒントがあります。
order表に定義されている参照整合性制約によりcustomer_idがcustomsers表に存在していることを確認するための結合は不要と、オプティマイザーが判断した結果なんですよね。これ。
上記以外のケースでも無駄な結合を排除しようとする最適化を行うことがあります。内部的にはSQL文を書き換えてくれているわけですね。無駄に結合を行わないために。。。10053トレースをとって、 Join Elimination で grep をかけてみるとオプティマイザの気持ちが見えてきます:)
ORCL@OE> explain plan for
2 select
3 distinct
4 order_id
5 from
6 orders o
7 , customers c
8 where
9 o.customer_id = c.customer_id
10 and order_id < 2400;

Explained.

ORCL@OE> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 1653993310

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46 | 184 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| ORDER_PK | 46 | 184 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("ORDER_ID"<2400)
上記、SQL文は、参照整合性制約により、orders表に存在するcustomer_idがcustomers表に存在することが保証されているため、結合により存在確認が不要となり、Optimizerは内部的にSQL文を以下のように書き換えたということになります。賢いですよね。
ORCL@OE> r
1 explain plan for
2 select
3 distinct
4 order_id
5 from
6 orders o
7 where
8* order_id < 2400

Explained.

ORCL@OE> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 1653993310

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46 | 184 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| ORDER_PK | 46 | 184 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("ORDER_ID"<2400)

13 rows selected.


order表の参照整合性制約を確認しておきます。

ORCL@OE> r
1 select
2 table_name
3 ,owner
4 ,constraint_name
5 ,constraint_type
6 ,r_owner
7 ,r_constraint_name
8 ,status
9 ,rely
10 from
11 user_constraints
12 where
13* constraint_type='R'

TABLE_NAME OWNER CONSTRAINT_NAME C R_OWNER R_CONSTRAINT_NAME STATUS RELY
------------------------------ ------------------------------ ------------------------------ - ------------------------------ ------------------------------ -------- ----
ORDERS OE ORDERS_CUSTOMER_ID_FK R OE CUSTOMERS_PK ENABLED
INVENTORIES OE INVENTORIES_WAREHOUSES_FK R OE WAREHOUSES_PK ENABLED
INVENTORIES OE INVENTORIES_PRODUCT_ID_FK R OE PRODUCT_INFORMATION_PK ENABLED
ORDER_ITEMS OE ORDER_ITEMS_ORDER_ID_FK R OE ORDER_PK ENABLED
ORDER_ITEMS OE ORDER_ITEMS_PRODUCT_ID_FK R OE PRODUCT_INFORMATION_PK ENABLED
PRODUCT_DESCRIPTIONS OE PD_PRODUCT_ID_FK R OE PRODUCT_INFORMATION_PK ENABLED

ORCL@OE> r
1 select
2 table_name
3 ,column_name
4 ,constraint_name
5 from
6 user_cons_columns
7 where
8 table_name in ('ORDERS','CUSTOMERS')
9 order by
10* table_name

TABLE_NAME COLUMN_NAME CONSTRAINT_NAME
------------------------------ ------------------------------ ------------------------------
CUSTOMERS CUSTOMER_ID CUSTOMERS_PK
CUSTOMERS CUST_FIRST_NAME CUST_FNAME_NN
CUSTOMERS CUSTOMER_ID CUSTOMER_ID_MIN
CUSTOMERS CREDIT_LIMIT CUSTOMER_CREDIT_LIMIT_MAX
CUSTOMERS CUST_LAST_NAME CUST_LNAME_NN
ORDERS ORDER_ID ORDER_PK
ORDERS ORDER_TOTAL ORDER_TOTAL_MIN
ORDERS ORDER_MODE ORDER_MODE_LOV
ORDERS CUSTOMER_ID ORDER_CUSTOMER_ID_NN
ORDERS CUSTOMER_ID ORDERS_CUSTOMER_ID_FK
ORDERS ORDER_DATE ORDER_DATE_NN




Oracle SQL DeveloperでリバースエンジニアリングしたERDは以下のとおり

20190321-144842

では、最後に、参照整合性制約を無効化した場合、実行計画はどうなるか見ておきましょう。
ORCL@OE> alter table orders disable constraint orders_customer_id_fk;

Table altered.

ORCL@OE> r
1 select
2 table_name
3 ,owner
4 ,constraint_name
5 ,constraint_type
6 ,r_owner
7 ,r_constraint_name
8 ,status
9 ,rely
10 from
11 user_constraints
12 where
13* constraint_type='R'

TABLE_NAME OWNER CONSTRAINT_NAME C R_OWNER R_CONSTRAINT_NAME STATUS RELY
------------------------------ ------------------------------ ------------------------------ - ------------------------------ ------------------------------ -------- ----
ORDERS OE ORDERS_CUSTOMER_ID_FK R OE CUSTOMERS_PK DISABLED
INVENTORIES OE INVENTORIES_WAREHOUSES_FK R OE WAREHOUSES_PK ENABLED
INVENTORIES OE INVENTORIES_PRODUCT_ID_FK R OE PRODUCT_INFORMATION_PK ENABLED
ORDER_ITEMS OE ORDER_ITEMS_ORDER_ID_FK R OE ORDER_PK ENABLED
ORDER_ITEMS OE ORDER_ITEMS_PRODUCT_ID_FK R OE PRODUCT_INFORMATION_PK ENABLED
PRODUCT_DESCRIPTIONS OE PD_PRODUCT_ID_FK R OE PRODUCT_INFORMATION_PK ENABLED

あらまあ、不思議w わざとらしいw
customers表げ結合されちゃってネステッドループ結合に!
ORCL@OE> r
1 explain plan for
2 select
3 distinct
4 order_id
5 from
6 orders o
7 , customers c
8 where
9 o.customer_id = c.customer_id
10* and order_id < 2400

Explained.

ORCL@OE> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 2552081916

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46 | 552 | 3 (34)| 00:00:01 |
| 1 | SORT UNIQUE NOSORT | | 46 | 552 | 3 (34)| 00:00:01 |
| 2 | NESTED LOOPS SEMI | | 46 | 552 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| ORDERS | 46 | 368 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | ORDER_PK | 46 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 319 | 1276 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("O"."CUSTOMER_ID">0)
4 - access("ORDER_ID"<2400)
5 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")

目黒方面の密林で、美登利の寿司弁当を食べるのが最近のマイブームw
ではまた。

| | コメント (0)

2019年2月21日 (木)

Wait Events

データベース関連で待機イベントと言えば、これまでは、Oracle Database しか浮かばなかったわけですが、今は、PostgreSQL、そして、MySQL にも実装された。

待機イベントを知らずして、どうするの? でも大丈夫。 今までOracleの待機イベントに親しんできたデータベースエンジニアの活躍の場が広がるんじゃないかなぁ。。。と遠くをみている。。。

Oracle Database Wait Events

PostgreSQL Wait Events

MySQL : 25.12.15.1 Wait Event Summary Tables

| | コメント (0) | トラックバック (0)

2019年2月11日 (月)

SQL*Plusでcsv出力できるんですよ #2 null はどうなる? / FAQ

前回は、SQL*Plusでcsvファイルをお手軽にできることを確認したので、今回はもう少し細かいところを確認しておきます。

csvファイルを作成するOracle Databaseのバージョン等は以下のとおり。

SQL> select
2 banner_full
3 from
4 v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

データベースキャラクタセットは最近では一般的なAL32UTF8

SQL> r
1 select
2 parameter
3 , value
4 from
5 nls_database_parameters
6 where
7* parameter in ('NLS_CHARACTERSET')

PARAMETER VALUE
---------------------------------------- ------------------------------
NLS_CHARACTERSET AL32UTF8

SQL>
SQL> !echo $NLS_LANG
Japanese_Japan.AL32UTF8

SQL> !echo $LANG
ja_JP.UTF-8

適当に作成した表は以下のとおり。NULLの取り込みを見ておきたかったのでnullも含めてあります。

SQL> desc test
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
DATA VARCHAR2(10)
FOO NOT NULL VARCHAR2(10)

SQL> select * from test order by id;

ID DATA FOO
---------- ---------- ----------
1 テスト note
2 平成 note
3 abcdbef note
4 あ note
5 A note
6 note

6行が選択されました。


id=6のdata列は null なのですが空白区別しにくいので可視化して確認しておきます。
注意)set null コマンドで設定した文字列は csv作成時のにも反映されるため空にリセットすることをお忘れなく。

SQL> set null [null]
SQL> select * from test order by id;

ID DATA FOO
---------- ---------- ----------
1 テスト note
2 平成 note
3 abcdbef note
4 あ note
5 A note
6 [null] note

6行が選択されました。

SQL> set null ""

csvファイルの作成。スクリプトの例は前回の記事(SQL*Plusでcsv出力できるんですよ / FAQ)参照のこと。

SQL> @makecsv test
SQL> !cat loaddata_test.csv
1,"テスト","note"
2,"平成","note"
3,"abcdbef","note"
4,"あ","note"
5,"A","note"
6,,"note"

SQL> exit
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Productionとの接続が切断されました。
discus-mother:˜ oracle$


ということで、 nullは、,, としてcsvファイルに書き出されることを確認しました。
だたし、set nullでnullを他の文字列に置き換えている場合には、置換した文字列がそのままcsvファイルへかきだされてしまうので注意が必要です。




previously on Mac De Oracle
SQL*Plusでcsv出力できるんですよ / FAQ


数日前の朝、仕事先に向かおうとしら、ちょいと熱っぽい?、頭痛もあるな! と体温を測ったら37度、インフル?
と思い仕事を休んで夕方まで様子見。。。熱は夜更けすぎに、平熱と変わっていましたw めでたしめでたし:) インフルじゃなくてよかった。
家庭内隔離解除されてほっとしているところ。。
では、また。

| | コメント (0) | トラックバック (0)

SQL*Plusでcsv出力できるんですよ / FAQ

SQL*Plusでcsv出力する簡単な方法って、意外に知られてないようなのでメモ程度に書いておきます。
自分でもコピペネタとするためにw

SQL> select * from q order by id;

ID DATA
---------- ----------
1 テスト
2 平成
3 abcdbef
4 あ
5 A

SQL> set markup csv on
SQL> select * from q order by id;

"ID","DATA"
1,"テスト"
2,"平成"
3,"abcdbef"
4,"あ"
5,"A"

SQL> set markup csv off

set markup csv on でcsv出力を簡単に取得できます。
これがなかったころはパッケージ作ったりしてましたけど、これなら手間いらず:)

スプールしてファイルに書き出すスクリプトを作っておくと便利です。
以下のスクリプトは &1 パラメータでcsv化する表名称を渡すだけ。

SQL> !cat makecsv.sql
--
-- parameter 1 : table name
--
set feed off
set timi off
set head off
set termout off
set veri off
set markup csv on
spool loaddata_&1..csv
select * from &1 order by id;
spo off
set markup csv off
set termout on
set head on
set feed on
set veri on
undefine 1

SQL> @makecsv q
SQL>
SQL> !cat loaddata_q.csv
1,"テスト"
2,"平成"
3,"abcdbef"
4,"あ"
5,"A"

| | コメント (0) | トラックバック (0)

2019年2月10日 (日)

18cのv$versionは列が増えてるのね / FAQ

今気づいたが、Oracle Database 18cのv$versionがいくつかの表示パターンにあわせたのか、3列になったのね。

SQL> select * from v$version;

BANNER BANNER_FULL BANNER_LEGACY CON_ID
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production 0
Version 18.3.0.0.0

| | コメント (0) | トラックバック (0)

2019年1月20日 (日)

Understanding the Oracle Database 18c Technical Architecture

私が、Ingresを使ってた横で、
Oracle 7のマニュアルよんでも意味わからなーいというプロジェクトメンバーのヘルプために、
なんで俺が読まなきゃいけないのーーと思いながら、
Oracle 7のアーキテクチャをマニュアル読み進めていくうちにどっぷりはまっていったという
くらい綺麗だなーと思ったことを、最新のマニュアルを見て思い出すなどw(どんな昔話だよw

今見ても綺麗だなーとはおもいますね。

Understanding the Oracle Database 18c Technical Architecture
https://www.oracle.com/webfolder/technetwork/tutorials/architecture-diagrams/18/technical-architecture/database-technical-architecture.html#

| | コメント (0) | トラックバック (0)