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

DROP SHADOW CTRL

TEXT DROP SHADOW OFF

GaragebandとiMovieで

検索

検索用語を入力

Web Blog内

検索フォームを送信

プロフィール

Twitter

Tweets by @discus_hamburg

2024年12月
日	月	火	水	木	金	土
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31

最近のトラックバック

[memo]メモを取りつつ楽しめるようなスタイル (wmo6hash::blog)
ダーリンはGoogleで軽食なり。 (今日のゴハン)
�� プログラムは読む時間の方が長い - 遥か彼方の彼方から (�� プログラムは読む時間の方が長い - 遥か彼方の彼方から)
今流行のケブンリッジ関数・・・しってました！？ (あにずむ)
poken (drmarのメモ)
Photo exhibition "Natural Faces of AIDS Orphans"　写真展「エイズ孤児の素顔」@JICA地球ひろば　広尾 (メガ放談)
創る心とおもてなし―第1回 Webエンジニア料理対決に参加する (Kwappa開発室)
創る心とおもてなし～第一回webエンジニア料理対決に参加した (Mac De Oracle)
徹底比較!! PostgreSQL vs MySQLパート2 with 勝手に vs Firebird (14) (キムラデービーブログ)
Oracle de XMLDB #18 (discus)

Oracle

OTN US

Apple

その他

ブログ de ノベルティ
ノベルティグッズをただ、ただ、記録していくブログ。Mac De Oracleでは出来ないネタなので、別ブログを立てた次第。

Googleの広告

RSS

RSSを表示する

当サイトについて

プライバシーポリシー

■プライバシーポリシー

■個人情報の利用目的

　当ブログでは、メールでのお問い合わせ、ブログへのコメントの際、名前（ハンドルネーム）等の個人情報をご登録いただく場合がございます。これらの個人情報は質問に対する回答などでご連絡する場合や、スパムコメント抑止のために利用させていただくものであり、個人情報をご提供いただく際の目的以外では利用いたしません。

■個人情報の第三者への開示

　当サイトでは、個人情報は適切に管理し、以下に該当する場合を除いて第三者に開示することはありません。

本人のご了解がある場合
法令等への協力のため、開示が必要となる場合

個人情報の開示、訂正、追加、削除、利用停止については、ご本人からの個人データの開示、訂正、追加、削除、利用停止のご希望の場合には、ご本人であることを確認させていただいた上で速やかに対応させていただきます。

■アクセス解析ツールについて

　当サイトでは、Googleによるアクセス解析ツール「Googleアナリティクス」を利用しています。このGoogleアナリティクスはトラフィックデータの収集のためにCookieを使用しています。このトラフィックデータは匿名で収集されており、個人を特定するものではありません。この機能はCookieを無効にすることで収集を拒否することが出来ますので、お使いのブラウザの設定をご確認ください。

目的：アクセス数を把握するため
送信先：Google LCC
Googleは受け取った情報をどのように利用するか：Googleの規約に従います
送信される情報：以下４点(R), ユーザー数、セッションの統計情報、おおよその位置情報、ブラウザとデバイスの情報

■広告の配信について

当サイトはGoogleとAmazonの広告を配信しています。

Googleについて

Google及びGoogleのパートナー（第三者配信事業者）の提供する広告を設置しております。その広告配信にはCookieを使用し、当サイトやその他のサイトへの過去のアクセス情報に基づいて広告を配信します。

Google が広告 Cookie を使用することにより、当サイトや他のサイトにアクセスした際の情報に基づいて、Google やそのパートナーが適切な広告を表示しています。

お客様はGoogleアカウントの広告設定ページ（https://adssettings.google.com/u/0/authenticated）で、パーソナライズ広告を無効にできます。また aboutads.info のページにアクセスして頂き、パーソナライズ広告掲載に使用される第三者配信事業者のCookieを無効にできます。

その他、Googleの広告におけるCookieの取り扱い詳細については、Googleのポリシーと規約ページ（https://policies.google.com/technologies/ads）をご覧ください。

Amazonについて

AmazonはアソシエイトリンクのURLに含まれるパラメータを通じて利用者が直前に閲覧したアソシエイトサイトに関する情報などを収集し、これをアソシエイトパートナーに対する紹介料の算定、報告及び支払いに使用したり、アソシエイトパートナーによる複数のウェブサイトや商品戦略毎のパフォーマンスの分析に供したりしています。

アソシエイトパートナーの利用目的：閲覧の傾向や履歴の分析のため広告効果の分析のため
外部送信プログラム名：Amazonアソシエイトプログラム
送信先の会社名：アマゾンジャパン合同会社
送信する情報の内容：閲覧した内容についての情報送信先の利用目的：自社の広告宣伝を目的とした広告情報の掲載のため、ウェブサイトやアプリの監視のため

Amazonによる個人に関する情報の取得及び取扱いに関する方針につきましてはAmazon.co.jpプライバシー規約をご覧ください。

■免責事項

　当サイトからリンクやバナーなどによって他のサイトに移動された場合、移動先サイトで提供される情報、サービス等について一切の責任を負いません。当サイトのコンテンツ・情報につきまして、可能な限り正確な情報を掲載するよう努めておりますが、誤情報が入り込んだり、情報が古くなっていることもございます。当サイトに掲載された内容によって生じた損害等の一切の責任を負いかねますのでご了承ください。

■プライバシーポリシーの変更について

　当サイトは、個人情報に関して適用される日本の法令を遵守するとともに、本ポリシーの内容を適宜見直しその改善に努めます。
修正された最新のプライバシーポリシーは常に本ページにて開示されます。

2022年4月 1日 (金) / Author : Hiroshi Sekiguchi.

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

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 |       |     . |          |                 |
==================================================================================================================================================================================

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

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

投稿日時 2022年4月 1日 (金) 21時13分 Oracle, チューニング, FAQ, SQL, Oracle Database 12c release 1, パフォーマンス, Oracle Database 18c, Oracle Database 19c, Oracle Database 21c | 固定リンク | 0

Mac De Oracle

MacOS X で動作する Oracle　が正式にリリースされたのだ！ 1996年にリリースされた、Personal Oracle7 for MacOS以来、封印されていたMacOS版が、（2005年）９年ぶりに正式リリースされたのでいろいろあそんでみたくなった！

DROP SHADOW CTRL

GaragebandとiMovieで

検索

Twitter

気まぐれ写真館

最近の記事

最近のコメント

最近のトラックバック

バックナンバー

カテゴリー