掲載元 November/December 2012

テクノロジー： Ask Tom

外部表の問合せ、データの一貫性、NULLについて

オラクルの技術者が、オペレーティング・システムへの問合せ、手動ロック、正しいNULLの使用法を説明します。

いくつかの顧客のデータベースでは、自動拡張機能を有効にしたデータファイルを使用しており、次のように、多くのデータファイルが同じファイル・システムを共有しています。

tablespace A, datafiles /u01/oradata/ts_A_file01.dbf autoextend unlimited
tablespace B, datafiles /u01/oradata/ts_B_file01.dbf autoextend unlimited

拡張では、すべてのデータファイルが現在のサイズの少なくとも20%ずつ拡張できる必要があります。たとえば、ts_A_file01.dbfの現在のサイズが100GB、ts_B_file01.dbfの現在のサイズが200GBの場合は、/u01/oradataファイル・システムに20+40=60GB以上の空き領域が必要です。

質問の内容は次のとおりです。 データベース内の1つの問合せでこれを監視するには、どうすればよいでしょうか。 現在は、複雑なスクリプトによって、dfコマンドを実行して空き領域をテキスト・ファイルに収集し、カーソルをオープンし、DBA_DATA_FILESより現在の割当て済み領域を計算し、外部表を介してdfデータを読み取っています。

これを1つのSQL問合せで実行することは可能です。 そのためには、空き領域表示コマンド（df）に対して、スクリプトの実行や出力のリダイレクトなどの複雑な操作なしに、インタラクティブに問合せを実行する必要があります。 まず、dfの出力に対して、表と同じように問合せを実行できるようにします。 そのために、Oracle Database 11g Release 2で導入され、バージョン10.2.0.5にも遡って移植された機能を利用します（したがって、この方法は、Oracle Database 10g Release 2以降で動作します）。 その機能とは、プリプロセッサ・ディレクティブです （これについては以前の記事で取り上げています）。

最初に、df出力を生成する小さなシェル・スクリプトを保管するためのディレクトリを作成します。

SQL> create or replace
  2  directory exec_dir
  3  as '/home/tkyte/df'
  4  /
Directory created.

次に、そのディレクトリ内にrun_df.shというシェル・スクリプトを作成します。 このスクリプトの内容は、これだけです。

#!/bin/bash
/bin/df –Pl

このスクリプトの出力はリスト1のようになります。

コード・リスト1：dfシェル・スクリプトの出力

SQL> !./run_df.sh
Filesystem         1024-blocks      Used  Available   Capacity  Mounted on
/dev/mapper/VolGr...  18156292  10827600    6391528    63%      /
/dev/sda1               101086     12062      83805    13%      /boot
tmpfs                   517520         0     517520    0%       /dev/shm

run_df.shスクリプトで、dfを実行するために絶対パスを指定していることに注意してください。環境に依存しないように、特にパス環境変数に依存しないようにしました。 この点は非常に重要です。 外部表に関するスクリプトを記述する場合、あるいは、一般的にスクリプトを記述する場合、実行することを意図したプログラムが実際に実行されるようにするためには、かならず絶対パスを指定してください。 このスクリプトが実行される環境を制御することはできないので、特定の環境設定に依存することは災いの元です。

スクリプトとディレクトリを作成したので、外部表を作成できます。 リスト1の出力から分かるように、必要な作業は、外部表で1行目のレコードをスキップし、その後の各行を、空白を区切り文字として解析することだけです。 これは、リスト2に示すとおり、外部表を使用して容易に実行できます。

コード・リスト2：df外部表の作成

SQL> create table df
  2  (
  3   fsname   varchar2(100),
  4   blocks   number,
  5   used     number,
  6   avail    number,
  7   capacity varchar2(10),
  8   mount    varchar2(100)
  9  )
 10  organization external
 11  (
 12    type oracle_loader
 13    default directory exec_dir
 14    access parameters
 15    (
 16      records delimited
 17      by newline
 18      preprocessor
 19      exec_dir:'run_df.sh'
 20      skip 1
 21      fields terminated by
 22      whitespace ldrtrim
 23    )
 24    location
 25    (
 26      exec_dir:'run_df.sh'
 27    )
 28  )
 29  /
Table created.

このようにdf外部表を作成すれば、リスト3に示すように、df出力を1つの問合せで容易に確認できます。

コード・リスト3：df外部表の問合せ

SQL> select * from df;

FSNAME                           BLOCKS   USED      AVAIL    CAPACITY MOUNT
———————————————————————————————  ———————— ————————  ———————  ———————  ——————
/dev/mapper/VolGroup00-LogVol00  18156292 10827600  6391528   63%     /
/dev/sda1                         101086    12062    83805    13%     /boot
tmpfs                             517520        0   517520     0%     /dev/shm

注： お分かりだと思いますが、この方法はps、ls、duなどに対しても容易に適用できます。すべてのUNIXユーティリティを"表"とみなすことができるのです。

データをdf外部表で取得できるようになったので、問合せの作成は容易です。 必要な作業は、dfをDBA_DATA_FILESと結合することだけです。結合条件として、それぞれのファイル名を、できるだけ長いマウント・ポイントと一致させます。 その前に、テストの目的で、dfの出力を変更します。 上記の例では、ファイル・システムが小さく、マウント・ポイントが1つしかありません。ロジックをテストするため、スクリプトをリスト4のように変更して、dfデータを"偽造"します。

コード・リスト4：dfの偽造データ（テスト用）

SQL> !cat run_df.sh
#!/bin/bash
#/bin/df -Pl

echo Filesystem         1024-blocks      Used  Available  Capacity  Mounted on
echo /dev/mapper/VolG...   18156292  10827600    6391528       63%  /
echo /dev/mapper/VolG...   18156292  10827600    6391528       63%  /home/
ora11gr2/app/ora11gr2/oradata/ora11gr2/ORA11GR2 
echo /dev/sda1               101086     12062     83805        13% /boot
echo tmpfs                   517520         0    517520         0% /dev/shm

dfを実行する代わりに、データファイルで利用される可能性のある2つのマウント・ポイントをエコーして、出力を偽造します。 また、20%というデータファイル拡張条件についてテストするために、利用可能なデータ量についても変更します。

この質問に対する、1つの問合せを使用した解答は、リスト5のとおりです。 いくつかの行について以下に解説します。

コード・リスト5：1つの問合せによる監視方法

SQL> with fs_data
  2  as
  3  (select /*+ materialize */ *
  4     from df
  5  )
  6  select mount,
  7         file_name,
  8         bytes,
  9         tot_bytes,
 10         avail_bytes,
 11         case
 12         when 0.2 * tot_bytes < avail_bytes
 13         then 'OK'
 14         else 'Short on disk space'
 15          end status
 16    from (
 17  select file_name, mount, avail_bytes, bytes,
 18         sum(bytes) over
 19           (partition by mount) tot_bytes
 20    from (
 21  select a.file_name,
 22         b.mount,
 23         b.avail*1024 avail_bytes, a.bytes,
 24         row_number() over
 25           (partition by a.file_name
 26            order by length(b.mount) DESC) rn
 27    from dba_data_files a,
 28         fs_data b
 29   where a.file_name
 30             like b.mount || '%'
 31         )
 32   where rn = 1
 33         )
 34   order by mount, file_name
 35  /

MOUNT FILE_NAME                       BYTES     TOT_BYTES   AVAIL_BYTES  STATUS
————— ——————————————————————————————— ————————— ——————————  ———————————  ——————
/     /home/ora11gr2/app/ora11gr2/    360710144 2410283008  6544924672   OK
      oradata/ora11gr2/example01.dbf

/     /home/ora11gr2/app/ora11gr2/   1101004800 2410283008  6544924672   OK
      oradata/ora11gr2/sysaux01.dbf

/     /home/ora11gr2/app/ora11gr2/    924844032 2410283008  6544924672   OK
      oradata/ora11gr2/system01.dbf

/     /home/ora11gr2/app/ora11gr2/       131072 2410283008  6544924672   OK
      oradata/ora11gr2/system02.dbf

/     /home/ora11gr2/app/ora11gr2/      23592960 2410283008  6544924672  OK
      oradata/ora11gr2/users.dbf

/home/or /home/ora11gr2/app/ora11gr2/  144703488 376438784   6544924672   OK
a11gr2/a oradata/ora11gr2/ORA11GR2/
pp/ora11 datafile/o1_mf_big_tabl_
gr2/orad 7y3thv78_.dbf
ata/ora1
1gr2/ORA
11GR2

/home/or /home/ora11gr2/app/ora11gr2/    231735296  376438784  6544924672 OK
a11gr2/a oradata/ora11gr2/ORA11GR2/
pp/ora11 datafile/o1_mf_undotbs_
gr2/orad 78w1hprj_.dbf
ata/ora1
1gr2/ORA
11GR2

7 rows selected.

3行目と4行目で、df外部表に対する問合せを実行しています。 この問い合わせでは外部表の再読取りが発生する可能性がありますが、問合せの実行中にdf表の結果が変わる可能性があるため、materializeヒントを使用して、オプティマイザがdfデータをグローバル一時表と同等の表にロードするようにしています。 こうすることで、dfデータに対するロジックが読取り一貫性と同等になります。 また、問合せ計画で実際に外部表の再読取りが行われると、実行時に次のエラー・メッセージが表示されます。

KUP-04108 unable to reread file string 

このエラーは、ドキュメントで次のように説明されています。

原因：実行している問合せでは、外部表のデータソースが複数回読み込まれることが必要です。 しかし、データソースは再読込み不可能なシーケンシャル・デバイスです。 このようなデータソースとしてはテープまたはパイプがあります。

処置：この問題に対処する方法はいくつかあります。 1つは、外部表を1度のみ参照するように問合せを作成しなおす方法です。 もう1つは、ディスク・ファイルなど再読込み可能なデバイスにデータソースを移動することです。 さらにもう1つの方法は、外部表のデータを一時表にロードして、問合せが一時表を参照するように変更することです。

27～30行目では、DBA_DATA_FILESをdfデータに結合します。WHERE句ではLIKEを使用しています。 これによって、DBA_DATA_FILESのそれぞれのファイルが、df出力に出現する可能性のあるすべてのマウント・ポイントに結合されます。 ただし、目標は、一致する"最長"のマウント・ポイントを見つけることです。その目標を達成するために、24～26行目で、各行にROW_NUMBERを割り当てています。 このROW_NUMBERは、DBA_DATA_FILESにある重複行に順次割り当てられます。つまり、1つのFILE_NAMEが複数のMOUNTに一致する場合、FILE_NAMEが出現するたびに、1ずつ増加する一意のROW_NUMBER値が割り当てられます。 このROW_NUMBERは、データがMOUNTの長さによって長い順にソートされた後に割り当てられます。

そのデータが得られた後、WHERE句を適用して、各FILE_NAME値の最初の項目のみを保存しています。そのための条件は、32行目のWHERE rn = 1です。 一方、18～19行目で、TOT_BYTESという列を追加しています。 これによって、20%のしきい値を検証できます。

最後に、データを整形し、出力します。 必要な列を出力し、11～15行目のCASE文を追加して、特定のマウント・ポイントで割り当てられている記憶域の合計容量の20%が利用可能な空き領域を上回っていないか検証しています。

以上で、df、ps、lsなどオペレーティング・システムの出力に対して、外部表を使用して問合せを行う方法がお分かりいただけたでしょう。 ほかにも、gunzipやsedなど、標準出力に出力するあらゆるコマンドに対して、外部表を使用して問合せを実行できます。 外部表を使用した、gzipで圧縮された複数のファイルに対する問合せの実行方法に関する興味深い例については、bit.ly/PQgOolを参照してください。

ロックに関する質問

Oracle Databaseに自動ロック機能があり、デッドロックの状況が自動的に解消されるのであれば、なぜ手動ロックやデッドロックの解消をするのでしょうか。

これには多くの理由があります。 自動ロックには80/20の法則が当てはまります。 80%のケースは自動ロックで十分です。 しかし、開発者は、ロックに注意を払うべき状況を理解しておく必要があります。 もっとも単純な理由の1つは、更新の消失の検出です。

たとえば、ID（主キー）､名前、住所、電話番号を含む表があるとします。 さらに、この表から1行を読み取って画面上に表示し、エンドユーザーがその値を更新できるアプリケーションがあるとします。 ユーザーがボタンをクリックすると、このアプリケーションがupdate table t set name = :name, address = :address, phone = :phone where id = :idというSQLを発行します。

ここで、2人がほぼ同時にレコードを取得し、そのうち1人が住所を変更し、もう1人が電話番号を変更するという場合を考えます。 2人が同時にボタンをクリックしたら、どうなるでしょう。

一方の更新、たとえば住所の変更が成功するでしょう。 ここまでは問題ありません。

しかし、2件目の更新である電話番号の変更によって行全体が再度更新されると、その行の住所が以前の住所に戻され､更新された住所が消失します。.

クライアント/サーバー・アプリケーションでは、悲観的並行性制御と呼ばれる方法を採用する場合があります。 このアプリケーションでは、住所や電話番号を入力できるようにする前に、SELECT * FROM t WHERE id = :id AND name = :name AND address = :address AND phone = :phone FOR UPDATE NOWAITを発行して、レコードが存在する場合に手動でロックします。 レコードが存在する場合、なにも問題ありません。列を変更でき、更新が成功すること、および他の人による変更を上書きしないことを保証できます。 このSELECT文が0行を返した場合、他の人がレコードを変更したことが分かります。変更するためには、そのレコードを再度問い合わせる必要があります。 この問合せによりエラー・メッセージが返される場合は、他の人が現在その行を操作していることが分かります。

これが、手動ロックを行う理由の1つです。 次に、もう1つの状況を見てみましょう。 "部門あたりの従業員の総数は100人以下とする"というビジネス・ルールがあるとします。 このルールを適用するにはどうすればよいでしょうか。 EMP表への挿入を行う際に、他の人が同じ部門に挿入を行っていないことを確認する必要があります。 つまり、シリアライズする必要があります。 そのためには、DBMS_LOCK（ユーザー定義のロック）を使用する方法や、LOCK TABLEコマンドを使用する方法、select * from dept where deptno=:x for update nowaitによって部門レベルでシリアライズする方法などがあります。 いずれにせよ、手動ロックが必要です。

自動ロックでは、必要なことの80%に対応できます。 それ以外の部分で論理的なデータ一貫性を確保するために、手動ロックが必要です。

NULLとカーディナリティ

以前の記事で、2011年のOracle OpenWorldで私が行ったプレゼンテーションの一部について紹介しました。 それは、"SQLについてあなたが知らないだろう5つのこと"というプレゼンテーションの一部でした。 その記事で、このプレゼンテーションのほかの部分についても今後紹介するとお伝えしたので、ここで１つ取り上げたいと思います。 今回は、NULLについて、そしてカーディナリティの見積もりと索引の利用に対してNULLが及ぼしうる影響について説明します。

私はこれまで何度も（bit.ly/Pjrbyrなどを参照）、コストベース・オプティマイザ（CBO）にとってカーディナリティの見積もりが重要だと書いてきました。 手短に言えば、問合せ計画の各手順で扱う行数の推測をオプティマイザが誤った場合、その計画はおそらく不適切なものとなり、問合せのパフォーマンスが低下します。 たとえば、オプティマイザが、1,000,000行のデータから2行を取得すると推定した場合、索引を利用できるのであれば利用するでしょう。 しかし、実行時に、取得される行数が2行ではなく500,000行だと分かった場合はどうなるでしょうか。 この場合、索引の使用は考えられる中でもっとも不適切な方法です。

カーディナリティ値を正しく見積もることがオプティマイザにとって重要であり、それを混乱させるような、正しい見積もりの取得が困難になるようなことは、すべて不適切です。

しかし、このことがNULL値とどのように関係するのでしょうか。 直接の関係はありませんが、開発者がNULL値を使用すべきときに使用しないと起きることに関係があります。 多くの開発者は、NULLの使用を恐れています。 NULLを理解しておらず、NULLには索引を付けられないと考えているため、使用を避けるのです。 そのような開発者は、欠落している日付の値を表すために01-JAN-9999のような"偽"の値を使用します。 これは多くの理由で不適切な考えです。1つ目の理由として、カーディナリティの見積もりが混乱する例を示します。

まず、NULL値を使用する表を作成します。 この表は、"有効期間"付きのレコードを表します。すなわち、各レコードに有効期間の開始日と終了日があります。 レコードが"有効"である場合、つまり、終了日がまだ存在しない場合、終了日はNULLです。 この表のCREATE TABLE文は次のとおりです。

SQL> create table t
  2  pctfree 20
  3  as
  4  select a.*,
  5      case when mod(rownum,100) <= 50
  6           then last_ddl_time
  7      end end_date
  8    from all_objects a;

Table created.

この表では、約半分の行にEND_DATE値が設定され、残りの約半分ではこの値はNULLです。 次に、END_DATE列に対して、検索のための索引を作成します。

SQL> create index t_idx 
  2  on t(end_date);

Index created.

このデータを調べてみると、値の多くが、ある月にまとまっています。

SQL> select count(*)
  2    from t
  3   where end_date
  4   between to_date('01-sep-2010', 
                      'dd-mon-yyyy')
  5       and to_date('30-sep-2010', 
                      'dd-mon-yyyy');

  COUNT(*)
—————————————
     36267

ALL_OBJECTSビューには約72,000レコードが含まれているため、表Tにも約72,000レコードが存在します。そのうちの半数が2010年9月に収まっています。 次に、この表に関する統計情報を収集します。この統計情報には、END_DATE列に関するヒストグラムが含まれます。 ヒストグラムの計算方法やヒストグラムを計算する理由を知りたい場合は、bit.ly/PQnpPBの"Why Does My Plan Change?"を参照してください。

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

先に進む前に、このデータを確認して理解しましょう。

SQL> select count(*),
  2         count(distinct end_date),
  3         count(end_date),
  4         min(end_date),
  5         max(end_date)
  6    from t;

CNT    CNTD  CNT2   MIN       MAX
—————— ————— —————— ————————— —————————
72228  703   36850  01-OCT-02 30-SEP-11

このデータから分かることは、この表には72,228行のデータがあり、最小値が2002年10月1日、最大値が2011年9月30日だということです。 これらの行の約半数が2010年9月に収まっていることが分かっています（データに偏りがあります）。 また、重複を除いた日付は703個あります。これは、オプティマイザで保存できるヒストグラムの種類に影響します。 重複を除いた値が255個より多いため、オプティマイザは頻度ヒストグラムではなく高さ調整済みヒストグラムを利用します。

ここで、この表に対して2010年9月の範囲にある行を取得する問合せを実行した場合、オプティマイザは、リスト6で示すとおり、適切な問合せ計画を作成できます。

コード・リスト6：NULLがある場合の問合せと適切な計画

SQL> set autotrace traceonly explain
SQL> select *
  2    from t
  3   where end_date
  4         between to_date( '01-sep-2010', 'dd-mon-yyyy' )
  5             and to_date( '30-sep-2010', 'dd-mon-yyyy' );

Execution Plan
——————————————————————————————————————————————————————————————————————————
Plan hash value: 1601196873
——————————————————————————————————————————————————————————————————————————
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————————————————————————————————————————————————
|   0 | SELECT STATEMENT  |      | 36024 |  3588K|   339   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T    | 36024 |  3588K|   339   (1)| 00:00:05 |
——————————————————————————————————————————————————————————————————————————
Predicate Information (identified by operation id):
——————————————————————————————————————————————————————————————————————————
   1 - filter(“END_DATE”<=TO_DATE(' 2010-09-30 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "END_DATE">=TO_DATE(' 2010-09-01 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))

オプティマイザは、この問合せのカーディナリティを36,024と正しく見積もったため、全表スキャンを実行すべきだと判断しました。 この場合、索引を使用することは不適切な考えです。

では、開発者がNULLではなく偽の日付を使用した場合、 どうなるでしょうか。 データを更新し、再度統計情報を収集します。

SQL> update t
  2    set end_date = 
  3         to_date( '01-jan-9999',
'dd-mon-yyyy' )
  4   where end_date is null;
35378 rows updated.

SQL> commit;
Commit complete.

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

ここで、リスト6の問合せを再度実行すると、リスト7の結果が得られます。

コード・リスト7：偽のデータに対してリスト6の問合せを再実行した結果と不適切な計画

——————————————————————————————————————————————————————————————————————————
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| 
——————————————————————————————————————————————————————————————————————————
|   0 | SELECT STATEMENT            |       |   175 | 18375 |    10   (0)| 
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   175 | 18375 |    10   (0)| 
|*  2 |   INDEX RANGE SCAN          | T_IDX |   175 |       |     2   (0)| 
——————————————————————————————————————————————————————————————————————————
Predicate Information (identified by operation id):
——————————————————————————————————————————————————————————————————————————
   1 - filter("END_DATE"<=TO_DATE(' 2010-09-30 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "END_DATE">=TO_DATE(' 2010-09-01 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))

オプティマイザは、この条件に対するカーディナリティを正確に見積もっておらず、そのため、索引の使用が実際には適していないにもかかわらず適していると判断しました。 これはすべて、偽の値を使用していることが原因です。

偽の値の使用を避けるべきもう1つの理由を紹介しましょう。 ワシントンDCのある自動車所有者が、バニティ・プレート（自分で決められるナンバー・プレート）を購入することにしました。 この人が選んだナンバー・プレートはNO TAGS（ナンバー・プレートなし）でした。 この人は冗談で選んだのですが、ワシントンDCの車両管理局（DMV）のプログラマーが、欠落しているナンバー・プレートの値を表すために（NULLの代わりに）使用していた値は何だったと思いますか。 この件を伝えるニュース記事（yhoo.it/NRUisL）のタイトルは、"DCの男性、'ナンバー・プレートなし'というバニティ・プレートで$20,000のチケットを切られる"です。

修正は簡単だと思うかもしれません。 NO TAGSというナンバー・プレートを買わなければよいと。 ところが、そう簡単ではありません。 別のドライバーNick Vautier氏は、自分のイニシャルであるNVをナンバー・プレートとして使用したいと思いました。 ところが、彼の住むカリフォルニア州では、DMVは"ナンバー・プレートなし"を表す値としてNV（"not visible"の略）を使用していました。

アラバマ州では、XXXXXXXを"ナンバー・プレートなし"の値として使用しており、自動車所有者のScottie Roberson氏がそのナンバー・プレートを購入しました。この件に関するニュース記事では、次のような解決法が言及されています。 "バーミングハム市の交通エンジニアであるGregory Dawkins氏は、Roberson氏がこれ以上チケットを切られないように、同市がシステムを変更するかもしれないと語りました。 同氏は、「その部分をまったく空白にする必要があるかもしれない」と述べています。"

そのほかの例については、bit.ly/OgmnJqを参照してください。 欠落しているデータを表す適切なデフォルト値は存在しません。 NULLを使用してください。