Mac De Oracle: in-memory

DROP SHADOW CTRL

TEXT DROP SHADOW OFF

GaragebandとiMovieで

検索

検索用語を入力

Web Blog内

検索フォームを送信

プロフィール

Twitter

Tweets by @discus_hamburg

2025年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プライバシー規約をご覧ください。

■免責事項

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

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

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

2025年7月 4日 (金)

実行計画は, SQL文のレントゲン写真だ！ No.67 / AI Vector Search - VECTOR INDEX HNSW SCAN のバリエーション

Previously on Mac De Oracle
前回は、VECTOR INDEX はどこ？、見積もりサイズだとそれなりのサイズだったのに... の謎を探るべく、我々は洞窟の奥へ向かった！(完結編)でした。
今日は、再びレントゲン写真に戻り、VECTOR INDEX HNSW SCAN のバリエーションをいくつか確認しておこうと思います。
（なかなか興味深いので、一度診ておけば、いざというときに慌てなくて済むと思います）

いきなりってのもあれなので、先に以下のマニュアルを一読しておくと良いと思います。マニュアルでもポイントが解説されているネタなので:)　解説しないとちょっと分かりずらい点が多いからだと思いますがw

Oracle Database 23ai / Oracle AI Vector Search ユーザーズ・ガイド / HNSW ベクトルインデックスのオプティマイザプランのバリエーションを解説している章があります。なかなか興味深い。
おそらく、表には積極的に登場してこない補助表が、突然実行計画に現れることへの戸惑いと実行計画の読み方にちょっとした癖がある点の緩和と実行計画のバリエーションごとのメリデメを理解してもらうためにも解説が必要だったのだろうなぁ。と想像。
Oracle Database / Release 23 / Oracle AI Vector Search User's Guide / Optimizer Plans for HNSW Vector Indexes

INMEMORYな索引なのに、OperationにINMEMORYというキーワードが無くて、おや？　と違和感があったり、ちょっとめんどくさい癖があるなぁと。。。w

まずは、
vector_index_neighbor_graph_reloadパラメータはCDBレベルで restart に設定した ( Oracle Database 23ai 23.6以降はデフォルトが restart になっています ) ので再起動しても vector index (HNSW) はポピュレートされメモリー上に復活しているはず。。。という確認から。

[oracle@localhost ~]$ sudo service oracle stop
[sudo] oracle のパスワード:
Stopping oracle (via systemctl):  
                                                           [  OK  ]
[oracle@localhost ~]$ 
[oracle@localhost ~]$ sudo service oracle start
Starting oracle (via systemctl):  
                                                           [  OK  ]
[oracle@localhost ~]$ 
[oracle@localhost ~]$ sqlplus scott@localhost:1521/freepdb1

...略...

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
に接続されました。
SCOTT@localhost:1521/freepdb1> @show_vector_mem_pool    

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL                     369098752  236978176 DONE
64KB POOL                    150994944    2686976 DONE
IM POOL METADATA              16777216   16777216 DONE

経過: 00:00:00.05
SCOTT@localhost:1521/freepdb1> @show_vector_segments

       OBJ   MEMBYTES
---------- ----------
         0     131072
     80126  239534080

経過: 00:00:00.02

それぞれの実行計画のpros/consはマニュアルで読んでもらうとして、
VECTOR INDEX HNSW SCAN PRE-FILTER WITH JOIN BACKから診てみましょう。

JOIN BACKするかしないかの違いは最後にもう一度ベース表をアクセスするかどうか。（次の実行計画で言うと、Id=5でSEARCH_DATAをtable access by index rowidでアクセスしている箇所がJOIN BACK)
これらのバリエーションはデータ量とフィルタリング量との兼ね合いになるのでベクトル索引で近傍検索の実行計画をヒントで固定化するのは比較的難易度が高そう（どちらの傾向に固定した方が良いかの判断は難しい）だろうな、と思っているところ。固定できるか、したほうが良いかの見極めというか、割り切りなのかもしれないが決め打ちするだけの情報は揃えた上で決める必要はりそう。最初は経過観察なのが良いだろうと思っているところだが。。。とはいえ、覚えていて損ないかなぁ。

また、これらのバリエーションでは、VECTOR INDEX (HNSW)以外に、補助表の主役であるMAP表が登場します（Id=9の部分)。
VECTOR INDEX (HNSW)本体だけでなく補助表の存在も把握しておくことが大切な理由はここにもあります。（ベース表に比べるとサイズは小さいわけですが）

後半で別途まとめますが、この実行では内部ビューが新たに作られています。
Id=7の VW_HPJ_91CF1FF7 がそれです。内部的に作成されるビューにはそれぞれのトランスフォームに関連する短縮名が付与されるのが、これまでのOracle Databaseのオプティマイザのお約束ですね。
VW_HPJ_、 Hnsw scan Pre-filter with Join back -> HPJ になりそうですよね。 VW_HPJ_という内部ビューをみたらVECTOR_INDEX_TRANSFORM VECTOR INDEX HNSW SCAN PRE_FILTER WITH JOIN BACKが行われていると考えてよいでしょうね。

SELECT
  /*+
    GATHER_PLAN_STATISTICS
  */
  id
  , description
  , community
  , location_desc
  , district
  , TO_NUMBER( v_distance ) AS v_distance
FROM
(
  SELECT
    /*+ 
      VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix pre_filter_with_join_back)
    */
    id
    , description
    , community
    , location_desc
    , district
    , VECTOR_DISTANCE
      (
        vector_desc
        , VECTOR_EMBEDDING
         (
           all_minilm_l6 USING 'Incident in which someone may have been murdered' AS data
         )
      , COSINE
    ) v_distance
  FROM
    search_data
  WHERE
    community = 'AUSTIN'
  ORDER BY
    v_distance
  FETCH APPROX FIRST 20 ROWS ONLY
)
/


Plan hash value: 3994424349

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                                                        | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                                                             |      1 |        |       |       |  6649 (100)|          |     20 |00:00:00.93 |     412 |    410 |       |       |          |
|   1 |  VIEW                                  |                                                             |      1 |      1 |   157 |       |  6649   (1)| 00:00:01 |     20 |00:00:00.93 |     412 |    410 |       |       |          |
|*  2 |   COUNT STOPKEY                        |                                                             |      1 |        |       |       |            |          |     20 |00:00:00.93 |     412 |    410 |       |       |          |
|   3 |    VIEW                                |                                                             |      1 |      1 |   157 |       |  6649   (1)| 00:00:01 |     20 |00:00:00.93 |     412 |    410 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY              |                                                             |      1 |      1 |  1665 |       |  6649   (1)| 00:00:01 |     20 |00:00:00.93 |     412 |    410 |  4096 |  4096 | 4096  (0)|
|*  5 |      TABLE ACCESS BY INDEX ROWID       | SEARCH_DATA                                                 |      1 |      1 |  1665 |       |  6648   (1)| 00:00:01 |     20 |00:00:00.94 |     412 |    410 |       |       |          |
|   6 |       VECTOR INDEX HNSW SCAN PRE-FILTER| SEARCH_DATA_HNSW_IX                                         |      1 |      1 |  1665 |       |  6648   (1)| 00:00:01 |     20 |00:00:00.93 |     392 |    390 |       |       |          |
|   7 |        VIEW                            | VW_HPJ_91CF1FF7                                             |      1 |   6235 |   152K|       |  6647   (1)| 00:00:01 |   6235 |00:00:00.06 |     392 |    390 |       |       |          |
|*  8 |         HASH JOIN RIGHT OUTER          |                                                             |      1 |   6235 |     9M|  3296K|  6647   (1)| 00:00:01 |   6235 |00:00:00.06 |     392 |    390 |  8506K|  2096K| 9004K (0)|
|   9 |          TABLE ACCESS FULL             | VECTOR$SEARCH_DATA_HNSW_IX$78074_80224_0$HNSW_ROWID_VID_MAP |      1 |    125K|  1831K|       |   102   (0)| 00:00:01 |    125K|00:00:00.01 |     373 |    371 |       |       |          |
|* 10 |          INDEX RANGE SCAN              | SEARCH_DATA_COMMNITY_IX                                     |      1 |   6235 |       |       |    23   (0)| 00:00:01 |   6235 |00:00:00.01 |      19 |     19 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$3        / "from$_subquery$_001"@"SEL$1"
   2 - SEL$3
   3 - SEL$7185E227 / "from$_subquery$_003"@"SEL$3"
   4 - SEL$7185E227
   5 - SEL$7185E227 / "SEARCH_DATA"@"SEL$2"
   6 - SEL$7185E227 / "SEARCH_DATA"@"SEL$2"
   7 - SEL$BF33016E / "VW_HPJ_91CF1FF7"@"SEL$2"
   8 - SEL$BF33016E
   9 - SEL$BF33016E / "VTIX_RIDVID"@"SEL$2"
  10 - SEL$BF33016E / "SEARCH_DATA"@"SEL$2"

...略...

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

   2 - filter(ROWNUM<=20)
   4 - filter(ROWNUM<=20)
   5 - filter("SEARCH_DATA"."COMMUNITY"='AUSTIN')
   8 - access("SEARCH_DATA".ROWID="VTIX_RIDVID"."BASE_TABLE_ROWID")
  10 - access("SEARCH_DATA"."COMMUNITY"='AUSTIN')

...略...

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

  10 -  SEL$BF33016E / "SEARCH_DATA"@"SEL$2"
           -  VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix pre_filter_with_join_back)

次に、VECTOR INDEX HNSW SCAN PRE-FILTER WITHOUT JOIN BACKを診てみましょう。
違いは、JOIN BACKがないところですよね、ヒントの通りw。
実行計画のId=5でVECTOR INDEX HNSW SCAN PRE_FILTERが行われていますが、その後でベース表を再度アクセスすることはありません。
なお、今回の検索パターンだと join back したほうが多少軽めですよね。んーーー難しい。普段はオプティマイザにお任せのほうが良いかもなと思うわけです。よほどのことがない限り。はい。

ここでも、新たな内部ビュー、 VW_HPF_475999B9 が作成されています。 VW_HPF_、 Hnsw scan Pre-Filter without join back -> HPF でしょうか。ちょっとムズイw VW_HPF_内部ビューを見つけたら、VECTOR INDEX HNSW SCAN PRE-FILTER WITHOUT JOIN BACKが行われてると理解して良さそうです。

このケースでも補助表であるMAP表が登場します。なにこれ？　俺は作ってないぞ！　と驚かないようにしてくださいねw。（当ブログを読んだ方は驚くことはないはずですがw）
また、マニュアルにも記載されていますが、Hash join だけでなく Nested Loops Joinになることもあります。データ量と索引有無次第ではありますが、覚えておくと良いでしょう。（この例では Apaptive Planが選択されているため、どちらの結合方式になるかは、Join Cardinarity次第です）

SELECT
  /*+
    GATHER_PLAN_STATISTICS
  */
  id
  , description
  , community
  , location_desc
  , district
  , TO_NUMBER( v_distance ) AS v_distance
FROM
(
  SELECT
    /*+ 
      VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix pre_filter_without_join_back) 
    */
    id
    , description
    , community
    , location_desc
    , district
    , VECTOR_DISTANCE
      (
        vector_desc
        , VECTOR_EMBEDDING
         (
           all_minilm_l6 USING 'Incident in which someone may have been murdered' AS data
         )
      , COSINE
    ) v_distance
  FROM
    search_data
  WHERE
    community = 'AUSTIN'
  ORDER BY
    v_distance
  FETCH APPROX FIRST 20 ROWS ONLY
)
/


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                                    | Name                                                        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                             |                                                             |      1 |        |       |  5998 (100)|          |     20 |00:00:02.77 |    5929 |   5904 |       |       |          |
|     1 |  VIEW                                        |                                                             |      1 |     20 |  3140 |  5998   (1)| 00:00:01 |     20 |00:00:02.77 |    5929 |   5904 |       |       |          |
|  *  2 |   COUNT STOPKEY                              |                                                             |      1 |        |       |            |          |     20 |00:00:02.77 |    5929 |   5904 |       |       |          |
|     3 |    VIEW                                      |                                                             |      1 |     20 |  3140 |  5998   (1)| 00:00:01 |     20 |00:00:02.77 |    5929 |   5904 |       |       |          |
|  *  4 |     SORT ORDER BY STOPKEY                    |                                                             |      1 |     20 | 35220 |  5998   (1)| 00:00:01 |     20 |00:00:02.77 |    5929 |   5904 |  4096 |  4096 | 4096  (0)|
|     5 |      VECTOR INDEX HNSW SCAN PRE-FILTER       | SEARCH_DATA_HNSW_IX                                         |      1 |     20 | 35220 |  5997   (1)| 00:00:01 |     20 |00:00:02.77 |    5929 |   5904 |  1278K|  1076K| 1175K (0)|
|     6 |       VIEW                                   | VW_HPF_475999B9                                             |      1 |   6235 |  1071K|  5996   (1)| 00:00:01 |   6235 |00:00:02.13 |    5914 |   5904 |       |       |          |
|  *  7 |        HASH JOIN OUTER                       |                                                             |      1 |   6235 |   499K|  5996   (1)| 00:00:01 |   6235 |00:00:02.13 |    5914 |   5904 |  1448K|  1287K| 1856K (0)|
|-    8 |         NESTED LOOPS OUTER                   |                                                             |      1 |   6235 |   499K|  5996   (1)| 00:00:01 |   6235 |00:00:10.37 |    5540 |   5533 |       |       |          |
|-    9 |          STATISTICS COLLECTOR                |                                                             |      1 |        |       |            |          |   6235 |00:00:10.36 |    5540 |   5533 |       |       |          |
|    10 |           TABLE ACCESS BY INDEX ROWID BATCHED| SEARCH_DATA                                                 |      1 |   6235 |   407K|  5893   (1)| 00:00:01 |   6235 |00:00:10.36 |    5540 |   5533 |       |       |          |
|  * 11 |            INDEX RANGE SCAN                  | SEARCH_DATA_COMMNITY_IX                                     |      1 |   6235 |       |    23   (0)| 00:00:01 |   6235 |00:00:00.01 |      19 |     19 |       |       |          |
|-   12 |          TABLE ACCESS BY INDEX ROWID         | VECTOR$SEARCH_DATA_HNSW_IX$78074_80224_0$HNSW_ROWID_VID_MAP |      0 |      1 |    15 |   102   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|- * 13 |           INDEX UNIQUE SCAN                  | SYS_C0013920                                                |      0 |        |       |            |          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|    14 |         TABLE ACCESS FULL                    | VECTOR$SEARCH_DATA_HNSW_IX$78074_80224_0$HNSW_ROWID_VID_MAP |      1 |    125K|  1831K|   102   (0)| 00:00:01 |    125K|00:00:00.03 |     374 |    371 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$3        / "from$_subquery$_001"@"SEL$1"
   2 - SEL$3
   3 - SEL$2D1A9934 / "from$_subquery$_003"@"SEL$3"
   4 - SEL$2D1A9934
   5 - SEL$2D1A9934 / "SEARCH_DATA"@"SEL$2"
   6 - SEL$6D23FDEA / "VW_HPF_475999B9"@"SEL$475999B9"
   7 - SEL$6D23FDEA
   8 - SEL$6D23FDEA
  10 - SEL$6D23FDEA / "SEARCH_DATA"@"SEL$2"
  11 - SEL$6D23FDEA / "SEARCH_DATA"@"SEL$2"
  12 - SEL$6D23FDEA / "VTIX_RIDVID"@"SEL$EBB9871C"
  13 - SEL$6D23FDEA / "VTIX_RIDVID"@"SEL$EBB9871C"
  14 - SEL$6D23FDEA / "VTIX_RIDVID"@"SEL$EBB9871C"

...略...

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

   2 - filter(ROWNUM<=20)
   4 - filter(ROWNUM<=20)
   7 - access("SEARCH_DATA".ROWID="VTIX_RIDVID"."BASE_TABLE_ROWID")
  11 - access("SEARCH_DATA"."COMMUNITY"='AUSTIN')
  13 - access("SEARCH_DATA".ROWID="VTIX_RIDVID"."BASE_TABLE_ROWID")

...略...

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

  10 -  SEL$6D23FDEA / "SEARCH_DATA"@"SEL$2"
           -  VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix pre_filter_without_join_back)

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

次は、VECTOR INDEX TRANSFORM IN-FILTER WITH JOIN BACK これまでの PRE-FILTERではなく、IN-FILTER + JOIN BACKをヒントで強制しています

なお、マニュアルにも記載がありますが、In-filterの実行計画の読み方は少々癖があります！

Id=8の元表のアクセスを見てください！。いきなり、TABLE ACCESS BY USER ROWID で SEARCH_DATA 表をアクセスしています！！！
この実行計画の開始ポイントは、Id=8ではなく、Id=6の VECTOR INDEX HNSW SCAN IN-FILTER で、VECTOR INDEX (HNSW)である SEARCH_DATA_HNSW_IX 索引をトラバースしている部分です:)
Id=6で識別されたベクトルごとに、Id=8の元表に対応するrowidのフィルタが適用され、関連する列が抽出されます!!!!!!!

VECTOR INDEX TRANSFORMの時の実行計画の読み方は正しく覚えないと軽くハマりそうですねw 少々癖があるので覚えるしかないですよ！w（ここも試験にでるよ！！w しらんけど）

最後に、Id=5でJOIN BACKして、COUNT STOPKEYの操作へ遷移していきます！！！！

また、ここでも内部生成の新たなビューが登場しています。
VW_HIJ_ ですね。 HIJ -> Hnsw scan In-filter with Join back ということでしょうね。想像するに。　

SELECT
  /*+
    GATHER_PLAN_STATISTICS
  */
  id
  , description
  , community
  , location_desc
  , district
  , TO_NUMBER( v_distance ) AS v_distance
FROM
(
  SELECT
    /*+ 
      VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix in_filter_with_join_back) 
    */
    id
    , description
    , community
    , location_desc
    , district
    , VECTOR_DISTANCE
      (
        vector_desc
        , VECTOR_EMBEDDING
         (
           all_minilm_l6 USING 'Incident in which someone may have been murdered' AS data
         )
      , COSINE
    ) v_distance
  FROM
    search_data
  WHERE
    community = 'AUSTIN'
  ORDER BY
    v_distance
  FETCH APPROX FIRST 20 ROWS ONLY
)
/


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                     |      1 |        |       |     3 (100)|          |     20 |00:00:02.28 |    4725 |   4431 |       |       |          |
|   1 |  VIEW                                 |                     |      1 |      1 |   157 |     3  (34)| 00:00:01 |     20 |00:00:02.28 |    4725 |   4431 |       |       |          |
|*  2 |   COUNT STOPKEY                       |                     |      1 |        |       |            |          |     20 |00:00:02.28 |    4725 |   4431 |       |       |          |
|   3 |    VIEW                               |                     |      1 |      1 |   157 |     3  (34)| 00:00:01 |     20 |00:00:02.28 |    4725 |   4431 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY             |                     |      1 |      1 |  1652 |     3  (34)| 00:00:01 |     20 |00:00:02.28 |    4725 |   4431 |  4096 |  4096 | 4096  (0)|
|*  5 |      TABLE ACCESS BY INDEX ROWID      | SEARCH_DATA         |      1 |      1 |  1652 |     2   (0)| 00:00:01 |     20 |00:00:02.28 |    4725 |   4431 |       |       |          |
|   6 |       VECTOR INDEX HNSW SCAN IN-FILTER| SEARCH_DATA_HNSW_IX |      1 |      1 |  1652 |     2   (0)| 00:00:01 |     20 |00:00:02.28 |    4705 |   4423 |       |       |          |
|   7 |        VIEW                           | VW_HIJ_475999B9     |   4773 |      1 |       |     1   (0)| 00:00:01 |    265 |00:00:01.59 |    4705 |   4423 |       |       |          |
|*  8 |         TABLE ACCESS BY USER ROWID    | SEARCH_DATA         |   4773 |      1 |  1652 |     1   (0)| 00:00:01 |    265 |00:00:01.59 |    4705 |   4423 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$3        / "from$_subquery$_001"@"SEL$1"
   2 - SEL$3
   3 - SEL$79710E8E / "from$_subquery$_003"@"SEL$3"
   4 - SEL$79710E8E
   5 - SEL$79710E8E / "SEARCH_DATA"@"SEL$2"
   6 - SEL$79710E8E / "SEARCH_DATA"@"SEL$2"
   7 - SEL$860F096D / "VW_HIJ_475999B9"@"SEL$2"
   8 - SEL$860F096D / "SEARCH_DATA"@"SEL$2"

...略...

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

   2 - filter(ROWNUM<=20)
   4 - filter(ROWNUM<=20)
   5 - filter("SEARCH_DATA"."COMMUNITY"='AUSTIN')
   8 - filter("SEARCH_DATA"."COMMUNITY"='AUSTIN')

...略...

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

   8 -  SEL$860F096D / "SEARCH_DATA"@"SEL$2"
           -  VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix in_filter_with_join_back)

最後に、VECTOR INDEX TRANSFORM IN-FILTER WITHOUT JOIN BACK In-filtewrでJoin Backなしというタイプです。

このタイプがもっとも無駄がなさそうですね。今回の例で使っている検索パターンだと。。。:)

これもマニュアルの記述されているとおり、実行計画の開始位置にクセがあります。
Id=5の VECTOR INDEX HNSW SCAN IN-FILTER で、VECTOR INDEX (HNSW) をトラバースするところがスタートです。
次に、Id=7のベース表をVECTOR INDEXから取得したrowidでアクセス。
その後、COUNT STOPKEYの操作へ入ります。

そして、ここでも新顔の内部ビュー、 VW_HIF_ -> Hnsw scan In-Filter with join back ということで、 HIFになっていると思われます:)

SELECT
  /*+
    GATHER_PLAN_STATISTICS
  */
  id
  , description
  , community
  , location_desc
  , district
  , TO_NUMBER( v_distance ) AS v_distance
FROM
(
  SELECT
    /*+ 
      VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix in_filter_without_join_back) 
    */
    id
    , description
    , community
    , location_desc
    , district
    , VECTOR_DISTANCE
      (
        vector_desc
        , VECTOR_EMBEDDING
         (
           all_minilm_l6 USING 'Incident in which someone may have been murdered' AS data
         )
      , COSINE
    ) v_distance
  FROM
    search_data
  WHERE
    community = 'AUSTIN'
  ORDER BY
    v_distance
  FETCH APPROX FIRST 20 ROWS ONLY
 )
/


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |      1 |        |       |     3 (100)|          |     20 |00:00:00.52 |    4705 |    733 |       |       |          |
|   1 |  VIEW                                |                     |      1 |     20 |  3140 |     3  (34)| 00:00:01 |     20 |00:00:00.52 |    4705 |    733 |       |       |          |
|*  2 |   COUNT STOPKEY                      |                     |      1 |        |       |            |          |     20 |00:00:00.52 |    4705 |    733 |       |       |          |
|   3 |    VIEW                              |                     |      1 |     20 |  3140 |     3  (34)| 00:00:01 |     20 |00:00:00.52 |    4705 |    733 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY            |                     |      1 |     20 | 34960 |     3  (34)| 00:00:01 |     20 |00:00:00.52 |    4705 |    733 |  4096 |  4096 | 4096  (0)|
|   5 |      VECTOR INDEX HNSW SCAN IN-FILTER| SEARCH_DATA_HNSW_IX |      1 |     20 | 34960 |     2   (0)| 00:00:01 |     20 |00:00:00.52 |    4705 |    733 |   835K|   835K|  543K (0)|
|   6 |       VIEW                           | VW_HIF_475999B9     |   4773 |      1 |   151 |     1   (0)| 00:00:01 |    265 |00:00:00.02 |    4705 |    733 |       |       |          |
|*  7 |        TABLE ACCESS BY USER ROWID    | SEARCH_DATA         |   4773 |      1 |    67 |     1   (0)| 00:00:01 |    265 |00:00:00.02 |    4705 |    733 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$3        / "from$_subquery$_001"@"SEL$1"
   2 - SEL$3
   3 - SEL$81BAFB36 / "from$_subquery$_003"@"SEL$3"
   4 - SEL$81BAFB36
   5 - SEL$81BAFB36 / "SEARCH_DATA"@"SEL$2"
   6 - SEL$066A4CD4 / "VW_HIF_475999B9"@"SEL$475999B9"
   7 - SEL$066A4CD4 / "SEARCH_DATA"@"SEL$2"

...略...

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

   2 - filter(ROWNUM<=20)
   4 - filter(ROWNUM<=20)
   7 - filter("SEARCH_DATA"."COMMUNITY"='AUSTIN')

...略...

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

   7 -  SEL$066A4CD4 / "SEARCH_DATA"@"SEL$2"
           -  VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix in_filter_without_join_back)

まとめ VECTOR INDEX TRANSFORM の特徴

・VECTOR INDEX (HNSW)以外に、補助表であるMAP表が使われる（場合によってはその索引も）
・In-filter時の実行計画開始の開始ポイントに癖があるので要注意
・内部で生成されるビューがバリエーション分増加した

最後に、今回新たに登場した内部ビュー名と、これまでに把握されている内部ビューのまとめ。

VECTOR INDEX TRANSFORM

VW_HPJ_ / Hnsw scan Pre-filter with Join back
VW_HPF_ / Hnsw scan Pre-Filter with join back
VW_HIJ_ / Hnsw scan In-filter with Join back
VW_HIF_ / Hnsw scan In-Filter with join back

上記に加え以前からいくつかメジャーな内部生成ビューがまとめられていいます。覚えておくとなにが行われているか分かり易いと思いますよ。
Internal Views / Oracle Scratchpad / Jonathan Lewis

では、また！

Enjoin Execution Plans, SQLs, and AI Vector Search!

Related article on Mac De Oracle
実行計画は, SQL文のレントゲン写真だ！

投稿日時 2025年7月 4日 (金) 12時48分 Oracle, チューニング, FAQ, SQL, パフォーマンス, in-memory, VECTOR, AI, Oracle Database 23ai | 固定リンク | 0 | コメント (0)

2025年6月25日 (水)

VECTOR INDEX はどこ？、見積もりサイズだとそれなりのサイズだったのに... の謎を探るべく、我々は洞窟の奥へ向かった！(完結編)

Previously on Mac De Oracle
前回はVECTOR INDEX はどこ？、見積もりサイズだとそれなりのサイズだったのに... の謎を探るべく、我々は洞窟の奥へ向かった！(後編) でした。
謎は、ほぼ解決したかな？と、思ったのですが、モヤモヤは晴れずwww
ということで、完結編的な何か。という位置付けのまとめを書いておくことにしました。（なお、23.5以降VECTOR INDEX関連も機能追加があったり。。。まあ、追いかけるの大変です）

注）ちょい古いリリースなので最新リリースとは挙動など異なる点がある可能性がある点、ご了承ください

SCOTT@localhost:1521/freepdb1> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

インメモリー近傍グラフ・ベクトル索引の特徴まとめ (的なもの)

インメモリー近傍グラフ・ベクトル索引は、ディクショナリービューからは、OBJECTでもSEGMENTも無い扱いになっているよう見えてしまう。
しかし、vecsys.vector$indexにリストされるOBJECT_IDは、*_OBJECTSと共通で、vecsys.vector$indexにはあるが、*_OBJECTS上はIDが欠番のように見える（後述）。無いけどあるみたいな不思議な扱いになっている。（今のところ）
なぜそうしたのだろう？....

Oraclerならお馴染みの *_OBJECTS　や　*_SEGMENTS　には現れないが、in-memory onlyのオブジェクトとして専用のプール上にポピューレートされた場合に存在している in-memory indexである。
（ここテストにでますよ。嘘w）

また、in-memory vector indexの補助表及び関連する索引はリストされる（セグメントサイズは、deferred segment creationであるものも含むため状況次第）
メモリー上とは言っても他と同じ扱いで、属性としてin-memory onlyのような列を追加すればよかったのでは？と、個人的には思っていたりする。。。

参考）
(上記の時事検証 23ai 23.4で行ったため、最新のリリースとは差異がある可能性があり、気づいた差分について随時補足追記してあります)
作成したin-memory vector index ( SEARCH_DATA_HNSW_IX ) の IDX_OBJN(列名の短縮系も*_OBJECTSで利用されているOBJECT_IDとな異なり、IDX_OBJN ( N は Numberの略と思われる) という、いわゆるシノニムになってしまっている（この点も状況をわかりにくくしている原因でしょうね。Oraclerじゃない方が作ったような違和感を感じる）

SCOTT@localhost:1521/freepdb1> select idx_objn, idx_name from vecsys.vector$index;

  IDX_OBJN IDX_NAME
---------- -------------------------------
     80113 SEARCH_DATA_HNSW_IX

で、この IDX_OBJN = OBJECT_ID ( *_OBJECTS で使われている　）、いきなりシノニムになってる！　と仮説をたて UESR_OBJECTSを検索してみると、存在しない！　（何ぃ〜〜〜っ！）
ちがうのか？（*_OBJECTSにはリストされないだけで、採番元は同じなので、はやり仮説は正しかったw。後述）

SCOTT@localhost:1521/freepdb1> select object_name,object_type from user_objects where object_id = 80113;

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

念の為、data_object_idでも検索してみる（こちらにもないですよねーw）

SCOTT@localhost:1521/freepdb1> select object_name,object_type from user_objects where data_object_id = 80113;

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

USER_SEGMENTSをVECTOR INDEX NAMEで検索してみるがやはり無い。ストレージ上のデータをそのままインメモリー化しているわけではないいうことだろうと想像するが。ではメモリー上の情報は。。。

SCOTT@localhost:1521/freepdb1> select segment_name from user_segments where segment_name = 'SEARCH_DATA_HNSW_IX';

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

VECTOR INDEXとしては、 *_INDEXES から確認は可能であるが”！。。。

SCOTT@localhost:1521/freepdb1> select index_name,index_type,table_name from user_indexes where table_name='SEARCH_DATA';

 INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
------------------------------ --------------------------- ------------------------------
SYS_IL0000078074C00009$$       LOB                         SEARCH_DATA
SEARCH_DATA_HNSW_IX            VECTOR                      SEARCH_DATA

in-memory vector indexはSGA上の専用メモリープール上に造られる。
では、v$vector_memory_poolからポピュレートされていることを確認。

SCOTT@localhost:1521/freepdb1> l
  1  SELECT
  2    pool
  3    , alloc_bytes
  4    , used_bytes
  5    , populate_status
  6  FROM
  7*   v$vector_memory_pool
SCOTT@localhost:1521/freepdb1> /

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL                     369098752  236978176 DONE
64KB POOL                    150994944    2686976 DONE
IM POOL METADATA              16777216   16777216 DONE

ちなみに、vector indexがポピュレートされる前の状態は以下。1MB/64KB Poolが消費されたことがわかります。つまり、236,978,176 + 2,686,976 = 239,665,152 = 228.6MB

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL                     369098752          0 DONE
64KB POOL                    150994944          0 DONE
IM POOL METADATA              16777216   16777216 DONE

in-memory vector indexのメモリセグメントの詳細を確認するには　v$vector_mem_segments_detail　を参照する。vecsys.vector$indexのidx_objnにリストされていた 80113 が 239MB　程度使用されていることがわかる。
（このサイズ、覚えておいてくださいね。後で使います。 239,534,080 = 228.4MB）

SCOTT@localhost:1521/freepdb1> l
  1  SELECT
  2    obj
  3    , membytes
  4  FROM
  5*   v$vector_mem_segments_detail
SCOTT@localhost:1521/freepdb1> /

       OBJ   MEMBYTES
---------- ----------
         0     131072
     80113  239534080

in-memory vector index (HNSW)の補助表と関連索引の object_id を見てみましょう。
vecsys.vector$indexでは、idx_objn となっていた列の値と連続していること点が確認できます。つまり、object_id　を採番しているシーケンスを利用している。。。in-memory vector index (HNSW) も *_OBJECTSに含まれている扱い。。。だが、*_objectsには含まれていません！！！！

CREATE VECTOR INDEXで作成された、in-memory vector index（ SEARCH_DATA_HNSW_IX ）の idx_objn = 80113 , その後に作成される補助表及び索引の *_objects.object_id　は、80114 以降が採番されています。
かつ、80113 という object_id は、*_objects にはリストされず。
妙ですよね。内部的には *_objectsに含まれていてもおかしくないと思われる扱いにであるかのように見えますよね。うーーむ。

SCOTT@localhost:1521/freepdb1> l
  1  WITH
  2    vector_idx_auxiliary_tables
  3    AS (
  4      SELECT
  5       idx_name AS vector_index_name
  6       , REPLACE(aux_table_name,'"','') AS aux_table_name
  7      FROM
  8      (
  9          SELECT
 10            vvi.idx_name AS idx_name
 11            ,vvi.idx_auxiliary_tables.rowid_vid_map_name
 12            ,vvi.idx_auxiliary_tables.shared_journal_transaction_commits_name
 13            ,vvi.idx_auxiliary_tables.shared_journal_change_log_name
 14          FROM
 15            vecsys.vector$index vvi
 16      ) objs
 17      UNPIVOT (
 18        aux_table_name FOR related_obj_name IN
 19        (
 20          rowid_vid_map_name
 21          , shared_journal_transaction_commits_name
 22          , shared_journal_change_log_name
 23        )
 24      )
 25    )
 26  SELECT
 27    object_id
 28  ,object_name
 29  ,object_type
 30  FROM
 31    user_objects
 32  WHERE
 33    object_name in (
 34  SELECT
 35    aux_table_name AS segment_name
 36  FROM
 37    vector_idx_auxiliary_tables
 38  WHERE
 39    vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
 40  UNION ALL
 41  SELECT
 42    index_name AS segment_name
 43  FROM
 44    user_indexes
 45  WHERE
 46    EXISTS
 47    (
 48       SELECT
 49         1
 50       FROM
 51         vector_idx_auxiliary_tables
 52       WHERE
 53         vector_idx_auxiliary_tables.aux_table_name = user_indexes.table_name
 54         AND vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
 55    )
 56* )
SCOTT@localhost:1521/freepdb1> /

 OBJECT_ID OBJECT_NAME                                                                                                                      OBJECT_TYPE
---------- -------------------------------------------------------------------------------------------------------------------------------- -----------------------
     80114 VECTOR$SEARCH_DATA_HNSW_IX$78074_80113_0$HNSW_ROWID_VID_MAP                                                                      TABLE
     80115 SYS_C0013840                                                                                                                     INDEX
     80117 VECTOR$SEARCH_DATA_HNSW_IX$78074_80113_0$HNSW_SHARED_JOURNAL_TRANSACTION_COMMITS                                                 TABLE PARTITION
     80116 VECTOR$SEARCH_DATA_HNSW_IX$78074_80113_0$HNSW_SHARED_JOURNAL_TRANSACTION_COMMITS                                                 TABLE
     80118 PK_XID_80113                                                                                                                     INDEX
     80120 VECTOR$SEARCH_DATA_HNSW_IX$78074_80113_0$HNSW_SHARED_JOURNAL_CHANGE_LOG                                                          TABLE PARTITION
     80119 VECTOR$SEARCH_DATA_HNSW_IX$78074_80113_0$HNSW_SHARED_JOURNAL_CHANGE_LOG                                                          TABLE
     80124 SYS_IL0000080119C00007$$                                                                                                         INDEX PARTITION
     80123 SYS_IL0000080119C00007$$                                                                                                         INDEX

9行が選択されました。

SCOTT@localhost:1521/freepdb1> l
  1 select object_id, data_object_id from user_objects where object_name = 'SEARCH_DATA'
SCOTT@localhost:1521/freepdb1> /

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     78074          78796

ここまでで、in-memory vector indexはどこ？
って話の場所的なところは見切れたかなと思いますが、
もう一つ、explain plan for create vector index及び、dbms_space.create_index_costプロシージャでの見積もりサイズは、信じて良いのか？、よくないのか？

エラーにはなってないが、信用してよいのだろうか。。。
ちなみに、23.6以降だが、INDEX_VECTOR_MEMORY_ADVISORプシージャによりインメモリーサイズを見積もることができるようになった。とか。。。すぐには用意できないのでw 23.4で一旦確認してみる

CREATE VECTOR INDEXで作成されるINMEMORYの索引オブジェクトに加え、補助表とよばれる表が複数存在する。未検証だが変更をトラックするタイプの補助表は更新量によってはセグメントサイズが一時的に増加する可能性あり

変更をトラッキングするのための2つのパーティション表と関連索引に関しては変更が無い限りセグメントは作成されない。はず。（deferred segment creationになっているようなので）
つまり新規作成時にはオブジェクトとしては存在するものの、セグメントは存在しない。
ということは、残る一つの補助表（仮でMAP表と呼ぶことにする）とその索引はある程度のセグメントを保持することになるだろうと予想。
また、インメモリー索引自体はベース表のVECTOR列のサイズに依存するのではないだろうか。。。と。

とりあえず、補助表のセグメントサイズを改めて確認してみる（in-memory vector index を何度かdrop/createしていためセグメント名に含まれるobject_idが異なりますが気になさらず。。）
今回のケース 125,000行のデータでは合計で 9MB 程度です。誤差の範囲程度のサイズではあります。ではメモリ上のサイズを占めるデータのベースとなる表のセグメントサイズも今一度確認しておきましょう。

SEGMENT_NAME                                                                     SEGMENT_TYPE               MB
-------------------------------------------------------------------------------- ------------------ ----------
VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_ROWID_VID_MAP                      TABLE                       3
SYS_C0013800                                                                     INDEX                       6

VECTORデータ型の列以外もありますが、ベース表のセグメントサイズは、 249MB あります。。。おやおや、サイズ、近似してますよね。。

SCOTT@localhost:1521/freepdb1> r
  1* select segment_name,segment_type,bytes / 1024 /1024 "MB" from user_segments where segment_name = 'SEARCH_DATA'

SEGMENT_NAME                   SEGMENT_TYPE               MB
------------------------------ ------------------ ----------
SEARCH_DATA                    TABLE                     248

もう少しベース表のセグメントサイズを調べてみましょう。

SCOTT@localhost:1521/freepdb1> l
  1  create table search_data_without_vector_desc
  2  as
  3  select
  4    id
  5    ,primary_description
  6    ,description
  7    ,location_desc
  8    ,district
  9    ,ward
 10    ,community
 11    ,c_year
 12* from search_data
SCOTT@localhost:1521/freepdb1> /

表が作成されました。

SCOTT@localhost:1521/freepdb1> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>upper('search_data_without_vector_desc'),cascade=>true,no_invalidate=>false);

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

おお、10MBなので、VECTORデータ型の列を含むSEARCH_DATA表のVECTORデータ型部分のサイズは、238MB　程度みたいですね。むむむ。。。

SCOTT@localhost:1521/freepdb1> r
  1  select
  2    segment_name
  3    ,segment_type
  4    ,bytes / 1024 /1024 "MB"
  5  from
  6    user_segments
  7  where
  8*   segment_name = upper('search_data_without_vector_desc')

SEGMENT_NAME                    SEGMENT_TYPE               MB
------------------------------- ------------------ ----------
SEARCH_DATA_WITHOUT_VECTOR_DESC TABLE                      10

in-memory vector index (HNSW)のメモリー消費サイズは、　228.4MB
（これに、補助表のMAP表と索引のセグメントサイズ 9MB を加算すると、237.4MB　おおおおおおおおーっ）

ベース表のVECTORデータ型だけのセグメントサイズは、238MB　

で、ですねぇ〜、EXPLAIN PLAN FOR CREATE VECTOR INDEX ...の見積もりサイズと、DBMS_SPACE.CREATE_INDEX_COSTプロシージャによる見積もり。。。VECTOR INDEXに対応できているという情報は未確認のままですが。。。

EXPLAIN PLAN FOR CREATE VECTOR INDEX ...
- estimated index size: 293M bytes

DBMS_SPACE.CREATE_INDEX_COSTプロシージャによるセグメントサイズ見積もり...
Segment Size (MB) :280

という結果になりました。偶然近い値だったの？？。。ということでもなさそうな気はします。。

ただし、ストレージのセグメントサイズではなくて、VECTOR MEMORY AREAで消費される索引サイズの見積もりとして....

みなさんは、どう思います？？？？

おまけの疑問というか違和感（これまでも少しだけ触れたが、妙な違和感がある）

これまでのIN-MEMORYとはことなり、VECTOR INDEX (HNSW)の場合、実行計画のオペレーション ( VECTOR INDEX HNSW SCAN ) には、INMEMORYというワードが含まれず勘違いしやすい気がするのに、
なぜ？ INMEMORYを含めなかったのだろう。また、*_OBJECTS/*_SEGMENTSを使わず、OBJECT_IDをIDX_OBJNというシノニムまで作り、vecsys.vector$index　で別管理かつ、補助表をJSONに書き込んでいる。
SQL文面倒なんすけど。。というのとこれまでの*_OBJECTSの存在と、vecsys.vector$indexのidx_objnの分離（*_objectsのobject_idの採番と同じなのに。。）といい、これまでと違う空気感が強い。

少し調べたのですが、
以下メッセージからも読み取れるように　INMEMORY というワードは、Oracle Database上、In-Memory Column Store Architectureをイメージさせるものではないことは確かようだ、
では、なぜ、INMEMORYというキーワードを実行計画のオペレーション名に含めなかった理由は少々理解しにくい。INMEMORY VECTOR INDEX HNSW SCANのほうがイメージしやすいと思うのは私だけだろうか。。。
このフワフワしてるところが違和感の原因でもあるな。。。

ORA-51815
INMEMORY NEIGHBOR GRAPH HNSW vector index snapshot is too old.
Oracle Database 23ai / Error Messages Oracle Database / Database Error Messages / ORA-51815

最後に、vector_index_neighbor_graph_reload を restartにすると再ロードされるよ（scope=bothでspfileにも同時に反映できるよ！）

23.4までは初期化パラメータ vector_index_neighbor_graph_reload のデフォルトが OFF であった。
インスタンス再起動で VECTOR INDEX (HNSW) がインメモリー上に再作成されない！！！w（なんだとーーー！）
23.6以降では、デフォルトが変更になり、 restart がデフォルトになっています。ここ試験にでるよ（知らんけどw

おまけに、ADB-Sだと、V$VECTOR_INDEXなんてビューが提供されていたり。。。なぜ全てで提供しないのだろう。。。23.4だからって話でもなさそうで。。

関連するディクショナリービューなどが整備されてないように見える（今後整備されるような気はする）。対応しきれていない部分はJSON化して回避しているようにも見える（やめて〜〜w）

ということで、最終的な私の理解のビジュアル化w 2025/6/25時点、かつ、Oracle Database 23ai 23.4 を元にした理解は以下の通り（まだ理解不足な箇所はあるかもしれない）

なお、
DBMS_VECTOR.GET_INDEX_STATUSプロシージャが返すステータスをみると興味深い内容が載っている
Oracle Database 23 / Oracle AI Vector Search User's Guide / Vector Index Status, Checkpoint, and Advisor Procedures / GET_INDEX_STATUS

CREATE VECTOR INDEX (HNSW)に関する状態を取得するプロシージャが返すステータスには以下のように記載されている。In-memory vector index (HNSW)のDDL発行からvector memory poolにロードされ、multi-layered HNSW graphができあがるまでのステータスがわかります！！！

そしてここでも、なんでプロシージャ必要だったのだろう。。。。と、言う素朴な疑問が！！！

Oracle Databaseには昔から、V$SESSION_LONGOPSってビューがあって。。。。
長時間操作のステータスがわかるようになっているのだが.....

ますます、なぜ、これまでの機能やビューを有効に再利用していないのだろう。。。という点が気になる...。が、現状は見切れたかな、と。
（もう一つのVector Indexのタイプと更新トラッキングと反映、いくつかの実行計画パターンの確認を除く）

HNSW Index Initialization
Initialization phase for the HNSW vector index creation

HNSW Index Auxiliary Tables Creation
Creation of the internal auxiliary tables for the HNSW Neighbor Graph vector index

HNSW Index Graph Allocation
Allocation of memory from the vector memory pool for the HNSW graph

HNSW Index Loading Vectors
Loading of the base table vectors into the vector pool memory

HNSW Index Graph Construction
Creation of the multi-layered HNSW graph with the previously loaded vectors

HNSW Index Creation Completed
HNSW vector index creation finished

では、また。

Enjoy SQL! and AI vector search!

・Oracle Database 23ai freeで試すVector Search - ONNXモデル準備編
・Oracle Database 23ai freeで試すVector Search - データ準備編
・実行計画は, SQL文のレントゲン写真だ！ No.66 / AI Vector Search - VECTOR INDEX HNSW SCAN
・VECTOR INDEX はどこ？、見積もりサイズだとそれなりのサイズだったのに... の謎を探るべく、我々は洞窟の奥へ向かった！(前編)
・VECTOR INDEX はどこ？、見積もりサイズだとそれなりのサイズだったのに... の謎を探るべく、我々は洞窟の奥へ向かった！(後編)

投稿日時 2025年6月25日 (水) 00時00分 Oracle, FAQ, SQL, in-memory, INDEX, Oracle Database 23c Free Developer Release, JSON, VECTOR, AI | 固定リンク | 0 | コメント (0)

2025年6月18日 (水)

VECTOR INDEX はどこ？、見積もりサイズだとそれなりのサイズだったのに... の謎を探るべく、我々は洞窟の奥へ向かった！(後編)

Previously on Mac De Oracle
前回は、VECTOR INDEX はどこ？、見積もりサイズだとそれなりのサイズだったのに... の謎を探るべく、我々は洞窟の奥へ向かった！(前編) でした。
今日はその後編です。

10046トレースよりVECTOR INDEX（HNSW）作成では前回の通り、補助表3つ（うち2つは変更をトラックするためなので作成直後は空）、それらの表に加え、各1つの索引が作成されるということがハッキリしました。
ただ、VECTOR INDEXの補助表はvecsys.vector$indexという表にJSONとして保持されている点と、USER_INDEXESから確認できるVECTOR INDEX自体は実態を持たない、さらに、Oraclerにはお馴染みの*_INDEXESなどから単純に取得できないことも見えてきました（今後もっと便利になることを期待したいですね）

(2025/6/18追記)
これ、HNSWってインメモリーと言っても、元ネタは永続化されているわけで、それをメモリープールにポピュレートしてるって理解（間違ってないとは思うけど、違ってたらコメントもらえるとありがたいです）なので、後編ではそのあたりを見ておこうかと。。。
インメモリー近傍グラフ・ベクトル索引

(作成される補助表の名称にVECTOR INDEXのベース表の名称が入っているようなので中間一致で検索していますが、これだとノイズも多くなるので検索キツイですよね。索引名はシステム生成名称なのでベース表の名称で中間一致検索はできません！。このケースに限ればなんとか拾えてるけどという感じではありますね。。。ということで後半ではJSONから引っこ抜いてなんとかするパズルもwやってますw)

SCOTT@localhost:1521/freepdb1> select index_name , table_name, index_type from user_indexes where table_name like '%SEARCH_DATA%';

INDEX_NAME                     TABLE_NAME                                                                       INDEX_TYPE
------------------------------ -------------------------------------------------------------------------------- ---------------------------
SYS_IL0000079525C00007$$       VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_SHARED_JOURNAL_CHANGE_LOG          LOB
SYS_IL0000078074C00009$$       SEARCH_DATA                                                                      LOB
SEARCH_DATA_HNSW_IX            SEARCH_DATA                                                                      VECTOR
SYS_C0013800                   VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_ROWID_VID_MAP                      NORMAL
PK_XID_79519                   VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_SHARED_JOURNAL_TRANSACTION_COMMITS NORMAL

SCOTT@localhost:1521/freepdb1> r
  1  SELECT
  2    idx_name
  3    ,(
  4       SELECT
  5         table_name
  6       FROM
  7         user_tables
  8       WHERE
  9         user_tables.table_name = (
 10           SELECT
 11             object_name
 12           FROM
 13             user_objects
 14           WHERE
 15             user_objects.object_id = vecsys.vector$index.idx_base_table_objn
 16        )
 17     ) AS tab_name
 18    ,JSON_SERIALIZE(
 19      idx_auxiliary_tables
 20      RETURNING VARCHAR2 PRETTY
 21    ) AS idx_auxiliary_tables
 22  FROM
 23    vecsys.vector$index
 24  WHERE
 25*   idx_name = 'SEARCH_DATA_HNSW_IX'

IDX_NAME                       TAB_NAME                       IDX_AUXILIARY_TABLES
------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------
SEARCH_DATA_HNSW_IX            SEARCH_DATA                    {
                                                                "rowid_vid_map_objn" : 79520,
                                                                "shared_journal_transaction_commits_objn" : 79522,
                                                                "shared_journal_change_log_objn" : 79525,
                                                                "rowid_vid_map_name" : "VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_ROWID_VID_MAP",
                                                                "shared_journal_transaction_commits_name" : "VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_SHARED_JOURNAL_TRANSACTION_COMMITS",
                                                                "shared_journal_change_log_name" : "VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_SHARED_JOURNAL_CHANGE_LOG"
                                                              }

これまでの調査の結果、VECTOR INEDEX (HNSW) であるSEARCH_DATA_HNSW_IXはオブジェクトでもなく、セグメントもない（当然、シノニムにもなれない。仮にも索引ですから）
VECTOR INDEX (HNSW)の索引セグメントのサイズは、作成直後のIDX_AUXILIARY_TABLESと、索引があればそれら索引のセグメントサイズの合計ということになりますよね！！！
（やっと見えてきたw、なんなんだこれ）

こんな感じでJSONからIDX_AUXILIARY_TABLESにある関連表のオブジェクトID、または、テーブル名を取り出しUSER_INDEXESから索引も合わせて取得したうえで、それぞれのセグメントサイズの合計を取得すれば物理的なサイズは見えますよね。。。。（めんどくさいw）

SCOTT@localhost:1521/freepdb1> l
  1  WITH
  2    vector_idx_auxiliary_tables
  3    AS (
  4      SELECT
  5       idx_name AS vector_index_name
  6       , REPLACE(aux_table_name,'"','') AS aux_table_name
  7      FROM
  8      (
  9          SELECT
 10            vvi.idx_name AS idx_name
 11            ,vvi.idx_auxiliary_tables.rowid_vid_map_name
 12            ,vvi.idx_auxiliary_tables.shared_journal_transaction_commits_name
 13            ,vvi.idx_auxiliary_tables.shared_journal_change_log_name
 14          FROM
 15            vecsys.vector$index vvi
 16      ) objs
 17      UNPIVOT (
 18        aux_table_name FOR related_obj_name IN
 19        (
 20          rowid_vid_map_name
 21          , shared_journal_transaction_commits_name
 22          , shared_journal_change_log_name
 23        )
 24      )
 25    )
 26  SELECT
 27    aux_table_name AS segment_name
 28  FROM
 29    vector_idx_auxiliary_tables
 30  WHERE
 31    vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
 32  UNION ALL
 33  SELECT
 34    index_name AS segment_name
 35  FROM
 36    user_indexes
 37  WHERE
 38    EXISTS
 39    (
 40       SELECT
 41         1
 42       FROM
 43         vector_idx_auxiliary_tables
 44       WHERE
 45         vector_idx_auxiliary_tables.aux_table_name = user_indexes.table_name
 46         AND vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
 47    )
 48*
SCOTT@localhost:1521/freepdb1> /

SEGMENT_NAME
---------------------------------------------------------------------------------------
VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_ROWID_VID_MAP
VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_SHARED_JOURNAL_TRANSACTION_COMMITS
VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_SHARED_JOURNAL_CHANGE_LOG
SYS_C0013800
PK_XID_79519
SYS_IL0000079525C00007$$

6行が選択されました。

経過: 00:00:00.16

では、あらためて、EXPLAIN PLAN FOR CREATE VECTOR INDEX...の見積もりサイズと、実際のサイズを比較してみましょう!

SCOTT@localhost:1521/freepdb1> l
  1  explain plan for
  2  CREATE VECTOR INDEX search_data_hnsw_ix ON search_data ( vector_desc )
  3    ORGANIZATION
  4      INMEMORY NEIGHBOR GRAPH
  5      DISTANCE COSINE
  6*     WITH TARGET ACCURACY 90
SCOTT@localhost:1521/freepdb1> /

解析されました。

経過: 00:00:00.02
SCOTT@localhost:1521/freepdb1> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 2727344110

----------------------------------------------------------------------------------------------
| Id  | Operation              | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |                     |   125K|   188M| 14689   (1)| 00:00:01 |
|   1 |  VECTOR INDEX BUILD    | SEARCH_DATA_HNSW_IX |       |       |            |          |
----------------------------------------------------------------------------------------------

Note
-----
   - estimated index size: 293M bytes

なんどかコメントしてますが、SEARCH_DATA_HNSW_IXという索引セグメントは無く！、複数の補助表と索引群から構成されている。それらの作成直後のサイズは....

SCOTT@localhost:1521/freepdb1> r
  1  WITH
  2    vector_idx_auxiliary_tables
  3    AS (
  4      SELECT
  5       idx_name AS vector_index_name
  6       , REPLACE(aux_table_name,'"','') AS aux_table_name
  7      FROM
  8      (
  9          SELECT
 10            vvi.idx_name AS idx_name
 11            ,vvi.idx_auxiliary_tables.rowid_vid_map_name
 12            ,vvi.idx_auxiliary_tables.shared_journal_transaction_commits_name
 13            ,vvi.idx_auxiliary_tables.shared_journal_change_log_name
 14          FROM
 15            vecsys.vector$index vvi
 16      ) objs
 17      UNPIVOT (
 18        aux_table_name FOR related_obj_name IN
 19        (
 20          rowid_vid_map_name
 21          , shared_journal_transaction_commits_name
 22          , shared_journal_change_log_name
 23        )
 24      )
 25    )
 26  SELECT
 27    user_segments.segment_name
 28    , user_segments.segment_type
 29    , user_segments.bytes / 1024 / 1024 AS "MB"
 30  FROM
 31    user_segments
 32    INNER JOIN
 33    (
 34      SELECT
 35        aux_table_name AS segment_name
 36      FROM
 37        vector_idx_auxiliary_tables
 38      WHERE
 39        vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
 40      UNION ALL
 41      SELECT
 42        index_name AS segment_name
 43      FROM
 44        user_indexes
 45      WHERE
 46        EXISTS
 47        (
 48           SELECT
 49             1
 50           FROM
 51             vector_idx_auxiliary_tables
 52           WHERE
 53             vector_idx_auxiliary_tables.aux_table_name = user_indexes.table_name
 54             AND vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
 55        )
 56     ) vector_idx_aux_objects
 57     ON
 58       vector_idx_aux_objects.segment_name = user_segments.segment_name
 59*

SEGMENT_NAME                                                                     SEGMENT_TYPE               MB
-------------------------------------------------------------------------------- ------------------ ----------
VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_ROWID_VID_MAP                      TABLE                       3
SYS_C0013800                                                                     INDEX                       6

誤差が大きいですよね。正しく見積もれてない可能性が高いように思いますが、少なく出るよりマシ程度かもしれません。今後のチューニングに期待。。。。。というところでしょうか。。。

SCOTT@localhost:1521/freepdb1> l
  1  explain plan for
  2  CREATE VECTOR INDEX search_data_hnsw_ix ON search_data ( vector_desc )
  3    ORGANIZATION
  4      INMEMORY NEIGHBOR GRAPH
  5      DISTANCE COSINE
  6*     WITH TARGET ACCURACY 90
SCOTT@localhost:1521/freepdb1> /

だと、

- estimated index size: 293M bytes
ですが、
実際には以下の通り、　9M Bytes程度でした。
（もしかして、VECTOR INDEX　(HNSW)ってINMEMORYだからメモリーサイズ？　んなことないか、いや、あったりしてw　と思ったりし始めているw　 23ai 23.6では、DBMS_VECTOR.INDEX_VECTOR_MEMORY_ADVISORプロシージャによりメモリーサイズを見積もれるようになっていたりする。。怪しい。2025/6/19追記）

SEGMENT_NAME                                                                     SEGMENT_TYPE               MB
-------------------------------------------------------------------------------- ------------------ ----------
VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_ROWID_VID_MAP                      TABLE                       3
SYS_C0013800                                                                     INDEX                       6

Bツリー索引の見積り（19cで確かめたときは、これほど大きな差にはなっていませんでした）に比べると精度は低そうですよね。と言うより、VECTOR INDEXには対応できてないのかもしれませんね....

おまけで、
DBMS_SPACEパッケージの索引サイズ見積もりも試しておきましょう。。。。。やはり、explain plan for文同様にVECTOR INDEXのセグメントサイズ見積もりには対応してなさそう。（小さくでるわけではないのですがw）

SCOTT@localhost:1521/freepdb1> set serveroutput on
SCOTT@localhost:1521/freepdb1> l
  1  DECLARE
  2    used_bytes NUMBER;
  3    segment_bytes NUMBER;
  4  BEGIN
  5    DBMS_SPACE.CREATE_INDEX_COST (
  6      ddl=> 'CREATE VECTOR INDEX search_data_hnsw_ix ON search_data ( vector_desc )'
  7            || ' ORGANIZATION'
  8            || ' INMEMORY NEIGHBOR GRAPH'
  9            || ' DISTANCE COSINE'
 10            || ' WITH TARGET ACCURACY 90'
 11      , used_bytes => used_bytes
 12      , alloc_bytes =>  segment_bytes
 13    );
 14    DBMS_OUTPUT.PUT_LINE('Segment Size (MB) :'||segment_bytes/1024/1024);
 15    DBMS_OUTPUT.PUT_LINE('Index data Size (MB) :'||used_bytes/1024/1024);
 16* END;
SCOTT@localhost:1521/freepdb1> /
Segment Size (MB) :280
Index data Size (MB) :188.94672393798828125

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

Enjoy SQL, PL/SQL and VECTOR SEARCH!

VECTOR INDEXの謎はもう少し探る必要があるようだ...w (後編のおまけにつづくw）

めちゃ快適な気温の場所なので、
東京は猛暑と聞いて恐怖している

では、また。

参考）
今回利用したコードの一部

WITH
  vector_idx_auxiliary_tables
  AS (
    SELECT
     idx_name AS vector_index_name
     , REPLACE(aux_table_name,'"','') AS aux_table_name
    FROM
    (
        SELECT
          vvi.idx_name AS idx_name
          ,vvi.idx_auxiliary_tables.rowid_vid_map_name
          ,vvi.idx_auxiliary_tables.shared_journal_transaction_commits_name
          ,vvi.idx_auxiliary_tables.shared_journal_change_log_name
        FROM
          vecsys.vector$index vvi
    ) objs
    UNPIVOT (
      aux_table_name FOR related_obj_name IN
      (
        rowid_vid_map_name
        , shared_journal_transaction_commits_name
        , shared_journal_change_log_name
      )
    )
  )
SELECT
  aux_table_name AS segment_name
FROM
  vector_idx_auxiliary_tables
WHERE
  vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
UNION ALL
SELECT
  index_name AS segment_name
FROM
  user_indexes
WHERE
  EXISTS
  (
     SELECT
       1
     FROM
       vector_idx_auxiliary_tables
     WHERE
       vector_idx_auxiliary_tables.aux_table_name = user_indexes.table_name
       AND vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
  )
;

WITH
  vector_idx_auxiliary_tables
  AS (
    SELECT
     idx_name AS vector_index_name
     , REPLACE(aux_table_name,'"','') AS aux_table_name
    FROM
    (
        SELECT
          vvi.idx_name AS idx_name
          ,vvi.idx_auxiliary_tables.rowid_vid_map_name
          ,vvi.idx_auxiliary_tables.shared_journal_transaction_commits_name
          ,vvi.idx_auxiliary_tables.shared_journal_change_log_name
        FROM
          vecsys.vector$index vvi
    ) objs
    UNPIVOT (
      aux_table_name FOR related_obj_name IN
      (
        rowid_vid_map_name
        , shared_journal_transaction_commits_name
        , shared_journal_change_log_name
      )
    )
  )
SELECT
  user_segments.segment_name
  , user_segments.segment_type
  , user_segments.bytes / 1024 / 1024 AS "MB"
FROM
  user_segments
  INNER JOIN
  (
    SELECT
      aux_table_name AS segment_name
    FROM
      vector_idx_auxiliary_tables
    WHERE
      vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
    UNION ALL
    SELECT
      index_name AS segment_name
    FROM
      user_indexes
    WHERE
      EXISTS
      (
         SELECT
           1
         FROM
           vector_idx_auxiliary_tables
         WHERE
           vector_idx_auxiliary_tables.aux_table_name = user_indexes.table_name
           AND vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
      )
   ) vector_idx_aux_objects
   ON
     vector_idx_aux_objects.segment_name = user_segments.segment_name
;

set serveroutput on
DECLARE
  used_bytes NUMBER;
  segment_bytes NUMBER;
BEGIN
  DBMS_SPACE.CREATE_INDEX_COST (
    ddl=> 'CREATE VECTOR INDEX search_data_hnsw_ix ON search_data ( vector_desc )'
          || ' ORGANIZATION'
          || ' INMEMORY NEIGHBOR GRAPH'
          || ' DISTANCE COSINE'
          || ' WITH TARGET ACCURACY 90'
    , used_bytes => used_bytes
    , alloc_bytes =>  segment_bytes
  );
  DBMS_OUTPUT.PUT_LINE('Segment Size (MB) :'||segment_bytes/1024/1024);
  DBMS_OUTPUT.PUT_LINE('Index data Size (MB) :'||used_bytes/1024/1024);
END;
/

投稿日時 2025年6月18日 (水) 00時00分 Oracle, PL/SQL, チューニング, FAQ, SQL, パフォーマンス, in-memory, INDEX, Oracle Database 23c Free Developer Release, JSON, VECTOR, AI | 固定リンク | 0 | コメント (0)

2016年9月24日 (土)

VirtualBoxとSIMD

Previously on Mac De Oracle
SIMDのメモをあれこれ貼り付けただけでしたが、実は今回のことを調べてる次いでに見つけたものだったんですよ。

OracleもSIMDを使う操作が多くなりつつあるようなのでどうなんだろというわけで、今回もメモです。

元ネタは以下のエントリ

VirtualBoxゲストでSIMD命令: DB In-Memory
https://blogs.oracle.com/LetTheSunShineIn/entry/virtualboxゲストでsimd命令_db_in_memory

上記は約２年前の記事で現在のVirtualBoxでは、SSE4.1/SSE4.2は有効化されています。
以下ドキュメントを参照のこと。

9.27. Experimental support for passing through SSE4.1 / SSE4.2 instructions
https://www.virtualbox.org/manual/ch09.html#sse412passthrough

ただ、AVXはどうなのか書かれてはいないのでどうなってんのかな〜とか、ググってたら

Ticket #14427 (new defect)
https://www.virtualbox.org/ticket/14427#comment:3
こんな記事を見つけたのであー有効になってるよねーということで、うちの環境での確認

確認に利用したVirtualBoxは現時点の最新版です。

MacBook:˜ lampeyes$ VBoxManage -version
5.1.6r110634
MacBook:˜ lampeyes$

うちのMacファミリーでは最古参w　（そろそろなんとか新しいのにしたいなぁ〜w）
古いCPUだけど、マニュアル通り、SSE4.1/4.2はguestでも有効化されていました。おし！

MacPro:˜ lampeyes$ /usr/sbin/system_profiler SPHardwareDataType | grep -E 'Model|Processor|Core|Cache'
      Model Name: Mac Pro
      Model Identifier: MacPro5,1
      Processor Name: 6-Core Intel Xeon
      Processor Speed: 2.4 GHz
      Number of Processors: 2
      Total Number of Cores: 12
      L2 Cache (per Core): 256 KB
      L3 Cache (per Processor): 12 MB
      Processor Interconnect Speed: 5.86 GT/s
MacPro:˜ lampeyes$ 
MacPro:˜ lampeyes$ grep -E 'Mnemonic|SSE4|AVX' '/Users/lampeyes/VirtualBox VMs/Linux  Group/OralceLinux (Oracle12c R1)/Logs/VBox.log'
00:00:02.472827   Mnemonic - Description                                  = guest (host)
00:00:02.472916   SSE4_1 - SSE4_1 support                                 = 1 (1)
00:00:02.472919   SSE4_2 - SSE4_2 support                                 = 1 (1)
00:00:02.472934   AVX - AVX support                                       = 0 (0)
00:00:02.472942   Mnemonic - Description                                  = guest (host)
00:00:02.472951   AVX2 - Advanced Vector Extensions 2                     = 0 (0)
00:00:02.472968   AVX512F - AVX512 Foundation instructions                = 0 (0)
00:00:02.472978   AVX512PF - AVX512 Prefetch instructions                 = 0 (0)
00:00:02.472980   AVX512ER - AVX512 Exponential & Reciprocal instructions = 0 (0)
00:00:02.472981   AVX512CD - AVX512 Conflict Detection instructions       = 0 (0)
00:00:02.473041   Mnemonic - Description                                  = guest (host)
00:00:02.473103   SSE4A - SSE4A instructions                              = 0 (0)
MacPro:˜ lampeyes$

最近のメインマシンw
SSE4.1/4.2に加えてAVXもguestで有効化されていました。
（探したかぎりではドキュメントには見当たらないかった。もしご存知の方がいたら教えてくだい！

ただし、AVX2はguestでは無効化されています。。。。大人の事情でもあるのかとググってますが今の所見つからず。

MacBookAir:˜ lampeyes$ /usr/sbin/system_profiler SPHardwareDataType | grep -E 'Model|Processor|Core|Cache'
      Model Name: MacBook Air
      Model Identifier: MacBookAir7,2
      Processor Name: Intel Core i7
      Processor Speed: 2.2 GHz
      Number of Processors: 1
      Total Number of Cores: 2
      L2 Cache (per Core): 256 KB
      L3 Cache: 4 MB
MacBookAir:˜ lampeyes$ grep -E 'Mnemonic|SSE4|AVX' '/Users/lampeyes/VirtualBox VMs/OracleLinux/Logs/VBox.log'
00:00:02.654824   Mnemonic - Description                                  = guest (host)
00:00:02.654876   SSE4_1 - SSE4_1 support                                 = 1 (1)
00:00:02.654877   SSE4_2 - SSE4_2 support                                 = 1 (1)
00:00:02.654886   AVX - AVX support                                       = 1 (1)
00:00:02.654890   Mnemonic - Description                                  = guest (host)
00:00:02.654894   AVX2 - Advanced Vector Extensions 2                     = 0 (1)
00:00:02.654903   AVX512F - AVX512 Foundation instructions                = 0 (0)
00:00:02.654909   AVX512PF - AVX512 Prefetch instructions                 = 0 (0)
00:00:02.654909   AVX512ER - AVX512 Exponential & Reciprocal instructions = 0 (0)
00:00:02.654910   AVX512CD - AVX512 Conflict Detection instructions       = 0 (0)
00:00:02.654987   Mnemonic - Description                                  = guest (host)
00:00:02.655021   SSE4A - SSE4A instructions                              = 0 (0)

AVX2をguestで有効化することは可能なようです。

MacBookAir:˜ lampeyes$ 
MacBookAir:˜ lampeyes$ VBoxManage setextradata "OracleLinux" VBoxInternal/CPUM/IsaExts/AVX2 1

MacBookAir:˜ lampeyes$  grep -E 'Mnemonic|SSE4|AVX' '/Users/lampeyes/VirtualBox VMs/OracleLinux/Logs/VBox.log'
00:00:02.557798   AVX2  = 0x0000000000000001 (1)
00:00:02.815177   Mnemonic - Description                                  = guest (host)
00:00:02.815226   SSE4_1 - SSE4_1 support                                 = 1 (1)
00:00:02.815227   SSE4_2 - SSE4_2 support                                 = 1 (1)
00:00:02.815235   AVX - AVX support                                       = 1 (1)
00:00:02.815248   Mnemonic - Description                                  = guest (host)
00:00:02.815253   AVX2 - Advanced Vector Extensions 2                     = 1 (1)
00:00:02.815262   AVX512F - AVX512 Foundation instructions                = 0 (0)
00:00:02.815267   AVX512PF - AVX512 Prefetch instructions                 = 0 (0)
00:00:02.815268   AVX512ER - AVX512 Exponential & Reciprocal instructions = 0 (0)
00:00:02.815269   AVX512CD - AVX512 Conflict Detection instructions       = 0 (0)
00:00:02.815348   Mnemonic - Description                                  = guest (host)
00:00:02.815381   SSE4A - SSE4A instructions                              = 0 (0)
MacBookAir:˜ lampeyes$

最後に Intel Core m5のMacBookですが、こちらは、MacBook Airと同様で、AVX2がguest側で無効化されていて、同様に手順で有効化できました。

何か不都合があったらオフればいいので、このまま使ってみようかと。:)

MacBook:˜ lampeyes$  /usr/sbin/system_profiler SPHardwareDataType | grep -E 'Model|Processor|Core|Cache'
      Model Name: MacBook
      Model Identifier: MacBook9,1
      Processor Name: Intel Core m5
      Processor Speed: 1.2 GHz
      Number of Processors: 1
      Total Number of Cores: 2
      L2 Cache (per Core): 256 KB
      L3 Cache: 4 MB
MacBook:˜ lampeyes$
 MacBook:˜ lampeyes$ grep -E 'Mnemonic|SSE4|AVX' '/Users/lampeyes/VirtualBox VMs/DOC-921720/Logs/VBox.log' 
00:00:02.791014   Mnemonic - Description                                  = guest (host)
00:00:02.791076   SSE4_1 - SSE4_1 support                                 = 1 (1)
00:00:02.791077   SSE4_2 - SSE4_2 support                                 = 1 (1)
00:00:02.791088   AVX - AVX support                                       = 1 (1)
00:00:02.791093   Mnemonic - Description                                  = guest (host)
00:00:02.791100   AVX2 - Advanced Vector Extensions 2                     = 0 (1)
00:00:02.791112   AVX512F - AVX512 Foundation instructions                = 0 (0)
00:00:02.791119   AVX512PF - AVX512 Prefetch instructions                 = 0 (0)
00:00:02.791120   AVX512ER - AVX512 Exponential & Reciprocal instructions = 0 (0)
00:00:02.791121   AVX512CD - AVX512 Conflict Detection instructions       = 0 (0)
00:00:02.791227   Mnemonic - Description                                  = guest (host)
00:00:02.791270   SSE4A - SSE4A instructions                              = 0 (0)
MacBook:˜ lampeyes$ 
MacBook:˜ lampeyes$ VBoxManage setextradata "DOC-921720" VBoxInternal/CPUM/IsaExts/AVX2 1
MacBook:˜ lampeyes$ 
MacBook:˜ lampeyes$ 
MacBook:˜ lampeyes$ grep -E 'Mnemonic|SSE4|AVX' '/Users/lampeyes/VirtualBox VMs/DOC-921720/Logs/VBox.log' 
00:00:01.243213   AVX2  = 0x0000000000000001 (1)
00:00:01.405462   Mnemonic - Description                                  = guest (host)
00:00:01.405592   SSE4_1 - SSE4_1 support                                 = 1 (1)
00:00:01.405593   SSE4_2 - SSE4_2 support                                 = 1 (1)
00:00:01.405604   AVX - AVX support                                       = 1 (1)
00:00:01.405610   Mnemonic - Description                                  = guest (host)
00:00:01.405616   AVX2 - Advanced Vector Extensions 2                     = 1 (1)
00:00:01.405628   AVX512F - AVX512 Foundation instructions                = 0 (0)
00:00:01.405635   AVX512PF - AVX512 Prefetch instructions                 = 0 (0)
00:00:01.405636   AVX512ER - AVX512 Exponential & Reciprocal instructions = 0 (0)
00:00:01.405637   AVX512CD - AVX512 Conflict Detection instructions       = 0 (0)
00:00:01.405758   Mnemonic - Description                                  = guest (host)
00:00:01.405836   SSE4A - SSE4A instructions                              = 0 (0)
MacBook:˜ lampeyes$

一つ忘れてた、うちで一番古いやつ。（これもそろそろ刷新したいｗ）
古いのでAVX以降はないですね。Windows 10 Proのリアルマシンなのでコマンドプロンプトからbashで情報取得。う～～ん便利bash w

lampeyes@LAMPEYES:/mnt/c$ cat /proc/cpuinfo | grep -E 'model name|cpu core|siblings' | sort -n | uniq


cpu cores       : 6
model name      : Intel(R) Core(TM) i7 CPU       X 980  @ 3.33GHz
siblings        : 12
lampeyes@LAMPEYES:/mnt/c$
lampeyes@LAMPEYES:/mnt/c$ grep -E 'Mnemonic|SSE4|AVX' '/mnt/c/Users/lampeyes/VirtualBox VMs/CentOS6.5 64bit/Logs/VBox.log'
00:00:04.805674   Mnemonic - Description                                  = guest (host)
00:00:04.805719   SSE4_1 - SSE4_1 support                                 = 1 (1)
00:00:04.805720   SSE4_2 - SSE4_2 support                                 = 1 (1)
00:00:04.805728   AVX - AVX support                                       = 0 (0)
00:00:04.805732   Mnemonic - Description                                  = guest (host)
00:00:04.805756   AVX2 - Advanced Vector Extensions 2                     = 0 (0)
00:00:04.805765   AVX512F - AVX512 Foundation instructions                = 0 (0)
00:00:04.805770   AVX512PF - AVX512 Prefetch instructions                 = 0 (0)
00:00:04.805771   AVX512ER - AVX512 Exponential & Reciprocal instructions = 0 (0)
00:00:04.805772   AVX512CD - AVX512 Conflict Detection instructions       = 0 (0)
00:00:04.805811   Mnemonic - Description                                  = guest (host)
00:00:04.805842   SSE4A - SSE4A instructions                              = 0 (0)
lampeyes@LAMPEYES:/mnt/c$

投稿日時 2016年9月24日 (土) 22時40分 FAQ, VirtualBox, in-memory, SIMD | 固定リンク | 0 | コメント (0) | トラックバック (0)

2016年9月22日 (木)

メモ：SIMDあれこれ

SIMDあれこれ自分メモ

primitive: blog / introdunction to SIMD programming
http://i-saint.hatenablog.com/entry/2015/05/26/212441

wikipedia / Streaming SIMD Extensions
https://ja.wikipedia.org/wiki/Streaming_SIMD_Extensions

IA Software User Society / コンパイラー最適化入門：第1回 SIMD 命令とプロセッサーの関係
http://www.isus.jp/products/c-compilers/compiler_part1/

IA Software User Society / コンパイラー最適化入門：第2回 SIMD 命令と伝統的な IA 命令
http://www.isus.jp/products/c-compilers/compiler_part2/

IA Software User Society / コンパイラー最適化入門：第3回インテル® コンパイラーのベクトル化レポートを活用する
http://www.isus.jp/products/c-compilers/compiler_part3/

IA Software User Society / コンパイラー最適化入門：第4回自動ベクトル化はどんな時に行われるか
http://www.isus.jp/products/c-compilers/compiler_part4/

IA Software User Society / コンパイラー最適化入門：第5回明示的にベクトル化されたコードを記述する
http://www.isus.jp/products/c-compilers/compiler_part5/

IA Software User Society / コンパイラー最適化入門：第6回ベクトル化の裏技集
http://www.isus.jp/products/c-compilers/compiler_part6/

Oracle In-Memory Column Store Internals – Part 1 – Which SIMD extensions are getting used?
http://blog.tanelpoder.com/2014/10/05/oracle-in-memory-column-store-internals-part-1-which-simd-extensions-are-getting-used/

SIMD-Scan: Ultra Fast in-Memory Table Scan using on- Chip Vector Processing Units
http://www.vldb.org/pvldb/2/vldb09-327.pdf

Rethinking SIMD Vectorization for In-Memory Databases
http://www.cs.columbia.edu/~orestis/sigmod15.pdf

Oracle … as usual / SIMD Extensions in and out Oracle 12.1.0.2
https://laurent-leturgez.com/2015/04/22/simd-extensions-in-and-out-oracle-12-1-0-2/

Laurent Leturgez, / Ukoug15 SIMD outside and inside Oracle 12c (12.1.0.2)
http://www.slideshare.net/lolo115/ukoug15-simd-outside-and-inside-oracle-12c-12102

Cellプログラミングチュートリアル Version 0.2c
http://cell.fixstars.com/ps3linux/tutorial/index.html

投稿日時 2016年9月22日 (木) 00時27分その他, チューニング, FAQ, パフォーマンス, in-memory | 固定リンク | 0 | コメント (0) | トラックバック (0)

2014年7月30日 (水)

Oracle Database 12c R1 12.1.0.2.0 In-memory option　はじめの一歩。

こまけーことは置いといて、
Oracle Database 12c R1 12.1.0.2.0 In-memory optionが利用できるようになったので簡単に試してみた。

環境は以下の通り

Oracle VM VirtualBox 4.3.12 for OS X
Oracle Linux 6.4
Oracle Database 12c EE 12.1.0.2.0 for Linux x86-64

時間が無かったので、マニュアルを斜め読みしつつ、とにかくインストール....
inmemory系初期化パラメータの確認。　(12.1.0.1と12.1.0.2の初期化パラメータの差分確認はあとで：TODO)

inmemory_sizeパラメータが0なのでin-memory column storeは機能しない状態がデフォルトなのか..

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
に接続されました。
SYS> 
SYS> show parameter inmemory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     0
inmemory_query                       string      ENABLE
inmemory_size                        big integer 0
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE
SYS>

以下の表を作成してあります。 10万行のうちb='1'である行は10行だけにしてあります。

SCOTT> desc foobar
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER
 B                                                  CHAR(1)


SCOTT> select count(1) from foobar;

  COUNT(1)
----------
    100000

今まで通りに表を作成すると、in-memory column storeはdisabledで作成されるのか! ふむふむ。

SCOTT> select table_name,inmemory from user_tables where table_name='FOOBAR';

TABLE_NAME                     INMEMORY
------------------------------ --------
FOOBAR                         DISABLED

索引は作成していないので以下のようなクエリを実行すれば、TABLE ACCESS FULLですよね。

SCOTT> select /*+ gather_plan_statistics */ count(1) from foobar where b = '1';

  COUNT(1)
----------
        10

SCOTT> @show_actual_plan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  4zurd70pg2qna, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(1) from foobar where b = '1'

Plan hash value: 2479556450

---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.01 |     191 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |     191 |
|*  2 |   TABLE ACCESS FULL| FOOBAR |      1 |     15 |     10 |00:00:00.01 |     191 |
---------------------------------------------------------------------------------------

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

   2 - filter("B"='1')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


23行が選択されました。

in-memory column storeを利用するための準備〜〜〜。
alter table文でin-memory column storeを有効化。（簡単だな！）

SCOTT> alter table foobar inmemory;

表が変更されました。

おっと、忘れてました、 inmemory_sizeパラメータを設定しないといけないんだった...

ちなみに、inmemory_sizeパラメータは静的パラメータなので、scope=spfileを付けることをお忘れなく...

SYS> alter system set inmemory_size = 10m;
alter system set inmemory_size = 10m
*
行1でエラーが発生しました。:
ORA-02097: 指定した値が無効なので、パラメータを変更できません。 ORA-02095:
指定した初期化パラメータを変更できません。

メモリ関係のパラメータ変えて起動しなくなるって過去やらかしたことがあるので、手が勝ってに動くw

SYS> create pfile from spfile;

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

変更できたました。
SYS> alter system set inmemory_size = 10m scope=spfile;

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

SYS> 

....中略....

起動してみましょう！　あらららら。

SYS> startup
ORA-64353: in-memory area size cannot be less than 100MB

ううううう、なんということでしょう！ w このパラメータ間違うと起動しないのな!!!!! 注意しないと。＞　俺

in-memory初期化パラメータは 100MB以上に設定しましょうね。マニュアルにも書いてますから。（読みましょうね。 (^^;;;;

気を取り直して、再チャレンジ :)

[oracle@emperor ˜]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on 火 7月 29 06:34:46 2014

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

アイドル・インスタンスに接続しました。

SYS> create spfile from pfile;

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

SYS> startup
ORACLEインスタンスが起動しました。

Total System Global Area 3875536896 bytes
Fixed Size		    3717856 bytes
Variable Size		 2248148256 bytes
Database Buffers	 1610612736 bytes
Redo Buffers		   13058048 bytes
データベースがマウントされました。
データベースがオープンされました。

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

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

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


SYS> startup
ORACLEインスタンスが起動しました。

Total System Global Area 3875536896 bytes
Fixed Size		    3717856 bytes
Variable Size		 2231371040 bytes
Database Buffers	 1509949440 bytes
Redo Buffers		   13058048 bytes
In-Memory Area		  117440512 bytes
データベースがマウントされました。
データベースがオープンされました。
SYS> 
SYS>

In-Memory Areaとリストされています。
うまくいったようなので in-memory column storeを試してみます！！！！

SCOTT> select /*+ gather_plan_statistics */ count(1) from foobar where b = '1';

  COUNT(1)
----------
        10

SCOTT> 
SCOTT> 
SCOTT> @show_actual_plan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID  4zurd70pg2qna, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(1) from foobar where b = '1'

Plan hash value: 2479556450

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |      1 |00:00:00.01 |       8 |
|   1 |  SORT AGGREGATE             |        |      1 |      1 |      1 |00:00:00.01 |       8 |
|*  2 |   TABLE ACCESS INMEMORY FULL| FOOBAR |      1 |     15 |     10 |00:00:00.01 |       8 |
------------------------------------------------------------------------------------------------

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

   2 - inmemory("B"='1')
       filter("B"='1')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


24行が選択されました。

in-memory column storeを利用しない場合、Buffer Getsが191ありましたが、in-memory column storeを利用した場合、Buffer Getsが8と大幅に減少しています！！！。データにもよるだろうけど、このケースだと、4%程度になってますね。

KEEP乗せとか手動でやらなくて済むケースあるんだろうな〜と、遠くを眺めながら.......in-memory column store初めの一歩はここまで。

つづく。

投稿日時 2014年7月30日 (水) 07時47分 SQL, Oracle Database 12c release 1, in-memory | 固定リンク | 0 | コメント (0) | トラックバック (0)

Mac De Oracle

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