オラクルのバージョンや環境によってSQLクエリの動きを簡単に変える方法

はじめに

オラクルユーザであれば、ディクショナリビューやダイナミックビューを参照するSQLクエリのスクリプトをたくさん持ってると思います。かくゆう私も数百にのぼる独自のSQLスクリプトを駆使してDBAやってます。まぁ、それはともかくとしてこのディクショナリビューやダイナミックビューなんですが、オラクルのバージョンがあがるたびに新たなカラムが追加されます。新しいカラムは非常に有用なことが多いのでこれを表示させるようスクリプトを変更したりするわけですが、そうするとそのスクリプトは古いバージョンのデータベースではエラーになってしまいます。結果、バージョン毎にスクリプトをコピーしてスクリプトの数が異常に増えたりバージョン毎の使い分けが面倒になったりしてないでしょうか？

実は意外と簡単な方法で、マルチバージョン対応SQLスクリプトが作れます。以下その方法です。

定義変数の設定

それではまず、マルチバージョン対応にしたいスクリプトの先頭に下記の定義変数設定SQLを追加するか、別ファイルに保存して呼び出します。一番良いのはlogin.sqlに入れておくことですね。

定義変数の設定

set termout off
COL DB10L NEW_VALUE _DB10L NOPRINT
COL DB11L NEW_VALUE _DB11L NOPRINT
COL DB11U NEW_VALUE _DB11U NOPRINT
COL DB12U NEW_VALUE _DB12U NOPRINT
select
    case when &_O_RELEASE >= 1100000000 then '--' end DB10L, -- 10g以下 
    case when &_O_RELEASE >= 1200000000 then '--' end DB11L, -- 11g以下
    case when &_O_RELEASE <  1100000000 then '--' end DB11U, -- 11g以上
    case when &_O_RELEASE <  1200000000 then '--' end DB12U  -- 12g以上
from dual;
COL DB10L CLEAR
COL DB11L CLEAR
COL DB11U CLEAR
COL DB12U CLEAR
set termout on

これは、何をしてるかといえば、適合するバージョンの変数にはNULL、適合しないバージョンの変数には「--」を設定してるだけです。適合しないほうが「--」なのがミソです。ちなみに、_O_RELEASEは、データベースのバージョン情報が格納されているSQL*PLUSの事前定義変数です。

例えば、11gで実行すると以下のように設定されます。SQL*Plusのdefineコマンドで確認できます。

SQL> define
（略）
DEFINE _O_RELEASE      = "1102000400" (CHAR)   <--- 11.2.0.4 (事前定義)
DEFINE _DB10L          = "--" (CHAR)           <--- 10g以下: FALSE
DEFINE _DB11L          = "" (CHAR)             <--- 11g以下: <TRUE>
DEFINE _DB11U          = "" (CHAR)             <--- 11g以上: <TRUE>
DEFINE _DB12U          = "--" (CHAR)           <--- 12c以上: FALSE

マルチバージョンクエリの作成

次にバージョンによって動きを変えたいSQLスクリプトに少し手を加えます。以下のように特定のバージョン以上にしか存在しないカラム名のラインの先頭に上記の適応する定義変数を置いていきます。

バージョンによるカラムの追加

select   INST_ID
        ,CHILD_NUMBER
        ,OUTLINE_CATEGORY ,SQL_PROFILE
&_DB11U ,SQL_PATCH ,SQL_PLAN_BASELINE                  -- 11g以上
&_DB12U ,IS_REOPTIMIZABLE ,IS_RESOLVED_ADAPTIVE_PLAN   -- 12c以上
from    v$sql 
where   sql_id = '&SQL_ID';

上記の例では、V$SQLテーブルのカラムのうち、SQL_PATCH等いくつかは11g以上にしか存在しないため、&_DB11Uを配置しています。同様にIS_REOPTIMIZABLE等は12c以上のため&_DB12Uになっています。変更点はこれだけです。これで上記のスクリプトは10g, 11g, 12cを区別するマルチバージョン対応になりました。

動きは説明するまでもないと思いますが、適合しないバージョンの変数には「--」が格納されていますので、その行はコメントと見なされ実行時にスキップされるというわけです。非常に簡単なトリックですね。

応用すればカラムの追加だけでなく切替えもできます。以下の例では、10gではカラムDEF_TAB_COMPRESSION、11g以上ではCOMPRESS_FORを同じカラム位置に出力します。

バージョンによるカラムの切替

select
        tablespace_name,
&_DB10L DEF_TAB_COMPRESSION
&_DB11U COMPRESS_FOR
        default_compression
from    dba_tablespaces;

コメントによるSQLコードの切り替えなので、基本的にどんなことでもできます。ただし、SQL*Plusの仕様としてコマンド記述が開始される前に定義変数を使用することはできません。したがって、SELECTの前に定義変数をおいてクエリそのものをスキップさせることはできないことになります。

上記の制約により、あるバージョン以上にのみ存在するテーブルにアクセスする場合は、FROMを含むSELECT以降すべてを切り替えて対処します。以下の例では、対象のディクショナリービューが11g以上にしか存在しないため、10gではDUALにアクセスしてお茶を濁しています。

バージョンによるテーブルの切替

prompt Users with default password;
select
&_DB10L 'N/A' nop from dual
&_DB11U username from DBA_USERS_WITH_DEFPWD
;

ある程度複雑な切り替えも簡単にできます。以前の投稿で紹介したDBMSHSXP_SQL_PROFILE_ATTRへのクエリですが、10gと11gではアクセス方法が大きく異なります。

キャッシュやAWRから任意の実行計画でSQLプロファイルを簡単に作成する方法

-- 10gのみ
select ATTR_VALUE OUTLINE_HINT
from   DBMSHSXP_SQL_PROFILE_ATTR
where  profile_name = '&SQLPROFILE_NAME'
order by ATTR_NUMBER;

-- 11g以上
SELECT OUTLINE_HINT
FROM   DBMSHSXP_SQL_PROFILE_ATTR,
       xmltable('//hint' passing xmltype(comp_data) columns outline_hint varchar2(100) path 'text()')
WHERE  profile_name = '&SQLPROFILE_NAME';

マルチバージョンにするとこんな風になります。

SELECT
&_DB10L  ATTR_VALUE
         OUTLINE_HINT
FROM     DBMSHSXP_SQL_PROFILE_ATTR
&_DB11U ,xmltable('//hint' passing xmltype(comp_data) columns outline_hint varchar2(100) path 'text()')
WHERE    profile_name = '&SQLPROFILE_NAME'
&_DB10L  order by ATTR_NUMBER
;

SQL内の文字列やパラメータをバージョンによって変更する場合は、もっと単純に直接バージョン定義変数を使用できます。以下は、12cのときだけ"ADAPTIVE"をフォーマットパラメーターに追加しています。これも意外と便利です。

パラメータの変更

select * 
from table(dbms_xplan.display_cursor(null, null, 
            'ALLSTATS LAST' || decode(sign(&_O_RELEASE - 1200000000), 1, ' ADAPTIVE'));

バージョン以外への応用

同様に、データベース名や接続ユーザで動きを変えることができるのも想像に難くないでしょう。その場合はテーブル等から情報を得て、定義変数にセットすればいいだけです。

例えば以下は、ログイン時にセッションやデータベース情報を表示するためにLOGIN.SQLに入れているクエリですが、ログインしたアカウントがSELECT ANY DICTIONARY権限を持っていれば詳細な情報を、そうでなければSYS_CONTEXTから簡単な情報をとってきています。

まぁ細かいことを言えば、初期パラメータや個別権限次第ではダイナミックパフォーマンスビューにアクセスできるので完璧ではないですが、権限がなくてもエラーを出さないという意味では結構使えてます。

権限の有無によるクエリの変更

set termout off
col anydict new_value _ANYDICT noprint
select '--' ANYDICT 
from   session_privs 
where  PRIVILEGE = 'SELECT ANY DICTIONARY'
having count(*) = 0;
col anydict clear
set termout on

select 
           to_number(sys_context('userenv', 'sid')) sid
&_ANYDICT ,serial#
          ,g.global_name
          ,sys_context('userenv', 'instance_name') instance
          ,sys_context('userenv', 'session_user') db_user
&_ANYDICT ,(select spid || '/' || pid from v$process where addr = paddr) "OS/PID"
&_ANYDICT ,process src_pid
&_ANYDICT ,d.created
&_ANYDICT ,i.startup_time started
from       global_name g
&_ANYDICT ,v$session s, v$instance i, v$database d       
&_ANYDICT where sid = USERENV('SID')
;

おわりに

昔から使われている方法だとは思うんですが、あまりメジャーではないようなので紹介記事を書いてみました。

以上です。