皆さんこんにちは、今年も朝晩がだいぶ冷え込んできましたので、紅葉も綺麗になってきましたね。私は今月初めに紅葉を見てリフレッシュしてきました。皆さんもたまにはリフレッシュするのも良いものですよ。
高速にロードするような場合に、SQL*Loaderや外部表のダイレクト・パス・モードはよく使用すると思いますが、何気なく使用しているこの機能にも注意することがありますので、今回はそのSQL*Loaderや外部表についてまとめてみました。後半に、第32回の続きとしてライブラリ・キャッシュの待機についても少し説明していますので、参考にしてください。
1. SQL*Loader/外部表
高速にロードするときに、よく使用するSQL*Loaderや外部表のダイレクト・パス・モードですが、いろいろと拡張されていることや注意することがあるので、以下について少し整理してみました。
- パラレル・ダイレクト・パス・ロード
- SQL*Loaderエクスプレス・モード
- LOGGINGモード
（１）パラレル・ダイレクト・パス・ロード
パラレルでダイレクト・パス・ロード（またはダイレクト・パス・インサート）を行うと、対象となる表がパーティション表か非パーティション表によって、以下の処理方法が行われることを知らない方が多いようなので、それぞれにどのような特徴があって、どのようなときに動作するかなどを知っておくと、性能問題のときに役に立つと思うので少し説明します。
- High Water Mark(HWM) Loading
第18回で説明したHWM以降にデータをロードして、終了時にHWMを移動する動作になります。これは、セグメントに対してシリアルで行う動作になるので、パーティション表で”PQ_DISTRIBUTE(<表>,PARTITION)”ヒントを使用した場合などで使用します。そのため、多くのパーティションを同時にロードするときに有効な方法になります。 - Temp Segment Merge(TSM)Loading
これがSQL*Loaderによって、最初に提供されたパラレル・ダイレクト・ロードの処理方法です。それぞれのパラレル実行プロセスで、TEMPセグメントを作成して行うことで競合をなくしますが、パラレル度が多くエクステント・サイズが大きいと、ロードに必要な領域が多くなるや終了時にマージ（TEMPセグメントのベース・セグメントへの組み入れ）のオーバーヘッドが大きくなります。Oracle Database 11g（Oracle11g）からはシングル・セグメント（非パーティション、パーティション指定）に対するパラレル・ロードで使用します。 - High Water Mark Brokering(HWMB) Loading
パーティション表に対するTSM Loadingでは、多くのTEMPセグメントを使用してしまうので、Oracle11gからパーティション表へのパラレル・ロードとして提供されました（SQL*Loaderによるパラレル・ダイレクト・パス・ロードは、TSM Loadingだけが使用されるので注意してください）。これは、HWM Loadingと同じようにHWM以降にデータをロードしますが、パラレル実行できるようにパーティションごとにHVエンキュー（データ最終位置を同時に更新しないように）を使用して行います。そのため、領域の使用効率が向上しますが、パラレル度が多くエクステント・サイズが小さいと、HVエンキューの競合が発生する場合があります。 - Hybrid TSM/HWMB Loading
シングル・セグメントに対するHWMB Loadingでは、1つのHVエンキューになるので、パラレル度が多いと競合が発生しやすくなります（第2回で説明したRAC環境ではより影響が大きくなります）。そのため、パラレル度が多いときのHVエンキューの競合とTEMPセグメントのオーバーヘッドを改善するために、Oracle Database 12c（Oracle12c）からシングル・セグメントへのパラレル・ロードとして提供されました。これは、RACのインスタンスごとにTEMPセグメントを作成して、インスタンス内ではHVエンキューを使用して行います。そのため、非RAC環境でパラレル度が多いときには注意が必要です。
このように状況によって動作が異なるので、どの処理方法かを判断する必要があり難しいところがあります。そのため、Oracle12c(12.1.0.2)から実行計画の”LOAD AS SELECT”操作に、この処理方法が出力されるようになり、実行計画を見るだけで分かるようになっています。それぞれの処理方法は、以下のように出力されます（この例では、非パーティション表’tt1’とパーティション表’tt2’に対して、それぞれの処理方法が動作するようにロードしています）。
- 非パーティション表／パーティション表（パーティション指定）
シングル・セグメントへのロードは、’HYBRID TSM/HWMB’と出力されます。
- パーティション表
パーティション表への単純なロードは、’HIGH WATER MARK BROKERED’と出力されます。
- パーティション表（PQ_DISTRIBUTEヒント指定）
パーティション表にこのヒントでロードすると、パーティションごとに1つのPQプロセスが行うので’HIGH WATER MARK’と出力されます。
ここで注意する必要があるのが、パーティション表に対するロードをHWMB LoadingとHWM Loadingのどちらで行うかです。HWMB Loadingは、パラレル度を多くすると性能も向上しますが、第28回で説明した表圧縮の圧縮率が低下する場合があります（同じデータ値は同一プロセスで行う方が圧縮率は向上するからです）。HWM Loadingは、逆に圧縮率は向上しますが、性能の問題が発生する場合があります（例えば、ロード対象パーティションが一つの場合はパラレル実行がシングル動作になってしまいます）。そのため、どちらを優先するかを検討する必要があります（性能と圧縮率を低下させたくないときは、HWMB Loadingでパラレル度を多くしないという方法も良いでしょう）。
パーティション表のパラレル・ダイレクト・パス・ロードは、デフォルトがHWMB Loadingになりますが、Exadata Database Machine（Exadata）のHCC圧縮では、圧縮率を向上させるためにHWM Loadingになります。そのため、ExadataでHWMB Loadingにするには、以下のように行う必要があるので注意してください。また、第10回で説明したEXCHANGE PARTITIONを使用したロードを行うと、常にHybrid TSM/HWMB Loadingで行うことができます。
（２）SQL*Loaderエクスプレス・モード
表にデータ・ロードするには、SQL*Loaderや外部表を使用しますが、単純なロードでも指定するのが大変なところがありました（SQL*Loader制御ファイルや外部表は、複雑で設定する内容も多いので、慣れていても作成するのが大変なものです）。そのため、Oracle12cから簡単にロードできるように、SQL*Loaderエクスプレス・モードが提供されたので、簡単に紹介しようと思います。
このエクスプレス・モードでは、SQL*Loader制御ファイルまたは外部表を作成する必要がないように、入力フィールドの順序とデータ型は’ALL_TAB_COLUMNS’ビューにある表の列定義を使用して判断します。そして、その他の設定については、デフォルト値が使用されますが、コマンドライン・パラメータで上書きすることもできるので、簡単に行うことや指定を変更して行うことも可能になっています。そのため、表名の指定だけで、ダイレクト・パス・ロードを実行することもできます（このとき、データ・ファイルは、カレント・ディレクトリ上の”<表名>.dat”になります）。ただし、使用できるのは以下の場合だけになるので注意してください。
- 表の列が単純なデータ型（文字、数値、日時など）
- 入力データ・ファイルがデリミタ付き文字データ（カンマ、改行）
例えば、以下のように表名だけを指定してロードを行うことができます。
このとき「確認するログ・ファイル」に出力される”<表名>.log”に、実行されたSQL（外部表のCREATE TABLE文とINSERT文）が出力されます。EXTERNAL_TABLEパラメータに’GENERATE_ONLY’を指定すると、このファイルの作成だけを行って、外部表の定義を編集して使用するなども簡単に行うことができます（これだけでも嬉しい機能だと思います）。デフォルトは、’EXECUTE’で外部表を使用してロードするになります。
このように、非常に簡単になっているので、これからはこれを使用することをお薦めします。
（３）LOGGINGモード
第15回でNOLOGGINGモードがパフォーマンスに有効と説明しましたが、高可用性（災害対策など）のためには使用できない場合もあります。ただし、そのような場合でも、性能の低下を最小限にすることが重要になるので、LOGGINGモードについても少し説明しておきます。
LOGGINGモードでは、ダイレクト・パス・モードのときブロック・イメージをRedoログに出力するので、Redoログが増えることで性能が低下します。そのため、性能低下を最小限にするには、その増加するサイズをできるだけ少なくする必要がありますが、それは表構造やロードするデータ量に関係することになるので簡単ではありません（チューニングできるものではありません）。
そこで、表圧縮を使用するようにします。知らない方も多いかもしれませんが、表圧縮ではデータ・ブロックだけでなく、Redoログも削減することが可能です（ブロック・イメージを出力するので、そのブロックが少なくなればRedoログ・サイズも少なくできるからです）。そのため、圧縮される割合で性能低下も少なくすることができます。また、表圧縮は、表領域暗号化に対しても性能を向上することができます。これも知っているとそのようなときに役に立つと思い載せておきました。
2. ライブラリ・キャッシュの待機
ライブラリ・キャッシュの待機は、第32回で説明したように、ハード・パースが多いときに（共有されないカーソルが多いや共有プール領域の不足などで）発生するのが一般的ですが、ライブラリ・キャッシュ・オブジェクトの競合により発生する場合もあるので、そのようなオブジェクトの競合について説明します。
（１）ライブラリ・キャッシュ・オブジェクトの競合
ライブラリ・キャッシュ・オブジェクトには、共有カーソル（SQL、無名PL/SQLブロック）とストアド・オブジェクト（表、ビュー、PL/SQLパッケージ、PL/SQLファンクションなど）がありますが、これで競合することはそんなにありません。ただし、同じオブジェクトを多くのセッションで実行すると発生する場合があります。その代表的なものが以下の待機になります。
- PL/SQLパッケージの’library cache: mutex X’待機
実行時にライブラリ・キャッシュのハッシュ・バケットを保護するために獲得しますが、多くのセッションで同じPL/SQLパッケージを実行すると発生する場合があります（プロシージャやファンクションはパッケージにまとめますが、一つのパッケージに登録するのが多い、パッケージ内に頻繁に実行されるものがあるときなどに、このような競合になります）。 - 共有カーソルの’cursor: pin S’待機
第32回で説明したように、存在しているカーソルを参照するために獲得しますが、同じSQLを多くのセッションで実行すると発生する場合があります。改善するにはオブジェクト名を変えるために、SQLにコメントなどを入れます。
そのため、AWRなどのTop 5 Timed Events（最近ではTop 10 Foreground Events by Total Wait Time)に、このような待機が多いときには注意してください。そのような場合には、改善するために以下のようなことを行う必要があります。
- 複数のオブジェクトに分割する（手動で行う）
- DBMS_SHARED_POOL.MARKHOTを使用する（自動で行う）
（２）DBMS_SHARED_POOL.MARKHOTプロシージャ
DBMS_SHARED_POOL.MARKHOTプロシージャは、ライブラリ・キャッシュ・オブジェクトの競合が多発しているのを改善するために、Oracle11gR2(11.2.0.2)から提供された機能になります。
このようなライブラリ・キャッシュ・オブジェクトは、ハッシュ・バケットで管理されているので、オブジェクト名を変える（または別のオブジェクト名を作成して分割する）などを行うことで競合を回避できますが、それはプログラムに影響するので簡単ではありませんでした。そのため、ホット・オブジェクトとして登録することで、内部的にコピーを作成して競合を回避するようにします（これはCPUコア数まで作成します）。
ホット・オブジェクトは、以下のようにオブジェクト名を指定して行います。共有カーソルのようにオブジェクト名がSQLで指定できないために、ハッシュ値（V$DB_OBJECT_CACHE.FULL_HASH_VALUEなどの16バイトのハッシュ値）でも指定できるようになっています。<global>は、RAC環境ですべてのインスタンスに設定するかを指定します（デフォルトはTRUEで、すべてのインスタンスに設定するになります）。
ここからは、PL/SQLパッケージの待機に対する設定例を使用して説明していきます。
①.まずは、待機イベントの上位にライブラリ・キャッシュ・オブジェクトの競合があるか確認します。
AWRのTop 5 Timed Eventsなどを確認します（この例では’library cache: mutex X’が多いとします）。
②.次に、その待機の時間が多いライブラリ・キャッシュ・オブジェクトを調べます。
以下のSQLのように、ビュー’V$ACTIVE_SESSION_HISTORY’と’V$DB_OBJECT_CACHE’を使用して、待機が多い（CNTの値が多い）オブジェクトを調べます（V$DB_OBJECT_CACHEのDISTINCTは、親カーソルと子カーソルを一つにするために行っています）。
③.そして、待機が多いオブジェクトをホット・オブジェクトに設定します。
以下のように待機の多いオブジェクトに設定します。ただし、DBMS_SHARED_POOLパッケージを使用するには、DBMSPOOL.SQLスクリプト（$ORACLE_HOME/rdbms/admin/dbmspool.sql）を実行して、パッケージを作成する必要があるので注意してください。
これを設定したオブジェクトは、DBMS_SHARED_POOL.UNMARKHOTプロシージャを実行するまで、ホット・オブジェクトとして実行されます。ただし、インスタンスの再起動時には、再設定する必要があるので注意してください。
Oracle12cからビュー’V$DB_OBJECT_CACHE’に列’PROPERTY’が追加されて、ホット・オブジェクトを確認することができるので、より使いやすいようになっています。以下のSQLのように、ホット・オブジェクトは列’PROPERTY’に’HOT’と出力されます（コピーが作成されたオブジェクトは、列’PROPERTY’が’HOTCOPY’になります）。
3. おわりに
今回はSQL*Loader/外部表とライブラリ・キャッシュの待機について説明しましたが、少しは参考になりましたでしょうか。今回で第50回を迎えることができましたので、読んでいただいている皆様に感謝いたします。これからも頑張りますのでよろしくお願いします。質問をお待ちしています（このような内容を取り上げて欲しいというご要望などもお待ちしております）。
それでは、次回まで、ごきげんよう。