Oracle Technology Night #79 -2 Oracle Database 23ai 新機能 - Large Object および SecureFiles

Oracle Technology Night #79 -2 Oracle Database 23ai 新機能 -
Large Object および SecureFiles 日本オラクル株式会社 2024年 5月辻研一郎、津島浩樹、服部純大、橋本琢爾

以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことはできません。以下の事項は、マテリアルやコード、機能を提供することを確約するものではないため、購買決定を行う際の判断材料になさらないで下さい。オラクル製品に関して記載されている機能の開発、リリース、時期及び価格については、弊社の裁量により決定され、変更される可能性があります。 Safe harbor statement
2 Copyright © 2024, Oracle and/or its affiliates

Oracle Database 23ai 新機能 - Large Object および SecureFiles Copyright
© 2024, Oracle and/or its affiliates 3 2 23ai 新機能 23aiで強化されたポイントを詳細に解説 1 LOBの基礎 LOBデータ型の基礎情報と新機能に関連する技術的な予備知識

23ai 新機能 - Large Object および SecureFiles LOBの基礎知識 - ラージ・オブジェクトのデータを扱う２つの格納パターン
Copyright © 2024, Oracle and/or its affiliates 4 Large Object CLOB/NCLOB/BLOB SecureFiles SecureFiles DBFS (OFS) ファイル・システムインタフェース DBのデータ型として実装リレーショナル・データとしてSQLで操作オブジェクトを格納した領域をファイル・システムI/Fを介して公開 ※ BFILEは割愛

23ai 新機能 - Large Object および SecureFiles LOBの基礎知識 - LOBデータ型の種類
Copyright © 2024, Oracle and/or its affiliates 5 CLOB/NCLOB/BLOB SecureFiles (BasicFiles) SQLデータ型説明区分 BLOB バイナリ・ラージ・オブジェクトすべての種類のデータがバイナリ形式で格納されます。イメージ、オーディオおよびビデオに使用されます。内部LOB CLOB キャラクタ・ラージ・オブジェクト文字列データがデータベース文字セット形式で格納されます。データベース文字セットを使用する大きい文字列またはドキュメント専用です。データベース文字セットの文字は固定幅形式です。 NCLOB 各国語文字セット・ラージ・オブジェクト文字列データ(通常はラージ文字列またはドキュメント)を各国語文字セット書式で格納します。可変幅形式の文字がサポートされます。 BFILE 外部バイナリ・ファイルホスト・オペレーティング・システムのファイル・システム内でデータベースの外部に格納される、データベース表からアクセス可能なバイナリ・ファイルです。 BFILEには、アプリケーションから読取り専用でアクセスできます。BFILEは、アプリケーションで操作されない、イメージ・データなどの静的データを格納するために使用します。 BFILEには、あらゆる種類のデータ、つまり任意のオペレーティング・システム・ファイルを格納できます。たとえば、文字データをBFILEに格納し、ロード時に文字セットを指定してBFILEデータをCLOBにロードできます。外部LOB

23ai 新機能 - Large Object および SecureFiles LOBの基礎知識 - -
LOBのタイプ Copyright © 2024, Oracle and/or its affiliates 6 LOB (CLOB/NCLOB/BLOB) SecureFiles (BasicFiles) 永続一時読込み専用値コード上で一時的に生成・利用 (PGA/temp表領域に確保) ⇨ 永続化可能読込み専用LOBの拡張値に特化したLOBで性能向上および、LOB データの蓄積回避に寄与

LOBの基礎知識 - LOBのタイプ 23ai 新機能 - Large Object および SecureFiles
Copyright © 2024, Oracle and/or its affiliates 7 永続LOB • データベースの表の行に存在するLOBインスタンスで、一般的なSecureFilesまたはBasicFilesとして格納するLOB 一時LOB (SecureFiles管理ではなく) • ローカル・アプリケーション内で一時的にインスタンス化して使用するLOB (他のローカル変数と同様に一時的なもの) で、データ操作のための一時的なワークスペースとして理想的で、OCI、PL/SQLなどのインタフェースを使用して作成または操作する (SQLセマンティクスまたはPL/SQLセマンティクスで暗黙的に作成される場合もある) • PL/SQLでLOBを更新する場合に、LOB変数にはロケータが格納されるため、一時LOBを定義しないと永続LOBを更新する • ロギングが行われず、REDO レコードも生成されないため、永続LOB よりもパフォーマンスが優れている • LOBに断片的な操作を多く実行するアプリケーションでは一時LOBを使用することでパフォーマンスが大幅に向上 • LOB を書き換えたり更新したりするたびに、Oracle は LOB全体をにコピーする (UNDOとして更新前LOBデータを残すため) • サイズに応じてPGAメモリまたは一時表領域に存在し (以下がデフォルト値)、表の行に挿入すると永続LOBになる • 一時LOBインスタンスを解放する必要がある (21cからは解放する前に一時LOB/永続LOBの確認をする必要がない) • アプリケーションが実行している間、一時LOBは存在する。実行完了後リソースを解放しないと、領域が蓄積され、性能劣化可能性がある BFILE (外部LOB) • データベース表領域外のオペレーティング・システム・ファイルに格納された読み取り専用のデータ・オブジェクト

23ai 新機能 - Large Object および SecureFiles LOBの基礎知識 - SecureFiles
と BasicFiles (領域管理の違い) Copyright © 2024, Oracle and/or its affiliates 8 SecureFiles (BasicFiles) LOB (CLOB/NCLOB/BLOB) 連続した領域確保を優先 (12.1からDefault) 空きチャンクを探して格納 ⇩ 連続した領域を確保しにくい解放時に断片化し易い【参考情報】 LOB INDEX ツール提供

23ai 新機能 - Large Object および SecureFiles LOBの基礎知識 - LOBデータの格納方式
- インラインとアウトライン Copyright © 2024, Oracle and/or its affiliates 9 SecureFiles (BasicFiles) LOB (CLOB/NCLOB/BLOB) 表領域列1 … 列n LOBデータ(<4000) LOBセグメント(SYS_LOBxxxx) LOBデータセグメント列1 … 列n L L 表領域中に確保される LOBセグメント(オブジェクト) 最大4000/8000 インラインアウトライン ※ SecureFiles において LOB INDEXは重複除外に使用される

23ai 新機能 - Large Object および SecureFiles LOBの基礎知識 - LOB
ロケータとは? LOBインスタンスはロケータと値を持つ LOBロケータは、LOB値が物理的に格納されている場所への参照またはポインタで、LOB値は、LOBに格納されているデータ 10 Copyright © 2024, Oracle and/or its affiliates 表領域列1 … 列n LOBデータ(<4000) LOBセグメント(SYS_LOBxxxx) LOBデータセグメント列1 … 列n L L tab 0, row 0, @0x1ebc tl: 182 fb: --H-FL-- lb: 0x2 cc: 2 col 0: [ 3] c2 03 02 col 1: [174] 00 70 00 01 01 0c 00 80 00 01 00 00 00 01 00 00 00 0d 51 63 00 9a 48 90 00 94 00 00 90 01 68 74 74 70 73 3a 2f 2f 64 6f 63 73 2e 6f 72 61 63 6c 65 2e 63 6f 6d 2f 65 6e 2f 64 61 74 61 62 61 73 65 2f 6f 72 61 63 6c 65 2f 6f 72 61 63 6c 65 2d 64 61 74 61 62 61 73 65 2f 32 33 2f 61 64 6c 6f 62 2f 61 75 74 6f 6d 61 74 69 63 2d 73 65 63 75 72 65 66 69 6c 65 73 2d 73 68 72 69 6e 6b 31 2e 68 74 6d 6c 23 47 55 49 44 2d 35 44 46 34 44 43 36 42 2d 43 38 39 35 2d 34 34 35 30 2d 39 32 35 39 2d 44 42 41 33 30 38 37 35 32 31 42 34 end_of_block_dump 2024-02-29 10:20:26.596 : kjbmbassert [0x1e0c.e] 2024-02-29 10:20:26.596 : kjbmsassert(0x1e0c.e)(to 2)(lvl 4)(domid 0) End dump data blocks tsn: 6 file#: 14 minblk 7692 maxblk 7692 ブロック・ダンプによって確認が可能 (弊社サポートとの連携必須) TEST_BLOB表

参考： LOBデータの格納方式 - インラインとアウトラインの留意点行移行(UPDATE/INSERTで発生) 更新行や新規行がそのブロックに入りきらなくなった際、該当行のデータをすべて新規ブロックに移動行連鎖(INSERTで発生) 行が1ブロックに入りきらない場合に複数ブロックにまたがって格納 11
Copyright © 2024, Oracle and/or its affiliates ←ポインタ行1のデータ行2のデータ行3のデータ行1のデータ行3のデータ行2のデータ行の挿入 UPDATE 行2 行1のデータ行の挿入行1のデータ移行先のポインタ↓ INSERT 行1 行移行と行連鎖について - 性能に与える影響大

LOBのアクセスについて確認 LOB (ラージ・オブジェクト)とは Copyright © 2024, Oracle and/or its affiliates
12 1. SQLを使用したLOBのアクセス (SQLおよびPLSQL組込み関数と暗黙的割当て) • SQL文字列演算子やSQL関数など、SQL VARCHAR2セマンティクスを使用して、CLOBデータ型とNCLOBデータ型にアクセスできる (これらの手法を使用すると、LOBをSQLコードで直接使用し、次のような場合に役立つ) • 比較的小さいサイズ(約100KB以下)のLOBに対して操作を実行する場合 • 既存のPL/SQLアプリケーションに含まれるSQL文字列関数が引き続き機能するようにLONG列からLOBデータ型に移行した後 • PL/SQLでのLOB型から非LOB型などへの割当て (アクセスできるデータ量は最大文字バッファ・サイズ32KBで制限される) 2. LOBのデータ・インタフェース (JavaおよびOCI API) • レガシー・データ型(VARCHAR2、RAW、LONG、LONG RAWなど) の使用で設計されたが (旧LONG API) LOBデータ型でも使用可能 • LOB列に関連付けられているバインド変数を使用して (CLOBに文字データを挿入するか、BLOBにRAWデータを挿入) • アプリケーションで出力バッファを定義して(バッファにCLOBから選択された文字データ、BLOBから選択されたRAWデータが保持) 3. ロケータを使用したLOBの使用 (PL/SQL、JDBC、OCIおよびODP.NETなど) • LOBロケータを使用して、データベースで提供されるLOB APIにLOBロケータを渡すことで、LOB値にアクセスして変更できる。これらの操作では、LOBに対する効率的なピース単位(またはランダム)の読取りおよび書込みがサポートされる SQLを使用したLOBのアクセス、 LOBのデータ・インタフェースは、VARCHAR2またはRAWデータ型の使用方法と同様の多くの操作をLOBロケータを使用せずに実行できる

一時LOBの使用イメージ（PL/SQL）一時LOBインスタンスの作成 • 指定されたLOBデータ型の変数を宣言し、一時LOB作成API (DBMS_LOB.CREATETEMPORARY) に渡す • 一時LOBを生成するSQLまたはPL/SQL組込みファンクション
(SUBSTR関数など) を起動する • 一時LOBをOUTバインド変数または戻り値として戻す PL/SQLストアド・プロシージャまたはファンクションを起動する一時LOBインスタンスの解放 • 継続時間 (セッションまたはコール) が終了するか、または明示的に解放(DBMS_LOB.FREETEMPORARYプロシージャなど)するまで、アプリケーションに存在する • 一時LOBが蓄積されるとシステムが遅くなる可能性があるため、解放することを推奨する LOB (ラージ・オブジェクト) とは Copyright © 2024, Oracle and/or its affiliates 13 DECLARE blob1 BLOB; clob1 CLOB; clob2 CLOB; nclob1 NCLOB; BEGIN -- CREATETEMPORARY を使用して一時LOBを作成し、データを入力する DBMS_LOB.CREATETEMPORARY(blob1,TRUE, DBMS_LOB.SESSION); writeDataToLOB_proc(blob1); -- create a temp LOB using SQL built-in function SELECT substr(ad_sourcetext, 5) INTO clob1 FROM print_media WHERE product_id=1 AND ad_id=1; -- create a temp LOB using a PLSQL built-in function nclob1 := TO_NCLOB(clob1); -- create a temp LOB using a PLSQL procedure. -- Assume foo creates a temp lob and it's parameter is IN/OUT foo(clob2); -- Other APIs CALL_LOB_APIS(blob1, clob1, clob2, nclob1); -- free temp LOBs DBMS_LOB.FREETEMPORARY(blob1); DBMS_LOB.FREETEMPORARY(clob1); DBMS_LOB.FREETEMPORARY(clob2); DBMS_LOB.FREETEMPORARY(nclob1); END; /

一時LOBの暗黙的な作成 LOB (ラージ・オブジェクト) とは Copyright © 2024, Oracle and/or its
affiliates 14 アプリケーションでLOBにSQLセマンティクスまたはPL/SQLセマンティクスを使用すると、多くの一時LOBが暗黙的に作成されます。特に、これらの一時LOBが暗黙的に作成されるのは、次の事項を使用または実行する場合です。 • LOBに対するSQLファンクション • LOBに対するPL/SQL組込み文字ファンクション • VARCHAR2からCLOB、RAWからBLOBへの変数割当て • LONGからLOBへの移行 PL/SQL内の問合せで戻された一時LOBはPL/SQLプログラムが終了時に自動的に解放される（OCI、Javaは解放されない）すべてのオープン・セッションでの一時LOBの使用を監視するためのv$temporary_lobsビューが用意されている SQL> select * from v$temporary_lobs; SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS CON_ID ---------- ---------- ------------ ------------- ---------- 141 2 3 4 0 146 0 0 1 0 148 0 0 1 0 • CACHE_LOBS : CACHEがオンの一時表領域に存在する一時LOB • NOCACHE_LOBS : CACHEがオフの一時表領域に存在する一時LOB • ABSTRACT_LOBS : PGAメモリーに存在する一時LOB

LOBに格納されるOracleデータ型 • 4000バイトを超えるサイズのVARCHAR2データ型またはRAWデータ型 • JSONデータ型 • BINARY XMLまたはCLOBとして格納されるXMLType • LOBとして格納されるVARRAY
• VECTOR型 (23ai～) LOB (ラージ・オブジェクト) とは Copyright © 2024, Oracle and/or its affiliates 15

Oracle Database 23ai 新機能 - Large Object および SecureFiles Copyright
© 2024, Oracle and/or its affiliates 16 2 23ai 新機能 23aiで強化されたポイントを詳細に解説 1 LOBの基礎 LOBデータ型の基礎情報と新機能に関連する技術的な予備知識

23ai 新機能一覧 23ai 新機能 - Large Object および SecureFiles Copyright
© 2024, Oracle and/or its affiliates 17 新機能概要説明 1 インラインLOBの最大サイズを8000バイトに拡大インラインLOBの最大サイズが4000バイトから8000バイトに拡大された 2 読取り専用値LOB (Read-Only Value LOBs) SQL問合せでのLOB値の読取りを最適化する値LOBが追加された 3 LOBのread/writeのパフォーマンスが向上 LOBのread/writeのパフォーマンスが向上 4 LOB重複排除時の機能拡張既存の LOB に対してLOB 重複排除により節約できるスペースを見積り可能に 5 LOB セグメント名の変更物理的な移動(ALTER TABLE ... MOVE) なしにLOBセグメント名を変更できる 6 SecureFiles の自動縮小機能 Autonomous DatabaseのSecureFilesの自動縮小が可能に 7 分散およびシャード環境におけるLOB拡張分散およびシャード環境でインラインLOB、値LOB含むすべての一時LOBが使用可能に 8 OFS における Parallel File System 操作の最適化初期化パラメータ OFS_THREDS による機能拡張 9 OFS における Read-Write 性能の向上 DBMS_FS プロシージャ db_access オプションの追加

インラインLOBの最大サイズが8000まで拡張概要とメリット • 概要 • LOB値はテーブル行の中（インライン）か、テーブル行の外（アウトライン）に格納される • 19c以前のインラインLOBの最大サイズは4000バイトだったが、23aiでは8000バイトに • メリット
• 4000バイト以上8000バイト以下のサイズのLOBに関わる処理の入出力パフォーマンスが向上 • フルテーブルスキャン、レンジスキャン、DMLなどの処理実行でパフォーマンスが改善される • ブロックサイズに対してレコード長が長い場合、行移行、行連鎖が発生しやすくなる点には注意 18 Copyright © 2024, Oracle and/or its affiliates

8000バイトへの設定方法 4000も8000も指定しない場合、インラインLOB最大サイズは4000バイトになる実行手順動画で紹介のある4パターンの動作確認を同じ手順で行った ① インラインLOB最大サイズを4000に設定時に、サイズ4000以下のCLOBデータを格納するとどんな動作をするのか確認 ② インラインLOB最大サイズを4000に設定時に、サイズ4000以上のCLOBデータを格納するとどんな動作をするのか確認 ③ インラインLOB最大サイズを8000に設定時に、サイズ8000以下のCLOBデータを格納するとどんな動作をするのか確認 ④
インラインLOB最大サイズを8000に設定時に、サイズ8000以上のCLOBデータを格納するとどんな動作をするのか確認参考：インラインLOBの最大サイズが8000まで拡張 Copyright © 2024, Oracle and/or its affiliates 19 ENABLE STORAGE IN ROW [{4000|8000}] YouTube リンク https://www.youtube.com/watch?v=P4dYZNNFXWw&t=3128s 動作確認

インラインLOBの最大サイズが8000まで拡張 CLOBに対する検証のサマリ Copyright © 2024, Oracle and/or its affiliates 20
インライン最大サイズデータ挿入サイズブロックダンプの結果より Select insert update 1 4000 512 (*2byte) Inline 格納を確認 LENGTH(1060) - - - 2 4000 2000(*2byte) Outline LENGTH(40)を確認 9.86秒 9.97秒 6.28秒 3 8000 2000(*2byte) Inline 格納を確認 LENGTH(4036) 8.34 0.4秒 0.49秒 4 8000 3900(*2byte) Outline LENGTH(40)を確認 - - - https://docs.oracle.com/cd/F82042_01/nlspg/supporting-multilingual-databases-with-unicode.html#GUID-68A4CB7B-2359-4E1A-B966-EAD9823FD00D 6.5.3 LOBデータ型への複数言語によるドキュメントの格納 LOBは NOCACHEに設定

SQL問合せでのLOB値の読取りを最適化する値LOBが追加された機能概要 • 多くのアプリケーションでは、中規模オブジェクト (数MB程度) を格納するためにLOBを使用し、SQL問合せのコンテキストでLOB値を読み取ることだけを目的とする。そのようなアプリケーションに対して、参照LOB (永続LOBと一時LOB) に比べてパフォーマンスと管理性が向上するように最適化された特殊な種類の読取り専用一時LOBが提供された •
LOBの読取りパフォーマンスを高速化し、一時LOBの蓄積を防ぎ (カーソルに対して次のフェッチが実行されると自動的に解放)、アプリケーション内のLOB管理を簡素化する • SQL問合せに対してLOBロケータではなくLOB値を直接転送することで、ネットワークの通信を少なくする • 32Kより大きなサイズのデータをVARCHAR2(32K)と同じように使用することができる • SQL問合せ、クライアント側プログラム・インタフェース (JDBC、OCI、ODP.NETなど) に存在し、PL/SQLは概念がない • SQLからPL/SQLに渡された値LOBは、読取り専用一時LOBに変換され、保持する変数の持続期間になる • 21c以前のクライアントに送信された値LOBは、読取り専用一時LOBに変換される • DDL(query as value構文)で指定でき、SQL問合せ時だけ影響し、その他のDMLは参照LOBと変わらないメリット • アプリケーションで中規模オブジェクトに大きいVARCHARまたはRAWデータ型としてLOBを使用する場合に、アプリケーションの読取りパフォーマンスが向上し、LOBによるアプリケーションの実装がよりシンプルで管理しやすくする読取り専用の値LOB Copyright © 2024, Oracle and/or its affiliates 29

値LOBの制限事項読取り専用の値LOB (Read-Only Value LOBs) Copyright © 2024, Oracle and/or
its affiliates 33 値LOBを使用する際には、次の制限事項に留意してください • 値LOBは読取り専用LOBであるため、それらに対する変更操作は実行できない • OCILobLocatorAssign()などのロケータ割当て操作は、値LOBではサポートされない • 値LOBは、JDBCで次の操作ではサポートされない • クライアント側の結果キャッシュ • 値LOBは、OCIにおける次の操作ではサポートされない • スクロール可能カーソル • クライアント側の結果キャッシュ • 値LOBを、問合せにおいて同じ操作で参照LOBと組み合せることはできない • select concat(valuelob, referencelob) from agents;

23ai 新機能 - Large Object および SecureFiles LOBの基礎知識 - -
LOBのタイプ (再掲) Copyright © 2024, Oracle and/or its affiliates 34 LOB (CLOB/NCLOB/BLOB) SecureFiles (BasicFiles) 永続一時読込み専用値コード上で一時的に生成・利用 (PGA/temp表領域に確保) ⇨ 永続化可能読込み専用LOBの拡張値に特化したLOBで性能向上および、LOB データの蓄積回避に寄与 ➢ 一時的に使用される領域 ➢ 値のみが必要 ➢ 読込み専用 = 更新されない • ロケータや領域管理軽減 • UNDO情報管理が軽減 • N/W通信コスト

読取り専用の値LOB (Read-Only Value LOBs) パフォーマンス比較 (参照LOB、値LOB、VARCHAR32K)のサマリ Copyright © 2024, Oracle
and/or its affiliates 35 LOBタイプ CACHE/NOCACHE consistent gets physical reads 補足 – データ読込み時の設定関連 1 値LOB デフォルトがCACHEのため物理リードが発生しない 147827 0 ARRAYSIZEおよび ROWPREFETCH が有効 2 値LOB (LOB_VALUE演算子) デフォルトがNOCACHEのため物理リードが発生 3875 144000 3 VARCHAR32K LOBがCACHEのため物理リードが発生しない 183819 0 ARRAYSIZEおよび ROWPREFETCH が有効 4 参照LOB デフォルトがNOCACHEのため物理リードが発生 3862 144000 ARRAYSIZEが効かない LOBPREFETCHの設定でARRAYSIZEが有効

パフォーマンス比較 (参照LOB、値LOB、VARCHAR32K) • 参照LOB (lobprefetch=16384) • デフォルトがNOCACHEのため物理リードが発生 • 値LOB (lobprefetch=0)
• デフォルトがCACHEのため物理リードが発生しない読取り専用の値LOB (Read-Only Value LOBs) Copyright © 2024, Oracle and/or its affiliates 36 SQL> create table t_ref (pk int, c clob) lob(c) query as reference; ... SQL> set autotrace traceonly stat SQL> set arraysize 10 SQL> select pk, substr(c,9999,4000) from t_ref; 36000 rows selected. Elapsed: 00:00:36.94 Statistics ---------------------------------------------------------- ... 3862 consistent gets 144000 physical reads 291457027 bytes sent via SQL*Net to client 2167722 bytes received via SQL*Net from client 3602 SQL*Net roundtrips to/from client ... 36000 rows processed ... 10.07 CPU used by this session (sec) SQL> create table t_val (pk int, c clob) lob(c) query as value; ... SQL> set autotrace traceonly stat SQL> set arraysize 10 SQL> select pk, substr(c,9999,4000) from t_val; 36000 rows selected. Elapsed: 00:00:03.71 Statistics ---------------------------------------------------------- ... 147827 consistent gets 0 physical reads 291457027 bytes sent via SQL*Net to client 90522 bytes received via SQL*Net from client 3602 SQL*Net roundtrips to/from client ... 36000 rows processed ... 1.96 CPU used by this session (sec)

パフォーマンス比較 (参照LOB、値LOB、VARCHAR32K) • VARCHAR2_32K • LOBがCACHEのため物理リードが発生しない • 値LOB (LOB_VALUE演算子) •
デフォルトがNOCACHEのため物理リードが発生読取り専用の値LOB (Read-Only Value LOBs) Copyright © 2024, Oracle and/or its affiliates 37 SQL> create table t_v32k (pk int, c varchar2(32767)); ... SQL> set autotrace traceonly stat SQL> set arraysize 10 SQL> select pk, substr(c,9999,4000) from t_v32k; 36000 rows selected. Elapsed: 00:00:02.99 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 183819 consistent gets 0 physical reads 0 redo size 1078070 bytes sent via SQL*Net to client 90083 bytes received via SQL*Net from client 3601 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 36000 rows processed SQL> set autotrace traceonly stat SQL> set arraysize 10 SQL> select pk, substr(LOB_VALUE(c),9999,4000) from t_ref; 36000 rows selected. Elapsed: 00:00:35.56 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3875 consistent gets 144000 physical reads 0 redo size 291457038 bytes sent via SQL*Net to client 90522 bytes received via SQL*Net from client 3602 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 36000 rows processed

パフォーマンス比較 (参照LOB、値LOB、VARCHAR32K) • 値LOB (arraysize 20 と 40) • 参照LOB
(lobprefetch=0) 読取り専用の値LOB (Read-Only Value LOBs) Copyright © 2024, Oracle and/or its affiliates 38 SQL> set arraysize 20 SQL> select pk, substr(c,9999,4000) from t_val; Elapsed: 00:00:02.76 Statistics ---------------------------------------------------------- ... 291091667 bytes sent via SQL*Net to client 45522 bytes received via SQL*Net from client 1802 SQL*Net roundtrips to/from client SQL> set arraysize 40 SQL> select pk, substr(c,9999,4000) from t_val; Elapsed: 00:00:02.63 Statistics ---------------------------------------------------------- ... 290909047 bytes sent via SQL*Net to client 23022 bytes received via SQL*Net from client 902 SQL*Net roundtrips to/from client SQL> create table t_ref (pk int, c clob) lob(c) query as reference; ... SQL> set autotrace traceonly stat SQL> set arraysize 10 SQL> select pk, substr(c,9999,4000) from t_ref; 36000 rows selected. Elapsed: 00:00:36.35 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3875 consistent gets 144000 physical reads 0 redo size 16381027 bytes sent via SQL*Net to client 16002522 bytes received via SQL*Net from client 39602 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 36000 rows processed

パフォーマンス比較 (参照LOB、値LOB、VARCHAR32K) のまとめ値LOB • ARRAYSIZEが効く (ROWPREFETCHが効く) VARCHAR32K • ARRAYSIZEが効く
(ROWPREFETCHが効く) 参照LOB • ARRAYSIZEが効かない (LOBロケータを渡してから LOB値にアクセスする) • LOBPREFETCHの設定でARRAYSIZEが効く • LOBをプリフェッチすることでロケータと一緒に転送する • “bytes received via SQL*Net from client”が多い • Oracle Net Servicesを介してクライアントから受信したバイト • 値LOBはLOBデータを直接提供しているため行き来する通信が少ない (CPU時間も少ない) • LOB情報とデータ挿入読取り専用の値LOB (Read-Only Value LOBs) Copyright © 2024, Oracle and/or its affiliates 39 SQL> select TABLE_NAME, COLUMN_NAME, SEGMENT_NAME, CACHE, SECUREFILE, VALUE_BASED from user_lobs; TABLE_NAME COLUMN_NAME SEGMENT_NAME CACHE SEC VAL ---------- ----------- ------------------------- ----- --- --- T_REF C SYS_LOB0000097288C00002$$ NO YES NO T_VAL C SYS_LOB0000097291C00002$$ YES YES YES T_V32K C SYS_LOB0000098421C00002$$ YES YES NO declare l_clob clob := rpad('x',1024,'x'); begin -- 64Kにサイズを拡張 for i in 1 .. 6 loop l_clob := l_clob || l_clob; end loop; for i in 1 .. 36000 loop insert into t_ref values (i, l_clob); insert into t_val values (i, l_clob); insert into t_v32k values (i,substr(l_clob,1,32767)); end loop; commit; end; /

LOBの読取りおよび書込みパフォーマンスが向上した機能概要 • 以下の機能強化により、LOBの読取りと書込みのパフォーマンスが向上された • 単一トランザクションに複数の LOB が同時にバッファリングされることで、トランザクション内の混合ワークロード (単一トランザクション内で書込み中にLOBの切り替える)
のパフォーマンスが向上する • 1つのトランザクション内でLOB1に書込んだ後、LOB2に書込み、さらにLOB1に再度書込む場合に、LOB1とLOB2は同時にバッファリングされるため、スループットが向上し、領域の断片化が最小化される • 圧縮されたLOBへの読取りと書込み • 様々な機能拡張 (圧縮されたLOBの追加や圧縮ユニットのキャッシングが高速化されるなど) により、圧縮されたLOBへの読取りと書込みのパフォーマンスが向上 • NOCACHEオプションを使用したLOBへの大きなダイレクト書込み • 入力出力バッファのサイズは、LOBへの入力データ・サイズに基づいて変更され、DBFSおよびOFS上のファイル・システムへの書込みなどの大規模なダイレクト書込みのパフォーマンスが向上するメリット • この機能により、JSONドキュメントベースのアプリケーション、データベース・ファイル・システムから発行される書込みコール、およびストレージ節約のためにデータが圧縮されているLOBのワークロードに対してもSecureFilesのI/Oが高速化される LOBの書込みパフォーマンスの向上 Copyright © 2024, Oracle and/or its affiliates 41

LOBの書込みパフォーマンスの向上性能検証のサマリ Copyright © 2024, Oracle and/or its affiliates 42
検証内容 21c 23ai 総括 1 単一トランザクションに複数LOBの書込みテスト① - (inset/update) physical write IO requests 1040 physical write IO requests 434 約58%削減 2 圧縮LOBへの書込みテスト CPU used by this session 19253 CPU used by this session 7199 約62%削減 3 圧縮LOBの問合せテスト V$MYSTATによるCPU時間(消費)が多い V$MYSTATによるCPU時間(消費)が少ない 4 NOCACHEオプションLOBへのダイレクト書込みテスト physical write IO requests 3271 physical write IO requests 1277 約60%削減 ※ご注意 - 簡易検証の結果であり性能向上を保証するものではありません

Copyright © 2024, Oracle and/or its affiliates 55 重複除外 •
重複LOBデータを自動的に検出 • データのコピーを1つだけ格納することで領域を節約 23ai • 重複除外の有効前に、節約できるスペースの見積もりが可能 • GET_LOB_DEDUPLICATION_RATIO関数を提供 LOB重複排除によって節約されるスペースの見積もり管理領域チャンク LOBセグメント SECUREFILE <Insert> ID LOB DATA A001 ID LOB DATA A002 除外対象

Copyright © 2024, Oracle and/or its affiliates 57 従来 •
既存の LOB セグメントの名前を変更するには、ユーザーはALTER TABLE ... MOVE のような操作を実行 →この操作は名前変更の一環として LOB データを物理的に移動するため、実行速度が遅くなる可能性がある 23ai • ALTER TABLE MODIFYでLOBセグメントの名前変更はメタデータ変更で対応可能 →LOB データの物理的な移動が不要になったので、LOB セグメントの名前変更の操作がシンプルに LOBセグメントの名前変更 ALTER TABLE MODIFY ALTER TABLE MOVE LOB

自動SecureFiles縮小概要とメリット • 概要 • SecureFiles LOBセグメント領域の自動縮小機能。クラウド環境（Autonomous DB）ではデフォルトで有効 • 全てのLOBセグメントをカバー。ユーザー作成のLOBセグメントの他にJSONやDBFSのセグメントもカバー
• メリット • SecureFiles LOBの縮小が定期的に自動実行されるようになったため、LOBの領域管理負荷を削減 • 21cでの手動縮小機能のときにあった、 DDL/DMLへの一時的なブロックによるパフォーマンス影響がほぼなくなり、 UNDOのRETENSIONへの配慮も不要 59 Copyright © 2024, Oracle and/or its affiliates

自動SecureFiles縮小 SecureFiles領域の細分化の問題 SecureFiles (LOBセグメント) 細分化の要因とは？ (あくまでサンプル・イメージです) 60 Copyright © 2024,
Oracle and/or its affiliates 管理領域 *1 管理領域 *1 *2 *3 *4 連続した領域の確保複数の領域に跨る Shrink コマンドによりメタデータを整え、領域確保をより効率的にする LOBデータの削除でチャンクが解放されるが、新たに登録されるデータがそのサイズと異なる場合、チャンクが細分化される (大きな連続領域を持つチャンクが減る) ➡ 領域の拡張による連続領域を持つチャンクの確保 ➡ 細分化された複数チャンクによる分散格納

自動SecureFiles縮小 21c SecureFiles LOBの縮小機能（手動SecureFiles縮小）と課題 • 21cにてオンライン縮小コマンドが実行可能に • 課題 • 縮小中に、該当セグメントを操作するDMLやDDL文を待たせてしまう
• UNDO保持期間を考慮せずに縮小操作を実行するため、ORA-1555の原因になる（※補足参照） • 上記を考慮したタイミングにて手動縮小の実行タイミングを調整する必要がある • DBMS_SPACE.SPACE_USAGEを利用してSecureFiles LOBのセグメントブロックのうち、使用中のブロック、保持期間が失効していないブロックがどのくらいあるかを把握することは可能 61 Copyright © 2024, Oracle and/or its affiliates --特定のLOB列 ALTER TABLE <table_name> MODIFY LOB <lob_column> SHRINK SPACE --テーブル内のLOB全部 ALTER TABLE <table_name> SHRINK SPACE CASCADE （補足） UNDOはUNDOセグメントに格納されないことがある LOBのインライン格納 → UNDOセグメントに格納 LOBのアウトライン格納 → LOBセグメントに格納（★ここが注意が必要） RETENSION句で保持期間を設定

自動SecureFiles縮小 23ai 自動SecureFiles縮小での解決 • 23ai 自動SecureFiles縮小での課題解決 • 縮小中に、DMLとDDL文を待たせてしまう → 使用中のセグメントはスキップしアイドル状態のセグメントのみ縮小対象
• UNDO保持期間を考慮せずに縮小操作を実行するため、ORA-1555の原因になる → UNDO保持期間を考慮した解放ルールを適用 • 上記を考慮したタイミングにて手動縮小の実行タイミングを調整する必要がある → バックグラウンドで縮小を自動で定期実行。一度ですべての空き領域を解放するのではなく、縮小の反復ごとに適度な量のスペースを解放する。トリクル閾値制限により、反復ごとに縮小する量を定義 62 Copyright © 2024, Oracle and/or its affiliates

自動SecureFiles縮小 SecureFile縮小とUndo Retension設定との関係 Undo Retention Flavor DMLクエリなし同時DMLクエリ Undo Retensionなし
ユーザーの観点からは基になるデータが変更されていないため、長時間実行されるクエリはORA- 1551 Snapshots too oldで失敗しません。自動 SecureFiles Shrink は、長時間実行されるクエリの中断をさけられます。 DML クエリがクエリ対象の LOB に触れる場合、クエリはいつでも ORA-1551 Snapshots too oldで失敗する可能性があります。 Undo Retension最小値(N秒) 同上「N 秒」より短い DML クエリは、DML が LOB を変更しても失敗しません。「N 秒」より長く続くクエリはORA-1551 Snapshots too oldで失敗する可能性があります。異なる動作が必要な場合は、「N 秒」を増やす必要があります。 Undo Retension Auto (UNDO_RETENSIONを使用) 同上 UNDO_RETENTION の秒数より短いDMLクエリは失敗しません。 UNDO_RETENTION の秒数より長く続くクエリは失敗する可能性があります Undo Retension最大長時間実行されるクエリはORA-1551 Snapshots too oldで失敗しません。自動SecureFiles縮小はこのロブセグメントをスキップする LOB セグメントが大きくできる場合、DML が LOB を変更しても、ORA-1551 Snapshots too old でクエリが失敗しない。LOB セグメントのスペースが圧迫されている場合、DML が LOB を変更すると、ORA-1551 Snapshots too old でクエリが失敗することがある。クエリが成功しなければならない "N秒 "の期間はありません。つまり、最悪の場合、Undo Retention MaxはUndo Retention MinやUndo Retention Autoよりも厳密ではありません。 63 Copyright © 2024, Oracle and/or its affiliates

自動SecureFiles縮小実行時の縮小サイズの決まり方 64 Copyright © 2024, Oracle and/or its affiliates
• 事前割り当てしきい値と縮小トリクル閾値による計算できまる • A:LOBセグメントサイズ − (使用領域＋期限切れでない領域）/(100 - 事前割り当て閾値) • 事前割り当て閾値の決め方は右下の表 • B: LOBセグメントサイズ − 縮小トリクル閾値 • 縮小トリクル閾値の計算式の記載なし。 1 回の自動縮小反復によって解放されるセグメントごとの最大スペース量 • 縮小トリクル閾値は反復毎に増えていく • アイドル時間が24時間（デフォルト）以上のLOBセグメントが縮小対象 LOBセグメントサイズ使用領域期限切れでない領域セグメントサイズ実際のしきい値（余裕率）最小空き容量 <= 1 GB 10% 100MB <= 10 GB 5% 500MB <= 100 GB 2% 2GB > 100 GB 1% 1GB 事前割り当てしきい値縮小トリクル閾値ＡＢ最終圧縮ターゲットサイズ＝LOBセグメントサイズ - MAX(A, B) 縮小トリクル閾値のスペースを引いて、Ａの領域まで触れなければ、縮小トリクル閾値を採用する、というロジック使用領域期限切れでない領域縮小トリクル閾値ＡＢ縮小縮小

自動SecureFiles縮小自動SecureFiles縮小のタスク 65 Copyright © 2024, Oracle and/or its affiliates
タスクは30分毎にバックグラウンドで実行され、次の手順を実行 1. タスクの進行に合わせて、これまで費やした時間と、縮小コールの平均期間が両方追跡されます。後者を使用して、次回の縮小コールにかかる時間が予測されます。もう1回コールするには残り時間が足りない場合、縮小タスクは終了されます。縮小コールは、 30分間という目標を超えると終了されます 2. 内部カタログ表から次の1回分のLOBセグメントがフェッチされます(これはobjd順になっています)。前回の縮小タスクでの最後の objdが、次回の縮小タスクの開始点として使用されます 3. SecureFiles LOBセグメントの選択基準から基準フィルタが適用されて、縮小タスクの対象外のセグメントを除去除外の選択基準：アイドル時間が24時間（デフォルト）以内、余剰がない、RETENSION MAXが指定されている、現在縮小中、前回失敗 4. 対象になるセグメントが見つかると、縮小タスクで、そのセグメントに対する作業を開始できるようになります。 5. 縮小を開始する前に、縮小目標が計算されます。縮小目標は、事前割当てしきい値、および自動SecureFiles縮小のトリクルしきい値に基づいています 6. 自動SecureFiles縮小により、縮小コマンドが実行されます。ALTER TABLE ... SHRINK SPACEコマンドは、OCIインタフェースを使用して実行されます 7. 次回の縮小のタイムスタンプフィールドのSGA構造が更新されます。このフィールドでは、自動SecureFiles縮小でこのLOBセグメントを再度選択できる最早時間が示されます。領域が正常に解放された場合、このタイムスタンプには現在時間が使用されます。そうでない場合は、縮小に将来の時間が割り当てられます。縮小に成功しなかった場合は、自動SecureFiles縮小で将来の縮小タスクにおいて同じセグメントが選択されないように、ペナルティ時間が課されます

Large Object および SecureFiles の基礎分散LOB (12.2～) • 分散LOBのサポートローカルLOB変数にリモート表からの永続LOBロケータを選択し、SQL文、DBMS_LOBおよびOCILob
APIで使用可能 67 Copyright © 2024, Oracle and/or its affiliates Oracle Globally Distributed Database 対応 declare my_ad varchar(6000) := lpad('b', 6000, 'b'); BEGIN INSERT INTO print_media@remote_site(product_id, ad_id, ad_finaltext) VALUES (10000, 10, my_ad); -- Reset the buffer value my_ad := 'a'; SELECT ad_finaltext INTO my_ad FROM print_media@dbs2 WHERE product_id = 10000; END; / 分散およびシャード環境で、インラインLOB、値LOBおよびすべての一時LOBを使用可能になった

Enhanced LOB Support for Distributed and Sharded Environments 21cまでの分散環境とシャード環境でサポートされていたのは「永続LOB」と「一時LOB」のみだった分散環境とシャード環境で、インラインLOB、値LOBを含む全ての一時LOBを使用できるようになり、パフォーマンス、スケーラビリティおよ
びガベージ・コレクションが向上する --- 問合せおよびDML文に関する注意 • LOBに関連するシャード間の問合せがサポートされる • 複数のシャードを含むDML文はサポートされない • コーディネータからは、単一のシャードを含むDML文がサポートされる • シャードから選択したロケータは、同じシャードにバインドする事が可能 68 Copyright © 2024, Oracle and/or its affiliates remote_site_1 remote_site_n

[参考情報] アプリケーションから Oracle GDD への接続 GDD : Globally Distributed Database
直接ルーティングデータベース・リクエストでシャーディング・キーを指定することで、シャードに直接接続して問合せおよびDMLを処理が可能。プロキシ・ルーティングプロキシ・ルーティングは、シャード・カタログ問合せコーディネータによって処理される。アプリケーションでは、複数のシャードからのデータを必要とする問合せや、シャーディング・キーを指定していない問合せを直接ルーティングができないため、アプリケーションとシャードの間でリクエストをルーティングするためのプロキシが必要になる。 69 Copyright © 2024, Oracle and/or its affiliates A) Direct Routing B) Proxy Routing ①接続リクエスト ②適切なShardにリダイレクト ①SQL実行 ③結果を戻す ②SQLを変換し、適切なShardで実行 https://docs.oracle.com/cd/F82042_01/shard/how-database-requests- are-routed-shards.html#GUID-0E965889-9C00-43EF-88BB-12DA1114E559

Large Object および SecureFiles の基礎 Database File System と Oracle
File Server 71 Copyright © 2024, Oracle and/or its affiliates • Database File System (DBFS) • Oracle File Server (OFS) Large Object SecureFiles DBFS (OFS) ファイル・システムインタフェースオブジェクトを格納した領域をファイル・システムI/Fを介して公開

File Server DBFS 概要データベース表に格納されているファイルおよびディレクトリの最上部に配置されたファイル・システム・インタフェース任意のオペレーティング・システム(OS)プログラムを使用して、データベース内のファイルに透過的なアクセスが可能になる ETL (抽出、変換およびロード)ツールは、データベースにステージング・ファイルを透過的に格納可能で、ファイルベースのアプリケーションは、アプリケーションを変更せずに、最大可用性アーキテクチャ(MAA)などのデータベース機能のメリットを享受できるリレーショナル・データとそれ以外のObjectでも同じトランザクション・レベルを共有できる、Converged Database の根幹とも言える設計思想 (外部LOB : BFILEによるデータ格納との差異が大きい) 72 Copyright © 2024, Oracle and/or its affiliates ファイル・システムインタフェース Linux Kernel FUSE DBFS Client

File Server OFS 概要 Oracle Clientがインストールされていない場合は、OFSを使用して、新しく作成されたDBFSをNFSにマウントし、複数のノードで使用可能にするすべてのファイル・システム・リクエストはOFSバックグラウンド・プロセスのスレッドによって処理される 73 Copyright © 2024, Oracle and/or its affiliates ファイル・システムインタフェース Linux Kernel FUSE DBFS Client

Optimized Performance for Parallel File System Operations 74 Copyright ©
2024, Oracle and/or its affiliates https://docs.oracle.com/en/database/oracle/oracle-database/23/adlob/what-is-database-file-system.html#GUID-B3257D5E-2B38-4D98-A23B-A85E40DABCC8 CDBに格納されるPDB数が増加しても性能を維持できるための拡張として初期化パラメータに OFS_THREDS ▲

Optimized Performance for Parallel File System Operations 初期化パラメータ：OFS_THREADS OFS_THREADSの数を更新して、パラレルに実行されるDBMS_FSリクエストの数を増やすことにより、Oracleデータベース内のOracle ファイル・システムでmake、mount、unmountおよびdestroy操作を実行しているワーカー・スレッドの数が増加する。
⇨ 複数のPDBを含む環境でパラレル・ファイル・システム・リクエストの実行にかかる時間が大幅に短縮される 75 Copyright © 2024, Oracle and/or its affiliates 特性説明パラメータ・タイプ整数デフォルト値 4 変更可能 ALTER SYSTEM PDBで変更可能いいえ値の範囲 2 to 128 基本いいえ Oracle RAC すべてのインスタンスで同じ値を指定する必要がある OFS_THREADSに関する情報は V$OFS_THREADSビューで確認できる

Optimized Read-Write Operations for Database Processes マウントオプションの拡張 (db_access) BEGIN DBMS_FS.MOUNT_ORACLE_FS
( fstype => 'dbfs’, fsname => 'dbfs_fs1’, mount_point => '/oracle/dbfs/testfs’, mount_options => 'default_permissions, allow_other, db_access'); END;/ 77 Copyright © 2024, Oracle and/or its affiliates マウント・オプション使用方法の説明 db_access OFSまたはDBFSによって管理されているファイルにアクセスしたときに、データベース・プロセスによって実行される読取りおよび書込み操作を最適化します。

Our mission is to help people see data in new
ways, discover insights, unlock endless possibilities.

Oracle Technology Night #79 -2 Oracle Database...

Oracle Technology Night #79 -2 Oracle Database 23ai 新機能 - Large Object および SecureFiles

Video

More Decks by oracle4engineer

Other Decks in Technology

Featured

Transcript