掲載元 January/February 2012

テクノロジー： Ask Tom

履歴、基礎、ネットワーク・パフォーマンスについて

オラクルの技術者が、Oracle Total Recallに関する質問に答え、ヒントの正しい使用法を示し、ネットワークのパフォーマンスについて話します。

私は最近、Oracle Databaseのセキュリティに関するオンラインWebセミナーを開催しました。 そのセミナーの最後に、参加者からの質問を受け付けました。 参加者が多く、Oracle Total Recallに関する質問が多かったため、セミナーが終わったときには、まだ回答されていない質問が多く残りました。

それに回答しましょう。 未回答の質問は私のところに転送されているので、このコラムの中で解決しようと思います。 その前に、まずはOracle Total Recallの目的と動作について簡単に説明します。

Oracle Total Recallの動作

Oracle Total Recallの目的は、長期間さかのぼるフラッシュバック問合せ機能を提供することです。数日前、数週間前、数か月前、あるいは数年前にさかのぼるフラッシュバック問合せも実行できます。 構文的に見れば、Oracle Total Recallの問合せと標準的なフラッシュバック問合せに違いはないように見えます。 Oracle Total Recallでも、FROMリスト内でAS OFとVERSIONS BETWEENを使用します。 しかし、その内部的な動作は大きく異なります。

フラッシュバック問合せがOracle9i Databaseで最初に導入されたとき、独自のカスタム監査証跡をこの新しいデータベース機能で置き換えることができるかという質問が多くの開発者から寄せられました。 そのような開発者は、:OLDレコードを監査証跡内に保存するようなカスタム・トリガーを開発しています。このような方法によって､過去の任意の時点で表に含まれていたデータを再構成できます。 しかし、この実装には2重の問題があります。 第1に、特定の時点でのデータを取得する問合せのコーディングは容易ではなく、正しいバージョンの1行を取得するために、現在の表と履歴表とのUNION ALLと、面倒で複雑なWHERE句が必要です。 第2に、このようなカスタム・トリガーを追加すると、必然的にUPDATEトランザクションとDELETEトランザクションのデータ量が少なくとも2倍になります。そのため、エンド・ユーザーに対する応答時間が増加します。 これらの理由によって、開発者は、追加の負荷が少なく実装しやすい方法を探す必要に迫られていました。

“独自のカスタム監査証跡の代わりにフラッシュバック問合せを使用できるか”という質問に対する答えは、単純に“使用できません”です。 この回答の技術的な理由は複数あります。 第1に、フラッシュバック問合せはUNDOに基づいているため、5時間前のデータに対するフラッシュバック問合せを実行するには、過去5時間に生成されたすべてのUNDOをオンラインで取得できるようにしておく必要があります。 同様に、2日前のデータに対するフラッシュバック問合せを実行するには、過去2日間に生成されたすべてのUNDOをオンラインで取得できるようにしておく必要があります。 UNDO表領域が2日分のUNDOを格納できるように構成されたシステムは、私はあまり多く知りません。5時間でも珍しいでしょう。数か月、数年分のUNDOは言うまでもありません。 UNDO表領域が巨大になってしまいます。

フラッシュバック問合せがカスタム監査証跡の代替にならないもう1つの理由は、フラッシュバック問合せの理論的な制限が5日間であることです。そのため、UNDOに基づくフラッシュバック問合せの使用は、データベースの過去5日分のアップタイムに制限されます。 非常に長期間にわたるUNDOを保管したとしても、6日以上前のデータに対するフラッシュバック問合せを実行することはできません。

最後の理由として、UNDOに基づくフラッシュバック問合せはスケーラビリティに欠けます。 フラッシュバック問合せが長い時間さかのぼるほど、実行する必要のある処理が多くなるため、実行時間が長くなります。 1時間前のデータに対してフラッシュバック問合せを実行する場合、問合せの実行中にヒットしたすべてのブロックをロールバックして、1時間前の状態に戻す必要があります。 たとえば、特定のブロックが過去1時間で100回の異なるトランザクションによって変更されたとすると、100回のロールバック操作を実行する必要があります。 2時間前の同じデータを問い合わせたとすると、そのブロックに対してさらに多くの変更をロールバックする必要があるでしょう。つまり、1時間前のフラッシュバック問合せを実行するよりも、2時間前のフラッシュバック問合せを実行する方が、時間がかかります。 フラッシュバック問合せで長い時間さかのぼるほど、ロールバックすべき変更が多くなるため、問合せの実行時間が長くなるでしょう。

ここで登場するのが、Oracle Database 11gで利用できるようになったOracle Total Recallです。 これは、フラッシュバック問合せに関連するパフォーマンスの問題もUNDO記憶域とスケーラビリティの問題も解決します。 Oracle Total Recallを使用する場合、クライアント・トランザクションは影響を受けません。 Oracle Total Recallによる処理は、フラッシュバック・データ・アーカイバ（FBDA）と呼ばれる新しいデータベース・プロセスを使用して、バックグラウンドで実行されます。 クライアント・トランザクションは単純に、変更を実行し、（通常どおり）その変更に対するUNDOを生成して、コミットします。 クライアント・トランザクションがコミットされるとまもなく、FBDAプロセスが、生成されたUNDOをマイニングして、フラッシュバック・データ・アーカイブ内の表に対して生成されたUNDOを取り出します。 フラッシュバック・データ・アーカイブによって長期間さかのぼる問い合わせを可能にする表は、DBAが指定します。FBDAプロセスは、それらの表に対して生成されたUNDOを探します。

FBDAプロセスによって、“関心のある”UNDO、つまりフラッシュバック・データ・アーカイブ内の表に対するUNDOが検出されると、その場で変更がロールバックされ、UPDATE操作やDELETE操作の前の行が再構成されます。 この再構成された行が、フラッシュバック・データ・アーカイブ表（いわば履歴表）に挿入されます。したがって、クライアント・トランザクションの応答時間は影響を受けません。 これによって、パフォーマンスの問題だけでなくスケーラビリティの問題も解決されます。 フラッシュバック・データ・アーカイブ内のデータに対して6か月前のフラッシュバック問合せを実行する場合、過去6か月間に行われたすべての変更をロールバックする必要はありません。FBDAプロセスによってすべてのロールバックがすでに実行されているからです。 6か月前に用意ができていた行を問い合わせるだけでよく、課題が大幅に単純になります。また、6か月間前でも6年前でも、データの問合せに必要な時間は同じです。 Oracle Total Recallは、必要な記憶域の削減にも役立ちます。 UNDOに基づくフラッシュバック問合せでは、データベース内のすべての表に対して生成されたすべてのUNDOを保存する必要があります。 一方、Oracle Total Recallで使用されるフラッシュバック・データ・アーカイブでは、データベース全体ではなく、必要な表の過去の行のみを保存します。

以上の背景知識をふまえて、Webセミナーの中で受け取った質問を見ていきましょう。 Oracle Total Recall製品ページ（）もぜひお読みください。

Oracle Total Recall： 質問

Oracle Total Recallを有効にすることで、どのようなパフォーマンス上の影響がありますか。

Oracle Total Recallは、追加の負荷をできる限り小さくするように設計されています。 Oracle Total Recallによって実行される処理の大部分は、トランザクションがコミットされた後に、バックグラウンドで非同期的に実行されます。 そのため、このバックグラウンド処理に対処する余力がデータベース・サーバーにあるとすれば、既存のアプリケーションに対する影響は無視できます。

そうではなくて、現在のデータベース・サーバーがすでに100%の利用率で稼働しているとすれば、もちろん影響がありますが、ハードウェアを適切にサイジングすることで影響を軽減できます。 FBDAプロセス向けにCPUを増強し、I/O性能を増強する必要があるでしょう（UNDOの読取りは通常バッファ・キャッシュから行いますが、システム・レベルでの全体的なUNDOやシステム・レベルでの全体的なREDOがより多く生成されるようになります。また、言うまでもなく、フラッシュバック・データ・アーカイブに対する書込みが追加されます）。さらに、これらすべてをスムーズに実行するために、メモリを少し追加する必要があるかもしれません。

常に言えることですが、このような構成変更を本番環境に適用する前には、ベンチマーキングを実施することをお勧めします。 ベンチマーキングは、独自のツールでも実行できますし、Oracle Real Application Testing（oracle.com/jp/products/database/options/real-application-testing）などの製品を使用して実行することもできます。

ある表をOracle Total Recallに対応するように設定した場合、その表に対するDMLやDDLは影響を受けますか。

この質問にはバージョン固有の部分があり、また、データ操作言語（DML）とデータ定義言語（DDL）という2つの要素が含まれています。

DMLについて短く答えるなら、フラッシュバック問合せ構文を使用して長期間さかのぼった時点の表を問い合わせることができるようになるほかは、DML操作への影響はありません。 SELECT文が影響を受けますが、良い影響だけです。 そのほかのDML操作は影響を受けません。

DDLについては、回答はバージョンによって異なります。 Oracle Database 11g Release 1でのOracle Total Recallの最初のリリースでは、DDLが大きく制限されていました。 Oracle Total Recallが使用する表に対して許可されたDDLは、列を追加するためのALTER文だけでした。 列の削除や表の切捨てなどは実行できません。 つまり、UNDOを生成しない操作はほぼすべて、表に対して実行できませんでした。

これらの制限は、Oracle Database 11g Release 2で取り除かれました。 このリリースでは、Oracle Total Recallが使用する表に対してほとんどのDDLがネイティブにサポートされており、直接サポートされていないDDLであっても実行可能です。 パーティション化された表のパーティションをALTER文によって交換するなど、サポートされていない操作を実行する必要がある場合、まずDBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBAプロシージャを呼び出し、目的の操作を実行したのちDBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBAを呼び出すことで、変更した表をその履歴と再結合できます。

まとめると、Oracle Total RecallではDMLの制約はなく、Oracle Database 11g Release 2ではDDLの制約も事実上ありません。

長期間さかのぼるUNDOベース・フラッシュバック問合せを実行しようとすれば、データベース全体について、その期間に生成されたすべてのUNDOを、オンラインで取得できるようにしておく必要があると言いました。 UNDOは、すぐに数TBになってしまいます。

Oracle Total Recallの処理では、必要となる全体的な記憶域が最小限に抑えられます。この機能はデータベース全体ではなく表ごとに有効化でき、しかもデータが圧縮形式で保管されるためです。 何よりも重要なことは、データベース内のすべての表ではなく、必要な表を格納するのに十分な記憶域のみが必要だということです。

どの程度の領域が必要かという質問に対する回答は、状況によるというものです。 主に挿入を行い、削除や更新をほとんど行わない表であれば、Oracle Total Recallで必要となる記憶域は最小限です。記録する必要があるのは、更新または削除された行の操作前イメージのみだからです。 対照的に、1行だけの表が1日あたり1,000回更新される場合、その表に対するOracle Total Recallの記憶域は、元の表の何倍にもなります。表自体は小さいままでも、アーカイブ履歴には毎日1,000行が入力されるからです。

したがって、長期的に必要となる記憶域を算出するためには、アーカイブ内の表に対する更新や削除の頻度を把握する必要があります。

Oracle Total Recallは、セキュリティ目的のほかに、アプリケーションでも使用できますか。 たとえば、“有効日処理”を使用する代わりに、Oracle Total Recallを使用して履歴データのビューを取得できますか。

もちろんできます。 これは確かに、Oracle Total Recallのひとつの使い方です。 アプリケーションを変更することなく、表で有効日処理を行わずにOracle Total Recallを有効化できます。これは、データ消去の問題の解決にも役立ちます。

通常、有効日処理のような処理を使用しているアプリケーションでは、特定の期間のデータを保持し、後でそのデータを消去する必要があります。 この消去は多くの場合、DELETE文で実行しますが、これは、古いデータの消去方法としては、おそらくもっとも時間がかかりもっともリソースを消費する方法でしょう。 さらに、DELETEを実行すると、表の再編成と索引の再構築を行って、解放された領域を再利用したいと思うかもしれません。 Oracle Total Recallでは、DELETEを使用せず、リソースを消費することもなく、古い情報を簡単に消去できます。

Oracle Total Recallを使用してフラッシュバック・データ・アーカイブを作成する際に、保存期間を指定します。これにより、アーカイブ内の表に対してどれだけさかのぼってフラッシュバック問合せを行うかをOracle Total Recallに伝えます。 Oracle Total Recallはパーティション化された表を設定します（パーティション化オプションがなくても、心配はいりません。Oracle Total Recallに付属しています）。パーティションが古くなって必要がなくなると、単純にパーティションが削除されます。 パーティションの削除では、DELETEと異なり、REDOやUNDOが生成されません。また、フラッシュバック・データ・アーカイブに対する索引があっても、DDLによって管理されます（たとえば、表パーティションを削除すると、対応するローカル索引パーティションも削除されます）。

Oracle Total Recallが割当てられた領域を超過した場合、データベースは停止しますか。

データベースは停止しませんが、影響を受ける表でのDMLの実行が妨げられる可能性があります。 たとえば、ある表のフラッシュバック・データ・アーカイブを含む表領域が一杯になり、FBDAプロセスが変更を記録できなくなると、その表を変更しようとするアプリケーションではORA-55617 “Flashback Archive <名前> has run out of space and tracking on <名前> is suspended”というエラー・メッセージが発生します。 そのため、停止範囲は、影響を受けるフラッシュバック・データ・アーカイブ内の表のみに限られます。

ほかにもいくつかの質問がありましたが、それらはすべて、以上の質問に似たものでした。 このWebキャストはbit.ly/omagdbsecurityでいつでも再生できます。

基本に戻る

われわれのアプリケーションでは、索引付けされた表とチューニングされた問合せを使用していますが、一部の表で索引が使用されません。

rec_noフィールドには、9999から0001までの値が降順で格納されています。

この表から1行選択すると、索引が作成されていないかのような、想定外の結果が得られます。 “想定外の結果”というのは、

という問合せが、instance1で正しく動作するとinstance2では誤った情報を返し、あるいはその逆になります。

ほかでもなく、この問合せに問題があります。 ヒントが遵守されることを前提にしてはいけません。 ヒントはヒントであり、指示ではありません。 索引が何らかの理由（名前が異なる、使用できないなどのあらゆる理由）で利用できない場合、問合せでは索引が単純に無視されます。

解決策は、以下に示すような方法で問合せをコーディングすることです。INDEX_ASCヒントは使用しないことをお勧めします。

この問合せで求めている内容は、SYSTEM_ID、CUST_ID、REC_NOに関する条件に一致する最初のレコードを検索することです （条件のROWNUM<2の部分がORDER BYの前に記述されています）。 そのため、条件に一致する1つのレコードを、それがどんなレコードであっても取得して、さらにそれをソートしています。

両方のインスタンスで正しい答えが得られています。 このSQLは、ほとんどどんな結果でも返す可能性があります。なぜなら、指示の内容が、“この条件が真になる最初のレコードを見つけ、それをソートする”ということだからです。

そうではなく、“これに一致するレコードをソートし、最初のものを返す”と指示する必要があります。 そのためには、正しい質問を投げかける必要があります。これが絶対に必要です。

正しい質問とは、次のようなものです。

select * 
  from 
(
select /*+ first_rows(1) */ * 
  from records 
 where system_id='123' 
   and cust_id='3456218791' 
   and rec_no>'0000'
 order by system_id
          asc,cust_id asc,
          rec_no asc
)
 where rownum < 2;

この問い合わせは、条件に一致するレコードを検索し、それをソートして、最初のものを返すように指示しています。 データベースでは、それほど多くの処理は必要にならないでしょう。データベースは、ソートしたセットの最初の行のみが求められていることがわかるので、自動的に（ヒントがなくても）索引を使用するでしょう。 FIRST_ROWS(1)ヒントは、INDEX_ASCヒントよりもはるかに適切な選択肢です。目的をオプティマイザに伝え、その目的を達成する最速の方法をオプティマイザが見つけます。 索引が利用できない場合は、行をできるだけ速く検索するため、TOP n問合せ最適化が使用されるでしょう。一方、索引が利用できる場合は、データベースは索引を使用するでしょう。

いずれにせよ、正しい質問を投げかければ、いつでも正しい答えが得られます。

Oracle OpenWorld： 1つのこと

Oracle OpenWorld 2011に参加できなかったのですが、あなたが“5つのこと”というプレゼンテーションを行ったと聞きました。 そのいくつかを紹介していただけますか。 

私はOracle OpenWorld 2011で“. . . .についてあなたが知らないだろう5つのこと”というプレゼンテーションを2回実施しました。 ひとつはSQLについて、もうひとつはPL/SQLについてです。 ここでは、SQLについてのプレゼンテーションから1つ紹介します。

Oracle Net Servicesの圧縮：何年か前から、オラクルがネットワーク上のデータをひっそりと圧縮していることをご存じでしょうか。 Oracle Net Servicesでは、サーバーからクライアントへのデータ・ストリームに前行からの変化（前行との差分）のみを乗せることで、データ・ストリーム内のデータを自動的に圧縮しています。 そのため、同じ値を多く含む2つの行を返す場合、2行目で送られるデータ量はわずかで、1行目との差分のみです。 この種の圧縮は、データベースのデータに対して、特にORDER BY句を使用する場合に、驚くほど効果があります。

この圧縮を示す例を見てみましょう。 まず、テスト用の表を作成します。

SQL> create table t
  2  as
  3  select *
  4    from all_objects;
Table created.

SQL> begin
  2    dbms_stats.gather_table_stats
  3    ( user, 'T' );
  4  end;
  5  /
PL/SQL procedure successfully completed.

次に、SQL*PlusでAUTOTRACE TRACEONLY STATISTICSを使用してデータを取得し、転送されるバイト数を計測します（リスト1を参照）。

コード・リスト1：Oracle Net Servicesの圧縮のテスト—対照実験

SQL> select * from t;
72228 rows selected.

Statistics
————————————————————————————————————————————————————
       5794  consistent gets
    8015033  bytes sent via SQL*Net to client
      53385  bytes received via SQL*Net from client
       4817  SQL*Net roundtrips to/from client
      72228  rows processed

結果セットを転送するために約8MBのネットワーク・トラフィックが発生しており、この問合せで5,794の論理I/O（consistent gets）が実行されています。 この問合せを少し変更してORDER BY句を追加すると、これらの数値が大幅に変化します。 ここではORDER BY TIMESTAMPを使用します。この列が非常に大きく（19バイト）、NOT NULLであり、表の行数と比較して値の重複がほとんどないためです。 この結果はリスト2のとおりです。

コード・リスト2：Oracle Net Servicesの圧縮のテスト—ソートあり

SQL> select * from t order by timestamp;
72228 rows selected.

Statistics
—————————————————————————————————————————————————————
       1031  consistent gets
    3427630  bytes sent via SQL*Net to client
      53385  bytes received via SQL*Net from client
       4817  SQL*Net roundtrips to/from client
      72228  rows processed

これは大きな変化です。 転送されるデータが8MBから3.4MBに減少しました。この減少はすべて、Oracle*Netの圧縮によるものです。 TIMESTAMPの値には繰り返しが多いため、何度も転送する必要がありません。 さらに、consistent getsが5,794から1,031に減少している点もお気づきでしょう。 これもORDER BYによる副作用のひとつです。 1回目の問合せの実行でデータが表から直接読み取られますが、ソートは実行されないため、データを一時領域に書き込む必要はありません。 したがって、データをフェッチするたびに（SQL*Plusのデフォルトの配列フェッチ・サイズは一度に15行）、バッファ・キャッシュからブロックを取得して、そのブロックから15行取得する必要があります。 この表には1ブロックあたり約73レコードが保存されているため、ORDER BYを使用しない場合、最初のブロックの73行すべてを取得するためには、キャッシュから一度に15行ずつ、5回ほど読取る必要があります。 データをソートする場合は、すべての行を読取り、一時メモリかディスク上の一時領域でそれらをソートする必要があります。 一度に15行取得するときには、バッファ・キャッシュからではなく、一時領域から読み取る必要があります。

ここからどんな結論が得られるでしょうか。 ORDER BY文をすべてのSQLに適用して、転送されるデータを削減し、論理I/Oを減少させるべきでしょうか。 もちろん違います。 データをソートするコストは、ほぼ確実に、圧縮や論理I/O数の削減によって得られるすべての効果を上回るでしょう。 この方法では（ソートのために）処理が大幅に増えるでしょう。 ここから得られる結論は、アプリケーションの要件を満たす必要性からデータをソートしている場合に、おそらくネットワーク上のデータ量の削減やバッファ・キャッシュ競合の減少という形でメリットを享受できるということです。

では、さらに一歩進んで、データをさらにソートしたらどうなるでしょうか。 TIMESTAMPには繰り返しが多く、OBJECT_TYPEとOWNERも同様である（たとえばSYSが多くのTABLESを所有している）ことが分かっているため、リスト3のように、転送されるデータがさらに減少すると見込まれます。

コード・リスト3：Oracle Net Servicesの圧縮のテスト—さらにソート

SQL> select * 
       from t
      order by timestamp, 
        object_type, owner;
72228 rows selected.

Statistics
————————————————————————————————————————————————————
       1031  consistent gets
    3280011  bytes sent via SQL*Net to client
      53385  bytes received via SQL*Net from client
       4817  SQL*Net roundtrips to/from client
      72228  rows processed

転送されるデータは3.4MBから3.2MBに減少しました。 論理I/Oに変化はありません。これは当然です。 問合せでは表のすべてのブロックを少なくとも1回読み取る必要があるため、1,031はI/Oの最小値です。しかし、転送されるデータは少し減少しています。

ここで、“一度に15行より多く取得した場合はどうなるか”という疑問がわくかもしれません。 論理的には、配列サイズは転送されるデータの量に影響すると考えられます。 一度により多くの行を送信すれば、繰り返すデータが増加するため、圧縮率が高くなるでしょう。 実際､これをテストで証明できます。

表1は、転送されたデータの量（MB単位）、転送されたデータの最初の問合せを基準とした割合（パーセント）、論理I/Oの回数を示したものです。 “ソートなし”列はORDER BYを使用せずに実行した問合せを、“ソートあり”列はORDER BY TIMESTAMPの実行を、“さらにソート”列は3列のソートを表します。 列見出しの数値は配列サイズを表します。

表1：Oracle*Netの圧縮に対する影響の比較（15行フェッチと100行フェッチ）

これから分かるように、配列サイズを15から100に大きくすることで、転送されるデータの量にかなりの影響が出ています。 最初のソートなしの問合せにも、他の問合せと同じだけの効果がありました。つまり、すべてが7ポイント低下しました。 しかし、“100が15よりも良いのであれば、1,000はもっと良いだろう”と思って喜びすぎないようにしてください。そうではありません。 ここで、限界収穫逓減の法則に突き当たります。 配列サイズ1,000でテストした結果を表2に示します。

表2： Oracle*Netの圧縮に対する影響の比較（1,000行フェッチ）

これから分かるように、1,000と100ではほとんど変化がありません。 転送されるデータが約1ポイント減少していますが、効果はほとんどありません。 また、1,000行のフェッチをパッケージ化するために必要なクライアントとサーバーのメモリ容量は、100行フェッチと比較して桁違いに大きくなります。 私の経験では、一度に100行から500行では一般に大きな効果があります。これまでのところ、100が適切なようです。 アプリケーションで使用する配列サイズを構成可能なパラメータとして、さまざまな値を試せるようにすることをお勧めします。

そのほかの“あなたが知らないだろう5つのこと”プレゼンテーションのほかの項目については、今後のコラムで取り上げます。