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月31日 (木)

Difference of Initialization Parameters between 19c (19.3.0.0.0) and 21c (21.3.0.0.0) - including hidden params

Difference of Initialization Parameters between 19c (19.3.0.0.0) and 21c (21.3.0.0.0) - including hidden params

Previously on Mac De Oracle
前回は、21cではお約束のパラメータ増加傾向を確認しました。今回もお約束のパラメータ差分をw

新しいメジャーリリースでたらやっとかないとねw

利用しているSQLなどは、昔のエントリーにサンプルがあるので参考にしてみてください。(うまく動作しないなどあれば、コメント頂けると幸いです。細かく確認してないので)

作成した19cと21cのパラメータ差分は↓↓↓↓↓↓↓
Difference of Initialization Parameters between 19c (19.3.0.0.0) and 21c (21.3.0.0.0)

作成方法は、みなさん知っていると思うので省略してもいいかもしれないですがw 軽めに書いておきます。
19cのパラメータをcsv化して、21cでは外部表として使います(詳細は前述のリンク先エントリーで確認ください)

適当なディレクトリに19cのパラメータのcsvファイルを置いて、ディレクトリオブジェクトを作成したあとに、外部表を作成しています。

[oracle@localhost ~]$ pwd
/home/oracle
[oracle@localhost ~]$ mkdir exttab
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on 水 3月 30 18:39:26 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
に接続されました。
SYS@ORCLCDB>
SYS@ORCLCDB> !ls -l exttab
合計 2688
-rw-r--r--. 1 oracle oinstall 2749046 3月 30 18:38 19.3.0.0.0.ksppi.csv

SYS@ORCLCDB>
SYS@ORCLCDB>
SYS@ORCLCDB> CREATE DIRECTORY ext_tab AS '/home/oracle/exttab';
SYS@ORCLCDB>
SYS@ORCLCDB> @cre_19c_paramtab
SYS@ORCLCDB>
SYS@ORCLCDB> !cat cre_19c_paramtab.sql
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'
)
)
/

できたみたいなので、外部表を問い合わせてみましょう! 

SYS@ORCLCDB> select * from ksppi_19_3_0_0_0 fetch first 10 rows only;

KSPPINM KSPPDESC
------------------------------------------- -------------------------------------------------------------------
_appqos_qt System Queue time retrieval interval
_appqos_po_multiplier Multiplier for PC performance objective value
_appqos_cdb_setting QoSM CDB Performance Class Setting
_ior_serialize_fault inject fault in the ior serialize code
_shutdown_completion_timeout_mins minutes for shutdown operation to wait for sessions to complete
_inject_startup_fault inject fault in the startup code
_session_modp_list send session's modified parameter list to client
_wait_outlier_detection_enable Enable wait outlier detection module?
_wait_outlier_event_names Wait events to watch for outliers
_wait_outlier_lambda_x1000 Lambda (in thousands) to compute outliers

10行が選択されました。


最後に、htmlファイル形式で 差分を取った結果を出力します :)
簡単ですよね :)

以前作ったものを多少変更しただけ

[oracle@localhost ~]$ cat get_diff.sql
col "19c 19.3.0.0.0" for a17
col "21c 21.3.0.0.0" for a17
set pagesize 10000
set timi off
set feed off
set markup html on spool on
spo param-diff.html
SELECT
CASE
WHEN prev.ksppinm = curr.ksppinm
OR (
prev.ksppinm IS NULL
AND curr.ksppinm IS NOT NULL
)
THEN curr.ksppinm
ELSE prev.ksppinm
END AS ksppinm
,CASE
WHEN prev.ksppinm = curr.ksppinm THEN '○'
WHEN prev.ksppinm IS NOT NULL AND curr.ksppinm IS NULL THEN '○'
ELSE 'n/a'
END AS "19c 19.3.0.0.0"
,CASE
WHEN prev.ksppinm = curr.ksppinm THEN '○'
WHEN prev.ksppinm IS NULL AND curr.ksppinm IS NOT NULL THEN '○'
ELSE 'n/a'
END AS "21c 21.3.0.0.0"
,CASE
WHEN prev.ksppdesc = curr.ksppdesc THEN curr.ksppdesc
WHEN prev.ksppinm IS NULL AND curr.ksppinm IS NOT NULL THEN curr.ksppdesc
WHEN prev.ksppinm iS NOT NULL AND curr.ksppinm IS NULL
THEN prev.ksppdesc
ELSE prev.ksppdesc
END AS kspdesc
,CASE
WHEN prev.ksppdesc = curr.ksppdesc
OR (prev.ksppinm IS NULL AND curr.ksppinm IS NOT NULL)
OR (prev.ksppinm IS NOT NULL AND curr.ksppinm IS NULL)
THEN NULL
ELSE curr.ksppdesc
END AS "New description"
FROM
(
SELECT * FROM x$ksppi WHERE con_id = 0
) curr
FULL OUTER JOIN ksppi_19_3_0_0_0 prev
ON
curr.ksppinm = prev.ksppinm
ORDER BY
1
;
spool off
set feed on
set markup html off

実行するとこんな感じで HTML ファイルの出来上がり!!

SYS@ORCLCDB> @get_diff.sql
...略...
are XML DB events enabled
</td>
<td>
 
</td>
</tr>
</table>
<p>
SYS@ORCLCDB>


さて、次は、再び、実行計画というレントゲンのお話に戻りますかw 



関連エントリー
21cの初期化パラメータ数や隠しパラメータ数変化
Difference of Initialization Parameters between 11gR2 (11.2.0.4.0) and 19c (19.3.0.0.0) - including hidden params
19cの初期化パラメータ数や隠しパラメータ数などどう変化したのか、久々に確認してみた
Difference of Initialization Parameters between 11g r1 (11.1.0.6.0) and 12c r1 (12.1.0.1.0) - including hidden params
Difference of Initialization Parameters between 11g and 12c #2

| | コメント (0)

21cの初期化パラメータ数や隠しパラメータ数変化

もうお約束になった感じですが、
21c環境を作ったので、お約束のパラメータ数差分確認を。増えることはあっても減ることは無いですよね。

09:31:44 SYS@ORCLCDB> select banner_full from v$version;

BANNER_FULL
------------------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0


いい感じで、隠しパラメータが増加してますよね。もう手に負えない数ですけども。 それ以外は、びっくりするほどの増加ではないですね。増えてはいますが。

CATEGORY                                 Num Of Parameters
---------------------------------------- -----------------
1. Single underscore parameters 5486
2. Double underscore parameters 32
3. Non hidden parameters 479
-----------------
Total 5997

リリース毎の各パラメータ数の変化をまとめたグラフ
20220330-225912


さて、次回は、19cと21cのパラメータ差分。(新バージョンリリースされたあとのルーティーンになってきたw



関連エントリー
Difference of Initialization Parameters between 11gR2 (11.2.0.4.0) and 19c (19.3.0.0.0) - including hidden params
19cの初期化パラメータ数や隠しパラメータ数などどう変化したのか、久々に確認してみた
Difference of Initialization Parameters between 11g r1 (11.1.0.6.0) and 12c r1 (12.1.0.1.0) - including hidden params
Difference of Initialization Parameters between 11g and 12c #2

| | コメント (0)

2022年3月30日 (水)

Oracle Database 21c EE for Linux on VirtualBox 6.1

諸事情w によりまだやってなかった Oracle Database 21c お遊び環境構築しました。今の所 VirtualBox向けrebuildなのはなさそうなので、400億年振りにw Linuxインストールから実施w とはいっても、インストール含め楽すぎて良いですね。(preinstallな方しか試して無いですが)

これで、またパラメータ数比較もできる:)


環境
VirtualBox (現時点の最新版 6.1 for macOS intel)
https://www.virtualbox.org/

Oracle Linux Installation Media (今回は8.4を使った)
https://yum.oracle.com/oracle-linux-isos.html

Oracle Database 21c (21.3) for Linux x86-64 (RPM)
https://www.oracle.com/database/technologies/oracle21c-linux-downloads.html

VirtualBoxのバージョン確認

discus-mother:~ oracle$ VBoxManage -v
6.1.32r149290

Oracle Linux 8.4のVM作成(ここは本題じゃないので省略)

Oracle Linuxのバージョン確認

[master@localhost ~]$ uname -rv
5.4.17-2102.201.3.el8uek.x86_64 #2 SMP Fri Apr 23 09:05:57 PDT 2021
[master@localhost ~]$
[master@localhost ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux release 8.4 (Ootpa)
[master@localhost ~]$ cat /etc/oracle-release
Oracle Linux Server release 8.4
[master@localhost ~]$


Oracle Database 21c EE 21.3.0.0.0のインストール

大人の事情w インストールからやるのかなり久々な上に、rpmでやるのは初!!!! しかも環境変数のあたり、ちょいと忘れているので怪しい(でツッコミ歓迎w)

では、first attempting... rpmは事前にダウンロード済み


master@localhost ~]$ ll -h oracle-database-ee-21c-1.0-1.ol8.x86_64.rpm
-rw-r--r--. 1 master master 2.6G 3月 29 03:07 oracle-database-ee-21c-1.0-1.ol8.x86_64.rpm

Install

[master@localhost ~]$ sudo dnf install oracle-database-ee-21c-1.0-1.ol8.x86_64.rpm
メタデータの期限切れの最終確認: 1:30:07 時間前の 2022年03月29日 02時07分01秒 に実施しました。
依存関係が解決しました。
======================================================================================================================
パッケージ アーキテクチャー バージョン リポジトリー サイズ
======================================================================================================================
インストール:
oracle-database-ee-21c x86_64 1.0-1 @commandline 2.6 G
依存関係のインストール:
compat-openssl10 x86_64 1:1.0.2o-3.el8 ol8_appstream 1.1 M
ksh x86_64 20120801-254.0.1.el8 ol8_appstream 927 k
libnsl x86_64 2.28-151.0.1.el8 ol8_baseos_latest 102 k
oracle-database-preinstall-21c x86_64 1.0-1.el8 ol8_appstream 30 k

トランザクションの概要
======================================================================================================================
インストール 5 パッケージ

合計サイズ: 2.6 G
インストール後のサイズ: 7.1 G
これでよろしいですか? [y/N]: y
パッケージのダウンロード:
[SKIPPED] libnsl-2.28-151.0.1.el8.x86_64.rpm: Already downloaded
[SKIPPED] compat-openssl10-1.0.2o-3.el8.x86_64.rpm: Already downloaded
[SKIPPED] ksh-20120801-254.0.1.el8.x86_64.rpm: Already downloaded
[SKIPPED] oracle-database-preinstall-21c-1.0-1.el8.x86_64.rpm: Already downloaded
----------------------------------------------------------------------------------------------------------------
合計 217 MB/s | 2.2 MB 00:00
トランザクションの確認を実行中
トランザクションの確認に成功しました。
トランザクションのテストを実行中
トランザクションのテストに成功しました。
トランザクションを実行中
準備 : 1/1
インストール中 : ksh-20120801-254.0.1.el8.x86_64 1/5
scriptletの実行中: ksh-20120801-254.0.1.el8.x86_64 1/5
インストール中 : compat-openssl10-1:1.0.2o-3.el8.x86_64 2/5
scriptletの実行中: compat-openssl10-1:1.0.2o-3.el8.x86_64 2/5
/sbin/ldconfig: /etc/ld.so.conf.d/kernel-5.4.17-2102.201.3.el8uek.x86_64.conf:6: hwcap directive ignored

インストール中 : libnsl-2.28-151.0.1.el8.x86_64 3/5
インストール中 : oracle-database-preinstall-21c-1.0-1.el8.x86_64 4/5
scriptletの実行中: oracle-database-ee-21c-1.0-1.x86_64 5/5
インストール中 : oracle-database-ee-21c-1.0-1.x86_64 5/5
scriptletの実行中: oracle-database-ee-21c-1.0-1.x86_64 5/5
[INFO] Executing post installation scripts...
[INFO] Oracle home installed successfully and ready to be configured.
To configure a sample Oracle Database you can execute the following service
configuration script as root: /etc/init.d/oracledb_ORCLCDB-21c configure

scriptletの実行中: oracle-database-preinstall-21c-1.0-1.el8.x86_64 5/5
scriptletの実行中: oracle-database-ee-21c-1.0-1.x86_64 5/5
/sbin/ldconfig: /etc/ld.so.conf.d/kernel-5.4.17-2102.201.3.el8uek.x86_64.conf:6: hwcap directive ignored

検証 : libnsl-2.28-151.0.1.el8.x86_64 1/5
検証 : compat-openssl10-1:1.0.2o-3.el8.x86_64 2/5
検証 : ksh-20120801-254.0.1.el8.x86_64 3/5
検証 : oracle-database-preinstall-21c-1.0-1.el8.x86_64 4/5
検証 : oracle-database-ee-21c-1.0-1.x86_64 5/5

インストール済み:
compat-openssl10-1:1.0.2o-3.el8.x86_64 ksh-20120801-254.0.1.el8.x86_64
libnsl-2.28-151.0.1.el8.x86_64 oracle-database-ee-21c-1.0-1.x86_64
oracle-database-preinstall-21c-1.0-1.el8.x86_64

完了しました!
[master@localhost ~]$


Configure
今回は、preinstallを構成します。カスタムな構成は今の所必要ないので。

[root@localhost ~]# cd /etc/init.d
[root@localhost init.d]# ll
合計 40
-rw-r--r--. 1 root root 1161 5月 19 2021 README
-rw-r--r--. 1 root root 18434 2月 15 2021 functions
-rwx------. 1 root root 1281 7月 13 2021 oracle-database-preinstall-21c-firstboot
-r-xr-xr-x. 1 root root 11307 7月 27 2021 oracledb_ORCLCDB-21c
[root@localhost init.d]#
[root@localhost ~]# /etc/init.d/oracledb_ORCLCDB-21c configure
Configuring Oracle Database ORCLCDB.
DB操作の準備
8%完了
データベース・ファイルのコピー中
31%完了
Oracleインスタンスの作成および起動中
32%完了
36%完了
40%完了
43%完了
46%完了
データベース作成の完了
51%完了
54%完了
プラガブル・データベースの作成
58%完了
77%完了
構成後アクションの実行
100%完了
データベースの作成が完了しました。詳細は、次の場所にあるログ・ファイルを参照してください:
/opt/oracle/cfgtoollogs/dbca/ORCLCDB。
データベース情報:
グローバル・データベース名:ORCLCDB
システム識別子(SID):ORCLCDB
詳細はログ・ファイル"/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log"を参照してください。

Database configuration completed successfully. The passwords were auto generated,
you must change them by connecting to the database using 'sqlplus / as sysdba' as the oracle user.
[root@localhost ~]#


おおおおおおーーー、起動してるーーーー!! 21c 楽だw
20220329-182633

いろいろ見ていくと、oracleユーザーや必要なグループの作成等はおこなわれているが、なぜか、home directoryが存在しなかった(ご愛嬌ってところかw)

ということで、なぜか存在しなかったホームディレクトリ作成とパスワードを変更して、.bash_profileに、ほぼ忘れかけてた環境変数設定など。(漏れてないかな。。w)

[root@localhost ~]# cat /etc/passwd | grep oracle
oracle:x:54321:54321::/home/oracle:/bin/bash
[root@localhost ~]#
[root@localhost ~]# mkdir -p /home/oracle
[root@localhost ~]# chown oracle:oinstall /home/oracle
[root@localhost ~]# passwd oracle
ユーザー oracle のパスワードを変更。
新しいパスワード:
新しいパスワードを再入力してください:
passwd: すべての認証トークンが正しく更新できました。
[root@localhost ~]# su - oracle
[oracle@localhost ~]$ cat .bash_profile
export ORACLE_BASE=/opt/oracle
export ORACLE_BASE_CONFIG=$ORACLE_BASE/dbs
export ORACLE_HOME=$ORACLE_BASE/product/21c/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=ORCLCDB
export NLS_LANG=Japanese_Japan.UTF8
export ORA_NLS10=$ORACLE_HOME/nls/data
[oracle@localhost ~]$ . .bash_profile
[oracle@localhost ~]$


そして、忘れる前にやっときましょうw glogin.sqlでSQL*Plusの環境を整えると。

[oracle@localhost ~]$ cat $ORACLE_HOME/sqlplus/admin/glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle. All Rights Reserved.
--
-- NAME
-- glogin.sql
--
-- DESCRIPTION
-- SQL*Plus global login "site profile" file
--
-- Add any SQL*Plus commands here that are to be executed when a
-- user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
-- This script is automatically run
--
set linesize 300
set pagesize 1000
set timi on
set time on
set tab off
define _EDITOR=vi
set sqlp "_user""@""_connect_identifier> "
[oracle@localhost ~]$

SQL*Plusで接続!!!

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on 火 3月 29 06:14:52 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.


Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
に接続されました。
06:14:52 SYS@ORCLCDB> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ WRITE NO
06:14:57 SYS@ORCLCDB>
06:14:58 SYS@ORCLCDB>
06:14:58 SYS@ORCLCDB>
06:15:26 SYS@ORCLCDB> select banner_full from v$version;

BANNER_FULL
-------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0


経過: 00:00:00.00
06:15:40 SYS@ORCLCDB> exit

Lisenerの状態も見ておきましょう

[oracle@localhost ~]$ lsnrctl status

LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 29-3月 -2022 07:09:29

Copyright (c) 1991, 2021, Oracle. All rights reserved.

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))に接続中
リスナーのステータス
------------------------
別名 LISTENER
バージョン TNSLSNR for Linux: Version 21.0.0.0.0 - Production
開始日 29-3月 -2022 03:57:55
稼働時間 0 日 3 時間 11 分 34 秒
トレース・レベル off
セキュリティ ON: Local OS Authentication
SNMP OFF
パラメータ・ファイル /opt/oracle/homes/OraDBHome21cEE/network/admin/listener.ora
ログ・ファイル /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
リスニング・エンドポイントのサマリー...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost)(PORT=5500))
(Security=(my_wallet_directory=/opt/oracle/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))
サービスのサマリー...
サービス"ORCLCDB"には、1件のインスタンスがあります。
インスタンス"ORCLCDB"、状態READYには、このサービスに対する1件のハンドラがあります...
サービス"ORCLCDBXDB"には、1件のインスタンスがあります。
インスタンス"ORCLCDB"、状態READYには、このサービスに対する1件のハンドラがあります...
サービス"db57eedbd976b81ce055000000000001"には、1件のインスタンスがあります。
インスタンス"ORCLCDB"、状態READYには、このサービスに対する1件のハンドラがあります...
サービス"orclpdb1"には、1件のインスタンスがあります。
インスタンス"ORCLCDB"、状態READYには、このサービスに対する1件のハンドラがあります...
コマンドは正常に終了しました。
[oracle@localhost ~]$

停止してみましょう!

おっと、systemctlで起動停止というところまではやってくれないのか... ということで、自分で止めましょうw あとで作ろう。。w

[master@localhost ~]$ sudo service oracle stop
[sudo] master のパスワード:
Redirecting to /bin/systemctl stop oracle.service
Failed to stop oracle.service: Unit oracle.service not loaded.
[master@localhost ~]$ sudo su - oracle
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on 火 3月 29 07:16:55 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
に接続されました。
07:16:55 SYS@ORCLCDB> shutdown immediate
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
07:17:01 SYS@ORCLCDB>

インストール楽すぎて、ワロタ。



一気に桜が咲いた。がいつまでもつのだろう。今年は東北方面も開花が早そうで、GWはもう葉桜だろうな。
では

| | コメント (0)

2022年3月21日 (月)

Oracle vagrant-projects

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

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

 

ひとまず、メモ 

Oracle Vagrant-projects

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

 

20220321-203926

| | コメント (0)

2021年8月17日 (火)

Oracle Database 21c Download for Linux x86-64がリリースされた

Oracle Database 21c Download for Linux x86-64

https://www.oracle.com/database/technologies/oracle21c-linux-downloads.html

 

やっと 21cがでたようなので、皆さんから少々遅れつつダウンロード

20210817-95239

 


天気は悪くて散歩というか、スィーツ買いに散歩できなくて、ツマラナイ 

| | コメント (0)