Database
技術記事
Ask Tom
2014年5/6月 |
テクノロジー：Ask Tom
Oracle Database 12cについて：パート5
Tom Kyte著
オラクルの技術者が、不可視の列、イントロスペクションの改良点、SQLの展開について説明します。
Ask Tomの各コラムでは通常、過去2か月の間にユーザーが投稿した質問を3つか4つ取り上げて、それらの質問に回答しています。しかし、過去4回と今回のコラムでは、Oracle Database 12cの重要な機能のいくつかについて説明しています。これらの重要な機能は、2012年と2013年にサンフランシスコで開催されたOracle OpenWorldで筆者が行ったプレゼンテーション"12 Things About Oracle Database 12c"または"12 More Things About Oracle Database 12c"で取り上げたものです（これらのプレゼンテーションのスライドは、asktom.oracle.comのFilesタブで公開しています）。1回目のコラムでは、Oracle Database 12cの最初の3つの機能（改善されたデフォルト値、より大きなデータ型、上位Nの問合せ）について説明しました。2回目のコラムでは、新しい行パターン・マッチングについて説明し、一時表のUNDOがOracle Database 12cでどのように変わったかを紹介しました。3回目のコラムでは、パーティション化の改良点、適応型実行計画、統計機能の改良点について取り上げました。前回は、Oracle Database 12cの一番の新機能であるOracle Multitenantオプションについて、いつもとは違うアプローチで説明しました。
今回は、Oracle Database 12cの新機能を紹介するシリーズの締めくくりとして、不可視の列、SQLテキストの展開、イントロスペクションの改良点について説明します。
不可視の列
Oracle Database 12cの不可視の列は、任意の表に列を追加する際に、新しく追加した列がSELECT *の問合せで表示されなく、また、その列を明示的に指定していないINSERT文では挿入対象とならないようにできる機能です。つまり、不可視の列を使用すれば、SELECT *や列指定なしの挿入操作などの不適切なコーディングを含むアプリケーションに影響を及ぼすことなく、表に列を追加することができます。
実際の例を示すために、まずは以下のように2列の表を作成します。
SQL> create table t 2 ( x int, 3 y int 4 ) 5 / Table created. SQL> insert into t values ( 1, 2 ); 1 row created.
次に、SELECT * FROM Tという文を含むアプリケーション・コード（本来は修正が必要）があると仮定します。さらに悪いことに、そのアプリケーション・コードにはINSERT INTO T VALUES (:x,:y)、つまり挿入先の列を指定していない挿入文も含まれているとします（筆者は、これらの状況はいずれも開発済みアプリケーション・コード内のバグであり、修正の必要があると考えます。しかし、今すぐ列を追加しなければならず、バグを含むコードの修正は後で行うということもあるでしょう）。さらに、この既存のコードに影響を及ぼさずに新しい列を追加したいと思っています。このような場合に、不可視の列の出番です。
Zという新しい列を、INVISIBLE属性を使用して追加できます。
SQL> alter table t add
( z int INVISIBLE );
Table altered.
SQL*Plusやその他の多くのツールでこの表について記述する必要がある場合に、表示される表にはこの新しい列が含まれません。
SQL> desc t Name Null? Type ————— ———————— —————————— X NUMBER(38) Y NUMBER(38)
これはSQL*Plusのデフォルト動作ですが、SET COLINVISIBLE設定によって、不可視の列を表示できます。
SQL> set colinvisible on SQL> desc t Name Null? Type ————————————— ———————— —————————— X NUMBER(38) Y NUMBER(38) Z (INVISIBLE) NUMBER(38)
この列は不可視であるため、SELECT *では選択されません。
SQL> select * from t;
X Y
———————————— ———————————
1 2
3 4
また、列の名前を明示的に指定していない既存の挿入文に影響を及ぼすこともありません。
SQL> insert into t values ( 3, 4 ); 1 row created.
下位互換性の観点から、この新機能は優れています。この列にアクセスする必要のある新しいアプリケーションでは、以下のように記述することでこの列に完全にアクセスできます。
SQL> insert into t (x,y,z)
2 values ( 5,6,7 );
1 row created.
SQL> select x,y,z from t;
X Y Z
——————————— ———————— ——————————
1 2
3 4
5 6 7
新しいアプリケーションでは、列Zを明示的に参照するだけでよく、このアプローチは適切です（前述のとおり、列のリストのないSELECT *やINSERTの使用部分は、修正の必要があるバグだと考えるべきです）。
最終的には、古いコードを修正した後、この列を可視へと変更できます。
SQL> alter table t modify z visible;
Table altered.
SQL> select * from t;
X Y Z
——————————— ———————— ——————————
1 2
3 4
5 6 7
不可視の列を使用する副次的なプラス効果として、表内の列を論理的に並べ替えることができます。ここで、この並べ替えは論理的であり物理的ではないことを強調しておきます。
列の並べ替えの例を示すために、表の中央に新しい列を追加することにします。物理的には（つまりディスク上では）、この新しく追加した列は最後の列となります。このデータベース・ブロックをトレース・ファイルにダンプすると、この列が最後に現れます。しかし、論理的には（つまりエンドユーザーの視点からは）、この新しく追加した列は表の中央にあるように表示されます。このことは、SQL*PlusのDESCRIBEコマンドやSELECT *の結果などで確認できます。
論理的な列の並べ替えの例を示すために、まず新しい表を作成します。
SQL> create table t 2 ( a int, 3 b int, 4 d int, 5 e int 6 ); Table created.
次に、アプリケーションのアップグレード中に、Cという新しい列を追加して、表の中央に表示するという要件に対応する必要があるとします。これは、デフォルトではSELECT *により返される順で列を表示する、一部の非定型レポート・ツールのために望まれる機能です。かつて、このような並べ替えは実際には不可能でしたが、以下のような対応により実現していました。
- 表を別の名前に変更する（T_TBLとする）
- この表に新しい列を追加する。その結果、新しい列が列のリストの最後に表示される
- 列を正しい順序で明示的に選択する、Tという名前のビューまたはエディション・ビューを作成する
- T_TBLへの権限付与を無効にして、ビューTに対して同じ権限付与を実行する
- この表の索引、トリガー、制約などを作成するすべてのスクリプトを、T_TBL表を参照するように更新する
この手順によって、新しい列が表の中央に現れるようになりますが、大量の作業が伴います。しかし、現在のバージョンでは、列の並べ替えが簡単になっています。不可視にされた後に可視に戻した列は、SELECT *の視点では表の最後の列と見なされるという、ドキュメントに明記された事実（技ではなくドキュメントに明記された振る舞い）を利用できます。したがって、論理的な並べ替えを行うには、まず列Dと列Eを不可視にして、その後列Cを追加します。この時点で、列Cが列Bの直後に現れます。次に、列Dと列Eを可視にすると、論理的には列Cの後にこれらの列が現れます。以下に、列の不可視設定、追加、可視設定の方法と、列の論理的順序を表す問合せ結果を示します。
SQL> alter table t modify 2 ( d invisible, e invisible ); Table altered. SQL> alter table t add c int; Table altered. SQL> alter table t modify 2 ( d visible, e visible ); Table altered. SQL> desc t Name Null? Type ——————————— ——————————— ——————————— A NUMBER(38) B NUMBER(38) C NUMBER(38) D NUMBER(38) E NUMBER(38)
ただし、ディスク上における実際の列の保存順は、A、B、D、E、Cです。不可視の列に関する詳細は、Oracle Database管理者ガイド12cリリース1（12.1）の第20章、"表の管理"を参照してください。
イントロスペクションの改良点
Oracle Databaseアプリケーションの開発者は、コード内で"今どこにいるか"を把握するために、これまでは以下の3つの組込みファンクションを利用してきました。
- DBMS_UTILITY.FORMAT_CALL_STACK
- DBMS_UTILITY.FORMAT_ERROR_ BACKTRACE
- DBMS_UTILITY.FORMAT_ERROR_STACK
これらのルーチンは便利ではありますが、使用法が限定されていました。FORMAT_CALL_STACKルーチンを見ながら、その理由を確認しましょう。現在のコール・スタックを表示するためのプロシージャ（print_call_stack）を以下に示します。
SQL> create or replace
2 procedure Print_Call_Stack
3 is
4 begin
5 DBMS_Output.Put_Line(
DBMS_Utility.Format_Call_Stack());
6 end;
7 /
Procedure created.
次に、PKGというパッケージの中にネストされたファンクションがあり、さらにファンクション名が一部重複しているとします。
SQL> create or replace 2 package body Pkg is 3 procedure p 4 is 5 procedure q 6 is 7 procedure r 8 is 9 procedure p is 10 begin 11 Print_Call_Stack(); 12 raise program_error; 13 end p; 14 begin 15 p(); 16 end r; 17 begin 18 r(); 19 end q; 20 begin 21 q(); 22 end p; 23 end Pkg; 24 / Package body created.
このPKG.Pプロシージャを実行した場合の出力はリスト1のようになります。
コード・リスト1：DBMS_UTILITY.FORMAT_CALL_STACKの出力とエラー・メッセージ
SQL> exec pkg.p
——————— PL/SQL Call Stack ———————
object line object
handle number name
0x6e891528 4 procedure OPS$TKYTE.PRINT_CALL_STACK
0x6ec4a7c0 10 package body OPS$TKYTE.PKG
0x6ec4a7c0 14 package body OPS$TKYTE.PKG
0x6ec4a7c0 17 package body OPS$TKYTE.PKG
0x6ec4a7c0 20 package body OPS$TKYTE.PKG
0x76439070 1 anonymous block
BEGIN pkg.p; END;
*
ERROR at line 1:
ORA-06501:PL/SQL: program error
ORA-06512: at "OPS$TKYTE.PKG", line 11
ORA-06512: at "OPS$TKYTE.PKG", line 14
ORA-06512: at "OPS$TKYTE.PKG", line 17
ORA-06512: at "OPS$TKYTE.PKG", line 20
ORA-06512: at line 1
BEGIN pkg.p; END;よりも上の部分はDBMS_UTILITY.FORMAT_CALL_STACKの出力であり、これより下の部分はクライアント・アプリケーションから返されたエラー・メッセージです（このエラー・メッセージはDBMS_UTILITY.FORMAT_ERROR_BACKTRACEの実行時にも生成されます）。見てのとおり、この出力には役に立つ情報が含まれていますが、利用するには出力を解析する必要があり、難しい操作になります。たとえば、これらの文字列の形式は固定されず、数年のうちに変更されてきました（筆者はOWA_UTIL.WHO_AM_Iというファンクションを作成しましたが、その際に上記の文字列を解析しました。だからこそ、年月とともに文字列が変更されることを知っています）。
しかし、嬉しいことに、Oracle Database 12cより導入された新しいUTL_CALL_STACKパッケージを使用して、コール・スタックや一連のAPIコールに対して構造的にアクセスして、この構造を詳細に調査できるようになりました。
実際の例を示すために、上記のprint_call_stackファンクションを、リスト2のようにUTL_CALL_STACKを使用して書き換えます。
コード・リスト2：print_call_stackファンクション（UTL_CALL_STACKを使用した改良バージョン）
SQL> create or replace 2 procedure Print_Call_Stack 3 as 4 Depth pls_integer := UTL_Call_Stack.Dynamic_Depth(); 5 6 procedure headers 7 is 8 begin 9 dbms_output.put_line( 'Lexical Depth Line Name' ); 10 dbms_output.put_line( 'Depth Number ' ); 11 dbms_output.put_line( '—————————— ——————— ———————— ——————' ); 12 end headers; 13 procedure print 14 is 15 begin 16 headers; 17 for j in reverse 1..Depth loop 18 DBMS_Output.Put_Line( 19 rpad( utl_call_stack.lexical_depth(j), 10 ) || 20 rpad( j, 7) || 21 rpad( To_Char(UTL_Call_Stack.Unit_Line(j), '99'), 9 ) || 22 UTL_Call_Stack.Concatenate_Subprogram 23 (UTL_Call_Stack.Subprogram(j))); 24 end loop; 25 end; 26 begin 27 print; 28 end; 29 /
この新しいprint_call_stackファンクションを使用すれば、UTL_CALL_STACK .DYNAMIC_DEPTHを使用して現在のコード内の深さを把握でき、さらにループを使用してこのスタックを上位へと辿ることができます。字句の深さを、実行中のユニット内の行番号およびユニット名とともに出力します。ユニット名は単独ではなく完全修飾名（パッケージ内のサブプログラム名、その中のサブプログラム名、さらにその中のサブプログラム名と続く）とします。たとえば、PKG.Pプロシージャを再度実行すると、リスト3の出力とエラー・メッセージが表示されます。
コード・リスト3：UTL_CALL_STACKの出力とエラー・メッセージ
SQL> exec pkg.p Lexical Depth Line Name Depth Number ——————— ——————— ———————— ——————— 1 6 20 PKG.P 2 5 17 PKG.P.Q 3 4 14 PKG.P.Q.R 4 3 10 PKG.P.Q.R.P 0 2 26 PRINT_CALL_STACK 1 1 17 PRINT_CALL_STACK.PRINT BEGIN pkg.p; END; * ERROR at line 1: ORA-06501:PL/SQL: program error ORA-06512: at "OPS$TKYTE.PKG", line 11 ORA-06512: at "OPS$TKYTE.PKG", line 14 ORA-06512: at "OPS$TKYTE.PKG", line 17 ORA-06512: at "OPS$TKYTE.PKG", line 20 ORA-06512: at line 1
DBMS_UTILITY.FORMAT_CALL_STACKで得られた情報と比較し、今回は、行番号とパッケージ名以上の情報が得られました。UTL_CALL_STACKパッケージでは、行番号とパッケージ（ユニット）名だけでなく、サブプログラム名も表示されており、ネストされたサブプログラムとしてPがQを、QがRを、RがPを順にコールしたことも分かります。さらに、字句の深さによって、より正確なコール・レベルも確認できます。たとえば、print_call_stackをコールするパッケージをステップ・アウトし、そのprint_call_stackが別のネストされたサブプログラムをコールしたことが分かります。
新しいUTL_CALL_STACKパッケージは、すべての開発者のエラー・ロギング・パッケージに追加できる優れた機能です。言うまでもなく、このパッケージには他にも、所有者名やコードが実行された実際のエディションなどを取得するためのさまざまなファンクションがあります。詳細は、Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス12cリリース1（12.1）を参照してください。
SQLテキストの展開
次に紹介するOracle Database 12cの機能は、SQLテキストの展開です。この新機能を使用すれば、SQL文を展開したテキスト（ビューのテキストが含まれたもの）を表示できます。そのため、オプティマイザで現在問題となっている実際の問合せを確認できます。
SELECT * FROM V WHERE X=5などの単純な問合せをチューニングするように依頼され、結果的に、問合せのEXPLAIN PLANが15ページあり、数十のオブジェクトを参照していると分かったことが何度もあります。このような問合せを理解するには、まずVが実際に何であり、VビューのSQLテキストが実際にどうなっているかを理解する必要があります。そうすると、Vビューのテキスト自体が、ビューのビューのビューを参照していることもあるのです。実際のSQLがどうなっているかを調査するのは実に面倒です。しかし、SQLテキストの展開により、このような調査が簡単になります。
SELECT * FROM ALL_USERSという単純な問合せを例にとります。ALL_USERS（さらにOracleデータ・ディクショナリ全体）は内部的にはビューです。内部的にどのような問合せが実行されているかを確認するために、SQLテキストの展開を以下のように利用できます。
SQL> variable x clob
SQL> begin
2 dbms_utility.expand_sql_text
3 ( input_sql_text =>
'select * from all_users',
4 output_sql_text => :x );
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> print x
X
——————————————————————————————————————
SELECT "A1"."USERNAME"
"USERNAME","A1"."USER_ID"
"USER_ID","A1"."CREATED"
"CREATED","A1"."COMMON" "COMMON" FROM
(SELECT "A4"."NAME"
"USERNAME","A4"."USER#"
"USER_ID","A4"."CTIME"
"CREATED",DECODE(BITAND("A4"."SPARE1",12
8),128,'YES','NO') "COMMON" FROM
"SYS"."USER$" "A4","SYS"."TS$"
"A3","SYS"."TS$" "A2" WHERE
"A4"."DATATS#"="A3"."TS#" AND
"A4"."TEMPTS#"="A2"."TS#" AND
"A4"."TYPE#"=1) "A1"
見てのとおり、DBMS_UTILITYパッケージ内にある新しいEXPAND_SQL_TEXTプロシージャがSQLテキストの展開機能を提供しており、これを使用すれば実際に実行されている問合せの調査が簡単になります。
SQLテキストの展開はビューに限った機能ではありません。たとえば、Oracle Virtual Private Databaseによって書き換えられている問合せをチューニングしなければならず、このチューニングを始めるために実際のSQLテキストについて把握する必要がある場合も多くあります。EXPAND_SQL_TEXTを使用すれば、DBMS_RLSパッケージによるそのような問合せで使用される実際のSQLテキストを調査できます。
この例を示すために、単純なOracle Virtual Private Databaseのポリシーを設定した小さな表を作成します。まずは以下のセキュリティ・ファンクションを実装します。
SQL> create or replace
2 function my_security_function
( p_schema in varchar2,
3 p_object in varchar2 )
4 return varchar2
5 as
6 begin
7 return 'owner = USER';
8 end;
9 /
Function created.
見てのとおり、これはかなり単純なファンクションです。このファンクションにより、関連付けられた表またはビューにアクセスするたびに、透過的にWHERE OWNER = USERという条件が追加されます。
次に、MY_TABLEを定義して、この条件ファンクションを関連付けます。
SQL> create table my_table
2 ( data varchar2(30),
3 OWNER varchar2(30) default USER
4 )
5 /
Table created.
SQL> begin
2 dbms_rls.add_policy
3 ( object_schema => user,
4 object_name => 'MY_TABLE',
5 policy_name => 'MY_POLICY',
6 function_schema => user,
7 policy_function =>
'My_Security_Function',
8 statement_types =>
'select, insert, update, delete',
9 update_check => TRUE );
10 end;
11 /
PL/SQL procedure successfully completed.
MY_TABLEに対する問合せをチューニングするように依頼されたら、書き換えられた後の問合せを調査して、実際の対象を確認することになるでしょう。この場合も、EXPAND_SQL_TEXTプロシージャが非常に便利です。
SQL> begin
2 dbms_utility.expand_sql_text
3 ( input_sql_text =>
'select * from my_table',
4 output_sql_text => :x );
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> print x
X
————————————————————————————————————————————
SELECT "A1"."DATA" "DATA","A1"."OWNER"
"OWNER" FROM (SELECT "A2"."DATA"
"DATA","A2"."OWNER" "OWNER" FROM
"OPS$TKYTE"."MY_TABLE" "A2" WHERE
"A2"."OWNER"=USER@!)"A1"
展開されたSQLを確認できるばかりか、このテキストから、Oracle Virtual Private Databaseの処理の仕組みについても有益な情報を得られます。この例では、Oracle Virtual Private Databaseは単純にFROMリストからMY_TABLE参照を取り出して、条件を付加したインライン・ビューへと変換しています。
以前のリリースでもこのような書き換え後の問合せをある程度確認できましたが、全体像の把握が非常に困難でした。たとえば、MY_TABLEへのSELECT権限を持つユーザーAがいて、ユーザーAは（チューニングの目的で）問合せの実行内容を確認しようとしているとします。この場合、ユーザーAは以下のようにEXPLAIN PLANを使用することになるでしょう。
SQL> set autotrace traceonly explain SQL> select * from ops$tkyte.my_table; —————————————————————————————————————— | Id | Operation | Name | —————————————————————————————————————— | 0 | SELECT STATEMENT | | |* 1 | TABLE ACCESS FULL| MY_TABLE | —————————————————————————————————————— Predicate Information (identified by … —————————————————————————————————————— 1 - filter(“OWNER”=USER@!)
書換え後のSQLを正確に確認できなくても、ある程度作業すれば全体像を把握することは可能です。
Oracle Virtual Private Databaseの詳細は、Oracle Database概要12cリリース1（12.1）の第19章、"データベース管理者および開発者向けのトピック"を参照してください。EXPAND_SQL_TEXTの詳細は、Oracle Database新機能ガイド12cリリース1（12.1）の第1章、"Oracle Database 12cリリース1（12.1）の新機能"を参照してください。
次のステップ
|
Tom Kyteは、オラクルのServer Technologies部門に籍を置くデータベース・エバンジェリストで、1993年からオラクルに勤務しています。Expert Oracle Database Architecture（Apress、2005年/2010年）、Effective Oracle by Design（Oracle Press、2003）などの著書があります。
Printer View