今週の名言

「今日できることを明日にまで延ばすな｡」
フィリップ・ チェスターフィールド

前回のおさらい（Oracle 11gR2）

1. リスナーにインスタンスがサービスとして登録されて初めて、インスタンス（データベース）に対するネット接続が可能となります。
2. リスナーを後から起動すると、インスタンスが登録するまでにある程度の時間がかかります。（PMONが定期的にリスナーに登録しにいくので最長60秒程度かかる場合があります。）
3. リスナーを先に起動しておくと、インスタンスは起動直後にリスナーに登録されます。
4. 2.の場合でも「ALTER SYSTEM REGISTER」コマンドを実行すると、インスタンスを即時にリスナーへ登録することができます。

前回は、インスタンスのリスナーへの登録に関する挙動についてOracle11gR2で確認をしてみましたが、Oracle12cR1で同様の検証をしてみます。

サービス登録の動作を確認する（12cR1）

1. インスタンスを先に起動し、リスナーを後から起動する。

インスタンスを起動する

xx:xx:xx SQL> startup
ORACLEインスタンスが起動しました。

Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             608176880 bytes
Database Buffers          222298112 bytes
Redo Buffers                5455872 bytes
データベースがマウントされました。
データベースがオープンされました。
14:37:15 SQL>

アラートログ

2015-07-05 14:37:15.408000 +09:00
Starting background process CJQ0
Completed: ALTER DATABASE OPEN
CJQ0 started with pid=31, OS id=3721 

14:37:15 にインスタンス（データベース）が起動されました。

（正確には、インスタンスとはプロセス群とメモリから成るため、メモリ情報が表示された時がインスタンスの起動時刻となりますが、ここではインスタンス＝データベースの意味で使っていますので、データベースがオープンされた時刻をインスタンス起動と言っています。

このタイミングで tnsping と接続の確認を行ってみます。

[xx:xx:xx] $ tnsping ora12c

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-7月 -2015 14:38:16

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

パラメータ・ファイルを使用しました:
/u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora

エイリアスを解決するためにTNSNAMESアダプタを使用しました。
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelinux6.onefact.jp)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ora12c)))に接続の試行中
TNS-12541: TNS: リスナーがありません。
[14:38:16] $ 

[xx:xx:xx] $ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on 日 7月 5 14:39:24 2015

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

xx:xx:xx SQL> conn scott/tiger@ora12c
ERROR:
ORA-12541: TNS: リスナーがありません

14:39:33 SQL>

リスナーが起動していないので、tnspingも接続も失敗します。
14:39:33 にネット接続を試みましたが、成功しませんでした。

リスナーを起動する

インスタンスの次にリスナーを起動します。

[xx:xx:xx] $ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-7月 -2015 14:40:54

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

/u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnrを起動しています。お待ちください...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
システム・パラメータ・ファイルは/u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.oraです。
ログ・メッセージを/u01/app/oracle/diag/tnslsnr/oraclelinux6/listener/alert/log.xmlに書き込みました。
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux6.onefact.jp)(PORT=1521)))
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelinux6.onefact.jp)(PORT=1521)))に接続中
リスナーのステータス
------------------------
別名                      LISTENER
バージョン                TNSLSNR for Linux: Version 12.1.0.2.0 - Production
開始日                    05-7月 -2015 14:40:54
稼働時間                  0 日 0 時間 0 分 0 秒
トレース・レベル          off
セキュリティ              ON: Local OS Authentication
SNMP                      OFF
パラメータ・ファイル      /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
ログ・ファイル            /u01/app/oracle/diag/tnslsnr/oraclelinux6/listener/alert/log.xml
リスニング・エンドポイントのサマリー...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux6.onefact.jp)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
リスナーはサービスをサポートしていません。
コマンドは正常に終了しました。
[14:40:54] $

14:40:54 にリスナーが起動しました。

リスナー起動直後に別コンソールからネット接続を試みます。

xx:xx:xx SQL> conn scott/tiger@ora12c
ERROR:
ORA-12514: TNS:
リスナーは接続記述子でリクエストされたサービスを現在認識していません

14:40:57 SQL> conn scott/tiger@ora12c
ERROR:
ORA-12514: TNS:
リスナーは接続記述子でリクエストされたサービスを現在認識していません

14:41:06 SQL> conn scott/tiger@ora12c
接続されました。
14:41:17 SQL>

14:40:57 と 14:41:06 にネット接続の試みは失敗し、14:41:17 に成功しました。
リスナー起動から23秒後に接続を確認できました。

リスナーログ

05-7月 -2015 14:40:57 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora12c)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux6.onefact.jp)(USER=ora12c))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.18)(PORT=24694)) * establish * ora12c * 12514
TNS-12514: TNS: リスナーは現在、接続識別子でリクエストされているサービスを認識していません
2015-07-05 14:41:06.873000 +09:00
05-7月 -2015 14:41:06 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora12c)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux6.onefact.jp)(USER=ora12c))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.18)(PORT=24713)) * establish * ora12c * 12514
TNS-12514: TNS: リスナーは現在、接続識別子でリクエストされているサービスを認識していません
2015-07-05 14:41:09.718000 +09:00
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oraclelinux6.onefact.jp)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/ora12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Dynamic address is already listened on (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux6.onefact.jp)(PORT=1521)))
05-7月 -2015 14:41:09 * service_register * ora12c * 0
2015-07-05 14:41:17.722000 +09:00
05-7月 -2015 14:41:17 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora12c)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux6.onefact.jp)(USER=ora12c))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.18)(PORT=24735)) * establish * ora12c * 0
05-7月 -2015 14:41:18 * service_update * ora12c * 0

リスナーログから、正確には 14:41:09 に「service_register」確認できていますので、リスナー起動後 15秒でインスタンスが登録されたことがわかります。

2. リスナーを先に起動し、インスタンスを後から起動する

リスナーがすでに起動されている状態から、インスタンスを起動します。

xx:xx:xx SQL> startup
ORACLEインスタンスが起動しました。

Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             608176880 bytes
Database Buffers          222298112 bytes
Redo Buffers                5455872 bytes
データベースがマウントされました。
データベースがオープンされました。
14:47:10 SQL> 

14:47:10 にインスタンスが起動しました。

別コンソールから、インスタンス起動前と起動後にネット接続を試みます。

xx:xx:xx SQL> conn scott/tiger@ora12c
ERROR:
ORA-12514: TNS:
リスナーは接続記述子でリクエストされたサービスを現在認識していません

14:47:03 SQL> conn scott/tiger@ora12c
接続されました。
14:47:13 SQL>

14:47:03 に接続失敗していますが、14:47:13 には成功しています。

2015-07-05 14:46:19.450000 +09:00
05-7月 -2015 14:46:19 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora12c)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux6.onefact.jp)(USER=ora12c))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.18)(PORT=24780)) * establish * ora12c * 12514
TNS-12514: TNS: リスナーは現在、接続識別子でリクエストされているサービスを認識していません
2015-07-05 14:47:03.841000 +09:00
05-7月 -2015 14:47:03 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora12c)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux6.onefact.jp)(USER=ora12c))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.18)(PORT=24783)) * establish * ora12c * 12514
TNS-12514: TNS: リスナーは現在、接続識別子でリクエストされているサービスを認識していません
Dynamic address is already listened on (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux6.onefact.jp)(PORT=1521)))
05-7月 -2015 14:47:04 * service_register * ora12c * 0
2015-07-05 14:47:07.074000 +09:00
05-7月 -2015 14:47:07 * service_update * ora12c * 0
2015-07-05 14:47:08.779000 +09:00
05-7月 -2015 14:47:08 * service_update * ora12c * 0
2015-07-05 14:47:10.028000 +09:00
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oraclelinux6.onefact.jp)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/ora12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
05-7月 -2015 14:47:10 * service_update * ora12c * 0
05-7月 -2015 14:47:10 * service_update * ora12c * 0
05-7月 -2015 14:47:10 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora12c)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux6.onefact.jp)(USER=ora12c))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.18)(PORT=24787)) * establish * ora12c * 0
2015-07-05 14:47:13.033000 +09:00
05-7月 -2015 14:47:13 * service_update * ora12c * 0
2015-07-05 14:47:16.037000 +09:00
05-7月 -2015 14:47:16 * service_update * ora12c * 0
2015-07-05 14:47:46.056000 +09:00
05-7月 -2015 14:47:46 * service_update * ora12c * 0

3. インスタンスを先に起動し、リスナーを後から起動する。その後サービス登録コマンドを実行する。

インスタンスは起動済みですが、リスナーは起動されていない状態から始めます。

リスナーを起動する

[xx:xx:xx] $ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-7月 -2015 14:54:43

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

/u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnrを起動しています。お待ちください...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
システム・パラメータ・ファイルは/u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.oraです。
ログ・メッセージを/u01/app/oracle/diag/tnslsnr/oraclelinux6/listener/alert/log.xmlに書き込みました。
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux6.onefact.jp)(PORT=1521)))
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelinux6.onefact.jp)(PORT=1521)))に接続中
リスナーのステータス
------------------------
別名                      LISTENER
バージョン                TNSLSNR for Linux: Version 12.1.0.2.0 - Production
開始日                    05-7月 -2015 14:54:43
稼働時間                  0 日 0 時間 0 分 0 秒
トレース・レベル          off
セキュリティ              ON: Local OS Authentication
SNMP                      OFF
パラメータ・ファイル      /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
ログ・ファイル            /u01/app/oracle/diag/tnslsnr/oraclelinux6/listener/alert/log.xml
リスニング・エンドポイントのサマリー...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux6.onefact.jp)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
リスナーはサービスをサポートしていません。
コマンドは正常に終了しました。
[14:54:43] $

14:54:43 にリスナーが起動しました。

サービス登録コマンドを実行する

xx:xx:xx SQL> alter system register;
 システムが変更されました。
14:54:49 SQL>

14:54:49 にサービス登録コマンドを実行しました。

別コンソールから接続確認を行う。

確認を行った時間に注目してください。

xx:xx:xx SQL> conn scott/tiger@ora12c
ERROR:
ORA-12541: TNS: リスナーがありません

警告: Oracleにはもう接続されていません。
14:54:14 SQL> conn scott/tiger@ora12c
ERROR:
ORA-12514: TNS:
リスナーは接続記述子でリクエストされたサービスを現在認識していません

14:54:46 SQL> conn scott/tiger@ora12c
接続されました。
14:54:55 SQL>

14:54:14 は、まだリスナーが起動されていません。
14:54:46 は、リスナーが起動されていますが、インスタンスが登録されていません。
14:54:55 は、インスタンスが登録されたので接続に成功しています。

リスナーログ

05-7月 -2015 14:54:46 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora12c)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux6.onefact.jp)(USER=ora12c))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.18)(PORT=24826)) * establish * ora12c * 12514
TNS-12514: TNS: リスナーは現在、接続識別子でリクエストされているサービスを認識していません
2015-07-05 14:54:49.477000 +09:00
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oraclelinux6.onefact.jp)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/ora12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
05-7月 -2015 14:54:49 * service_register * ora12c * 0
2015-07-05 14:54:55.269000 +09:00
05-7月 -2015 14:54:55 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora12c)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux6.onefact.jp)(USER=ora12c))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.18)(PORT=24846)) * establish * ora12c * 0
05-7月 -2015 14:54:55 * service_update * ora12c * 0
2015-07-05 14:55:34.498000 +09:00
05-7月 -2015 14:55:34 * service_update * ora12c * 0
2015-07-05 14:57:10.577000 +09:00
05-7月 -2015 14:57:10 * service_update * ora12c * 0

12cでも、状況を見る限りでは11gと同じ挙動が確認できました。

リスナー登録（LREG）プロセスについて深掘りする

サービス登録の挙動に関しては、11g と 12c で違いはありませんでしたが、12cから新しくできたLREGプロセスについてもっと深く調べてみます。

まずは、いきなりですがサービスが登録された状態からLREGプロセスをkillしてみます。

LREGをkillしてみる

[xx:xx:xx] $ ps -ef | grep lreg
ora12c    3982     1  0 14:47 ?        00:00:00 ora_lreg_ora12c
ora12c    4347  3108  0 15:01 pts/4    00:00:00 grep lreg
[xx:xx:xx] $ kill -9 3982
[15:01:22] $

15:01:22 にプロセスがkillされました。

アラートログ

2015-07-05 15:01:23.798000 +09:00
Instance Critical Process (pid: 19, ospid: 3982, LREG) died unexpectedly
PMON (ospid: 3946): terminating the instance due to error 500
System state dump requested by (instance=1, osid=3946 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_diag_3960_20150705150123.trc
Dumping diagnostic data in directory=[cdmp_20150705150123], requested by (instance=1, osid=3946 (PMON)), summary=[abnormal instance termination].
Instance terminated by PMON, pid = 3946

LREGの異常終了を検知して、最終的にPMONがインスタンスをダウンさせています。

つまり、LREGは必須プロセスです。

リスナーログ

2015-07-05 15:00:55.732000 +09:00
05-7月 -2015 15:00:55 * service_update * ora12c * 0
2015-07-05 15:01:22.006000 +09:00
リスニングしていません: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oraclelinux6.onefact.jp)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/ora12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
05-7月 -2015 15:01:22 * service_died * ora12c * 12537

LREGが異常終了してインスタンスがダウンすると、リスナーには「service_died」のステータスが伝達され、インスタンスの登録が抹消されます。

まとめ

リスナーにインスタンスがサービスとして登録されるところを見てきましたが、HA構成で運用しているデータベースでは起動スクリプトにおいて、「データベース⇨リスナー」の順に起動している場合があるかもしれません。（私が１５年前にDBAをしていた時のスクリプトはそうでした。）

RACになって、特に11g移行はGrid Infrastructureの一部としてリスナーはデータベースの前に起動する仕組みになっていますが、まだまだHA構成のデータベースは世の中にあると思いますので、フェイルオーバー時にサービス登録が速やかに行われるよう「alter system register」コマンドが実行されるようになっているかを確認しても良いのではないでしょうか？

終わり

次回は、このプロセスの内部動作に迫ってみます。

続く

今週の名言

「もし、８時間で木を切り倒せと言われたら、私は７時間を斧の刃を研ぐことにあてる。」
エイブラハム・リンカーン

TNS_ADMIN環境変数とは？

TNS_ADMIN環境変数とは、Oracle Net関連設定ファイルを任意のディレクトリに配置する際に使用するものです。

マニュアルには以下の記述があります。

Oracle® Database Net Servicesリファレンス
11gリリース2 (11.2)
B56287-05
Oracle Net Listener構成ファイルの概要
デフォルトで、listener.oraファイルはORACLE_HOME/network/adminディレクトリに配置されます。listener.oraファイルは次の場所に格納される場合もあります。

• 環境変数TNS_ADMINまたはレジストリ値で指定されたディレクトリ。
• LinuxおよびUNIXオペレーティング・システムの場合は、グローバル構成ディレクトリ。たとえば、Solarisオペレーティング・システムの場合、このディレクトリは/var/opt/oracleです。

ちなみにOracle Linux 6の場合、グローバル構成ディレクトリは「/etc」となります。

1. デフォルト・ディレクトリ
2. TNS_ADMINで指定された任意のディレクトリ
3. グローバル構成ディレクトリ

という３つのディレクトリの優先順位がどうなっているかを確認してみます。（念のため仕様が変わっていないかを確認するためにOracle12cで検証します。）

３つのlistener.oraを準備する。

３つのlistener.oraを用意しますが、２番目以降は違いがわかるようにパラメータを追加しています。

デフォルト・ディレクトリ

$ cat $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelinux6.onefact.jp)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

任意のディレクトリ（RECV_BUF_SIZEを追加）

$ cat $ORACLE_HOME/network/admin/test/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION = (RECV_BUF_SIZE=11784)
      (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelinux6.onefact.jp)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

グローバル構成ディレクトリ（SEND_BUF_SIZEを追加）

$ cat /etc/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION = (SEND_BUF_SIZE=11280)
      (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelinux6.onefact.jp)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

リスナーを起動してすぐ停止する。

デフォルト状態

TNS_ADMIN環境変数に何も設定されていないことを確認してからリスナーを起動します。

$ env|grep TNS_ADMIN
$
$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on xx-6月 -2015 xx:xx:xx

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

/u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnrを起動しています。お待ちください...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
システム・パラメータ・ファイルは/etc/listener.oraです。
ログ・メッセージを/u01/app/oracle/diag/tnslsnr/oraclelinux6/listener/alert/log.xmlに書き込みました。
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxxx)(PORT=1521))(SEND_BUF_SIZE=11280))
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))(SEND_BUF_SIZE=11280))

(DESCRIPTION=(SEND_BUF_SIZE=11280)(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxxx)(PORT=1521)))に接続中
リスナーのステータス
------------------------
別名                      LISTENER
バージョン                TNSLSNR for Linux: Version 12.1.0.2.0 - Production
開始日                    xx-6月 -2015 xx:xx:xx
稼働時間                  0 日 0 時間 0 分 0 秒
トレース・レベル          off
セキュリティ              ON: Local OS Authentication
SNMP                      OFF
パラメータ・ファイル      /etc/listener.ora
ログ・ファイル            /u01/app/oracle/diag/tnslsnr/oraclelinux6/listener/alert/log.xml
リスニング・エンドポイントのサマリー...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxxx)(PORT=1521))(SEND_BUF_SIZE=11280))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))(SEND_BUF_SIZE=11280))
リスナーはサービスをサポートしていません。
コマンドは正常に終了しました。
$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on xx-6月 -2015 xx:xx:xx

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

(DESCRIPTION=(SEND_BUF_SIZE=11280)(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxxx)(PORT=1521)))に接続中
コマンドは正常に終了しました。

パラメータファイルが「/etc/listener.ora」であることがわかります。
設定されたパラメータ（SEND_BUF_SIZE）も反映されています。

TNS_ADMIN環境変数を設定する。

TNS_ADMIN環境変数に検証用listener.oraを配置したディレクトリを指定してからリスナーを起動します。

$ export TNS_ADMIN=$ORACLE_HOME/network/admin/test/list
$ env|grep TNS_ADMIN
TNS_ADMIN=/u01/app/oracle/product/12.1.0.2/db_1/network/admin/test
[ora12c@oraclelinux6 etc]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-6月 -2015 22:38:14

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

/u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnrを起動しています。お待ちください...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
システム・パラメータ・ファイルは/u01/app/oracle/product/12.1.0.2/db_1/network/admin/test/listener.oraです。
ログ・メッセージを/u01/app/oracle/diag/tnslsnr/oraclelinux6/listener/alert/log.xmlに書き込みました。
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxxx)(PORT=1521))(RECV_BUF_SIZE=11784))
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))(RECV_BUF_SIZE=11784))

(DESCRIPTION=(RECV_BUF_SIZE=11784)(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxxx)(PORT=1521)))に接続中
リスナーのステータス
------------------------
別名                      LISTENER
バージョン                TNSLSNR for Linux: Version 12.1.0.2.0 - Production
開始日                    xx-6月 -2015 xx:xx:xx
稼働時間                  0 日 0 時間 0 分 0 秒
トレース・レベル          off
セキュリティ              ON: Local OS Authentication
SNMP                      OFF
パラメータ・ファイル      /u01/app/oracle/product/12.1.0.2/db_1/network/admin/test/listener.ora
ログ・ファイル            /u01/app/oracle/diag/tnslsnr/oraclelinux6/listener/alert/log.xml
リスニング・エンドポイントのサマリー...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxxx)(PORT=1521))(RECV_BUF_SIZE=11784))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))(RECV_BUF_SIZE=11784))
リスナーはサービスをサポートしていません。
コマンドは正常に終了しました。
$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on xx-6月 -2015 xx:xx:xx

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

(DESCRIPTION=(RECV_BUF_SIZE=11784)(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxxx)(PORT=1521)))に接続中
コマンドは正常に終了しました。

指定されたディレクトリ下のlistener.oraがパラメータ・ファイルとして有効になり、RECV_BUF_SIZEパラメータが設定されていることがわかります。

/etc/listener.oraを無効にする。

「/etc/listener.ora」をRenameして無効化した後、TNS_ADMIN環境変数をクリアし、リスナーを起動します。

$ mv /etc/listener.ora /etc/listener.ora.bak
$ ls /etc/listener.ora*
/etc/listener.ora.bak
$ unset TNS_ADMIN
$ env|grep TNS_ADMIN
$
$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on xx-6月 -2015 xx:xx:xx

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

/u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnrを起動しています。お待ちください...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
システム・パラメータ・ファイルは/u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.oraです。
ログ・メッセージを/u01/app/oracle/diag/tnslsnr/oraclelinux6/listener/alert/log.xmlに書き込みました。
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxxx)(PORT=1521)))
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxxx)(PORT=1521)))に接続中
リスナーのステータス
------------------------
別名                      LISTENER
バージョン                TNSLSNR for Linux: Version 12.1.0.2.0 - Production
開始日                    xx-6月 -2015 xx:xx:xx
稼働時間                  0 日 0 時間 0 分 0 秒
トレース・レベル          off
セキュリティ              ON: Local OS Authentication
SNMP                      OFF
パラメータ・ファイル      /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
ログ・ファイル            /u01/app/oracle/diag/tnslsnr/oraclelinux6/listener/alert/log.xml
リスニング・エンドポイントのサマリー...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxxx)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
リスナーはサービスをサポートしていません。
コマンドは正常に終了しました。
$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on xx-6月 -2015 xx:xx:xx

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

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxxx)(PORT=1521)))に接続中
コマンドは正常に終了しました。

パラメータ・ファイルはデフォルト・ディレクトリのlistener.oraで、追加のパラメータが設定されていないことがわかります。

結論：TNS_ADMINで指定されたディレクトリ ＞ グローバル構成ディレクトリ ＞ デフォルト・ディレクトリ の順にlistener.oraを探して起動する。

リスナーのパラメータを新たにテストする際、デフォルト・ディレクトリのlistener.oraを変更（編集）しなくても、TNS_ADMIN環境変数を一時的に設定し別のlistener.oraを使用してリスナーを起動することができます。

今日はここまで。

今週の名言

「世間で頭角をあらわす人物は、自分の望む環境を自ら捜し求める人物であり、もしそれが見つからない時は自分で創り出す人物である。」
ジョージ・バーナード・ショー

今回もトリビアネタ

Oracleエラーを数える回ではとんだ墓穴を掘ってしまいましたが、懲りずに今回はヒント句の種類、しかもOracleのバージョンが進化していく間にどんなヒント句が追加されてきたのかという変遷をたどってみます。

環境は12cR1

今回使用する環境はOracle12cR1です。

SQL> SELECT BANNER FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

使用するビューはV$SQL_HINT

V$SQL_HINTは11gからあるようですが、ヒント句にどんなのがあるのかを確認できるなかなか面白いビューです。
なぜか12cになってもアンドキュメントなビューなので気になって調べてみました。

ただ、一覧表示させるだけではつまらないので、バージョンごとにまとめて集計してみました。

Oracle8

SQL> SELECT VERSION,CLASS,NAME HINT_NAME,INVERSE,SQL_FEATURE
  2  FROM V$SQL_HINT
  3  ORDER BY
  4   TO_NUMBER(REGEXP_REPLACE(REGEXP_REPLACE(VERSION,'\.','',1,2),'\.','',1,2),99.999)
  5  ,CLASS,NAME;

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
8.0.0      ACCESS                           CLUSTER                                                           QKSFM_CBO
                                            INDEX                            NO_INDEX                         QKSFM_INDEX
                                            QUEUE_CURR                                                        QKSFM_CBO
                                            QUEUE_ROWP                                                        QKSFM_CBO
                                            ROWID                                                             QKSFM_CBO
           ANTIJOIN                         NL_AJ                                                             QKSFM_JOIN_METHOD
           EXPR_CORR_CHECK                  EXPR_CORR_CHECK                                                   QKSFM_CBO
           MERGE                            NO_MERGE                         MERGE                            QKSFM_CVM
           MERGE_CONST_ON                   MERGE_CONST_ON                                                    QKSFM_CBO
           NO_MONITORING                    NO_MONITORING                                                     QKSFM_ALL
           NO_ORDER_ROLLUPS                 NO_ORDER_ROLLUPS                                                  QKSFM_TRANSFORMATION
           NO_STATS_GSETS                   NO_STATS_GSETS                                                    QKSFM_ALL
           ORDERED_PREDICATES               ORDERED_PREDICATES                                                QKSFM_CBO
           SEMIJOIN                         NL_SJ                                                             QKSFM_JOIN_METHOD
********** ******************************** --------------------------------
count                                                                     14

INDEXヒントは8.0.0からあります。
NL_AJ, NL_SJ, NO_MERGE なんかもありますね。
この頃はヒントはまだ14個しかなかったのでしょうか。

Oracle8i

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
8.1.0      ACCESS                           AND_EQUAL                                                         QKSFM_AND_EQUAL
                                            FULL                                                              QKSFM_FULL
                                            HASH                                                              QKSFM_ALL
                                            INDEX_ASC                        NO_INDEX                         QKSFM_INDEX_ASC
                                            INDEX_COMBINE                                                     QKSFM_INDEX_COMBINE
                                            INDEX_DESC                       NO_INDEX                         QKSFM_INDEX_DESC
                                            INDEX_FFS                                                         QKSFM_INDEX_FFS
           ANTIJOIN                         HASH_AJ                                                           QKSFM_JOIN_METHOD
                                            MERGE_AJ                                                          QKSFM_JOIN_METHOD
           APPEND                           APPEND                           NOAPPEND                         QKSFM_CBO
                                            NOAPPEND                         APPEND                           QKSFM_CBO
           BITMAP                           BITMAP                                                            QKSFM_CBO
           CACHE                            CACHE                            NOCACHE                          QKSFM_EXECUTION
                                            NOCACHE                          CACHE                            QKSFM_EXECUTION
           DEREF_NO_REWRITE                 DEREF_NO_REWRITE                                                  QKSFM_ALL
           DRIVING_SITE                     DRIVING_SITE                                                      QKSFM_ALL
           FACT                             FACT                             NO_FACT                          QKSFM_STAR_TRANS
                                            NO_FACT                          FACT                             QKSFM_STAR_TRANS
           JOIN                             USE_HASH                         NO_USE_HASH                      QKSFM_USE_HASH
                                            USE_MERGE                        NO_USE_MERGE                     QKSFM_USE_MERGE
                                            USE_NL                           NO_USE_NL                        QKSFM_USE_NL
           MERGE                            MERGE                            NO_MERGE                         QKSFM_CVM
           MODE                             ALL_ROWS                                                          QKSFM_ALL_ROWS
                                            CHOOSE                                                            QKSFM_CHOOSE
                                            FIRST_ROWS                                                        QKSFM_FIRST_ROWS
                                            RULE                                                              QKSFM_RBO
           NESTED_TABLE_GET_REFS            NESTED_TABLE_GET_REFS                                             QKSFM_ALL
           ORDERED                          ORDERED                                                           QKSFM_CBO
           PARALLEL_INDEX                   NO_PARALLEL_INDEX                PARALLEL_INDEX                   QKSFM_PQ
                                            PARALLEL_INDEX                   NO_PARALLEL_INDEX                QKSFM_PQ
           PIV_GB                           PIV_GB                                                            QKSFM_ALL
                                            TIV_GB                                                            QKSFM_ALL
           PIV_SSF                          PIV_SSF                                                           QKSFM_ALL
                                            TIV_SSF                                                           QKSFM_ALL
           PUSH_PRED                        NO_PUSH_PRED                     PUSH_PRED                        QKSFM_FILTER_PUSH_PRED
                                            PUSH_PRED                        NO_PUSH_PRED                     QKSFM_FILTER_PUSH_PRED
           PUSH_SUBQ                        PUSH_SUBQ                        NO_PUSH_SUBQ                     QKSFM_TRANSFORMATION
           REMOTE_MAPPED                    REMOTE_MAPPED                                                     QKSFM_ALL
           SEMIJOIN                         HASH_SJ                                                           QKSFM_JOIN_METHOD
                                            MERGE_SJ                                                          QKSFM_JOIN_METHOD
           SEMIJOIN_DRIVER                  SEMIJOIN_DRIVER                                                   QKSFM_CBO
           SHARED                           NOPARALLEL                       SHARED                           QKSFM_PARALLEL
                                            SHARED                           NO_PARALLEL                      QKSFM_PARALLEL
           STAR                             STAR                                                              QKSFM_STAR_TRANS
           STAR_TRANSFORMATION              STAR_TRANSFORMATION              NO_STAR_TRANSFORMATION           QKSFM_STAR_TRANS
           SWAP_JOIN_INPUTS                 SWAP_JOIN_INPUTS                 NO_SWAP_JOIN_INPUTS              QKSFM_CBO
           USE_ANTI                         USE_ANTI                                                          QKSFM_CBO
           USE_CONCAT                       NO_EXPAND                        USE_CONCAT                       QKSFM_USE_CONCAT
                                            USE_CONCAT                       NO_EXPAND                        QKSFM_USE_CONCAT
           USE_SEMI                         USE_SEMI                                                          QKSFM_CBO
********** ******************************** --------------------------------
count                                                                     50

Oracle8iから一気に増えます。
FULLやINDEX関連のヒント、HASHヒントなんかもあります。RULEもありますね。

Oracle8i(8.1.5〜)

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
8.1.5      ACCESS                           INDEX_JOIN                                                        QKSFM_INDEX_JOIN
           BUFFER                           BUFFER                           NO_BUFFER                        QKSFM_CBO
                                            NO_BUFFER                        BUFFER                           QKSFM_CBO
           BYPASS_UJVC                      BYPASS_UJVC                                                       QKSFM_CBO
           CACHE                            CACHE_TEMP_TABLE                 NOCACHE                          QKSFM_ALL
           CACHE_CB                         CACHE_CB                         NOCACHE                          QKSFM_CBO
           CUBE_GB                          CUBE_GB                                                           QKSFM_CBO
           DOMAIN_INDEX_SORT                DOMAIN_INDEX_NO_SORT             DOMAIN_INDEX_SORT                QKSFM_CBO
                                            DOMAIN_INDEX_SORT                DOMAIN_INDEX_NO_SORT             QKSFM_CBO
           NESTED_TABLE_SET_SETID           NESTED_TABLE_SET_SETID                                            QKSFM_ALL
           NO_ACCESS                        NO_ACCESS                                                         QKSFM_ALL
           NO_INDEX                         NO_INDEX                         INDEX                            QKSFM_INDEX
           PQ_DISTRIBUTE                    PQ_DISTRIBUTE                                                     QKSFM_PQ_DISTRIBUTE
           RESTORE_AS_INTERVALS             RESTORE_AS_INTERVALS                                              QKSFM_CBO
           REWRITE                          NO_REWRITE                       REWRITE                          QKSFM_TRANSFORMATION
                                            REWRITE                          NO_REWRITE                       QKSFM_TRANSFORMATION
           SAVE_AS_INTERVALS                SAVE_AS_INTERVALS                                                 QKSFM_CBO
           SCN_ASCENDING                    SCN_ASCENDING                                                     QKSFM_ALL
********** ******************************** --------------------------------
count                                                                     18

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
8.1.6      LEADING                          LEADING                                                           QKSFM_JOIN_ORDER
           SYS_PARALLEL_TXN                 SYS_PARALLEL_TXN                                                  QKSFM_CBO
           UNNEST                           NO_UNNEST                        UNNEST                           QKSFM_UNNEST
                                            UNNEST                           NO_UNNEST                        QKSFM_UNNEST
********** ******************************** --------------------------------
count                                                                      4

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
8.1.7      LIKE_EXPAND                      LIKE_EXPAND                                                       QKSFM_TRANSFORMATION
           OR_EXPAND                        OR_EXPAND                                                         QKSFM_OR_EXPAND
********** ******************************** --------------------------------
count                                                                      2

8.1.5以降はだいぶ枯れてきた感があります。LEADINGヒントはRULEベース的にFROM句の後のテーブルを記述した順に結合するヒントですが8.1.6からなんですね。

Oracle9i

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
9.0.0      ACCESS                           INDEX_RRS                                                         QKSFM_CBO
                                            INDEX_SS                         NO_INDEX_SS                      QKSFM_INDEX_SS
                                            INDEX_SS_ASC                     NO_INDEX_SS                      QKSFM_INDEX_SS_ASC
                                            INDEX_SS_DESC                    NO_INDEX_SS                      QKSFM_INDEX_SS_DESC
           ANTIJOIN                         ANTIJOIN                                                          QKSFM_TRANSFORMATION
           BYPASS_RECURSIVE_CHECK           BYPASS_RECURSIVE_CHECK                                            QKSFM_ALL
           CARDINALITY                      CARDINALITY                                                       QKSFM_STATS
           CPU_COSTING                      CPU_COSTING                      NO_CPU_COSTING                   QKSFM_CPU_COSTING
                                            NO_CPU_COSTING                   CPU_COSTING                      QKSFM_CPU_COSTING
           CURSOR_SHARING_EXACT             CURSOR_SHARING_EXACT                                              QKSFM_CBO
           DML_UPDATE                       DML_UPDATE                                                        QKSFM_CBO
           GBY_CONC_ROLLUP                  GBY_CONC_ROLLUP                                                   QKSFM_TRANSFORMATION
           HWM_BROKERED                     HWM_BROKERED                                                      QKSFM_CBO
           INLINE                           INLINE                           MATERIALIZE                      QKSFM_TRANSFORMATION
                                            MATERIALIZE                      INLINE                           QKSFM_TRANSFORMATION
           LOCAL_INDEXES                    LOCAL_INDEXES                                                     QKSFM_CBO
           MV_MERGE                         MV_MERGE                                                          QKSFM_TRANSFORMATION
           NO_PRUNE_GSETS                   NO_PRUNE_GSETS                                                    QKSFM_TRANSFORMATION
           OVERFLOW_NOMOVE                  OVERFLOW_NOMOVE                                                   QKSFM_CBO
           PQ_MAP                           PQ_MAP                           PQ_NOMAP                         QKSFM_PQ_MAP
                                            PQ_NOMAP                         PQ_MAP                           QKSFM_PQ_MAP
           SEMIJOIN                         NO_SEMIJOIN                      SEMIJOIN                         QKSFM_TRANSFORMATION
                                            SEMIJOIN                         NO_SEMIJOIN                      QKSFM_TRANSFORMATION
           SKIP_EXT_OPTIMIZER               SKIP_EXT_OPTIMIZER                                                QKSFM_CBO
           SQLLDR                           SQLLDR                                                            QKSFM_CBO
           USE_TTT_FOR_GSETS                USE_TTT_FOR_GSETS                                                 QKSFM_TRANSFORMATION
********** ******************************** --------------------------------
count                                                                     26

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
9.2.0      DYNAMIC_SAMPLING                 DYNAMIC_SAMPLING                                                  QKSFM_DYNAMIC_SAMPLING
           DYNAMIC_SAMPLING_EST_CDN         DYNAMIC_SAMPLING_EST_CDN                                          QKSFM_DYNAMIC_SAMPLING_EST_CDN
           EXPAND_GSET_TO_UNION             EXPAND_GSET_TO_UNION             NO_EXPAND_GSET_TO_UNION          QKSFM_TRANSFORMATION
                                            NO_EXPAND_GSET_TO_UNION          EXPAND_GSET_TO_UNION             QKSFM_TRANSFORMATION
           FORCE_XML_QUERY_REWRITE          FORCE_XML_QUERY_REWRITE          NO_XML_QUERY_REWRITE             QKSFM_XML_REWRITE
                                            NO_XML_QUERY_REWRITE             FORCE_XML_QUERY_REWRITE          QKSFM_XML_REWRITE
           IGNORE_WHERE_CLAUSE              IGNORE_WHERE_CLAUSE                                               QKSFM_ALL
           NO_QKN_BUFF                      NO_QKN_BUFF                                                       QKSFM_CBO
           PUSH_SUBQ                        NO_PUSH_SUBQ                     PUSH_SUBQ                        QKSFM_TRANSFORMATION
           REF_CASCADE_CURSOR               NO_REF_CASCADE                   REF_CASCADE_CURSOR               QKSFM_CBO
                                            REF_CASCADE_CURSOR               NO_REF_CASCADE                   QKSFM_CBO
           SYS_DL_CURSOR                    SYS_DL_CURSOR                                                     QKSFM_CBO
           SYS_RID_ORDER                    SYS_RID_ORDER                                                     QKSFM_ALL
********** ******************************** --------------------------------
count                                                                     13

Oracle9iR1は当時400種類以上の新機能ができたなんて聞きましたが、ヒント句は8iの時と比べると半分くらいしか追加されていません。
マテリアライズド・ビュー関連のヒントが目立ちますが、ダイナミックサンプリングなんかも9iR2からですね。

Oracle10gR1

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
10.1.0.3   FBTSCAN                          FBTSCAN                                                           QKSFM_CBO
           GATHER_PLAN_STATISTICS           GATHER_PLAN_STATISTICS                                            QKSFM_GATHER_PLAN_STATISTICS
           IGNORE_OPTIM_EMBEDDED_HINTS      IGNORE_OPTIM_EMBEDDED_HINTS                                       QKSFM_ALL
           INCLUDE_VERSION                  INCLUDE_VERSION                                                   QKSFM_ALL
           MODEL_DONTVERIFY_UNIQUENESS      MODEL_DONTVERIFY_UNIQUENESS                                       QKSFM_TRANSFORMATION
           MODEL_MIN_ANALYSIS               MODEL_MIN_ANALYSIS                                                QKSFM_TRANSFORMATION
                                            MODEL_NO_ANALYSIS                                                 QKSFM_ALL
           MODEL_PUSH_REF                   MODEL_PUSH_REF                   NO_MODEL_PUSH_REF                QKSFM_TRANSFORMATION
                                            NO_MODEL_PUSH_REF                MODEL_PUSH_REF                   QKSFM_ALL
           NESTED_TABLE_FAST_INSERT         NESTED_TABLE_FAST_INSERT                                          QKSFM_ALL
           NO_INDEX_FFS                     NO_INDEX_FFS                     INDEX_FFS                        QKSFM_INDEX_FFS
           NO_INDEX_SS                      NO_INDEX_SS                      INDEX_SS                         QKSFM_INDEX_SS
           NO_PARTIAL_COMMIT                NO_PARTIAL_COMMIT                                                 QKSFM_CBO
           NO_QUERY_TRANSFORMATION          NO_QUERY_TRANSFORMATION                                           QKSFM_TRANSFORMATION
           NO_USE_HASH                      NO_USE_HASH                      USE_HASH                         QKSFM_USE_HASH
           NO_USE_MERGE                     NO_USE_MERGE                     USE_MERGE                        QKSFM_USE_MERGE
           NO_USE_NL                        NO_USE_NL                        USE_NL                           QKSFM_USE_NL
           OPAQUE_TRANSFORM                 OPAQUE_TRANSFORM                                                  QKSFM_TRANSFORMATION
           OPAQUE_XCANONICAL                OPAQUE_XCANONICAL                                                 QKSFM_TRANSFORMATION
           OPTIMIZER_FEATURES_ENABLE        OPTIMIZER_FEATURES_ENABLE                                         QKSFM_ALL
           OPT_ESTIMATE                     OPT_ESTIMATE                                                      QKSFM_OPT_ESTIMATE
           QB_NAME                          QB_NAME                                                           QKSFM_ALL
           RESTRICT_ALL_REF_CONS            RESTRICT_ALL_REF_CONS                                             QKSFM_ALL
           REWRITE                          NO_BASETABLE_MULTIMV_REWRITE     REWRITE                          QKSFM_ALL
                                            NO_MULTIMV_REWRITE               REWRITE                          QKSFM_ALL
                                            REWRITE_OR_ERROR                                                  QKSFM_TRANSFORMATION
           SET_TO_JOIN                      NO_SET_TO_JOIN                   SET_TO_JOIN                      QKSFM_SET_TO_JOIN
                                            SET_TO_JOIN                      NO_SET_TO_JOIN                   QKSFM_SET_TO_JOIN
           SHARED                           NO_PARALLEL                      SHARED                           QKSFM_CBO
           SKIP_UNQ_UNUSABLE_IDX            SKIP_UNQ_UNUSABLE_IDX                                             QKSFM_CBO
           STAR_TRANSFORMATION              NO_STAR_TRANSFORMATION           STAR_TRANSFORMATION              QKSFM_STAR_TRANS
           STREAMS                          STREAMS                                                           QKSFM_CBO
           SWAP_JOIN_INPUTS                 NO_SWAP_JOIN_INPUTS              SWAP_JOIN_INPUTS                 QKSFM_CBO
           TABLE_STATS                      COLUMN_STATS                                                      QKSFM_STATS
                                            INDEX_STATS                                                       QKSFM_STATS
                                            TABLE_STATS                                                       QKSFM_STATS
           TRACING                          TRACING                                                           QKSFM_EXECUTION
           USE_NL_WITH_INDEX                USE_NL_WITH_INDEX                NO_USE_NL                        QKSFM_USE_NL_WITH_INDEX
           USE_WEAK_NAME_RESL               USE_WEAK_NAME_RESL                                                QKSFM_ALL
           VECTOR_READ                      VECTOR_READ                                                       QKSFM_CBO
           VECTOR_READ_TRACE                VECTOR_READ_TRACE                                                 QKSFM_CBO
           X_DYN_PRUNE                      X_DYN_PRUNE                                                       QKSFM_CBO
********** ******************************** --------------------------------
count                                                                     42

Oracle10gからはまたグッと増えてきました。
特に統計情報関連のヒントが気になります。

Oracle10gR2

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
10.2.0.1   ACCESS                           BITMAP_TREE                                                       QKSFM_BITMAP_TREE
           DBMS_STATS                       DBMS_STATS                                                        QKSFM_DBMS_STATS
           ELIMINATE_JOIN                   ELIMINATE_JOIN                   NO_ELIMINATE_JOIN                QKSFM_TABLE_ELIM
                                            NO_ELIMINATE_JOIN                ELIMINATE_JOIN                   QKSFM_TABLE_ELIM
           ELIMINATE_OBY                    ELIMINATE_OBY                    NO_ELIMINATE_OBY                 QKSFM_OBYE
                                            NO_ELIMINATE_OBY                 ELIMINATE_OBY                    QKSFM_OBYE
           INLINE_XMLTYPE_NT                INLINE_XMLTYPE_NT                                                 QKSFM_ALL
           MODEL_COMPILE_SUBQUERY           MODEL_COMPILE_SUBQUERY                                            QKSFM_TRANSFORMATION
           MODEL_DYNAMIC_SUBQUERY           MODEL_DYNAMIC_SUBQUERY                                            QKSFM_TRANSFORMATION
           NO_CARTESIAN                     NO_CARTESIAN                                                      QKSFM_ALL
           NO_SQL_TUNE                      NO_SQL_TUNE                                                       QKSFM_ALL
           NO_XML_DML_REWRITE               NO_XML_DML_REWRITE                                                QKSFM_XML_REWRITE
           OLD_PUSH_PRED                    OLD_PUSH_PRED                                                     QKSFM_OLD_PUSH_PRED
           OPT_PARAM                        OPT_PARAM                                                         QKSFM_ALL
           OUTLINE                          OUTLINE                                                           QKSFM_ALL
           OUTLINE_LEAF                     OUTLINE_LEAF                                                      QKSFM_ALL
           PRECOMPUTE_SUBQUERY              PRECOMPUTE_SUBQUERY                                               QKSFM_TRANSFORMATION
           PRESERVE_OID                     PRESERVE_OID                                                      QKSFM_ALL
           PULL_PRED                        NO_PULL_PRED                     PULL_PRED                        QKSFM_PULL_PRED
                                            PULL_PRED                        NO_PULL_PRED                     QKSFM_PULL_PRED
           PX_JOIN_FILTER                   NO_PX_JOIN_FILTER                PX_JOIN_FILTER                   QKSFM_PX_JOIN_FILTER
                                            PX_JOIN_FILTER                   NO_PX_JOIN_FILTER                QKSFM_PX_JOIN_FILTER
           RBO_OUTLINE                      RBO_OUTLINE                                                       QKSFM_RBO
           USE_HASH_AGGREGATION             NO_USE_HASH_AGGREGATION          USE_HASH_AGGREGATION             QKSFM_ALL
                                            USE_HASH_AGGREGATION             NO_USE_HASH_AGGREGATION          QKSFM_ALL
********** ******************************** --------------------------------
count                                                                     25

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
10.2.0.2   CONNECT_BY_COST_BASED            CONNECT_BY_COST_BASED            NO_CONNECT_BY_COST_BASED         QKSFM_TRANSFORMATION
                                            NO_CONNECT_BY_COST_BASED         CONNECT_BY_COST_BASED            QKSFM_TRANSFORMATION
           CONNECT_BY_FILTERING             CONNECT_BY_FILTERING             NO_CONNECT_BY_FILTERING          QKSFM_ALL
                                            NO_CONNECT_BY_FILTERING          CONNECT_BY_FILTERING             QKSFM_ALL
********** ******************************** --------------------------------
count                                                                      4

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
10.2.0.3   ACCESS                           NUM_INDEX_KEYS                                                    QKSFM_CBO
           NATIVE_FULL_OUTER_JOIN           NATIVE_FULL_OUTER_JOIN           NO_NATIVE_FULL_OUTER_JOIN        QKSFM_ALL
                                            NO_NATIVE_FULL_OUTER_JOIN        NATIVE_FULL_OUTER_JOIN           QKSFM_ALL
********** ******************************** --------------------------------
count                                                                      3

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
10.2.0.4   CONNECT_BY_COMBINE_SW            CONNECT_BY_COMBINE_SW            NO_CONNECT_BY_COMBINE_SW         QKSFM_ALL
                                            NO_CONNECT_BY_COMBINE_SW         CONNECT_BY_COMBINE_SW            QKSFM_ALL
********** ******************************** --------------------------------
count                                                                      2

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
10.2.0.5   CONNECT_BY_CB_WHR_ONLY           CONNECT_BY_CB_WHR_ONLY           NO_CONNECT_BY_CB_WHR_ONLY        QKSFM_TRANSFORMATION
                                            NO_CONNECT_BY_CB_WHR_ONLY        CONNECT_BY_CB_WHR_ONLY           QKSFM_TRANSFORMATION
           GBY_PUSHDOWN                     GBY_PUSHDOWN                     NO_GBY_PUSHDOWN                  QKSFM_ALL
                                            NO_GBY_PUSHDOWN                  GBY_PUSHDOWN                     QKSFM_ALL
********** ******************************** --------------------------------
count                                                                      4

10gR2ともなると、普段ほとんど使わないようなヒントばかりになってきますね！
これ以降はコメントのしようがないので、そのまま表示させます。

Oracle11g以降

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
11.1.0.6   ACCESS                           INDEX_RS_ASC                                                      QKSFM_INDEX_RS_ASC
                                            INDEX_RS_DESC                                                     QKSFM_INDEX_RS_DESC
                                            NLJ_BATCHING                     NO_NLJ_BATCHING                  QKSFM_EXECUTION
                                            NLJ_PREFETCH                     NO_NLJ_PREFETCH                  QKSFM_EXECUTION
                                            NO_NLJ_BATCHING                  NLJ_BATCHING                     QKSFM_EXECUTION
                                            NO_NLJ_PREFETCH                  NLJ_PREFETCH                     QKSFM_EXECUTION
           CHECK_ACL_REWRITE                CHECK_ACL_REWRITE                NO_CHECK_ACL_REWRITE             QKSFM_CHECK_ACL_REWRITE
           COST_XML_QUERY_REWRITE           COST_XML_QUERY_REWRITE           NO_COST_XML_QUERY_REWRITE        QKSFM_COST_XML_QUERY_REWRITE
           DB_VERSION                       DB_VERSION                                                        QKSFM_ALL
           DOMAIN_INDEX_FILTER              DOMAIN_INDEX_FILTER              NO_DOMAIN_INDEX_FILTER           QKSFM_CBO
           JOIN                             USE_MERGE_CARTESIAN                                               QKSFM_USE_MERGE_CARTESIAN
           MONITOR                          MONITOR                          NO_MONITOR                       QKSFM_ALL
                                            NO_MONITOR                       MONITOR                          QKSFM_ALL
           NO_CHECK_ACL_REWRITE             NO_CHECK_ACL_REWRITE             CHECK_ACL_REWRITE                QKSFM_CHECK_ACL_REWRITE
           NO_COST_XML_QUERY_REWRITE        NO_COST_XML_QUERY_REWRITE        COST_XML_QUERY_REWRITE           QKSFM_COST_XML_QUERY_REWRITE
           NO_DOMAIN_INDEX_FILTER           NO_DOMAIN_INDEX_FILTER           DOMAIN_INDEX_FILTER              QKSFM_CBO
           NO_LOAD                          NO_LOAD                                                           QKSFM_EXECUTION
           OUTER_JOIN_TO_INNER              NO_OUTER_JOIN_TO_INNER           OUTER_JOIN_TO_INNER              QKSFM_OUTER_JOIN_TO_INNER
                                            OUTER_JOIN_TO_INNER              NO_OUTER_JOIN_TO_INNER           QKSFM_OUTER_JOIN_TO_INNER
           PLACE_GROUP_BY                   NO_PLACE_GROUP_BY                PLACE_GROUP_BY                   QKSFM_PLACE_GROUP_BY
                                            PLACE_GROUP_BY                   NO_PLACE_GROUP_BY                QKSFM_PLACE_GROUP_BY
           RESULT_CACHE                     NO_RESULT_CACHE                  RESULT_CACHE                     QKSFM_EXECUTION
                                            RESULT_CACHE                     NO_RESULT_CACHE                  QKSFM_EXECUTION
           SUBQUERY_PRUNING                 NO_SUBQUERY_PRUNING              SUBQUERY_PRUNING                 QKSFM_CBO
                                            SUBQUERY_PRUNING                 NO_SUBQUERY_PRUNING              QKSFM_CBO
           USE_INVISIBLE_INDEXES            NO_USE_INVISIBLE_INDEXES         USE_INVISIBLE_INDEXES            QKSFM_INDEX
                                            USE_INVISIBLE_INDEXES            NO_USE_INVISIBLE_INDEXES         QKSFM_INDEX
           XMLINDEX_REWRITE                 NO_XMLINDEX_REWRITE              XMLINDEX_REWRITE                 QKSFM_XMLINDEX_REWRITE
                                            NO_XMLINDEX_REWRITE_IN_SELECT    XMLINDEX_REWRITE_IN_SELECT       QKSFM_XMLINDEX_REWRITE
                                            XMLINDEX_REWRITE                 NO_XMLINDEX_REWRITE              QKSFM_XMLINDEX_REWRITE
                                            XMLINDEX_REWRITE_IN_SELECT       NO_XMLINDEX_REWRITE_IN_SELECT    QKSFM_XMLINDEX_REWRITE
           XML_DML_RWT_STMT                 XML_DML_RWT_STMT                                                  QKSFM_XML_REWRITE
********** ******************************** --------------------------------
count                                                                     32

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
11.1.0.7   BIND_AWARE                       BIND_AWARE                       NO_BIND_AWARE                    QKSFM_CURSOR_SHARING
                                            NO_BIND_AWARE                    BIND_AWARE                       QKSFM_CURSOR_SHARING
           CHANGE_DUPKEY_ERROR_INDEX        CHANGE_DUPKEY_ERROR_INDEX                                         QKSFM_DML
           IGNORE_ROW_ON_DUPKEY_INDEX       IGNORE_ROW_ON_DUPKEY_INDEX                                        QKSFM_DML
           RETRY_ON_ROW_CHANGE              RETRY_ON_ROW_CHANGE                                               QKSFM_DML
********** ******************************** --------------------------------
count                                                                      5

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
11.2.0.1   APPEND_VALUES                    APPEND_VALUES                    NOAPPEND                         QKSFM_CBO
           COALESCE_SQ                      COALESCE_SQ                      NO_COALESCE_SQ                   QKSFM_COALESCE_SQ
                                            NO_COALESCE_SQ                   COALESCE_SQ                      QKSFM_COALESCE_SQ
           CONNECT_BY_ELIM_DUPS             CONNECT_BY_ELIM_DUPS             NO_CONNECT_BY_ELIM_DUPS          QKSFM_ALL
                                            NO_CONNECT_BY_ELIM_DUPS          CONNECT_BY_ELIM_DUPS             QKSFM_ALL
           DST_UPGRADE_INSERT_CONV          DST_UPGRADE_INSERT_CONV          NO_DST_UPGRADE_INSERT_CONV       QKSFM_ALL
                                            NO_DST_UPGRADE_INSERT_CONV       DST_UPGRADE_INSERT_CONV          QKSFM_ALL
           EXPAND_TABLE                     EXPAND_TABLE                     NO_EXPAND_TABLE                  QKSFM_TABLE_EXPANSION
                                            NO_EXPAND_TABLE                  EXPAND_TABLE                     QKSFM_TABLE_EXPANSION
           FACTORIZE_JOIN                   FACTORIZE_JOIN                   NO_FACTORIZE_JOIN                QKSFM_JOINFAC
                                            NO_FACTORIZE_JOIN                FACTORIZE_JOIN                   QKSFM_JOINFAC
           NO_SUBSTRB_PAD                   NO_SUBSTRB_PAD                                                    QKSFM_EXECUTION
           PLACE_DISTINCT                   NO_PLACE_DISTINCT                PLACE_DISTINCT                   QKSFM_DIST_PLCMT
                                            PLACE_DISTINCT                   NO_PLACE_DISTINCT                QKSFM_DIST_PLCMT
           STATEMENT_QUEUING                NO_STATEMENT_QUEUING             STATEMENT_QUEUING                QKSFM_PARALLEL
                                            STATEMENT_QUEUING                NO_STATEMENT_QUEUING             QKSFM_PARALLEL
           TRANSFORM_DISTINCT_AGG           NO_TRANSFORM_DISTINCT_AGG        TRANSFORM_DISTINCT_AGG           QKSFM_TRANSFORMATION
                                            TRANSFORM_DISTINCT_AGG           NO_TRANSFORM_DISTINCT_AGG        QKSFM_TRANSFORMATION
           XMLINDEX_SEL_IDX_TBL             XMLINDEX_SEL_IDX_TBL                                              QKSFM_ALL
********** ******************************** --------------------------------
count                                                                     19

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
11.2.0.2   TABLE_LOOKUP_BY_NL               NO_TABLE_LOOKUP_BY_NL            TABLE_LOOKUP_BY_NL               QKSFM_TABLE_LOOKUP_BY_NL
                                            TABLE_LOOKUP_BY_NL               NO_TABLE_LOOKUP_BY_NL            QKSFM_TABLE_LOOKUP_BY_NL
           USE_HASH_GBY_FOR_PUSHDOWN        NO_USE_HASH_GBY_FOR_PUSHDOWN     USE_HASH_GBY_FOR_PUSHDOWN        QKSFM_ALL
                                            USE_HASH_GBY_FOR_PUSHDOWN        NO_USE_HASH_GBY_FOR_PUSHDOWN     QKSFM_ALL
           XDB_FASTPATH_INSERT              NO_XDB_FASTPATH_INSERT           XDB_FASTPATH_INSERT              QKSFM_ALL
                                            XDB_FASTPATH_INSERT              NO_XDB_FASTPATH_INSERT           QKSFM_ALL
********** ******************************** --------------------------------
count                                                                      6

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
11.2.0.3   FULL_OUTER_JOIN_TO_OUTER         FULL_OUTER_JOIN_TO_OUTER         NO_FULL_OUTER_JOIN_TO_OUTER      QKSFM_CBO
                                            NO_FULL_OUTER_JOIN_TO_OUTER      FULL_OUTER_JOIN_TO_OUTER         QKSFM_CBO
           NO_SEMI_TO_INNER                 NO_SEMI_TO_INNER                 SEMI_TO_INNER                    QKSFM_CBO
           OUTER_JOIN_TO_ANTI               NO_OUTER_JOIN_TO_ANTI            OUTER_JOIN_TO_ANTI               QKSFM_CBO
                                            OUTER_JOIN_TO_ANTI               NO_OUTER_JOIN_TO_ANTI            QKSFM_CBO
           SEMI_TO_INNER                    SEMI_TO_INNER                    NO_SEMI_TO_INNER                 QKSFM_CBO
********** ******************************** --------------------------------
count                                                                      6

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
11.2.0.4   ENABLE_PARALLEL_DML              DISABLE_PARALLEL_DML             ENABLE_PARALLEL_DML              QKSFM_DML
                                            ENABLE_PARALLEL_DML              DISABLE_PARALLEL_DML             QKSFM_DML
********** ******************************** --------------------------------
count                                                                      2

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
12.1.0.1   ANTIJOIN                         CUBE_AJ                                                           QKSFM_JOIN_METHOD
           AUTO_REOPTIMIZE                  AUTO_REOPTIMIZE                  NO_AUTO_REOPTIMIZE               QKSFM_AUTO_REOPT
                                            NO_AUTO_REOPTIMIZE               AUTO_REOPTIMIZE                  QKSFM_AUTO_REOPT
           BATCH_TABLE_ACCESS_BY_ROWID      BATCH_TABLE_ACCESS_BY_ROWID      NO_BATCH_TABLE_ACCESS_BY_ROWID   QKSFM_EXECUTION
                                            NO_BATCH_TABLE_ACCESS_BY_ROWID   BATCH_TABLE_ACCESS_BY_ROWID      QKSFM_EXECUTION
           BITMAP_AND                       BITMAP_AND                                                        QKSFM_BITMAP_TREE
           CLUSTERING                       CLUSTERING                       NO_CLUSTERING                    QKSFM_CLUSTERING
                                            NO_CLUSTERING                    CLUSTERING                       QKSFM_CLUSTERING
           CLUSTER_BY_ROWID                 CLUSTER_BY_ROWID                 NO_CLUSTER_BY_ROWID              QKSFM_CBO
                                            NO_CLUSTER_BY_ROWID              CLUSTER_BY_ROWID                 QKSFM_CBO
           DATA_SECURITY_REWRITE_LIMIT      DATA_SECURITY_REWRITE_LIMIT      NO_DATA_SECURITY_REWRITE         QKSFM_DATA_SECURITY_REWRITE
                                            NO_DATA_SECURITY_REWRITE         DATA_SECURITY_REWRITE_LIMIT      QKSFM_DATA_SECURITY_REWRITE
           DECORRELATE                      DECORRELATE                      NO_DECORRELATE                   QKSFM_DECORRELATE
                                            NO_DECORRELATE                   DECORRELATE                      QKSFM_DECORRELATE
           GATHER_OPTIMIZER_STATISTICS      GATHER_OPTIMIZER_STATISTICS      NO_GATHER_OPTIMIZER_STATISTICS   QKSFM_DBMS_STATS
                                            NO_GATHER_OPTIMIZER_STATISTICS   GATHER_OPTIMIZER_STATISTICS      QKSFM_DBMS_STATS
           JOIN                             NO_USE_CUBE                      USE_CUBE                         QKSFM_USE_CUBE
                                            USE_CUBE                         NO_USE_CUBE                      QKSFM_USE_CUBE
           PARTIAL_JOIN                     NO_PARTIAL_JOIN                  PARTIAL_JOIN                     QKSFM_PARTIAL_JOIN
                                            PARTIAL_JOIN                     NO_PARTIAL_JOIN                  QKSFM_PARTIAL_JOIN
           PARTIAL_ROLLUP_PUSHDOWN          NO_PARTIAL_ROLLUP_PUSHDOWN       PARTIAL_ROLLUP_PUSHDOWN          QKSFM_PQ
                                            PARTIAL_ROLLUP_PUSHDOWN          NO_PARTIAL_ROLLUP_PUSHDOWN       QKSFM_PQ
           PQ_CONCURRENT_UNION              NO_PQ_CONCURRENT_UNION           PQ_CONCURRENT_UNION              QKSFM_PQ
                                            PQ_CONCURRENT_UNION              NO_PQ_CONCURRENT_UNION           QKSFM_PQ
           PQ_DISTRIBUTE_WINDOW             PQ_DISTRIBUTE_WINDOW                                              QKSFM_PQ
           PQ_FILTER                        PQ_FILTER                                                         QKSFM_PQ
           PQ_REPLICATE                     NO_PQ_REPLICATE                  PQ_REPLICATE                     QKSFM_PQ_REPLICATE
                                            PQ_REPLICATE                     NO_PQ_REPLICATE                  QKSFM_PQ_REPLICATE
           PQ_SKEW                          NO_PQ_SKEW                       PQ_SKEW                          QKSFM_PQ
                                            PQ_SKEW                          NO_PQ_SKEW                       QKSFM_PQ
           PX_FAULT_TOLERANCE               NO_PX_FAULT_TOLERANCE            PX_FAULT_TOLERANCE               QKSFM_PQ
                                            PX_FAULT_TOLERANCE               NO_PX_FAULT_TOLERANCE            QKSFM_PQ
           SEMIJOIN                         CUBE_SJ                                                           QKSFM_JOIN_METHOD
           USE_HIDDEN_PARTITIONS            USE_HIDDEN_PARTITIONS                                             QKSFM_PARTITION
           WITH_PLSQL                       WITH_PLSQL                                                        QKSFM_ALL
           ZONEMAP                          NO_ZONEMAP                       ZONEMAP                          QKSFM_ZONEMAP
                                            ZONEMAP                          NO_ZONEMAP                       QKSFM_ZONEMAP
********** ******************************** --------------------------------
count                                                                     37

VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
12.1.0.2   ADAPTIVE_PLAN                    ADAPTIVE_PLAN                    NO_ADAPTIVE_PLAN                 QKSFM_ADAPTIVE_PLAN
                                            NO_ADAPTIVE_PLAN                 ADAPTIVE_PLAN                    QKSFM_ADAPTIVE_PLAN
           ANSI_REARCH                      ANSI_REARCH                      NO_ANSI_REARCH                   QKSFM_ANSI_REARCH
                                            NO_ANSI_REARCH                   ANSI_REARCH                      QKSFM_ANSI_REARCH
           ELIM_GROUPBY                     ELIM_GROUPBY                     NO_ELIM_GROUPBY                  QKSFM_TRANSFORMATION
                                            NO_ELIM_GROUPBY                  ELIM_GROUPBY                     QKSFM_TRANSFORMATION
           INMEMORY                         INMEMORY                         NO_INMEMORY                      QKSFM_EXECUTION
                                            NO_INMEMORY                      INMEMORY                         QKSFM_EXECUTION
           INMEMORY_PRUNING                 INMEMORY_PRUNING                 NO_INMEMORY_PRUNING              QKSFM_EXECUTION
                                            NO_INMEMORY_PRUNING              INMEMORY_PRUNING                 QKSFM_EXECUTION
           USE_VECTOR_AGGREGATION           NO_USE_VECTOR_AGGREGATION        USE_VECTOR_AGGREGATION           QKSFM_VECTOR_AGG
                                            USE_VECTOR_AGGREGATION           NO_USE_VECTOR_AGGREGATION        QKSFM_VECTOR_AGG
           VECTOR_TRANSFORM                 NO_VECTOR_TRANSFORM              VECTOR_TRANSFORM                 QKSFM_VECTOR_AGG
                                            VECTOR_TRANSFORM                 NO_VECTOR_TRANSFORM              QKSFM_VECTOR_AGG
           VECTOR_TRANSFORM_DIMS            NO_VECTOR_TRANSFORM_DIMS         VECTOR_TRANSFORM_DIMS            QKSFM_VECTOR_AGG
                                            VECTOR_TRANSFORM_DIMS            NO_VECTOR_TRANSFORM_DIMS         QKSFM_VECTOR_AGG
           VECTOR_TRANSFORM_FACT            NO_VECTOR_TRANSFORM_FACT         VECTOR_TRANSFORM_FACT            QKSFM_VECTOR_AGG
                                            VECTOR_TRANSFORM_FACT            NO_VECTOR_TRANSFORM_FACT         QKSFM_VECTOR_AGG
********** ******************************** --------------------------------
count                                                                     18

332行が選択されました。

Oracle12cでは全部で332ものヒント句が存在していることがわかります。

SQL_FEATUREとは

上の一覧で一番右端のカラムは「SQL_FEATURE」となっていますが、これは何でしょうか？
マニュアルに掲載されている「V$SYS_OPTIMIZER_ENV」の記述を参照すると、手がかりが見つかります。

「SQL_FEATURE＝関連機能の制御ID」とあるので、オプティマイザというのは多くの機能の集合体であって、初期化パラメータやヒント句は個々の機能（SQL_FEATURE）を制御するものなのだろうということが推察できます。

SQL> SELECT SQL_FEATURE,ID,NAME,ISDEFAULT,VALUE,DEFAULT_VALUE FROM V$SYS_OPTIMIZER_ENV
  2  ORDER BY SQL_FEATURE,ID;

SQL_FEATURE                              ID NAME                                     ISD VALUE           DEFAULT_VALUE
-------------------------------- ---------- ---------------------------------------- --- --------------- -------------
QKSFM_ADAPTIVE_PLAN                     411 optimizer_adaptive_features              YES true            true

QKSFM_ALL                                11 cpu_count                                YES 2               2
                                         12 active_instance_count                    YES 1               1
                                         14 hash_area_size                           YES 131072          131072
                                         15 bitmap_merge_area_size                   YES 1048576         1048576
                                         16 sort_area_size                           YES 65536           65536
                                         17 sort_area_retained_size                  YES 0               0
                                         24 pga_aggregate_target                     YES 286720 KB       286720 KB
                                         35 parallel_query_mode                      YES enabled         enabled
                                         36 parallel_dml_mode                        YES disabled        disabled
                                         37 parallel_ddl_mode                        YES enabled         enabled
                                         38 optimizer_mode                           YES all_rows        all_rows
                                        101 workarea_size_policy                     YES auto            auto
                                        167 optimizer_secure_view_merging            YES true            true
                                        212 result_cache_mode                        YES MANUAL          MANUAL
                                        218 transaction_isolation_level              YES read_commited   read_commited
                                        257 is_recur_flags                           YES 0               0
                                        264 db_file_multiblock_read_count            YES 69              69
                                        275 total_cpu_count                          YES 2               2
                                        286 dst_upgrade_insert_conv                  YES true            true
                                        378 PMO_altidx_rebuild                       YES 0               0
                                        389 total_processor_group_count              YES 1               1

QKSFM_AUTO_REOPT                        353 optimizer_adaptive_reporting_only        YES false           false

QKSFM_CBO                                 2 parallel_execution_enabled               YES true            true
                                          9 optimizer_features_enable                YES 12.1.0.2        12.1.0.2
                                         13 parallel_threads_per_cpu                 YES 2               2
                                         48 cursor_sharing                           YES exact           exact
                                         66 optimizer_index_cost_adj                 YES 100             100
                                         67 optimizer_index_caching                  YES 0               0
                                        105 optimizer_dynamic_sampling               YES 2               2
                                        112 statistics_level                         YES typical         typical
                                        114 skip_unusable_indexes                    YES true            true
                                        228 optimizer_use_pending_statistics         YES false           false
                                        238 optimizer_capture_sql_plan_baselines     YES false           false
                                        239 optimizer_use_sql_plan_baselines         YES true            true
                                        441 optimizer_inmemory_aware                 YES true            true

QKSFM_COMPILATION                       466 inmemory_size                            YES 0               0

QKSFM_EXECUTION                         262 cell_offload_processing                  YES true            true
                                        267 cell_offload_compaction                  YES ADAPTIVE        ADAPTIVE
                                        268 cell_offload_plan_display                YES AUTO            AUTO
                                        453 inmemory_force                           YES default         default
                                        454 inmemory_query                           YES enable          enable

QKSFM_INDEX                             258 optimizer_use_invisible_indexes          YES false           false

QKSFM_PARTITION                         323 deferred_segment_creation                YES true            true

QKSFM_PQ                                245 parallel_degree_policy                   YES manual          manual
                                        246 parallel_degree                          YES 0               0
                                        247 parallel_min_time_threshold              YES 10              10
                                        256 parallel_query_default_dop               YES 0               0
                                        272 parallel_degree_limit                    YES 65535           65535
                                        273 parallel_force_local                     YES false           false
                                        274 parallel_max_degree                      YES 4               4
                                        289 parallel_autodop                         YES 0               0
                                        290 parallel_ddldml                          YES 0               0
                                        317 parallel_execution_message_size          YES 16384           16384
                                        369 parallel_degree_level                    YES 100             100
                                        432 parallel_dblink                          YES 0               0

QKSFM_STAR_TRANS                         50 star_transformation_enabled              YES false           false

QKSFM_TRANSFORMATION                     70 query_rewrite_enabled                    YES true            true
                                         71 query_rewrite_integrity                  YES enforced        enforced


59行が選択されました。

まとめ

アクセスパスを固定化するためにヒント句でチューニングするということはよく行われていると思います。

しかし、12cで332種類（11gR2でも約280個）ものヒントがあることを考えると、ヒント句によるチューニングはオプティマイザのほんの一部を制御しているに過ぎないことを理解すべきです。

しかも、バージョンによってヒント句の数は全然違う、つまりオプティマイザの内部仕様はどんどん変わっているので、以前のバージョンで最適であった実行計画が今のバージョンでも最適である保証はないと考えるのが自然なのではないでしょうか。

今回はここまで。

今週の名言

「宇宙でもっとも強い力は、幅広い興味である。」
アルベルト・アインシュタイン

oerrとは？

前回からのつながりで、今回もエラー・メッセージについて考えてみたいと思います。

oerrという非常に便利なツールがあります。「ORA-4031」について意味を調べたい時は、以下の要領でエラーメッセージの意味だけでなく、原因や対処についての説明をシェルから次のコマンドで確認することができます。

$ oerr ora 4031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool or Streams pool.
// *Action: If the shared pool is out of memory, either use the
// DBMS_SHARED_POOL package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// initialization parameters SHARED_POOL_RESERVED_SIZE and
// SHARED_POOL_SIZE.
// If the large pool is out of memory, increase the initialization
// parameter LARGE_POOL_SIZE.
// If the error is issued from an Oracle Streams or XStream process,
// increase the initialization parameter STREAMS_POOL_SIZE or increase
// the capture or apply parameter MAX_SGA_SIZE.

ORA-以外のエラーメッセージ、例えば「TNS-12541」も以下のように確認することができます。

$ oerr tns 12541
12541, 00000, "TNS:no listener"
// *Cause: The connection request could not be completed because the listener
// is not running.
// *Action: Ensure that the supplied destination address matches one of
// the addresses used by the listener - compare the TNSNAMES.ORA entry with
// the appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to
// go by way of an Interchange). Start the listener on the remote machine.

oerrコマンドの正体は？

oerrは実はシェル・スクリプトで、$ORACLE_HOME/bin 以下にあります。

$ which oerr
/u01/app/oracle/product/11.2.0.4/db_1/bin/oerr

スクリプトの内容は以下の110行から成るリストのとおりですが

• 35行目に定義されたfacility.lisから接頭語と製品名の関連を取得
• 93行目で製品名ディレクトリ配下にあるmsgファイルを指定
• 104行目以下のawkコマンドにより該当メッセージの取得

という仕様になっています。

$ cat /u01/app/oracle/product/11.2.0.4/db_1/bin/oerr
#!/bin/sh
#
# $Id: oerr.sh /st_buildtools_11.2.0/1 2012/02/28 11:07:18 mrmehta Exp $
# Copyright (c) 1994, 2012, Oracle and/or its affiliates. All rights reserved.
#
# Usage: oerr facility error
#
# This shell script is used to get the description and the cause and action
# of an error from a message text file when a list of error numbers are passed
# to it.  It supports different language environments and errors from different
# facilities.
#

#
# Turn on script tracing if, requested
[ "$ORACLE_TRACE" = "T" ] && set -x

#
# If ORACLE_HOME is not set, we will not be able to locate
# the message text file.
if [ ! "$ORACLE_HOME" ]
then
echo "ORACLE_HOME not set.  Please set ORACLE_HOME and try again." 1>&2
exit 1
fi

#
# Ignore user locale
LC_ALL=C
export LC_ALL

#
# Definition script "constants"
Facilities_File=$ORACLE_HOME/lib/facility.lis

#
# Check script usage
if [ "$#" != "2" ]
then
exec 1>&2
echo 'Usage: oerr facility error'
echo
echo 'Facility is identified by the prefix string in the error message.'
echo 'For example, if you get ORA-7300, "ora" is the facility and "7300"'
echo 'is the error.  So you should type "oerr ora 7300".'
echo
echo 'If you get LCD-111, type "oerr lcd 111", and so on.'
exit 1
fi

#
# Pickup the command line arguments
Facility="$1"
Platform=`/bin/uname`

case $Platform in
SunOS)
SedPath='/usr/xpg4/bin/sed'
;;
*)
SedPath='/bin/sed'
;;
esac
Code=`echo $2|$SedPath 's/^[0]*//'`

#
# Get the facility information from the oerr data file
Fac_Info=`grep -i "^${Facility}:" $Facilities_File 2> /dev/null`
if [ $? -ne 0 ]
then
echo "oerr: Unknown facility '$Facility'" 1>&2
exit 1
fi

#
# Parse the components from the Fac_Info string into Shell variables
eval `echo "$Fac_Info" | awk -F: '{
if (index ($3, "*") == 0)
printf ("Facility=%s\n", $3);
else
printf ("Facility=%s\n", $1);
printf ("Component=%s\n", $2);
}'`
if [ -z "$Facility" -o -z "$Component" ]
then
echo "oerr: Invalid facilities entry '$Fac_Info'" 1>&2
exit 1
fi

#
# The message file searched is always the US English file
Msg_File=$ORACLE_HOME/$Component/mesg/${Facility}us.msg
if [ ! -r $Msg_File ]
then
echo "oerr: Cannot access the message file $Msg_File" 1>&2
exit 1
fi

#
# Search the message file for the error code, printing the message text
# and any following comments which should give the cause and action for
# the error.
awk "BEGIN { found = 0; }
/^[0]*$Code/	{ found = 1; print ; next;}
/^\/\//		{ if (found) { print; } next; }
{ if (found) { exit; } }" $Msg_File

exit 0

例えば、$OEACLE_HOME/rdbms/mesg/oraus.msg ファイルの内容は以下のようになっています。

1865 00000, 00000, "normal, successful completion"
1866 // *Cause:  Normal exit.
1867 // *Action: None.
1868 00001, 00000, "unique constraint (%s.%s) violated"
1869 // *Cause: An UPDATE or INSERT statement attempted to insert a duplicatee key.
1870 //         For Trusted Oracle configured in DBMS MAC mode, you may see
1871 //         this message if a duplicate entry exists at a different levell.
1872 // *Action: Either remove the unique restriction or do not insert the key.
1873 /0002        reserved for v2 compatibility (null column)
1874 /0003        reserved for v2 compatibility (column value truncated)
1875 /0004        reserved for v2 compatibility (end-of-fetch)
1876 /0009        reserved for v2 compatibility
1877 /
1878 / 10 - 49 user session and session switching errors
1879 /
1880 00017, 00000, "session requested to set trace event"
1881 // *Cause:  The current session was requested to set a trace event by another
1882 //          session.
1883 // *Action: This is used internally; no action is required.

oerrは英語圏の人にとっては非常に便利なツールですが、Oracle 8.1.6を最後に各国語サポートへは対応されなくなっています。

エラー・メッセージだけでも日本語化してみる

原因や対処までは無理としても、せめてエラー・メッセージの意味だけでも日本語で表示させるツールを作ってみたいと思いましたので、以下のようなストアド・ファンクションを作ってみました。
試作版なのですべての接頭語には対応していませんし、エラーハンドリングも考慮していません。（存在しないメッセージ番号を指定すると「該当メッセージはありません」という表示をさせています。）

SQL> CREATE OR REPLACE FUNCTION show_errmsg (err_fac IN VARCHAR2,err_num IN PLS_INTEGER)
  2  RETURN VARCHAR2
  3  IS
  4   wrk_fac VARCHAR2(8);
  5   err_lan VARCHAR2(20) := 'japanese';
  6   err_msg VARCHAR2(512);
  7   i        PLS_INTEGER;
  8  BEGIN
  9   wrk_fac := UPPER(err_fac);
 10  CASE wrk_fac
 11     WHEN 'CRS'  THEN i := UTL_LMS.GET_MESSAGE(err_num,'crs',    err_fac,err_lan,err_msg);
 12     WHEN 'TNS'  THEN i := UTL_LMS.GET_MESSAGE(err_num,'network',err_fac,err_lan,err_msg);
 13     ELSE             i := UTL_LMS.GET_MESSAGE(err_num,'rdbms',  err_fac,err_lan,err_msg);
 14  END CASE;
 15  IF err_msg LIKE 'Message%not found%'
 16  THEN err_msg := wrk_fac || '-' || TO_CHAR(err_num,'FM00000') || ': 該当メッセージはありません';
 17  ELSE err_msg := wrk_fac || '-' || TO_CHAR(err_num,'FM00000') || ': ' || err_msg;
 18  END IF;
 19  RETURN err_msg;
 20  END;
 21  /

ファンクションが作成されました。

このファンクションの使用例は以下のようになります。

SQL> SELECT show_errmsg('ora',4031) ERROR_MESSAGE from dual;

ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
ORA-04031: 共有メモリーの%sバイトを割当てできません("%s"、"%s"、"%s"、"%s")

SQL> SELECT show_errmsg('crs',214) ERROR_MESSAGE from dual;

ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
CRS-00214: リソース'%s'を登録解除できません。

SQL> SELECT show_errmsg('&fac',&num) ERROR_MESSAGE from dual;
facに値を入力してください: rman
numに値を入力してください: 567
旧   1: SELECT show_errmsg('&fac',&num) ERROR_MESSAGE from dual
新   1: SELECT show_errmsg('rman',567) ERROR_MESSAGE from dual

ERROR_MESSAGE
--------------------------------------------------------------------------------
RMAN-00567: Recovery Managerで印刷できなかったエラー・メッセージがあります

SQL> /
facに値を入力してください: tns
numに値を入力してください: 12541
旧   1: SELECT show_errmsg('&fac',&num) ERROR_MESSAGE from dual
新   1: SELECT show_errmsg('tns',12541) ERROR_MESSAGE from dual

ERROR_MESSAGE
--------------------------------------------------------------------------------
TNS-12541: TNS: リスナーがありません。

SQL> /
facに値を入力してください: ora
numに値を入力してください: 2
旧   1: SELECT show_errmsg('&fac',&num) ERROR_MESSAGE from dual
新   1: SELECT show_errmsg('ora',2) ERROR_MESSAGE from dual

ERROR_MESSAGE
--------------------------------------------------------------------------------
ORA-00002: 該当メッセージはありません

SQL> /
facに値を入力してください: abc
numに値を入力してください: 1
旧   1: SELECT show_errmsg('&fac',&num) ERROR_MESSAGE from dual
新   1: SELECT show_errmsg('abc',1) ERROR_MESSAGE from dual

ERROR_MESSAGE
--------------------------------------------------------------------------------
ABC-00001: 該当メッセージはありません

UTL_LMS.GET_MESSAGEはどこからメッセージを取得しているのか？

oerrが参照していたmsgファイルと同じディレクトリに「msb」という拡張子が付いたバイナリファイルがあるのですが、これらのファイルの中にメッセージが格納されているようです。

それでは、日本語と英語メッセージが格納されているであろうファイルをRenameして、メッセージが取得できなくなるかどうか確認してみましょう。

$ cd $ORACLE_HOME/rdbms/mesg
$ mv oraja.msb oraja.msb.bak
$ mv oraus.msb oraus.msb.bak

SQL> SELECT show_errmsg('ora',4031) ERROR_MESSAGE from dual;

ERROR_MESSAGE
--------------------------------------------------------------------------------
ORA-04031: 該当メッセージはありません

やはり、msbファイルが関係していました。

oerrは単純なテキストファイルの内容を表示させる仕様でしたが、Oracle9i以降は英語以外の言語でエラー・メッセージを取得するインターフェースはUTL_LMS.GET_MESSAGEだけになりました。

msbファイルの中にはmsgファイルのような原因や対処に関する説明は書いてあるのでしょうか？こればかりはOracle社の中の人でなければわかりませんが、将来そのような情報が使えるようになるといいですね。

今日はここまで

今週の名言

「勇気とは不安を感じないことではない｡勇気とは不安をコントロールすることだ｡」
マーク・トウェイン

すみません、大嘘ついてました。(^^;

４月１２日の投稿ORAエラーはいくつある？でORAエラーの数を数えてみたのですが、結果が大間違いであることがわかりました。

間違いの内容は既に記事を訂正していますのでそちらを見ていただければよいと思いますが、１ヶ月以上経っているのにこの件に対して誰からも何のツッコミもなかったということは、このブログもまだまだだなという悲しい現実を再認識させられました。

でも、他の人に指摘される前に自分で見つけられて正直ホッとしているというのも事実ですので、今後も自分できちんと検証し自信を持って記事を書いていくという方針は貫いていきたいと思います。

UTL_LMSパッケージを使う

最初に結論を書きますが、Oracleのエラー・メッセージを取得するにはUTL_LMS.GET_MESSAGEファンクションを使います。

このファンクションはかなり使えるので後で他の応用も紹介しますが、以前紹介したORAエラーの数を数えるのは以下のPL/SQLスクリプトで可能です。（環境はOracle 11.2.0.4）

SQL> set serveroutput on
SQL> DECLARE
  2   err_msg LONG;
  3   err_cod PLS_INTEGER;
  4   i       PLS_INTEGER;
  5   cnt     NUMBER :=0;
  6  BEGIN
  7   FOR err_cod IN 1..99999 LOOP
  8    i := UTL_LMS.GET_MESSAGE (err_cod, 'rdbms', 'ora', 'japanese', err_msg);
  9    IF err_msg not like 'Message%not found%' THEN
 10  --   DBMS_OUTPUT.PUT_LINE ('ORA-' || TO_CHAR (err_cod, 'FM00000') || ': ' || err_msg);
 11     cnt := cnt +1;
 12    END IF;
 13   END LOOP;
 14   DBMS_OUTPUT.PUT_LINE('Nuber of Error Code : ' || cnt);
 15  END;
 16  /

Nuber of Error Code : 18414

PL/SQL procedure successfully completed.

このスクリプト例ではあえてコメントアウトしていますが、11行目のコメントを外すとメッセージが得られます。

「ORAエラーはいくつある？」という問いに対しては18,414個という結果を答えとして挙げておきます。
（お時間のある方、是非数えてみてください。）

応用編：CRSエラーの数を数えてみる

SQLERRMファンクションは、ORAエラーのみを表示します（しかも余計な番号も含む）が、UTL_LMS.GET_MESSAGEファンクションは第２引数と第３引数を変えると、Oracle RDBMS以外の製品が出力するエラー・メッセージを取得することができます。（第４引数を変えると表示する言語を選択することができます。）

例えば、Oracle Grid Infrastructureのエラー・メッセージの場合は「crs」と「crs」を指定して以下のように実行します。（ハイライト行に注目）

SQL> DECLARE
  2   err_msg LONG;
  3   err_cod PLS_INTEGER;
  4   i       PLS_INTEGER;
  5   cnt     NUMBER :=0;
  6  BEGIN
  7   FOR err_cod IN 1..99999 LOOP
  8    i := UTL_LMS.GET_MESSAGE (err_cod, 'crs', 'crs', 'japanese', err_msg);
  9    IF err_msg not like 'Message%not found%' THEN
 10  --   DBMS_OUTPUT.PUT_LINE ('CRS-' || TO_CHAR (err_cod, 'FM00000') || ': ' || err_msg);
 11     cnt := cnt +1;
 12    END IF;
 13   END LOOP;
 14   DBMS_OUTPUT.PUT_LINE('Nuber of Error Code : ' || cnt);
 15  END;
 16  /

Nuber of Error Code : 1778

PL/SQL procedure successfully completed.

CRSエラーは1,778個あることがわかりました。

今回はここまで

今週の名言

「幸せを与えてくれるのは、富でも豪華さでもなく、穏やかさと仕事である。」
トーマス・ジェファーソン

各カレンダーを比べてみる

Oracle Database 11gR2 で和暦を含む７つのカレンダーをサポートしていることは前回紹介しました。（12cではさらに１つ追加）
私たち日本人は西暦（グレゴリオ暦）と和暦しか馴染みがないのですが、他のカレンダーを使う機会はまずないので、それらでちょっと遊んでみたいと思います。

２０１５年１月のカレンダーで比較する

例として、２０１５年１月を各カレンダーで表示させてみたいと思います。
１月１日のデータは作成しているので２日から３１日までのデータを追加します。

SQL> INSERT INTO jcal VALUES (11,TO_DATE('20150102','YYYYMMDD'));

1 row created.

SQL> INSERT INTO jcal VALUES (12,TO_DATE('20150103','YYYYMMDD'));

1 row created.
....................................................................
SQL> INSERT INTO jcal VALUES (39,TO_DATE('20150130','YYYYMMDD'));

1 row created.

SQL> INSERT INTO jcal VALUES (40,TO_DATE('20150131','YYYYMMDD'));

1 row created.

SQL> COMMIT;

Commit complete.

それでは、６つのカレンダーを比較してみましょう。

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'YYYY.MM.DD', 'NLS_CALENDAR = ''Gregorian''')         "グレゴリオ暦"
  4  ,TO_CHAR(sdate,'YYYY.MM.DD', 'NLS_CALENDAR = ''Arabic Hijrah''')     "イスラム暦"
  5  ,TO_CHAR(sdate,'YYYY.MM.DD', 'NLS_CALENDAR = ''Persian''')           "ペルシャ暦"
  6  ,TO_CHAR(sdate,'eeyy.mm.dd', 'NLS_CALENDAR = ''Japanese Imperial''') "和暦"
  7  ,TO_CHAR(sdate,'eeyyy.mm.dd','NLS_CALENDAR = ''ROC Official''')      "台湾暦"
  8  ,TO_CHAR(sdate,'eeYYYY.MM.DD','NLS_CALENDAR = ''Thai Buddha''')      "タイ仏教暦"
  9  FROM jcal
 10  WHERE id = 1 OR id BETWEEN 11 AND 40
 11  ORDER BY sdate
 12 ;

 ID グレゴリオ暦  イスラム暦  ペルシャ暦  和暦          台湾暦             タイ仏教暦
--- ------------ ---------- ---------- ------------- ----------------- --------------------
  1 2015.01.01   1436.03.10 1393.10.11 平成27.01.01  中華民國104.01.01  พุทธศักราช2558.01.01
 11 2015.01.02   1436.03.11 1393.10.12 平成27.01.02  中華民國104.01.02  พุทธศักราช2558.01.02
 12 2015.01.03   1436.03.12 1393.10.13 平成27.01.03  中華民國104.01.03  พุทธศักราช2558.01.03
 13 2015.01.04   1436.03.13 1393.10.14 平成27.01.04  中華民國104.01.04  พุทธศักราช2558.01.04
 14 2015.01.05   1436.03.14 1393.10.15 平成27.01.05  中華民國104.01.05  พุทธศักราช2558.01.05
 15 2015.01.06   1436.03.15 1393.10.16 平成27.01.06  中華民國104.01.06  พุทธศักราช2558.01.06
 16 2015.01.07   1436.03.16 1393.10.17 平成27.01.07  中華民國104.01.07  พุทธศักราช2558.01.07
 17 2015.01.08   1436.03.17 1393.10.18 平成27.01.08  中華民國104.01.08  พุทธศักราช2558.01.08
 18 2015.01.09   1436.03.18 1393.10.19 平成27.01.09  中華民國104.01.09  พุทธศักราช2558.01.09
 19 2015.01.10   1436.03.19 1393.10.20 平成27.01.10  中華民國104.01.10  พุทธศักราช2558.01.10

 ID グレゴリオ暦  イスラム暦  ペルシャ暦  和暦          台湾暦             タイ仏教暦
--- ------------ ---------- ---------- ------------- ----------------- --------------------
 20 2015.01.11   1436.03.20 1393.10.21 平成27.01.11  中華民國104.01.11  พุทธศักราช2558.01.11
 21 2015.01.12   1436.03.21 1393.10.22 平成27.01.12  中華民國104.01.12  พุทธศักราช2558.01.12
 22 2015.01.13   1436.03.22 1393.10.23 平成27.01.13  中華民國104.01.13  พุทธศักราช2558.01.13
 23 2015.01.14   1436.03.23 1393.10.24 平成27.01.14  中華民國104.01.14  พุทธศักราช2558.01.14
 24 2015.01.15   1436.03.24 1393.10.25 平成27.01.15  中華民國104.01.15  พุทธศักราช2558.01.15
 25 2015.01.16   1436.03.25 1393.10.26 平成27.01.16  中華民國104.01.16  พุทธศักราช2558.01.16
 26 2015.01.17   1436.03.26 1393.10.27 平成27.01.17  中華民國104.01.17  พุทธศักราช2558.01.17
 27 2015.01.18   1436.03.27 1393.10.28 平成27.01.18  中華民國104.01.18  พุทธศักราช2558.01.18
 28 2015.01.19   1436.03.28 1393.10.29 平成27.01.19  中華民國104.01.19  พุทธศักราช2558.01.19
 29 2015.01.20   1436.03.29 1393.10.30 平成27.01.20  中華民國104.01.20  พุทธศักราช2558.01.20

 ID グレゴリオ暦  イスラム暦  ペルシャ暦  和暦          台湾暦             タイ仏教暦
--- ------------ ---------- ---------- ------------- ----------------- --------------------
 30 2015.01.21   1436.03.30 1393.11.01 平成27.01.21  中華民國104.01.21  พุทธศักราช2558.01.21
 31 2015.01.22   1436.04.01 1393.11.02 平成27.01.22  中華民國104.01.22  พุทธศักราช2558.01.22
 32 2015.01.23   1436.04.02 1393.11.03 平成27.01.23  中華民國104.01.23  พุทธศักราช2558.01.23
 33 2015.01.24   1436.04.03 1393.11.04 平成27.01.24  中華民國104.01.24  พุทธศักราช2558.01.24
 34 2015.01.25   1436.04.04 1393.11.05 平成27.01.25  中華民國104.01.25  พุทธศักราช2558.01.25
 35 2015.01.26   1436.04.05 1393.11.06 平成27.01.26  中華民國104.01.26  พุทธศักราช2558.01.26
 36 2015.01.27   1436.04.06 1393.11.07 平成27.01.27  中華民國104.01.27  พุทธศักราช2558.01.27
 37 2015.01.28   1436.04.07 1393.11.08 平成27.01.28  中華民國104.01.28  พุทธศักราช2558.01.28
 38 2015.01.29   1436.04.08 1393.11.09 平成27.01.29  中華民國104.01.29  พุทธศักราช2558.01.29
 39 2015.01.30   1436.04.09 1393.11.10 平成27.01.30  中華民國104.01.30  พุทธศักราช2558.01.30

 ID グレゴリオ暦  イスラム暦  ペルシャ暦  和暦          台湾暦             タイ仏教暦
--- ------------ ---------- ---------- ------------- ----------------- --------------------
 40 2015.01.31   1436.04.10 1393.11.11 平成27.01.31  中華民國104.01.31  พุทธศักราช2558.01.31

31 rows selected.

西暦と年だけが異なるもの、月日も異なるもの、いろいろな違いがあることがわかります。
タイ仏教暦などはなんて書いてあるかもわかりませんね。

各カレンダーの紀元を調べてみる

それでは、各カレンダーで「１年１月１日」というデータを作成して、西暦（グレゴリオ暦）で確認してみましょう。

イスラム暦

イスラム暦に関してはOracle Databaseでは「Arabic Hijrah(イスラム暦)」と「English Hijrah(英語版イスラム暦)」をサポートしていますが、両者の正確な違いは確認していません。
今回は「Arabic Hijrah(イスラム暦)」を使います。
ちなみにWikipediaでは次のように紹介されています。
イスラム暦（ヒジュラ暦）

SQL> ALTER SESSION SET NLS_CALENDAR='Arabic Hijrah';

Session altered.

SQL> INSERT INTO jcal VALUES (41,TO_DATE('00010101','YYYYMMDD'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'YYYY.MM.DD', 'NLS_CALENDAR = ''Gregorian''') "A.D."
  4  FROM jcal
  5  WHERE id = 41;

 ID A.D.
--- ----------
 41 0622.07.16

上記Wikipediaのリンクには「預言者ムハンマドがマッカからマディーナへ聖遷（ヒジュラ）したユリウス暦622年を「ヒジュラの年」と定めヒジュラ暦元年とする新たな暦を制定した。なお、ヒジュラがあったとされる正確な日付は同622年7月16日（ユリウス通日1948439日）である。」との記述があります。

ペルシャ暦

これまたWikipediaからの引用になりますが次のリンクに解説があります。
ペルシャ暦（イラン暦）

SQL> ALTER SESSION SET NLS_CALENDAR='Persian';

Session altered.

SQL> INSERT INTO jcal VALUES (42,TO_DATE('00010101','YYYYMMDD'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'YYYY.MM.DD', 'NLS_CALENDAR = ''Gregorian''') "A.D."
  4  FROM jcal
  5  WHERE id = 42;

 ID A.D.
--- ----------
 42 0622.03.21

紀元はヒジュラ暦と同じ年ですが、年初がグレゴリオ暦の３月２１日となっているところが特徴です。

台湾暦

台湾暦とは「民国紀元」と呼ばれ、中華民国が成立された１９１２年を紀元とする暦です。
民国紀元（Wikipedia）
中華民国は、１９１１年の辛亥革命の結果、アジアで史上初の共和制国家として成立しました。
「台湾暦」というくらいなので中国（中華人民共和国）でこの暦法は使用されておらず、西暦が用いられているそうです。

SQL> ALTER SESSION SET NLS_CALENDAR='ROC Official';

Session altered.

SQL> INSERT INTO jcal VALUES (43,TO_DATE('00010101','YYYYMMDD'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'YYYY.MM.DD', 'NLS_CALENDAR = ''Gregorian''') "A.D."
  4  FROM jcal
  5  WHERE id = 43;

 ID A.D.
--- ----------
 43 1912.01.01

ちなみに、民国紀元の年は日本の大正（元年から１５年のみ）あるいは北朝鮮の主体暦（チュチェ暦－金日成の生誕年が紀元）と偶然同じになっています。
北朝鮮のOracle使いは裏技で台湾暦を使っているかもしれません。（冗談）
また、台湾では「中華民國99年」から「中華民國100年」（西暦2011年）にかけて、年データを一部のシステムにおいて２桁で管理していたことによる、いわゆる「民国100年問題」というどこかで聞いたことがある問題が起きたそうです。

タイ仏教暦

これもWikipediaからの受け売りですが、タイ仏教暦は「仏滅紀元（Wikipedia）」とも呼ばれ、紀元はお釈迦様が入滅（死去）された年だそうです。
この年は宗教上の伝来によるものとされているようで、学術的に言われている年とは一致しないようですが、Oracleの仕様上はこの年が採用されています。

他の暦法と違って、（グレゴリオ暦）の紀元前から始まっているので、日付書式も若干異なっていることに注意してください。

SQL> ALTER SESSION SET NLS_CALENDAR='Thai Buddha';

Session altered.

SQL> INSERT INTO jcal VALUES (44,TO_DATE('00010101','YYYYMMDD'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'BC YYYY.MM.DD', 'NLS_CALENDAR = ''Gregorian''') sdate
  4  FROM jcal
  5  WHERE id = 44;

 ID SDATE
--- --------------------
 44 紀元前 0542.01.01

日付書式BC/ADについて

上の問い合わせで日付書式「BC」は「紀元前」と日本語で表示されましたが、これは以下のように「NLS_DATE_LANGUAGE」パラメータが「JAPANESE」であることによります。
このパラメータは「NLS_LANGUAGE」パラメータから導出され、さらに「NLS_LANGUAGE」パラメータは環境変数「NLS_LANG」の先頭に指定された言語によって決まります。

SQL> SELECT PARAMETER,VALUE FROM NLS_SESSION_PARAMETERS
  2  WHERE PARAMETER LIKE 'NLS_%LANGUAGE';

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   JAPANESE
NLS_DATE_LANGUAGE              JAPANESE

SQL> !env|grep NLS_LANG
NLS_LANG=Japanese_Japan.AL32UTF8

また、NLS_DATE_LANGUAGE=JAPANESEの環境においては、以下のように日付書式「BC」は日付書式「AD」と同じ結果を返す、つまり日付データが西暦０年よりも前であればどちらも「紀元前」と表示されるようです。

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'AD YYYY.MM.DD', 'NLS_CALENDAR = ''Gregorian''') sdate
  4  FROM jcal
  5  WHERE id = 44;

 ID SDATE
--- --------------------
 44 紀元前 0542.01.01

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'AD YYYY.MM.DD', 'NLS_CALENDAR = ''Gregorian''') sdate
  4  FROM jcal
  5  WHERE id = 43;

 ID SDATE
--- --------------------
 43 西暦 1912.01.01

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'BC YYYY.MM.DD', 'NLS_CALENDAR = ''Gregorian''') sdate
  4  FROM jcal
  5  WHERE id = 43;

 ID SDATE
--- --------------------
 43 西暦 1912.01.01

前回から２回に渡ってカレンダーでいろいろ遊んでみましたがいかがでしたでしょうか？

このように日付データはDATE型で格納しておけば、様々な日付書式が使えるだけでなく、「２月３０日」のような明らかに不正なデータが混入する危険を防ぐことができるため大変有益です。

ところが、未だに文字型８桁のようなフォーマットで日付データを保持しているシステムを見ることがあります。
どうしてそのような設計になるのか理由はわかりませんが、わずかなパフォーマンス上のメリットを追求するあまりデータの正確性を犠牲にするような設計は根本的に間違っていると思います。

終わり

今週の名言

「知識に対する投資は常に一番の利益を生み出す」
－ ベンジャミン・フランクリン －

Oracleと暦法

以下はOracleデータベースがサポートする暦法（カレンダー）です。

Oracle® Databaseグローバリゼーション・サポート・ガイド
11gリリース2 (11.2)
B56307-04
NLS_CALENDAR 参照

• Arabic Hijrah(イスラム歴)
• English Hijrah(英語版イスラム歴)
• Gregorian(グレゴリオ暦)
• Japanese Imperial(日本の元号暦)
• Persian(ペルシャ暦)
• ROC Official(台湾暦)
• Thai Buddha(タイ仏教暦)
• Ethiopian(エチオピア歴、12c〜）

こんなにも多くのカレンダーを使うことができるというのも驚きですが、我々日本人にとって元号歴がサポートされているというのは非常にありがたいことです。（でも、どの年号からサポートされているのでしょうか？まさか「大化」というとこはないでしょうが。）

昭和天皇が崩御された時、私は某官公庁で大型汎用計算機の仕事をしていました。（Oracleアーキテクチャをどのように理解するか 参照）

官公庁における報告書等は基本的に和暦が使用されますので、元号が「昭和」から「平成」に変わるということは非常に大変でした。

西暦から和暦に変換するファンクションを作成していたのですが、「平成01年」ではなくて「平成元年」と表示させてくれという仕様変更があり大慌てで修正したことを今でもよく覚えています。

その当時の苦労から比べると、今回紹介する和暦変換は非常に簡単ですので、是非業務に活用していただければと思います。

検証環境

今回はOracle11g R2(Oracle Linux6)の環境を使用します。
NLS_LANGは日本語表示の設定であることを確認します。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE	11.2.0.4.0	Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> !uname -rs
Linux 3.8.13-44.el6uek.x86_64

SQL> !env|grep NLS_LANG
NLS_LANG=Japanese_Japan.AL32UTF8

テーブル作成とデータ作成

日付データを格納する単純なテーブルを作成し、特定の日付データ（西暦）を挿入します。

SQL> CREATE TABLE jcal (id NUMBER,sdate DATE);

Table created.

SQL> INSERT INTO jcal VALUES (1,to_date('20150101','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (2,to_date('20141231','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (3,to_date('19890108','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (4,to_date('19890107','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (5,to_date('19261225','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (6,to_date('19261224','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (7,to_date('19120730','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (8,to_date('19120729','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (9,to_date('18680908','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (10,to_date('18680907','yyyymmdd'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  FROM jcal
  5  ORDER BY sdate
  6 ;

  ID A.D.
---- ----------
  10 1868.09.07
   9 1868.09.08
   8 1912.07.29
   7 1912.07.30
   6 1926.12.24
   5 1926.12.25
   4 1989.01.07
   3 1989.01.08
   2 2014.12.31
   1 2015.01.01

10 rows selected.

１０件のデータが作成されました。

和暦変換した日付を表示させる

とりあえず和暦変換した日付を表示させましょう。
ポイントは「NLS_CALENDAR=’Japanese Imperial’」の指定です。

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  ,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
  5  FROM jcal
  6  ORDER BY sdate
  7 ;
,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
 *
ERROR at line 4:
ORA-01863: この年は現行カレンダではサポートされていません

おっと！いきなりエラーとなってしまいました。

サポートされるのは、明治元年９月８日以降

明治天皇が即位されたのは、１８９８年９月８日（慶応４年９月８日）で、この日を「明治元年９月８日」としたということです。
また、遡って「慶応４年１月１日」を「明治元年１月１日」と正式に定めたそうです。（Wikipedia「明治」参照。）

「NLS_CALENDAR=’Japanese Imperial’」では、１８９８年１月１日から９月７日までを、和暦（明治元年）で表示させようとするとエラーになるようです。（当然「慶応」以前の元号はサポートしていません。）

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  ,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
  5  FROM jcal
  6  ORDER BY sdate
  7 ;
,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
 *
ERROR at line 4:
ORA-01863: この年は現行カレンダではサポートされていません

日付範囲を指定し直して再挑戦

今度は、「１８９８年９月７日」のデータを除外して再挑戦してみましょう。

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  ,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
  5  FROM jcal
  6  WHERE id < 10
  7  ORDER BY sdate
  8 ;

  ID A.D.       WAREKI-S
---- ---------- ----------
   9 1868.09.08 M01.09.08
   8 1912.07.29 M45.07.29
   7 1912.07.30 T01.07.30
   6 1926.12.24 T15.12.24
   5 1926.12.25 S01.12.25
   4 1989.01.07 S64.01.07
   3 1989.01.08 H01.01.08
   2 2014.12.31 H26.12.31
   1 2015.01.01 H27.01.01

9 rows selected.

エラーとなる和暦変換データがないので、クエリーは正常な結果を返します。
「eyy」の日付書式は「年号（短縮表記）＋年（２桁）」を示します。

年号（漢字表記）を表示させる

「eeyy」の日付書式は「年号（漢字表記）＋年（２桁）」を示します。

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  ,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
  5  ,TO_CHAR(sdate,'eeyy"年"mm"月"dd"日"','nls_calendar = ''Japanese Imperial''') "WAREKI-L"
  6  FROM jcal
  7  WHERE id < 10
  8  ORDER BY sdate
  9 ;

  ID A.D.       WAREKI-S   WAREKI-L
---- ---------- ---------- ------------------
   9 1868.09.08 M01.09.08  明治01年09月08日
   8 1912.07.29 M45.07.29  明治45年07月29日
   7 1912.07.30 T01.07.30  大正01年07月30日
   6 1926.12.24 T15.12.24  大正15年12月24日
   5 1926.12.25 S01.12.25  昭和01年12月25日
   4 1989.01.07 S64.01.07  昭和64年01月07日
   3 1989.01.08 H01.01.08  平成01年01月08日
   2 2014.12.31 H26.12.31  平成26年12月31日
   1 2015.01.01 H27.01.01  平成27年01月01日

9 rows selected.

応用編：「01年」を「元年」と表示させる

DECODE関数を使って、「01年」のみ「元年」と表示させてみます。

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  ,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
  5  ,DECODE(TO_CHAR(sdate,'yy"','nls_calendar = ''Japanese Imperial''')
  6        ,'01',TO_CHAR(sdate,'ee"元年"mm"月"dd"日"','nls_calendar = ''Japanese Imperial''')
  7        ,     TO_CHAR(sdate,'eeyy"年"mm"月"dd"日"','nls_calendar = ''Japanese Imperial''')) "WAREKI-L"
  8  FROM jcal
  9  WHERE id < 10
 10  ORDER BY sdate
 11 ;

  ID A.D.       WAREKI-S   WAREKI-L
---- ---------- ---------- ------------------
   9 1868.09.08 M01.09.08  明治元年09月08日
   8 1912.07.29 M45.07.29  明治45年07月29日
   7 1912.07.30 T01.07.30  大正元年07月30日
   6 1926.12.24 T15.12.24  大正15年12月24日
   5 1926.12.25 S01.12.25  昭和元年12月25日
   4 1989.01.07 S64.01.07  昭和64年01月07日
   3 1989.01.08 H01.01.08  平成元年01月08日
   2 2014.12.31 H26.12.31  平成26年12月31日
   1 2015.01.01 H27.01.01  平成27年01月01日

9 rows selected.

曜日も表示させる

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  ,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
  5  ,DECODE(TO_CHAR(sdate,'yy"','nls_calendar = ''Japanese Imperial''')
  6        ,'01',TO_CHAR(sdate,'ee"元年"mm"月"dd"日"','nls_calendar = ''Japanese Imperial''')
  7        ,     TO_CHAR(sdate,'eeyy"年"mm"月"dd"日"','nls_calendar = ''Japanese Imperial''')) "WAREKI-L"
  8  ,TO_CHAR(sdate,'day') day
  9  FROM jcal
 10  WHERE id < 10
 11  ORDER BY sdate
 12 ;

  ID A.D.       WAREKI-S   WAREKI-L           DAY
---- ---------- ---------- ------------------ --------
   9 1868.09.08 M01.09.08  明治元年09月08日    火曜日
   8 1912.07.29 M45.07.29  明治45年07月29日    月曜日
   7 1912.07.30 T01.07.30  大正元年07月30日    火曜日
   6 1926.12.24 T15.12.24  大正15年12月24日    金曜日
   5 1926.12.25 S01.12.25  昭和元年12月25日    土曜日
   4 1989.01.07 S64.01.07  昭和64年01月07日    土曜日
   3 1989.01.08 H01.01.08  平成元年01月08日    日曜日
   2 2014.12.31 H26.12.31  平成26年12月31日    水曜日
   1 2015.01.01 H27.01.01  平成27年01月01日    木曜日

9 rows selected.

セッション単位で暦法を変更する

NLS_CALENDARは、「TO_CHAR」SQL関数だけでなく、ALTER SESSION、初期化パラメータおよび環境変数で指定することが可能です。
ここでは、ALTER SESSIONで変更する方法を示します。

SQL> ALTER SESSION SET NLS_CALENDAR='Japanese Imperial';

Session altered.

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'eyy.mm.dd') "WAREKI-S"
  4  ,DECODE(TO_CHAR(sdate,'yy"')
  5        ,'01',TO_CHAR(sdate,'ee"元年"mm"月"dd"日"')
  6        ,     TO_CHAR(sdate,'eeyy"年"mm"月"dd"日"')) "WAREKI-L"
  7  ,TO_CHAR(sdate,'day') day
  8  FROM jcal
  9  WHERE id < 10
 10  ORDER BY sdate
 11 ;

  ID WAREKI-S   WAREKI-L           DAY
---- ---------- ------------------ --------
   9 M01.09.08  明治元年09月08日    火曜日
   8 M45.07.29  明治45年07月29日    月曜日
   7 T01.07.30  大正元年07月30日    火曜日
   6 T15.12.24  大正15年12月24日    金曜日
   5 S01.12.25  昭和元年12月25日    土曜日
   4 S64.01.07  昭和64年01月07日    土曜日
   3 H01.01.08  平成元年01月08日    日曜日
   2 H26.12.31  平成26年12月31日    水曜日
   1 H27.01.01  平成27年01月01日    木曜日

9 rows selected.

このテーマは１回限りの小ネタのつもりでしたが、ちょっと面白くなってきたので次回も遊んでみたいと思います。

続く

今週の名言

「無知を恐れてはいけない。偽りの知識を恐れよ。」
ブレーズ・パスカル

「独立・依存エンティティ」をIdentity Columnで実装する

それでは、前回紹介した「独立・依存エンティティ（テーブル）」をIdentity Columnで実装してみましょう。
まずは、親表であるT_PAR表から作成します。Identity Columnのオプションは「ALWAYS」です。
忘れずに主キーも作成します。

SQL> CREATE TABLE t_par
  2  (pid  NUMBER GENERATED ALWAYS AS IDENTITY
  3  ,pval VARCHAR2(10)
  4  );

表が作成されました。

SQL> ALTER TABLE t_par ADD CONSTRAINT pk_t_par PRIMARY KEY (pid);

表が変更されました。

次に子表となるT_CHD表を作成します。親表とほぼ同じですが、PID列に参照整合性制約（外部キー）を作成するのがポイントです。

SQL> CREATE TABLE t_chd
  2  (cid  NUMBER GENERATED ALWAYS AS IDENTITY
  3  ,pid  NUMBER NOT NULL
  4  ,cval VARCHAR2(10)
  5  );

表が作成されました。

SQL> ALTER TABLE t_chd ADD CONSTRAINT pk_t_chd PRIMARY KEY (cid);

表が変更されました。

SQL> ALTER TABLE t_chd ADD CONSTRAINT fk_t_pid FOREIGN KEY (pid)
  2                        REFERENCES t_par(pid);

表が変更されました。

このタイミングで、表とともに作成された順序の名前も確認しておきましょう。

SQL> SELECT TABLE_NAME,COLUMN_NAME,SEQUENCE_NAME
  2  FROM USER_TAB_IDENTITY_COLS
  3  ORDER BY TABLE_NAME;

TABLE_NAME      COLUMN_NAME     SEQUENCE_NAME
--------------- --------------- ---------------
T_CHD           CID             ISEQ$$_91944
T_PAR           PID             ISEQ$$_91941

レコード作成スクリプト

連続したレコードを作成する以下のPL/SQLスクリプトを実行します。
ハイライト行に示すように、上で確認した順序名を直接指定しています。

SQL> get ins.sql
  1  DECLARE
  2   i NUMBER;
  3   j NUMBER;
  4  BEGIN
  5   FOR i IN 1..5 LOOP
  6    INSERT INTO t_par(pval) VALUES ('Parent');
  7    FOR j IN 1..5 LOOP
  8     INSERT INTO t_chd(pid,cval) VALUES (ISEQ$$_91941.currval,'Child');
  9    END LOOP;
 10    COMMIT;
 11   END LOOP;
 12* END;
SQL> @ins.sql

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

SQL> SELECT * FROM t_par;

       PID PVAL
---------- ----------
         1 Parent
         2 Parent
         3 Parent
         4 Parent
         5 Parent

SQL> SELECT * FROM t_chd;

       CID        PID CVAL
---------- ---------- ----------
         1          1 Child
         2          1 Child
         3          1 Child
................................
        23          5 Child
        24          5 Child
        25          5 Child

25行が選択されました。

親レコード１行毎に子レコードが５行作成され、全部で親レコード５行、子レコード２５行が作成されていることがわかります。

共有プールをフラッシュさせてみる

順序が生成したシーケンス番号は、共有プール上に「CACHE_SIZE」パラメータで指定された数だけキャッシュされています。
デフォルト値は２０となっており、次に生成されるシーケンス番号は以下のSQL文の「LAST_NUMBER」列で確認することができます。

SQL> SELECT SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
  2  FROM USER_SEQUENCES;

SEQUENCE_NAME   INCREMENT_BY CACHE_SIZE LAST_NUMBER
--------------- ------------ ---------- -----------
ISEQ$$_91941               1         20          21
ISEQ$$_91944               1         20          41

ここで、共有プールをフラッシュさせるとどのようになるかを確認します。

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

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

再度、レコード作成スクリプトを実行し新たな２５行をInsertします。

SQL> @ins.sql

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

SQL> SELECT * FROM t_par;

       PID PVAL
---------- ----------
         1 Parent
         2 Parent
         3 Parent
         4 Parent
         5 Parent
        21 Parent
        22 Parent
        23 Parent
        24 Parent
        25 Parent

10行が選択されました。

SQL> SELECT * FROM t_chd;

       CID        PID CVAL
---------- ---------- ----------
         1          1 Child
         2          1 Child
         3          1 Child
................................
        23          5 Child
        24          5 Child
        25          5 Child
        41         21 Child
        42         21 Child
        43         21 Child
................................
        63         25 Child
        64         25 Child
        65         25 Child

50行が選択されました。

上で確認した「LAST_NUMBER」列の値にシーケンス番号が飛んで採番されていることがわかります。（PID=21,CID=41）
飛び番は発生しますが重複が発生しているわけではないので、問題なくInsertできます。

レコード作成スクリプト改

「ISEQ$$_91941」のようにシステムが生成した順序の名前は、プログラムの中で使用するにはあまり適切ではありません。
できれば以下のハイライト行のように、開発者が認識できる順序の名前を使用できることが望ましいと考えます。

SQL> get ins1.sql
  1  DECLARE
  2   i NUMBER;
  3   j NUMBER;
  4  BEGIN
  5   FOR i IN 1..5 LOOP
  6    INSERT INTO t_par(pval) VALUES ('Parent');
  7    FOR j IN 1..5 LOOP
  8     INSERT INTO t_chd(pid,cval) VALUES (SEQ_PAR.currval,'Child');
  9    END LOOP;
 10    COMMIT;
 11   END LOOP;
 12* END;

順序は改名できない

テーブル名は「ALTER TABLE <テーブル名> RENAME <新テーブル名>」で改名することができます。
しかし、残念ながら順序は「ALTER SEQUENCE」文で名称を変更することはできません。

改名はできませんが、シノニムを作成することで「別名」を使用することができます。
順序名を再確認し、早速シノニムを作成してみましょう。

SQL> SELECT SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
  2  FROM USER_SEQUENCES;

SEQUENCE_NAME   INCREMENT_BY CACHE_SIZE LAST_NUMBER
--------------- ------------ ---------- -----------
ISEQ$$_91941               1         20          41
ISEQ$$_91944               1         20          81

SQL> CREATE SYNONYM seq_par FOR ISEQ$$_91941;

シノニムが作成されました。

レコード作成スクリプト改を実行する

それでは、順序名をシノニムに置き換えたスクリプトを実行してみます。

SQL> @ins1.sql

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

SQL> SELECT * FROM t_par;

       PID PVAL
---------- ----------
         1 Parent
         2 Parent
         3 Parent
         4 Parent
         5 Parent
        21 Parent
        22 Parent
        23 Parent
        24 Parent
        25 Parent
        26 Parent
        27 Parent
        28 Parent
        29 Parent
        30 Parent

15行が選択されました。

SQL> SELECT * FROM t_chd;

       CID        PID CVAL
---------- ---------- ----------
         1          1 Child
         2          1 Child
         3          1 Child
................................
        63         25 Child
        64         25 Child
        65         25 Child
        66         26 Child
        67         26 Child
        68         26 Child
................................
        88         30 Child
        89         30 Child
        90         30 Child

75行が選択されました。

問題なく、新規レコードが作成されました。

もし、Identity Columnを使用しなければ

Oracle12c以前の環境で、同様のレコード作成スクリプトを書くと以下のようになります。
ハイライト行を冗長と見るか否かがIdentity Columnを使う判断基準となるのではないでしょうか？

SQL> get ins2.sql
  1  DECLARE
  2   i NUMBER;
  3   j NUMBER;
  4  BEGIN
  5   FOR i IN 1..5 LOOP
  6    INSERT INTO t_par(pid,pval) VALUES (SEQ_PAR.nextval,'Parent');
  7    FOR j IN 1..5 LOOP
  8     INSERT INTO t_chd(cid,pid,cval) VALUES (SEQ_CHD.nextval,SEQ_PAR.currval,'Child');
  9    END LOOP;
 10    COMMIT;
 11   END LOOP;
 12* END;

Identity Columnは使える機能か？

私がIdentity Columnという新機能を知った時、今回検証した親子関係のテーブルにおいて問題なく使用できるのであれば、プログラムを簡単にすることで開発効率と品質を向上させることができる注目すべき機能である予感がしました。

上で確認したように、一旦テーブルを作成して順序名を確認してからシノニム（別名）を作成するという、ちょっと面倒な手順を踏めば何とか使えそうな気がしますが、このままでは今一な感じもします。

せめて、以下のようにテーブル作成時に任意の順序名を指定できる仕様であればよいのですが。。。

SQL> CREATE TABLE t_par
  2  (pid  NUMBER GENERATED ALWAYS AS IDENTITY "SEQ_PAR"
  3  ,pval VARCHAR2(10)
  4  );

Identity Columnは、現時点では他RDBMSからの移行を容易にするために実装された機能だと認識しています。

しかし、将来的により使いやすいように改良されることを期待しつつ、このシリーズを終えたいと思います。

終わり

今週の名言

「幸福になる秘訣は快楽を得ようとひたすらに努力することではなく、努力そのもののうちに快楽を見出すことである。」
アンドレ・ジット

マスタ/ディテール関係の実装を考える

今回は検証の前にデータ構造設計の話をしておきたいと思います。

売上－売上明細のように１対多のカーディナリティを持つ２つのテーブル（正確にはエンティティと表現すべきですが、物理的に実装するところまでを述べるのでテーブルと呼称しつつ、エンティティという表現も適宜使い分けることにします。）のリレーションシップをマスタ/ディテール関係と呼んだりすることがあります。

簡単に言うと「親子関係」であり、ER図（IE表記）で表現すると以下のようになります。（図１）

図１

• 親表（T_PAR）の主キー（PK）はPIDであり、子表（T_CHD）の主キーの一部を成すことで親子関係を表現することができる。
• この図では子表（T_CHD）の主キー（PK）は「PID + CSEQ（連番）」であり、ユニーク（一意）性を担保する必要がある。
• IE表記において、子表を角の丸い四角形で表現した場合、親表に対する「従属エンティティ」あるいは「依存エンティティ」であることを意味する。
• つまり、子表の主キーの一部であるPIDは必ず存在する必要がある。（Not Null制約）

この親子関係は、次のようにも表現することができます。（図２）

図２

• 子表（T_CHD）の主キー（PK）はCIDのみであり、このカラムだけで一意性を担保できる。
• 主キー項目以外に、子表には親表のPID（外部キー、FK）を持つ必要があり、かつこのカラムはNot Nullである必要がある。
• 子表を角がある四角形で表現した場合、「独立エンティティ」あるいは「非依存エンティティ」であることを意味する。
• つまり、PID列がNullであっても子レコードは存在できるが、前述のようにPID列は必ずNot Nullであるので論理的に従属関係にある。

今検証しているIdentity Columnを活用してマスタ/ディテール関係を実装しようとすると、必然的に図２の形になります。

従属的な親子関係を表現するためには必ずしも図１のような「従属エンティティ」を使う必要はないと思います。

子レコードが親レコードに依存しているということと、主キーの一部に親レコードの主キー値を持たなければならないというのは必ずしも同じでないということです。

重要なのは「親レコードを指す主キー値がNullであってはならない。」ということなのです。

親－子－孫関係を考える

図１、図２どちらでもよい、というのはある意味悩ましい事実です。

そこでこの問題をより掘り下げるために、親－子－孫の関係を考えてみましょう。

従属エンティティは先祖をたどることができる

図３

図３を見れば明らかですが、従属エンティティは孫エンティティから先祖である親エンティティを特定することができます。つまり、主キーに「PID」というカラムを持つテーブルは、すべて親エンティティに従属することがわかります。

例えばPID単位でパーティショニングを行うような場合、これは大きなメリットと言えるかもしれません。

ただし、主キーが複数のカラムで構成されるデメリットも当然あります。親エンティティの主キー構成が万一変更されるような場合、従属するすべてのエンティティに影響が及びます。

主キーは本来変更されない前提で設計されるのですが、未来永劫変更されない保証はありません。

また、設計の初期段階で親エンティティの主キー構成がなかなか決まらないと子エンティティの設計もなかなか決まらないという恐れもあります。（こちらの方が多いかもしれません。）

独立エンティティは構造がシンプル

図４

一方、独立エンティティは１つの主キー項目とそれ以外の項目というデータ構造は、親・子・孫で変わることはありません。
主キー項目は文字通りID（IDentity）項目であり、行を一意に識別することができます。そしてOracleデータベースの場合、シーケンス・オブジェクト（Sequence、順序）が生成する重複のない値を使用します。

この図をよく見ると、孫エンティティには親エンティティの主キーであるPIDを保持するカラムがありません。従って自分の先祖を簡単にたどることができません。

しかし、これを単純にデメリットと捉えることは間違いだと思います。実際にクエリを記述する場合、ジョイン（結合）するテーブルは２つだけですので、例えば子と孫テーブルをジョインする場合、その上の親テーブルを意識する必要はありません。ジョインの結果得られた結果セットと親テーブルをジョインするからです。

むしろ孫テーブルにPIDを持つと、親・孫という間違ったジョインを記述してしまう恐れがあります。そのような場合重複行が発生してしまい、無理矢理distinctで重複行を排除するようなことをしてしまうのです。これにより不要なFull Scanが発生するようなこととなり、ミスをミスで隠蔽するという最悪のSQL文を書いてしまっていたというケースをよく見かけます。

従属エンティティのメリットとしてパーティショニングがたやすい、ということを挙げましたが独立エンティティでも工夫次第ではパーティション化をすることができます。これは簡単な発想で解決できますので、是非考えてみてください。

「独立・依存」エンティティをIdentity Columnで実装する

独立した子エンティティでありながら外部キー項目をNot Nullとする設計を「独立・依存エンティティ」と呼びたいと思います。

一概に決め付けることは厳に慎むべきですが、設計時の混乱や運用中の性能問題を数多く見てきた経験から、この「独立・依存エンティティ」の考え方は有効な解決策となり得ると実感しています。

実際にデータ構造のリファクタリングを成功させた例では、この基本的な考え方に沿って設計を進めました。

ID列は複数のカラムからなる主キーとは違い、代替キーとかサロゲートキーと呼ばれます。何が何でもサロゲートキーにしなければならないと主張するつもりはありません。しかし、どんなキーが自然にユニークとなる識別キー（ナチュラルキー）になるかはよく考える必要があります。

私は、世の中の設計における混乱は、識別キーと検索キーを混同していることによることも多いと思います。

。。。

と前置きのつもりで書いていたら長くなってしまいましたので、「独立・依存エンティティ」をIdentity Columnで実装するというテーマの検証は次回に回したいと思います。

結論から言うと、この機能はまだまだイケてないと思うこともあるので、その辺の紹介も含め次回で検証したいと思います。

続く