1. SQLチューニング・ガイド
  2. オプティマイザの制御
  3. カーソル共有によるReal-World Performanceの改善

20 カーソル共有によるReal-World Performanceの改善

カーソル共有により、データベース・アプリケーションのパフォーマンスを大幅に向上させることができます。

20.1 カーソル共有の概要

Oracle Databaseでは、共有プール内のプライベートSQL領域へのポインタであるカーソルを共有できます。

20.1.1 カーソルについて

プライベートSQL領域には、解析済のSQL文に関する情報と、処理に使用するその他のセッション固有の情報が保持されます。

サーバー・プロセスでSQLまたはPL/SQLのコードが実行される場合、プロセスではプライベートSQL領域を使用してバインド変数値、問合せ実行状態情報、問合せ実行作業領域が格納されます。文の各実行におけるプライベートSQL領域は共有されず、異なる値やデータが含まれている可能性があります。

カーソルは、特定のプライベートSQL領域の名前またはハンドルです。カーソルには、バインド変数の値や結果セットなどのセッション固有の状態の情報が含まれています。

次の図に示すように、カーソルはクライアントから見るとポインタ、サーバーから見ると状態のようなものです。カーソルはプライベートSQL領域と密接に関連しているため、これらの用語は同じ意味で使用されることがあります。

図20-1 カーソル

図20-1の説明が続きます
「図20-1 カーソル」の説明
20.1.1.1 プライベートSQL領域と共有SQL領域

プライベートSQL領域のカーソルは、ライブラリ・キャッシュの共有SQL領域を指します。

セッション状態の情報を含むプライベートSQL領域とは異なり、共有SQL領域には、文の解析ツリーおよび実行計画が含まれています。たとえば、SELECT * FROM employeesを実行すると、計画および解析ツリーはある共有SQL領域に格納されます。構文的にも意味的にも異なるSELECT * FROM departmentsを実行すると、計画および解析ツリーは別の共有SQL領域に格納されます。

同じセッションまたは異なるセッションにある複数のプライベートSQL領域は、単一の共有SQL領域を参照でき、これはカーソル共有と呼ばれる現象です。たとえば、あるセッションにおけるSELECT * FROM employeesの実行と、別のセッションにおける(同じ表にアクセスする) SELECT * FROM employeesの実行では、同じ解析ツリーおよび計画を使用できます。複数の文によってアクセスされる共有SQL領域は、共有カーソルと呼ばれます。

図20-2 カーソル共有

図20-2の説明が続きます
「図20-2 カーソル共有」の説明

Oracle Databaseでは、次のステップを使用して、SQL文またはPL/SQLブロックを発行する際に、ライブラリ・キャッシュにテキスト的に同じ文が存在するかどうかを自動的に確認します。

  1. 文のテキストがハッシュされます。

  2. 共有プール内の既存のSQL文で、一致するハッシュ値が検索されます。次のオプションが可能です。

    • 一致するハッシュ値が存在しない場合。

      この場合、SQL文は共有プール内に現在存在せず、ハード解析が実行されます。これにより共有プール・チェックが終了されます。

    • 一致するハッシュ値が存在する場合。

      この場合、データベースは次のステップ(テキスト照合)に進みます。

  3. データベースは、一致する文のテキストとハッシュ文のテキストを比較して同一であるかどうかを判断します。次のオプションが可能です。

    • テキスト照合に失敗しました。

      この場合、テキスト照合プロセスは停止し、ハード解析になります。

    • テキスト照合に成功しました。

      この場合、データベースは次のステップに進み、SQLが既存の親カーソルを共有できるかどうかを決定します。

      テキスト的一致が発生するには、SQL文またはPL/SQLのブロックが、空白、大文字小文字の区別およびコメントも含めて文字単位で同一である必要があります。たとえば、次の文は同じ共有SQL領域を使用できません。

      SELECT * FROM employees;
SELECT * FROM Employees;
SELECT *  FROM employees;

      通常は、リテラルのみ異なるSQL文は同じ共有SQL領域を使用できません。たとえば、次の文は同じSQL領域に変換されません。

      SELECT count(1) FROM employees WHERE manager_id = 121;
SELECT count(1) FROM employees WHERE manager_id = 247;

      このルールの唯一の例外は、CURSOR_SHARINGパラメータがFORCEに設定されている場合で、このケースでは類似した文でSQL領域を共有できます。

関連項目:

20.1.1.2 親カーソルと子カーソル

すべての解析済のSQL文には、1つの親カーソルと1つ以上の子カーソルがあります。

親カーソルにはSQL文のテキストが格納されます。2つの文のテキストが同一である場合、これらの文は同じ親カーソルを共有します。しかし、テキストが異なる場合、データベースは別の親カーソルを作成します。

例20-1 親カーソル

この例で、最初の2つの文は構文的に異なりますが(文字“c”が最初の文では小文字で、2番目の文では大文字です)、意味的には同じです。構文的に異なるため、これらの文の親カーソルは異なります。3番目の文は最初の文と構文的には同じですが(小文字の“c”)、別のスキーマのcustomers表を参照しているため、意味的には異なります。構文的に同じであるため、3番目の文は最初の文と親カーソルを共有できます。 

SQL> CONNECT oe@inst1
Enter password: *******
Connected.
SQL> SELECT COUNT(*) FROM customers;

  COUNT(*)
----------
       319

SQL> SELECT COUNT(*) FROM Customers;

  COUNT(*)
----------
       319

SQL> CONNECT sh@inst1
Enter password:  *******
Connected.
SQL> SELECT COUNT(*) FROM customers;

  COUNT(*)
----------
    155500

次の問合せのV$SQLは、2つの親を示しています。SQL IDが8h916vv2yw400の文(小文字の“c”を含む文)には、1つの親カーソルと2つの子カーソル(子0と子1)があります。SQL IDが5rn2uxjtpz0wdの文(大文字の“c”を含む文)には、異なる親カーソルと1つの子カーソル(子0)のみがあります。 

SQL> CONNECT SYSTEM@inst1
Enter password:  *******
Connected.

SQL> COL SQL_TEXT FORMAT a30
SQL> COL CHILD# FORMAT 99999
SQL> COL EXEC FORMAT 9999
SQL> COL SCHEMA FORMAT a6
SQL> SELECT SQL_ID, PARSING_SCHEMA_NAME AS SCHEMA, SQL_TEXT, 
  2  CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC FROM V$SQL 
  3  WHERE SQL_TEXT LIKE '%ustom%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%' ORDER BY SQL_ID;

SQL_ID        SCHEMA SQL_TEXT                       CHILD# EXEC
------------- ------ ------------------------------ ------ -----
5rn2uxjtpz0wd OE     SELECT COUNT(*) FROM Customers	 0     1
8h916vv2yw400 OE     SELECT COUNT(*) FROM customers	 0     1
8h916vv2yw400 SH     SELECT COUNT(*) FROM customers	 1     1
20.1.1.2.1 親カーソルおよびV$SQLAREA

V$SQLAREAビューには、すべての親カーソルに対して1つの行が含まれています。

次の例で、V$SQLAREAの問合せは、それぞれが異なるSQL_IDで識別される2つの親カーソルを示しています。VERSION_COUNTは子カーソルの数を示します。 

COL SQL_TEXT FORMAT a30
SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE
FROM   V$SQLAREA
WHERE  SQL_TEXT LIKE '%mployee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';
 
SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE
------------------------------ ------------- ------------- ----------
SELECT * FROM Employees        5bzhzpaa0wy9m             1 2483976499
SELECT * FROM employees        4959aapufrm1k             2 1961610290

前述の出力で、SELECT * FROM employeesでのVERSION_COUNTの2は、複数の子カーソルを示しています。文が2つの異なるオブジェクトに対して実行されたため、2つの子カーソルが必要です。これに対して、文SELECT * FROM Employees (大文字の"E")が1回実行されると、親カーソルは1つ、子カーソルは1つ(VERSION_COUNTが1)となります。

20.1.1.2.2 子カーソルおよびV$SQL

すべての親カーソルには1つ以上の子カーソルがあります。

子カーソルには、実行計画、バインド変数、問合せで参照されるオブジェクトに関するメタデータ、オプティマイザ環境およびその他の情報が含まれています。親カーソルとは異なり、子カーソルにはSQL文のテキストは格納されません。

親カーソルを再利用できる文の場合、データベースはその文が既存の子カーソルを再利用できるかどうかをチェックします。データベースは、次を含めていくつかのチェックを実行します。

  • データベースは、発行された文で参照されているオブジェクトをプール内の文で参照されているオブジェクトと比較して、両方のオブジェクトがすべて同一であることを確認します。

    SQL文やPL/SQLブロック内でスキーマ・オブジェクトを参照する際には、同じスキーマ内の同じオブジェクトである必要があります。たとえば、2人のユーザーが次のSQL文を発行し、各ユーザーに独自のemployees表がある場合、次の文はユーザーごとに異なるemployees表を参照するので、この文は同一とみなされません。 

    SELECT * FROM employees;

    ノート:

    データベースは、プライベート一時表のカーソルを共有できますが、同一セッション内のみに限られます。データベースは、カーソル・コンテキストの一部としてセッション識別子を関連付けます。ソフト解析時に、データベースは、現在のセッションIDがカーソル・コンテキストのセッションIDと一致している場合にのみ子カーソルを共有できます。

  • データベースは、オプティマイザ・モードが同一であるか判定します。

    たとえば、SQL文は、同一の最適化目標を使用して最適化する必要があります。

例20-2 複数の子カーソル

V$SQLは、現在ライブラリ・キャッシュに存在する文を示しています。次の例に示すように、すべての子カーソルに対して1つの行が含まれています。 

SELECT SQL_TEXT, SQL_ID, USERNAME AS USR, CHILD_NUMBER AS CHILD#, 
       HASH_VALUE, PLAN_HASH_VALUE AS PLAN_HASHV
FROM   V$SQL s, DBA_USERS d
WHERE  SQL_TEXT LIKE '%mployee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%'
AND    d.USER_ID = s.PARSING_USER_ID;

SQL_TEXT                SQL_ID        USR CHILD# HASH_VALUE PLAN_HASHV
----------------------- ------------- --- ------ ---------- ----------
SELECT * FROM Employees 5bzhzpaa0wy9m  HR      0 2483976499 1445457117
SELECT * FROM employees 4959aapufrm1k  HR      0 1961610290 1445457117
SELECT * FROM employees 4959aapufrm1k  SH      1 1961610290 1445457117

前述の結果では、下の2つの文は、SQL_IDが同じでも、CHILD#が異なります(0と1)。つまり、これらの文の親カーソルは同じで、子カーソルは異なります。これに対して、SQL_IDが5bzhzpaa0wy9mの文には、1つの親カーソルと1つの子カーソル(CHILD#が0)があります。PLAN_HASH_VALUE列が同一の値で示されているように、3つのすべてのSQL文では、同じ実行計画が使用されています。

20.1.1.2.3 カーソルの不一致およびV$SQL_SHARED_CURSOR

親カーソルに複数の子カーソルがある場合、V$SQL_SHARED_CURSORビューは、カーソルが共有されなかった理由に関する情報を提供します。複数のタイプの非互換性について、TRANSLATION_MISMATCH列は値YまたはNで不一致を示します。

例20-3 変換の不一致

この例で、TRANSLATION_MISMATCH列は、2つの文(SELECT * FROM employees)が異なるオブジェクトを参照したことを示しており、最後の文でTRANSLATION_MISMATCHの値がYになります。共有できなかったため、CHILD_NUMBERが0と1で示されているように、それぞれの文には別の子カーソルがあります。 

SELECT S.SQL_TEXT, S.CHILD_NUMBER, s.CHILD_ADDRESS, 
       C.TRANSLATION_MISMATCH
FROM   V$SQL S, V$SQL_SHARED_CURSOR C
WHERE  SQL_TEXT LIKE '%employee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%'
AND S.CHILD_ADDRESS = C.CHILD_ADDRESS;

SQL_TEXT                       CHILD_NUMBER    CHILD_ADDRESS T
------------------------------ ------------ ---------------- -
SELECT * FROM employees                   0 0000000081EE8690 N
SELECT * FROM employees                   1 0000000081F22508 Y

20.1.2 カーソルおよび解析について

アプリケーションが文を発行し、Oracle Databaseがカーソルを再利用できない場合、新しく実行可能なバージョンのアプリケーション・コードを作成する必要があります。この操作はハード解析と呼ばれています。

ソフト解析はハード解析ではない解析で、データベースが既存のコードを再利用できる場合に発生します。ソフト解析には、ハード解析よりもリソース集中型ではないものがあります。たとえば、文の親カーソルがすでに存在する場合、Oracle Databaseは、様々な最適化を実行し、共有SQL領域に子カーソルを格納できます。しかし、親カーソルが存在しない場合、Oracle Databaseは、共有SQL領域に親カーソルも格納する必要があり、追加のメモリー・オーバーヘッドが作成されます。

実質的に、ハード解析では文を実行する前に再コンパイルします。すべての実行前にSQL文をハード解析することは、すべての実行前にCプログラムを再コンパイルすることに似ています。ハード解析では次のような操作を実行します。

  • SQL文の構文のチェック

  • SQL文のセマンティクスのチェック

  • 文を発行するユーザーのアクセス権のチェック

  • 実行計画の作成

  • データ・ディクショナリをチェックするための、ライブラリ・キャッシュおよびデータ・ディクショナリ・キャッシュへの多くのアクセス

ハード解析の特にリソース集中型の側面は、データ・ディクショナリをチェックするために、ライブラリ・キャッシュおよびデータ・ディクショナリ・キャッシュに何度もアクセスすることです。データベースはこれらの領域にアクセスするとき、チェック中に定義が変更されないように、必要なオブジェクト上にラッチと呼ばれるシリアライズ・デバイスを使用します。ラッチの競合が発生すると、文の実行時間が長くなり、同時実行性が低下します。

前述のすべての理由で、ハード解析のCPUおよびメモリーのオーバーヘッドにより深刻なパフォーマンスの問題が発生します。この問題が特に明白なのは、フォームからユーザー入力を受け入れ、SQL文を動的に生成するWebアプリケーションです。Real-World Performanceグループでは、ハード解析をできるだけ減らすことを強くお薦めします。

ビデオ:

例20-4 V$SQLを使用した解析情報の検出

様々な手法を使用して、ハード解析およびソフト解析を監視できます。この例では、セッション統計を問い合せて、DBA_JOBS問合せの繰返し実行がハード解析数を増やすかどうかを判断します。文の最初の実行では、ハード解析数が49に増加しましたが、2回目の実行ではハード解析数が変わりませんでした。これはOracle Databaseがアプリケーション・コードを再利用したことを意味します。 

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> COL NAME FORMAT a18

SQL> SELECT s.NAME, m.VALUE
  2  FROM   V$STATNAME s, V$MYSTAT m
  3  WHERE  s.STATISTIC# = m.STATISTIC#
  4  AND    s.NAME LIKE '%(hard%';

NAME                    VALUE
------------------ ----------
parse count (hard)         48

SQL> SELECT COUNT(*) FROM DBA_JOBS;

  COUNT(*)
----------
	 0

SQL> SELECT s.NAME, m.VALUE
  2  FROM   V$STATNAME s, V$MYSTAT m
  3  WHERE  s.STATISTIC# = m.STATISTIC#
  4  AND    s.NAME LIKE '%(hard%';

NAME                    VALUE
------------------ ----------
parse count (hard)         49

SQL> SELECT COUNT(*) FROM DBA_JOBS;

  COUNT(*)
----------
	 0

SQL> SELECT s.NAME, m.VALUE
  2  FROM   V$STATNAME s, V$MYSTAT m
  3  WHERE  s.STATISTIC# = m.STATISTIC#
  4  AND    s.NAME LIKE '%(hard%';

NAME                    VALUE
------------------ ----------
parse count (hard)         49

例20-5 トレース・ファイルを使用した解析情報の検出

この例では、SQLトレースおよびTKPROFユーティリティを使用して、解析情報を検出します。管理者権限でデータベースにログインした後、トレース・ファイル(サンプル出力を含む)のディレクトリの場所を問い合せます。

SET LINESIZE 120
COLUMN value FORMAT A80

SELECT value
FROM   v$diag_info
WHERE  name = 'Default Trace File';

VALUE
---------------------------------------------------------------------------
/disk1/oracle/log/diag/rdbms/orcl/orcl/trace/orcl_ora_23054.trc

トレースを有効にし、TRACEFILE_IDENTIFIER初期化パラメータを使用してトレース・ファイルに意味のある名前を付けた後、hr.employeesを問い合せます。 

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(waits=>TRUE, binds=>TRUE); 
ALTER SESSION SET TRACEFILE_IDENTIFIER = "emp_stmt";
SELECT * FROM hr.employees;
EXIT;

生成したトレース・ファイルに対するデフォルトのトレース・ファイル・ディレクトリを検索します。

% ls *emp_stmt.trc
orcl_ora_17950_emp_stmt.trc

TKPROFを使用してトレース・ファイルをフォーマットし、フォーマットされたファイルを開きます。

% tkprof orcl_ora_17950_emp_stmt.trc emp.out; vi emp.out

フォーマットされたトレース・ファイルには、hr.employeesの問合せの解析情報が含まれています。 

SQL ID: brmjpfs7dcnub Plan Hash: 1445457117

SELECT *
FROM
 hr.employees


call    count       cpu    lapsed    disk    query  current      rows
------- -----  -------- --------- ------- -------- --------  --------
Parse       1     0.07      0.08        0        0        0         0
Execute     1     0.00      0.00        0        0        0         0
Fetch       9     0.00      0.00        3       12        0       107
------- -----  ------- --------- -------- -------- --------  --------
total      11     0.07      0.08        3       12        0       107

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYSTEM
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------
       107        107        107  TABLE ACCESS FULL EMPLOYEES (cr=12 pr=3 
                                  pw=0 time=497 us starts=1 cost=2 
                                  size=7383 card=107)

ライブラリ・キャッシュ・ミスはハード解析を示しています。同じ文の2回目の実行で同じステップを実行すると、ライブラリ・キャッシュ・ミスがないことを示す次のトレース出力が生成されます。

SQL ID: brmjpfs7dcnub Plan Hash: 1445457117

SELECT *
FROM
 hr.employees


call     count     cpu   elapsed     disk    query   current      rows
------- ------  ------ --------- -------- -------- ---------  --------
Parse        1    0.00      0.00        0        0         0         0
Execute      1    0.00      0.00        0        0         0         0
Fetch        9    0.00      0.00        3       12         0       107
------- ------  ------ --------- -------- -------- ---------  --------
total       11    0.00      0.00        3       12         0       107

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYSTEM
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------
       107        107        107  TABLE ACCESS FULL EMPLOYEES (cr=12 pr=3
                                  pw=0 time=961 us starts=1 cost=2
                                  size=7383 card=107)

関連項目:

「共有プール・チェック」

20.1.3 リテラルおよびバインド変数について

Oracleデータベース・アプリケーションでのカーソル共有にはバインド変数が不可欠です。

20.1.3.1 リテラルおよびカーソル

SQL文を構成する際に、一部のOracleアプリケーションではバインド変数ではなくリテラルを使用します。

たとえば、文SELECT SUM(salary) FROM hr.employees WHERE employee_id < 101では、従業員IDにリテラル値101を使用しています。デフォルトでは、同様の文でバインド変数を使用しない場合、Oracle Databaseではカーソル共有を利用できません。そのため、Oracle Databaseでは、値102や他の任意のランダム値であることを除いて同一な文は、まったく新しい文とみなされ、ハード解析が必要です。

Real-World Performanceグループでは、リテラルを使用するアプリケーションは、パフォーマンス、スケーラビリティおよびセキュリティの問題の原因となることが多いことがわかりました。現状では、カーソル共有を考慮せずにアプリケーションが迅速に書き込まれるのは、よくあることです。典型的な例は、Webフォームからコンテンツをコピーし、文字列を連結してSQL文を動的に構成する、スクリーン・スクレイピング・アプリケーションです。

リテラル値の使用によって生じる主な問題は、次のとおりです。

  • エンド・ユーザーによるリテラル入力を連結するアプリケーションは、SQLインジェクション攻撃を受けやすくなります。この脅威をなくす唯一の方法は、バインド変数を使用するようにアプリケーションをリライトすることです。

  • すべての文がハード解析された場合、カーソルは共有されないため、データベースはカーソルの作成により多くのメモリーを消費する必要があります。

  • ハード解析の際には、Oracle Databaseは共有プールおよびライブラリ・キャッシュをラッチする必要があります。ハード解析の数が増加すると、共有プールのラッチを待機するプロセスの数も増加します。この状況では、同時実行性が低下し、競合が増加します。

ビデオ:

例20-6 リテラルおよびカーソル共有

リテラルのみが異なる次の文を実行するアプリケーションを考えてみます。

SELECT SUM(salary) FROM hr.employees WHERE employee_id < 101;
SELECT SUM(salary) FROM hr.employees WHERE employee_id < 120;
SELECT SUM(salary) FROM hr.employees WHERE employee_id < 165;

次の問合せのV$SQLAREAは、3つの文には3つの異なる親カーソルが必要であることを示しています。VERSION_COUNTに示すように、各親カーソルには独自の子カーソルが必要です。 

COL SQL_TEXT FORMAT a30
SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE
FROM   V$SQLAREA
WHERE  SQL_TEXT LIKE '%mployee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT                              SQL_ID VERSION_COUNT HASH_VALUE
------------------------------ ------------- ------------- ----------
SELECT SUM(salary) FROM hr.emp b1tvfcc5qnczb             1  191509483
loyees WHERE employee_id < 165
SELECT SUM(salary) FROM hr.emp cn5250y0nqpym             1 2169198547
loyees WHERE employee_id < 101
SELECT SUM(salary) FROM hr.emp au8nag2vnfw67             1 3074912455
loyees WHERE employee_id < 120

関連項目:

SQLインジェクションについて学習するには、「CURSOR_SHARING = FORCEを永続的な修正として使用しない」を参照してください

20.1.3.2 バインド変数およびカーソル

リテラルではなくバインド変数を使用するようにOracleアプリケーションを開発できます。

バインド変数は、問合せ内のプレースホルダです。たとえば、文SELECT SUM(salary) FROM hr.employees WHERE employee_id < :emp_idでは、従業員IDにバインド変数:emp_idを使用しています。

Real-World Performanceグループでは、バインド変数を使用するアプリケーションは、より適切に実行され、スケーラブルかつセキュアであることがわかりました。バインド変数の使用によって生じる主な利点は、次のとおりです。

  • バインド変数を使用するアプリケーションは、SQLインジェクション攻撃に対する脆弱性が、リテラルを使用するアプリケーションと同じではありません。

  • 同一の文でバインド変数を使用する場合、Oracle Databaseはカーソル共有を利用でき、同じ文に異なる値がバインドされると、計画およびその他の情報を共有します。

  • Oracle Databaseでは、ハード解析に必要な共有プールおよびライブラリ・キャッシュのラッチのオーバーヘッドが回避されます。

ノート:

65535を超えるバインド変数は問合せで使用できません。

ビデオ:

例20-7 バインド変数および共有カーソル

次の例では、SQL*PlusでVARIABLEコマンドを使用してemp_idバインド変数を作成し、3つの異なるバインド値(101、120および165)を使用して問合せを実行します。 

VARIABLE emp_id NUMBER

EXEC :emp_id := 101;
SELECT SUM(salary) FROM hr.employees WHERE employee_id < :emp_id;
EXEC :emp_id := 120;
SELECT SUM(salary) FROM hr.employees WHERE employee_id < :emp_id;
EXEC :emp_id := 165;
SELECT SUM(salary) FROM hr.employees WHERE employee_id < :emp_id;

次の問合せのV$SQLAREAは、1つの一意のSQL文を示しています。 

COL SQL_TEXT FORMAT a34
SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE
FROM   V$SQLAREA
WHERE  SQL_TEXT LIKE '%mployee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT                           SQL_ID        VERSION_COUNT HASH_VALUE
---------------------------------- ------------- ------------- ----------
SELECT SUM(salary) FROM hr.employe 4318cbskba8yh             1 615850960
es WHERE employee_id < :emp_id

VERSION_COUNTの値1は、3つの個別の子カーソルを作成するデータベースではなく、同じ子カーソルを再利用したデータベースを示しています。バインド変数を使用して、この再利用が可能になりました。

ノート:

最大65535個のバインド変数を問合せで使用できます。バインドの機密性が使用されない状況があることにも注意してください:
  • バインドが等価述語または範囲述語で使用されます。
  • オプティマイザはバインド値を照合してカーディナリティ予測を生成しました。
  • バインド変数の数は内部で定義されているしきい値を超えません。
20.1.3.3 バインド変数の照合

バインド変数の照合(バインド照合とも呼ばれる)では、オプティマイザはデータベースが文のハード解析を実行しているときにバインド変数の値を参照します。

オプティマイザは、すべての解析の前にバインド変数の値を参照しません。かわりに、オプティマイザの照合が行われるのは、オプティマイザが最初に起動されたときのみで、これはハード解析中です。

問合せがリテラルを使用する場合、オプティマイザはリテラル値を使用して最良の計画を見つけることができます。ただし、問合せがバインド変数を使用する場合は、オプティマイザはSQLテキストにリテラルが存在しない状態で最良の計画を選択する必要があります。このタスクは極めて困難になる可能性があります。最初のハード解析中にバインド値を照合することにより、オプティマイザは、まるでリテラルが使用されたかのようにWHERE句条件のカーディナリティを判断することができ、これによって計画が改善されます。

オプティマイザはハード解析中にバインド値でのみ照合するため、計画は可能なすべてのバインド値に対して最適ではない可能性があります。次の例に、この原則を示します。

例20-8 異なる実行計画でのリテラルの結果

次のような文を実行するとします。異なるリテラル(101、120および165)を使用した3つの異なる文を実行し、それぞれの実行計画を表示します。 

SET LINESIZE 167
SET PAGESIZE 0
SELECT SUM(salary) FROM hr.employees WHERE employee_id < 101;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
SELECT SUM(salary) FROM hr.employees WHERE employee_id < 120;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
SELECT SUM(salary) FROM hr.employees WHERE employee_id < 165;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

データベースは3つの文(同一ではない)をすべてハード解析しました。わかりやすく編集されたDISPLAY_CURSORの出力では、オプティマイザは、最初の2つの文では同じ索引範囲スキャン計画を選択し、リテラル165を使用した文では全表スキャン計画を選択したことが示されています。 

SQL_ID	cn5250y0nqpym, child number 0
-------------------------------------
SELECT SUM(salary) FROM hr.employees WHERE employee_id < 101

Plan hash value: 2410354593

-------------------------------------------------------------------------------------
|Id| Operation                            | Name         |Rows|Bytes|Cost(%CPU)|Time|
-------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                     |               |  |   |2 (100)|          |
| 1|  SORT AGGREGATE                      |               |1 | 8 |       |          |
| 2|   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES     |1 | 8 |2 (0)  | 00:00:01 |
|*3|    INDEX RANGE SCAN                  | EMP_EMP_ID_PK |1 |   |1 (0)  | 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("EMPLOYEE_ID"<101)

SQL_ID	au8nag2vnfw67, child number 0
-------------------------------------
SELECT SUM(salary) FROM hr.employees WHERE employee_id < 120

Plan hash value: 2410354593

-------------------------------------------------------------------------------------
|Id| Operation                            | Name         |Rows|Bytes|Cost(%CPU)|Time|
-------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                     |               |  |   |2 (100)|          |
| 1|  SORT AGGREGATE                      |               |1 | 8 |       |          |
| 2|   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES     |20|160|2 (0)  | 00:00:01 |
|*3|    INDEX RANGE SCAN                  | EMP_EMP_ID_PK |20|   |1 (0)  | 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("EMPLOYEE_ID"<120)

SQL_ID	b1tvfcc5qnczb, child number 0
-------------------------------------
SELECT SUM(salary) FROM hr.employees WHERE employee_id < 165

Plan hash value: 1756381138

-------------------------------------------------------------------------
| Id  | Operation          | Name      |Rows| Bytes |Cost(%CPU)| Time   |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    |       | 2 (100)|          |
|   1 |  SORT AGGREGATE    |           |  1 |     8 |        |          |
|*  2 |   TABLE ACCESS FULL| EMPLOYEES | 66 |   528 | 2   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("EMPLOYEE_ID"<165)

前述の出力では、多くの行を戻す問合せには、索引スキャンより全表スキャンのほうが効率的であるとオプティマイザがみなしていることを示しています。

例20-9 カーソル再利用でのバインド変数の結果

この例では、例20-8で実行された問合せを、リテラルではなくバインド変数を使用するようにリライトしています。同じ値(101、120および165)をバインド変数:emp_idにバインドし、それぞれの実行計画を表示します。 

VAR emp_id NUMBER

EXEC :emp_id := 101;
SELECT SUM(salary) FROM hr.employees WHERE employee_id < :emp_id;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
EXEC :emp_id := 120;
SELECT SUM(salary) FROM hr.employees WHERE employee_id < :emp_id;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
EXEC :emp_id := 165;
SELECT SUM(salary) FROM hr.employees WHERE employee_id < :emp_id;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

DISPLAY_CURSORの出力では、オプティマイザは、3つのすべての文でまったく同じ計画を選択することを示しています。 

SELECT SUM(salary) FROM hr.employees WHERE employee_id < :emp_id

Plan hash value: 2410354593

-------------------------------------------------------------------------------------
| Id | Operation                            | Name      |Rows|Bytes|Cost (%CPU)|Time|
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |               | |  |2 (100)|          |
|  1 |  SORT AGGREGATE                      |               |1|8 |       |          |
|  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES     |1|8 |  2 (0)| 00:00:01 |
|* 3 |    INDEX RANGE SCAN                  | EMP_EMP_ID_PK |1|  |  1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("EMPLOYEE_ID"<:EMP_ID)

これに対して、前述の文がリテラルを使用して実行された場合、従業員IDの値が165のときは、オプティマイザはコストの低い全表スキャンを選択します。これは、適応カーソル共有によって解決される問題です。

関連項目:

適応カーソル共有

20.1.4 共有カーソルのライフサイクルについて

オプティマイザがDDLでない新しいSQL文を解析する場合、データベースは新しい共有SQL領域を割り当てます。必要なメモリー量は、文の複雑度に応じて決められます。

共有SQL領域が長時間使用されていないオープンしているカーソルに対応していても、その共有SQL領域を共有プールから削除できます。オープンしているカーソルが、後でその文を実行するために使用される場合、その文はデータベースにより再解析されて新しい共有SQL領域が割り当てられます。データベースは、文を実行しているか、行が完全にフェッチされていないカーソルを削除しません。

依存するスキーマ・オブジェクトまたはオプティマイザ統計の変更のため、共有SQL領域が無効になる可能性があります。Oracle Databaseは、カーソル・ライフサイクルを管理する2つの手法(無効化とローリング無効化)を使用します。

関連項目:

共有プールのメモリー割当ての概要は、『Oracle Database概要』を参照してください

20.1.4.1 無効のマークが付けられたカーソル

共有SQL領域が無効とマークされている場合、データベースによりしばらく使用されていない有効なカーソルとともに共有プールから削除できます。

状況によっては、データベースは共有プールの無効な共有SQL領域に関連付けられている文を実行する必要があります。この場合、データベースは実行の前に文のハード解析を実行します。

次の条件が満たされている場合、データベースは依存する共有SQL領域をただちに無効とマークします。

  • DBMS_STATSは、NO_INVALIDATEパラメータがFALSEの場合、表、表クラスタまたは索引の統計を収集します。

  • SQL文は、即時のカーソル無効化(デフォルト)を使用するDDL文によって後で変更されるスキーマ・オブジェクトを参照します。

    ALTER TABLE ... IMMEDIATE VALIDATIONおよびALTER INDEX ... IMMEDIATE VALIDATIONなどの文の即時の無効化を手動で指定したり、セッションまたはシステム・レベルでCURSOR_INVALIDATION初期化パラメータをIMMEDIATEに設定できます。

    ノート:

    DEFERRED VALIDATION句を使用したDDL文が、CURSOR_INVALIDATION初期化パラメータのIMMEDIATE設定をオーバーライドします。

前述の条件が満たされると、データベースにより、次の実行で影響する文が再解析されます。

データベースがカーソルを無効化すると、V$SQL.INVALIDATIONS値は増加し(たとえば、0から1)、V$SQL.OBJECT_STATUSはINVALID_UNAUTHを示します。

例20-10 NO_INVALIDATE=FALSEを設定したカーソルの無効化の強制

この例では、管理者権限を付与されたユーザーshとしてログインします。この例では、salesを問い合せた後、NO_INVALIDATE=FALSEを使用してこの表の統計を収集します。その後で、カーソルのV$SQL.INVALIDATIONS値を0から1に変更して、データベースでカーソルに無効のフラグが付けられたことを示します。 

SQL> SELECT COUNT(*) FROM sales;

  COUNT(*)
----------
    918843

SQL> SELECT PREV_SQL_ID SQL_ID FROM V$SESSION WHERE SID = SYS_CONTEXT('userenv', 'SID');

SQL_ID
-------------
1y17j786c7jbh

SQL> SELECT CHILD_NUMBER, EXECUTIONS,
  2  PARSE_CALLS, INVALIDATIONS, OBJECT_STATUS 
  3  FROM V$SQL WHERE SQL_ID = '1y17j786c7jbh';

CHILD_NUMBER EXECUTIONS PARSE_CALLS INVALIDATIONS OBJECT_STATUS
------------ ---------- ----------- ------------- -------------
           0          1           1             0 VALID

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(null,'sales',no_invalidate => FALSE);

PL/SQL procedure successfully completed.

SQL> SELECT CHILD_NUMBER, EXECUTIONS,
  2  PARSE_CALLS, INVALIDATIONS, OBJECT_STATUS
  3  FROM V$SQL WHERE SQL_ID = '1y17j786c7jbh';

CHILD_NUMBER EXECUTIONS PARSE_CALLS INVALIDATIONS OBJECT_STATUS
------------ ---------- ----------- ------------- --------------
           0          1           1             1 INVALID_UNAUTH

関連項目:

20.1.4.2 ローリング無効のマークが付けられたカーソル

カーソルがローリング無効とマークされている場合(V$SQL.IS_ROLLING_INVALIDがYの場合)、データベースにより、ハード解析が長期にわたって徐々に実行されます。

ノート:

V$SQL.IS_ROLLING_REFRESH_INVALIDがYの場合、基礎となるオブジェクトが変更されていますが、カーソルの再コンパイルは必要ありません。データベースはカーソルのメタデータを更新します。

ローリング無効化の目的

ハード解析の急激な増加はパフォーマンスの大幅な低下につながるため、遅延した無効化とも呼ばれるローリング無効化は、多数のカーソルを同時に無効にするワークロードに役立ちます。データベースは、無効なカーソルのそれぞれにランダムに生成された期間を割り当てます。通常同時に無効化したSQL領域の期間は異なります。

ハード解析は、期間が過ぎた後で、カーソルにアクセスする問合せを実行する場合にのみ発生します。これにより、データベースでは、一定期間のハード解析のオーバーヘッドが分散されます。

ノート:

パラレルSQL文がローリング無効とマークされている場合、データベースでは、有効期限が切れているかどうかに関係なく、次の実行でハード解析を実行します。Oracle Real Application Clusters (Oracle RAC)環境では、この手法により、パラレル実行サーバーの実行計画と問合せコーディネータ間の一貫性が確保されます。

ローリング無効化は、古くなった事務用家具の順次交換に例えることができます。すべての家具を一度に交換して大きな財務支出を強いるかわりに、企業が各家具に異なる有効期限を割り当てます。年間を通じて、備品は交換するまで使用され、費用の支出は交換の時点で発生します。

遅延した無効化の指定

デフォルトでは、DDLは、オブジェクトにアクセスする文が即時のカーソル無効化を使用するように指定します。たとえば、表や索引を作成すると、その表や索引を参照するカーソルは即時の無効化を使用するようになります。

DDL文が遅延したカーソル無効化をサポートしている場合は、ALTER TABLE ... DEFERRED INVALIDATIONなどの文を使用して、デフォルトの動作をオーバーライドできます。オプションは、DDL文によって異なります。たとえば、ALTER INDEXは、UNUSABLEオプションまたはREBUILDオプションも指定されている場合にのみ、DEFERRED INVALIDATIONをサポートします。

DDLのかわりに、セッション・レベルまたはシステム・レベルで、CURSOR_INVALIDATION初期化パラメータをDEFERREDに設定するという方法もあります。IMMEDIATE INVALIDATION句を使用するDDL文は、CURSOR_INVALIDATION初期化パラメータのDEFERRED設定をオーバーライドします。

ローリング無効化が発生する状況

DDLまたは初期化パラメータの結果として、DEFERRED INVALIDATION属性がオブジェクトに適用されると、そのオブジェクトにアクセスする文は、遅延した無効化の影響を受ける可能性があります。データベースは、次のどちらかの状況で、共有SQL領域にローリング無効のマークを付けます。

  • NO_INVALIDATEパラメータがDBMS_STATS.AUTO_INVALIDATEに設定されている場合、DBMS_STATSは、表、表クラスタまたは索引の統計を収集します。これがデフォルトの設定です。

  • 遅延した無効化の使用が抑止されていない状況で、次のいずれかの文が、DEFERRED INVALIDATIONを指定して発行された場合。

    DDL文のサブセットは、DML (INSERT、UPDATE、DELETEまたはMERGE)に対して即時のカーソル無効化が必要になりますが、SELECT文では必要になりません。特定のDDL文と影響受けるカーソルに関連する多数の要因により、Oracle Databaseが遅延した無効化を使用するかどうかが決まります。

関連項目:

20.2 CURSOR_SHARINGおよびバインド変数置換

このトピックでは、CURSOR_SHARING初期化パラメータとは何か、このパラメータを異なる値に設定した場合のOracle Databaseによるバインド変数の使用方法への影響について説明します。

20.2.1 CURSOR_SHARING初期化パラメータ

CURSOR_SHARING初期化パラメータは、データベースによるバインド変数を使用した文の処理方法を制御します。

Oracle Database 12cでは、このパラメータは次の値をサポートします。

  • EXACT

    これはデフォルト値です。データベースでは、テキスト的に同じ文のみがカーソルを共有できます。データベースは、リテラル値をシステム生成のバインド変数に置換しようとしません。この場合、オプティマイザは、リテラル値に基づいて各文の計画を生成します。

  • FORCE

    データベースは、すべてのリテラルをシステム生成のバインド変数に置換します。バインド変数でリテラルが置換された後に同一となった文に対して、オプティマイザは同じ計画を使用します。

ノート:

CURSOR_SHARINGのSIMILAR値は非推奨です。

CURSOR_SHARINGはシステム・レベルまたはセッション・レベルで設定でき、CURSOR_SHARING_EXACTヒントは文レベルで使用できます。

関連項目:

「CURSOR_SHARING = FORCEを永続的な修正として使用しない」

20.2.2 CURSOR_SHARING = FORCEの場合の動作の解析

SQL文がバインド変数よりもリテラルを使用する場合、CURSOR_SHARING初期化パラメータをFORCEに設定すると、データベースはリテラルをシステム生成のバインド変数と置き換えることができます。この方法を使用して、データベースは共有SQL領域の親カーソルの数を減らせることがあります。

ノート:

文でORDER BY句が使用されている場合、定数列番号をリテラルと解釈することは意味的に正しくないため、データベースはこの句ではリテラル置換を行いません。ORDER BY句の列番号は問合せ計画と実行に影響するため、データベースは異なる列番号を持つ2つのカーソルを共有することはできません。

CURSOR_SHARINGがFORCE,に設定されている場合、データベースは解析中に次のステップを実行します。

  1. 文のすべてのリテラルをPGAにコピーし、システム生成のバインド変数に置換します

    たとえば、アプリケーションでは次の文を処理できます。

    SELECT SUBSTR(last_name, 1, 4), SUM(salary) 
FROM   hr.employees 
WHERE  employee_id < 101 GROUP BY last_name

    次のように、オプティマイザは、SUBSTR関数内のリテラルを含むリテラルを置換します。 

    SELECT SUBSTR(last_name, :"SYS_B_0", :"SYS_B_1"), SUM(salary) 
FROM   hr.employees 
WHERE  employee_id < :"SYS_B_2" GROUP BY last_name

  2. 共有プールで同一文(同じSQLハッシュ値)を検索します

    同一文が見つからない場合は、データベースはハード解析を実行します。そうでない場合は、データベースは次のステップに進みます。

  3. 文のソフト解析を実行します

前述のステップに示すように、CURSOR_SHARING初期化パラメータをFORCEに設定しても、解析数は減りません。かわりに、場合によっては、FORCEを使用するとデータベースでハード解析ではなくソフト解析を実行できます。また、インジェクションがすでに発生した後でOracle Databaseで値をバインドするため、FORCEによってSQLインジェクション攻撃が回避されません。

例20-11 システムのバインド変数へのリテラルの置換

この例では、セッション・レベルでCURSOR_SHARINGをFORCEに設定し、リテラルを含む3つの文を実行し、各文の計画を表示します。 

ALTER SESSION SET CURSOR_SHARING=FORCE;
SET LINESIZE 170
SET PAGESIZE 0
SELECT SUM(salary) FROM hr.employees WHERE employee_id < 101;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
SELECT SUM(salary) FROM hr.employees WHERE employee_id < 120;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
SELECT SUM(salary) FROM hr.employees WHERE employee_id < 165;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

読みやすく編集された次のDISPLAY_CURSORの出力では、3つの文はすべて同じ計画を使用したことを示しています。オプティマイザはその計画、索引レンジ・スキャンを選択します。これは、システムのバインド変数にバインドされた最初の値(101)を照合し、すべての値に最適なものとしてこの計画を選択したためです。実際には、この計画はすべての値に最適な計画ではありません。値が165の場合、全表スキャンのほうが効率的です。 

SQL_ID	cxx8n1cxr9khn, child number 0
-------------------------------------
SELECT SUM(salary) FROM hr.employees WHERE employee_id < :"SYS_B_0"

Plan hash value: 2410354593

-------------------------------------------------------------------------------------
| Id  | Operation                           | Name       |Rows|Bytes|Cost(%CPU)|Time|
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |               |  |   |2 (100)|        |
|  1 |  SORT AGGREGATE                      |               |1 | 8 |       |        |
|  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES     |1 | 8 |2 (0)  |00:00:01|
|* 3 |    INDEX RANGE SCAN                  | EMP_EMP_ID_PK |1 |   |1 (0)  |00:00:01|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("EMPLOYEE_ID"<101)

問合せのV$SQLAREAは、Oracle Databaseがリテラルをシステムのバインド変数:”SYS_B_0”に置換したこと、および3つのすべての文に1つの親カーソルと1つの子カーソル(VERSION_COUNT=1)を作成した(つまりすべての実行は同じ計画を共有した)ことを確認します。 

COL SQL_TEXT FORMAT a36
SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE
FROM   V$SQLAREA
WHERE  SQL_TEXT LIKE '%mployee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT                             SQL_ID        VERSION_COUNT HASH_VALUE
------------------------------------ ------------- ------------- ----------
SELECT SUM(salary) FROM hr.employees cxx8n1cxr9khn             1  997509652
 WHERE employee_id < :"SYS_B_0"

関連項目:

20.3 適応カーソル共有

適応カーソル共有機能により、バインド変数を含む1つの文が複数の実行計画を使用できるようになります。

カーソル共有には「適応」機能があります。つまり、データベースが各実行または各バインド変数値に常に同じ計画を使用することがないように、カーソルがその動作を適応させます。

20.3.1 適応カーソル共有の目的

バインド照合では、オプティマイザは、カーソルの最初の起動時にユーザー定義のバインド変数の値を照合します。

オプティマイザは、バインド変数のかわりにまるでリテラルが使用されたかのように、WHERE句条件のカーディナリティを判断します。ただし、WHERE句の列に偏ったデータがある場合、この列にヒストグラムが存在する可能性があります。オプティマイザがユーザー定義のバインド変数の値を照合し、計画を選択したとき、その計画がすべての値にとって最適ではない可能性があります。

適応カーソル共有では、データベースは、様々なバインド値に対して時間の経過とともにアクセスされるデータを監視し、特定のバインド値に対して最適なカーソルを確実に選択できるようにします。たとえば、オプティマイザは、バインド値10とバインド値50に対して、それぞれ異なる計画を選択することもあります。カーソル共有には「適応」機能があります。つまり、オプティマイザが各実行または各バインド変数値に常に同じ計画を選択することがないように、カーソルがその動作を適応させます。そのため、オプティマイザは、文の異なる実行により異なる実行計画の利点を得るタイミングを自動的に検出します。

ノート:

適応カーソル共有は、CURSOR_SHARING初期化パラメータとは独立しています。適応カーソル共有は、ユーザー定義のバインド変数やシステム生成のバインド変数を含む文にも同様に適用できます。適応カーソル共有は、リテラルのみを含む文には適用されません。

20.3.2 適応カーソル共有の仕組み: 例

適応カーソル共有では、バインド変数を使用する文を監視して、新しい計画が効率的であるかどうかを判断します。

アプリケーションで、毎回異なる値をバインドする次の文を5回実行するとします。

SELECT * FROM employees WHERE salary = :sal AND department_id = :dept

また、この例では、述語の少なくとも1つの列にヒストグラムが存在するとします。データベースは、この文を次のように処理します。

  1. アプリケーションが文を最初に発行すると、ハード解析が行われます。解析中、データベースは次のタスクを実行します。

    • バインド変数を照合して初期計画を生成します。

    • カーソルをバインド依存とマークします。バインド依存カーソルは、最適な計画がバインド変数の値に依存するカーソルです。別の計画が有効であるかどうかを判断するために、データベースは、別のバインド値を使用するバインド依存カーソルの動作を監視します。

    • バインドされた値のカーディナリティなど、述語に関するメタデータを格納します(この例では、5行のみが戻されたとします)。

    • ピーク値に基づいて実行計画(この例では索引アクセス)を作成します。

  2. データベースはカーソルを実行し、バインド値および実行統計をカーソルに格納します。

  3. アプリケーションは、異なるバインド変数を使用して、2回目に文を発行します。データベースはソフト解析を実行し、ライブラリ・キャッシュで一致するカーソルを検出します。

  4. データベースはカーソルを実行します。

  5. データベースは、次の実行後タスクを実行します。

    1. データベースは、2回目の実行の実行統計を1回目の実行統計と比較します。

    2. データベースは、前のすべての実行にわたって統計のパターンを監視して、カーソルをバインド対応カーソルとマークするかどうかを判断します。この例では、データベースはカーソルをバインド対応と判断すると仮定します。

  6. アプリケーションが異なるバインド変数を使用して3回目に文を発行すると、ソフト解析が行われます。カーソルがバインド対応であるため、データベースは次のことを行います。

    • 新しい値のカーディナリティが、格納されたカーディナリティと同じ範囲内にあるかどうかを判断します。この例では、カーディナリティは5行ではなく8行です。

    • 既存の子カーソルの実行計画を再利用します。

  7. データベースはカーソルを実行します。

  8. アプリケーションが異なるバインド変数を使用して4回目に文を発行すると、ソフト解析が行われます。カーソルがバインド対応であるため、データベースは次のことを行います。

    • 新しい値のカーディナリティが、格納されたカーディナリティと同じ範囲内にあるかどうかを判断します。この例では、カーディナリティは大きく異なります。5行ではなく102行(107行が含まれる表)です。

    • 一致する子カーソルは見つけません。

  9. データベースはハード解析を実行します。その結果、データベースは次を行います。

    • 2回目の実行計画(この例では全表スキャン)で新しい子カーソルを作成します

    • バインドされた値のカーディナリティなど、述語に関するメタデータをカーソルに格納します

  10. データベースは新しいカーソルを実行します。

  11. データベースは、新しいバインド値および実行統計を新しい子カーソルに格納します。

  12. アプリケーションが異なるバインド変数を使用して5回目に文を発行すると、ソフト解析が行われます。カーソルがバインド対応であるため、データベースは次のことを行います。

    • 新しい値のカーディナリティが、格納されたカーディナリティと同じ範囲内にあるかどうかを判断します。この例では、カーディナリティは20です。

    • 一致する子カーソルは見つけません。

  13. データベースはハード解析を実行します。その結果、データベースは次を行います。

    1. 3回目の実行計画(この例では索引アクセス)で新しい子カーソルを作成します

    2. この索引アクセスの実行計画は、文の最初の実行に使用された索引アクセスの実行計画と同じであると判断します

    3. 索引アクセスの計画を含む2つの子カーソルをマージします。組み合せたカーディナリティ統計が1つの子カーソルに格納され、もう1つの子カーソルは削除されます

  14. データベースは、索引アクセスの実行計画を使用してカーソルを実行します。

20.3.3 バインド依存カーソル

バインド依存カーソルは、最適な計画がバインド変数の値に依存するカーソルです。

データベースは、カーディナリティの計算時にバインド値を調べ、異なるバインド値に基づいた計画変更に対して問合せ依存であるとみなします。データベースは、別の計画が有効であるかどうかを判断するために、別のバインド値を使用するバインド依存カーソルの動作を監視します。

オプティマイザは、次の基準を使用して、カーソルがバインド依存であるかどうかを判断します。

  • オプティマイザはバインド値を照合してカーディナリティ予測を生成しました。

  • バインドが等価述語または範囲述語で使用されます。

新しいバインド値で問合せを実行するごとに、データベースは新しい値の実行統計を記録し、それらを前の値の実行統計と比較します。実行統計が大幅に異なると、データベースはカーソルをバインド対応としてマークします。

例20-12 大きなデータの偏りがある列

この例で、hr.employees.department_id列には大きなデータの偏りがあるとします。SYSTEMは次の設定コードを実行します。サンプル・スキーマのemployees表に部門50の従業員100,000人を追加し、合計は100,107行になります。その後、表統計を収集します。 

DELETE FROM hr.employees WHERE employee_id > 999;

ALTER TABLE hr.employees DISABLE NOVALIDATE CONSTRAINT emp_email_uk;

DECLARE
v_counter NUMBER(7) := 1000;
BEGIN
 FOR i IN 1..100000 LOOP    
 INSERT INTO hr.employees 
   VALUES (v_counter, null, 'Doe', 'Doe@example.com', null,'07-JUN-02',
     'AC_ACCOUNT', null, null, null, 50);
 v_counter := v_counter + 1;
 END LOOP;
END;
/
COMMIT; 

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS (ownname = 'hr',tabname => 'employees');
END;
/

ALTER SYSTEM FLUSH SHARED_POOL;

次の問合せでは、employees.department_id列のヒストグラムを示しています。 

COL TABLE_NAME FORMAT a15
COL COLUMN_NAME FORMAT a20
COL HISTOGRAM FORMAT a9

SELECT TABLE_NAME, COLUMN_NAME, HISTOGRAM
FROM   DBA_TAB_COLS
WHERE  OWNER = 'HR'
AND    TABLE_NAME = 'EMPLOYEES'
AND    COLUMN_NAME = 'DEPARTMENT_ID';

TABLE_NAME      COLUMN_NAME          HISTOGRAM
--------------- -------------------- ---------
EMPLOYEES       DEPARTMENT_ID        FREQUENCY

例20-13 カーディナリティの低い問合せ

この例は、例20-12の例の続きです。次の問合せでは、列department_idに対する値10は極端にカーディナリティが低く、行数の0.00099%を占めていることを示しています。 

VARIABLE dept_id NUMBER
EXEC :dept_id := 10;
SELECT COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id;

  COUNT(*) MAX(EMPLOYEE_ID)
---------- ----------------
         1              200

オプティマイザは、このようなカーディナリティの低い問合せの場合に予想されたように、索引レンジ・スキャンを選択します。 

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID	a9upgaqqj7bn5, child number 0
-------------------------------------
select COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id

Plan hash value: 1642965905

-------------------------------------------------------------------------------------
| Id| Operation                           | Name       |Rows|Bytes|Cost (%CPU)|Time |
-------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                     |                   |  |  |2(100)|        |
| 1|  SORT AGGREGATE                      |                   |1 |8 |      |        |
| 2|   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |1 |8 |2  (0)|00:00:01|
|*3|    INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |1 |  |1  (0)|00:00:01|
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPARTMENT_ID"=:DEPT_ID)

次の問合せのV$SQLは、カーソルに関する情報を取得します。 

COL BIND_AWARE FORMAT a10
COL SQL_TEXT FORMAT a22
COL CHILD# FORMAT 99999
COL EXEC FORMAT 9999
COL BUFF_GETS FORMAT 999999999
COL BIND_SENS FORMAT a9
COL SHARABLE FORMAT a9

SELECT SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC, 
       BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS, 
       IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHARABLE
FROM   V$SQL
WHERE  SQL_TEXT LIKE '%mployee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT               CHILD#  EXEC  BUFF_GETS BIND_SENS BIND_AWARE SHARABLE
---------------------- ------ ----- ---------- --------- ---------- --------
SELECT COUNT(*), MAX(e      0     1        196         Y          N        Y
mployee_id) FROM hr.em
ployees WHERE departme
nt_id = :dept_id

前述の出力は、カーディナリティの低い問合せで1回実行された1つの子カーソルを示しています。最適な計画がバインド変数の値に依存することがオプティマイザで認識されているため、カーソルはバインド依存とマークされました。

カーソルがバインド依存とマークされると、別のバインド値には別の計画がより効率的であるかどうかを判断するために、Oracle Databaseは別のバインド値を使用するカーソルの動作を監視します。オプティマイザはdepartment_id列でヒストグラムを使用して述語WHERE department_id = :dept_idの選択性を計算したため、データベースはこのカーソルをバインド依存とマークしました。ヒストグラムの存在は列が偏っていることを示すため、バインド変数の異なる値に異なる計画が必要になる可能性があります。

例20-14 カーディナリティの高い問合せ

この例は、例20-13の例の続きです。次のコードでは、値50 (行数の99.9%を占める)を使用して同じ問合せを再実行します。 

EXEC :dept_id := 50;
SELECT COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id;

  COUNT(*) MAX(EMPLOYEE_ID)
---------- ----------------
    100045	     100999

このような非選択性の問合せは全表スキャンを使用したほうが効率的であっても、オプティマイザは、department_id=10で使用された同じ索引レンジ・スキャンを選択します。理由は、カーソル内の既存の計画を共有できると仮定されているためです。 

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID	a9upgaqqj7bn5, child number 0
-------------------------------------
SELECT COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id

Plan hash value: 1642965905

-------------------------------------------------------------------------------------
| Id| Operation                           | Name       |Rows|Bytes|Cost (%CPU)|Time |
-------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                     |                   |  |  |2(100)|        |
| 1|  SORT AGGREGATE                      |                   |1 |8 |      |        |
| 2|   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |1 |8 |2  (0)|00:00:01|
|*3|    INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |1 |  |1  (0)|00:00:01|
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPARTMENT_ID"=:DEPT_ID)

問合せのV$SQLは、子カーソルが2回実行されたことを示しています。 

SELECT SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC, 
       BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS, 
       IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHARABLE
FROM   V$SQL
WHERE  SQL_TEXT LIKE '%mployee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT               CHILD#  EXEC  BUFF_GETS BIND_SENS BIND_AWARE SHARABLE
---------------------- ------ ----- ---------- --------- ---------- --------
SELECT COUNT(*), MAX(e      0     2       1329         Y          N        Y
mployee_id) FROM hr.em
ployees WHERE departme
nt_id = :dept_id

この段階では、オプティマイザはカーソルをバインド対応とまだマークしていません。

ノート:

バインドの機密性が使用されない状況があります。具体的には、次のとおりです:
  • PL/SQL配列など、コレクション・バインド変数が使用される場合。
  • バインド変数の数が内部制限を超える場合。

関連項目:

V$SQLについて学習するには、『Oracle Databaseリファレンス』を参照してください

20.3.4 バインド対応カーソル

バインド対応カーソルは、バインド値ごとに異なる計画を使用するのに適したバインド依存カーソルです。

カーソルをバインド対応にした後で、オプティマイザは、バインド値とそのカーディナリティの見積りを基に、今後の実行のための計画を選択します。そのため、「バインド対応」は基本的に、現在のバインド値に最適な計画を意味します。

バインド依存カーソルを含む文を実行する場合、オプティマイザは、内部アルゴリズムを使用して、カーソルをバインド対応としてマークするかどうかを判断します。これは、カーソルが生成するデータ・アクセス・パターンが、バインド値によって大幅に異なるかどうかによって判断され、結果としてパフォーマンス・コストが予測とは異なります。

データベースがカーソルをバインド対応としてマークすると、次回そのカーソルが実行されたときに、データベースは次のことを行います。

  • バインド値に基づいて、新規計画を生成します

  • その文に生成された元のカーソルを共有不可(V$SQL.IS_SHAREABLEがN)としてマークします。元のカーソルは使用できなくなり、ライブラリ・キャッシュからのエージ・アウトの対象となります

同じ問合せが別のバインド値を使用して繰り返し実行されると、データベースは新しいバインド値をSQL文のシグネチャ(オプティマイザ環境、NLS設定などを含む)に追加し、値を分類します。データベースはバインド値を調べ、現在のバインド値のデータ量が大幅に異なるか、または既存の計画が十分であるかを検討します。データベースは、新しい値ごとに新しい計画を作成する必要はありません。

12個の異なるバインド値を含む文を実行するシナリオを考えてみます(それぞれの異なる値を2回実行します)これにより、データベースは5つのハード解析をトリガーし、2つの追加の計画を作成します。データベースは5つのハード解析を実行するため、一部のカーソルに既存のカーソルと同じ実行計画がある場合でも、5つの新しい子カーソルが作成されます。データベースは余分なカーソルを使用不可とマークし、これらのカーソルは最終的にライブラリ・キャッシュからエージ・アウトされます。

最初のハード解析中に、オプティマイザは、基本的にはバインド値と適切な実行計画との間の関係をマップしています。この最初の期間の後、データベースは最終的には安定した状態になります。新しいバインド値で実行すると、ハード解析を必要とせずに、キャッシュ内の最適な子カーソルが選択されます。そのため、解析の数は異なるバインド値の数と対応しません。

例20-15 バインド対応カーソル

この例は、「バインド依存カーソル」の例の続きです。次のコードでは、2番目の問合せemployeesをバインド変数を50に設定して発行します。 

EXEC :dept_id := 50;
SELECT COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id;

  COUNT(*) MAX(EMPLOYEE_ID)
---------- ----------------
    100045           100999

最初の2つの実行中、データベースは問合せの動作を監視し、異なるバインド値によって問合せのカーディナリティが大幅に異なると判断しました。この違いに基づいて、同じ計画が常にこの問合せに対して共有されることがないように、データベースがその動作を適応させます。そのため、オプティマイザは、現在のバインド値(50)に基づいて新しい計画を生成します。 

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID	a9upgaqqj7bn5, child number 1
-------------------------------------
SELECT COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id

Plan hash value: 1756381138

---------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost(%CPU)| Time  |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |           |       |      |254 (100)|          |
|  1 |  SORT AGGREGATE    |           |     1 |    8 |         |          |
|* 2 |   TABLE ACCESS FULL| EMPLOYEES |  100K | 781K |254  (15)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DEPARTMENT_ID"=:DEPT_ID)

次の問合せのV$SQLは、カーソルに関する情報を取得します。 

SELECT SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC, 
       BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS, 
       IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHAREABLE
FROM   V$SQL
WHERE  SQL_TEXT LIKE '%mployee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT               CHILD#  EXEC  BUFF_GETS BIND_SENS BIND_AWARE SHAREABLE
---------------------- ------ ----- ---------- --------- ---------- ---------
SELECT COUNT(*), MAX(e      0     2       1329         Y          N         N
mployee_id) FROM hr.em
ployees WHERE departme
nt_id = :dept_id

SELECT COUNT(*), MAX(e      1     1        800         Y          Y         Y
mployee_id) FROM hr.em
ployees WHERE departme
nt_id = :dept_id

前述の出力は、データベースが追加の子カーソル(CHILD#が1)を作成したことを示しています。カーソル0は共有不可とマークされています。カーソル1は、カーソル0よりバッファ取得の数が少ないことを示し、バインド依存とバインド対応の両方としてマークされています。バインド対応カーソルは、バインド変数を含む述語の選択性に応じて、異なるバインド値に異なる計画を使用することがあります。

例20-16 バインド対応カーソル: 最適な計画の選択

この例は、例20-15の例の続きです。次のコードでは、極端にカーディナリティが低い(1行のみ)、値が10の同じemployeesの問合せを実行します。 

EXEC :dept_id := 10;
SELECT COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id;

  COUNT(*) MAX(EMPLOYEE_ID)
---------- ----------------
         1              200

次の出力では、オプティマイザは、現在のバインド値10に対する低いカーディナリティ予測に基づいて、最適な計画(索引スキャン)を選択したことを示しています。 

SQL> SELECT * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID	a9upgaqqj7bn5, child number 2
-------------------------------------
select COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id

Plan hash value: 1642965905

-------------------------------------------------------------------------------------
| Id| Operation                           | Name       |Rows|Bytes|Cost (%CPU)|Time |
-------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                     |                   |  |  |2(100)|        |
| 1|  SORT AGGREGATE                      |                   |1 |8 |      |        |
| 2|   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |1 |8 |2  (0)|00:00:01|
|*3|    INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX | 1|  |1  (0)|00:00:01|
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPARTMENT_ID"=:DEPT_ID)

V$SQLの出力は、3つの子カーソルが存在することを示しています。 

SELECT SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC, 
       BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS, 
       IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHAREABLE
FROM   V$SQL
WHERE  SQL_TEXT LIKE '%mployee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT               CHILD#  EXEC  BUFF_GETS BIND_SENS BIND_AWARE SHAREABLE
---------------------- ------ ----- ---------- --------- ---------- ---------
SELECT COUNT(*), MAX(e      0     2       1329         Y          N         N
mployee_id) FROM hr.em
ployees WHERE departme
nt_id = :dept_id

SELECT COUNT(*), MAX(e      1     1        800         Y          Y         Y
mployee_id) FROM hr.em
ployees WHERE departme
nt_id = :dept_id

SELECT COUNT(*), MAX(e      2     1          3         Y          Y         Y
mployee_id) FROM hr.em
ployees WHERE departme
nt_id = :dept_id

カーソルがバインド対応モードに切り替えられると、データベースは元のカーソル(CHILD#が0)を破棄しました。これは1回かぎりのオーバーヘッドです。データベースはカーソル0を共有不可とマークし(SHAREABLEがN)、これは、このカーソルは使用できず、カーソル・キャッシュから最初にエージ・アウトされることを意味します。

関連項目:

V$SQLについて学習するには、『Oracle Databaseリファレンス』を参照してください

20.3.5 カーソルのマージ

オプティマイザがバインド対応カーソルの計画を作成し、この計画が既存のカーソルと同じである場合、オプティマイザはカーソルのマージを実行できます。

この場合、データベースはライブラリ・キャッシュの領域を節約するためにカーソルをマージします。データベースは、新しいバインド値の選択性を含めるために、カーソルの選択性の範囲を拡大します。

問合せが新しいバインド変数を使用すると、オプティマイザは、バインド値の選択性の類似に基づいて適合すると思われるカーソルを見つけようとします。データベースでこのようなカーソルが見つからない場合、新たに作成します。新しいカーソルの計画が既存のカーソルの計画と同じ場合、データベースはライブラリ・キャッシュの領域を節約するために2つのカーソルをマージします。マージによって、データベースは1つのカーソルを共有不可とマークします。ライブラリ・キャッシュが領域削減の必要に迫られている場合、データベースは共有不可のカーソルを最初にエージ・アウトします。

関連項目:

「例20-12」

20.3.6 適応カーソル共有のビュー

適応カーソル共有では、V$ビューを使用して選択性の範囲、カーソル情報(カーソルがバインド対応かバインド依存かなど)および実行統計を表示できます。

具体的には、次のビューを使用します。

  • V$SQLは、カーソルがバインド依存とバインド対応のどちらであるかを示します。

  • V$SQL_CS_HISTOGRAMは、3バケットの実行履歴ヒストグラムにより、実行カウントの配分を示します。

  • V$SQL_CS_SELECTIVITYは、カーソル共有をチェックするために選択性が使用された場合に、バインド変数を含むすべての述語に格納されている選択性の範囲を示します。これには、述語のテキスト、および選択性の範囲の下限値と上限値が含まれています。

  • V$SQL_CS_STATISTICSは、カーソルをバインド対応としてマークするかどうかを判断するためにオプティマイザが使用する情報を示します。実行のサンプルでは、データベースは、処理された行、取得するバッファ、およびCPU時間を追跡します。バインド・セットがカーソルの作成に使用された場合、PEEKED列はYESを示します。それ以外の場合、この値はNOです。

関連項目:

V$SQLおよび関連するビューについて学習するには、Oracle Databaseリファレンスを参照してください

20.4 カーソル共有のためのReal-World Performanceのガイドライン

Real-World Performanceチームは、Oracleデータベース・アプリケーションでカーソル共有を最適化する方法に関するガイドラインを作成しました。

20.4.1 セキュリティおよびパフォーマンスのためのバインド変数を使用したアプリケーションの開発

Real-World Performanceグループでは、すべてのエンタープライズ・アプリケーションでバインド変数を使用することを強くお薦めします。

Oracle Databaseアプリケーションは、バインド変数を使用して記述されることを目的としていました。多数のユーザーが動的な非共有のSQL文を発行するようなアプリケーションを設計しないようにしてください。

Oracle Databaseがライブラリ・キャッシュ内の文の一致の検出に失敗するたびに、ハード解析を実行する必要があります。リテラルを使用してアプリケーションを開発すると危険性があるにもかかわらず、実在のアプリケーションのすべてにバインド変数が使用されているわけではありません。開発者は、リテラルを使用したプログラムを記述するほうが速くて簡単であることに気付く場合があります。しかし、開発時間を削減しても、開発後のパフォーマンスおよびセキュリティが向上するわけではありません。

ビデオ:

バインド変数を使用する主な利点は、次のとおりです。

  • リソースの効率

    すべての実行前にプログラムをコンパイルすると、リソースが効率的に使用されませんが、Oracle Databaseがハード解析を実行する際には、これは基本的に行われています。カーソルの作成、実行計画の生成および評価などを行うために、データベース・サーバーは、大量のCPUとメモリーを消費する必要があります。データベースでカーソルの共有を有効にすることによって、ソフト解析でのリソースの消費がはるかに少なくなります。アプリケーションでバインド変数ではなくリテラルを使用しているが、毎日少数の問合せしか実行されない場合、DBAでは、追加のオーバーヘッドがパフォーマンスの問題として認識されない場合があります。しかし、アプリケーションが1秒当たり何百または何千もの問合せを実行する場合、追加のリソース・オーバーヘッドにより、パフォーマンスが許容不可能なレベルにまで簡単に低下することがあります。バインド変数を使用すると、文が何回実行されても、データベースはハード解析を1回のみ実行できます。

  • スケーラビリティ

    データベースがハード解析を実行すると、共有プールおよびライブラリ・キャッシュでラッチの取得および保持に時間がかかります。ラッチは、低レベルのシリアライズ・デバイスです。データベースによる共有メモリー内の構造のラッチが長く頻繁になるほど、これらのラッチのキューも長くなります。複数の文が同じ実行計画を共有する場合、ラッチに対するリクエストおよびラッチの存続期間が停止します。この動作によってスケーラビリティが向上します。

  • スループットおよびレスポンス時間

    データベースがカーソルの再解析および作成を常に回避すると、ユーザー領域でより多くの時間が費やされます。Real-World Performanceグループでは、リテラルをバインドを使用するように変更すると、スループットおよびユーザーのレスポンス時間を桁違いに向上させることが多いことがわかりました。

    ビデオ:

  • セキュリティ

    SQLインジェクション攻撃を防ぐ唯一の方法は、バインド変数を使用することです。悪意のあるユーザーが、アプリケーションにコードを挿入することによって文字列を連結するアプリケーションを利用する可能性があります。

関連項目:

リテラルによって引き起こされるセキュリティ脆弱性を修正するアプリケーションの例については、『Oracle Database PL/SQL言語リファレンス』を参照してください

20.4.2 CURSOR_SHARING = FORCEを永続的な修正として使用しない

ベスト・プラクティスは、共有可能なSQLを書き込み、CURSOR_SHARINGにデフォルトのEXACTを使用することです。

ただし、多くの類似文のあるアプリケーションでは、CURSOR_SHARINGをFORCEに設定することでカーソル共有が大幅に改善されます。リテラルをシステム生成のバインド値で置き換えると、メモリー使用量が減少し、解析が高速になり、ラッチの競合が減少します。ただし、FORCEは、永続的な開発ソリューションとなることを意図しているわけではありません。

一般的なガイドラインとして、Real-World Performanceグループは、まれな状況の場合を除きCURSOR_SHARINGをFORCEに設定しないことをお薦めします。次のすべての条件を満たす場合のみ設定します。

  • 共有プール内の文は、リテラルの値のみが異なります。

  • 非常に多くのライブラリ・キャッシュ・ミスが発生するため、レスポンス時間が最適ではありません。

  • 既存のコードには、セキュリティおよびスケーラビリティの深刻な不具合(バインド変数がない)があり、ソース・コードが修正されるまで、一時的な対策を講じる必要があります。

  • この初期化パラメータは、インスタンス・レベルではなくセッション・レベルで設定します。

CURSOR_SHARINGをFORCEに設定する場合、次の短所があります。

  • アプリケーションではユーザー定義のバインド変数が使用されません。これはSQLインジェクションに対してオープンになっていることを意味します。CURSOR_SHARINGをFORCEに設定しても、SQLインジェクション・バグが修正されたり、コードがよりセキュアにレンダリングされることはありません。悪意のあるSQLテキストがすでに挿入された後でのみ、データベースは値をバインドします。

    ビデオ:

  • データベースは、共有プール内で類似文を検索するために、ソフト解析の間に追加作業を実行する必要があります。

  • データベースはすべてのリテラルを削除します。これは有効な情報も削除する可能性があることを意味します。たとえば、データベースがSUBSTR関数およびTO_DATE関数のリテラル値を削除するとします。リテラルのほうが最適な場合にシステム生成のバインド変数を使用すると、実行計画に悪影響を及ぼす可能性があります。

  • SELECT文にリテラルを含む選択された式の最大長(DESCRIBEからの戻り値)が増加します。ただし、戻されたデータの実際の長さは変わりません。

  • スター型変換はサポートされません。

関連項目:

20.4.3 カーソル再利用を増やすためのコーディング規則の確立

デフォルトでは、バインド変数の名前など、2つのSQL文のテキストの違いによって、カーソルの共有がデータベースで行われなくなります。また、バインド変数のサイズ変更によって、カーソルの不一致が発生する可能性があります。このため、アプリケーション・コードでバインド変数を使用しても、カーソル共有が十分に保証されるわけではありません。

Real-World Performanceグループでは、SQL文およびPL/SQLブロックの空白設定および大文字/小文字の区別の規則を標準化することをお薦めします。また、バインド変数の命名規則および定義を確立します。データベースが予想どおりにカーソルを共有しない場合、V$SQL_SHARED_CURSORを問い合せることによって診断を開始します。

例20-17 SQLテキストのバリエーション

この例では、バインド変数を使用するアプリケーションは、同じバインド変数値を使用する7つの文を実行しますが、文はテキスト的に同じではありません。

VARIABLE emp_id NUMBER
EXEC :emp_id := 101;

SELECT SUM(salary) FROM hr.employees WHERE employee_id < :emp_id;
SELECT SUM(salary) FROM hr.employees WHERE employee_id < :EMP_ID;
SELECT SUM(salary) FROM hr.employees WHERE employee_id < :Emp_Id;
SELECT SUM(salary)  FROM hr.employees WHERE employee_id < :emp_id;
select sum(salary) from hr.employees where employee_id < :emp_id;
Select sum(salary) From hr.employees Where employee_id < :emp_id;
Select sum(salary) From hr.employees Where employee_id< :emp_id;

問合せのV$SQLAREAは、カーソル共有が発生しなかったことを示しています。 

COL SQL_TEXT FORMAT a35
SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE
FROM   V$SQLAREA
WHERE  SQL_TEXT LIKE '%mployee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT                            SQL_ID        VERSION_COUNT HASH_VALUE
----------------------------------- ------------- ------------- ----------
SELECT SUM(salary) FROM hr.employee bkrfu3ggu5315             1 3751971877
s WHERE employee_id < :EMP_ID
SELECT SUM(salary) FROM hr.employee 70mdtwh7xj9gv             1  265856507
s WHERE employee_id < :Emp_Id
Select sum(salary) From hr.employee 18tt4ny9u5wkt             1 2476929625
s Where employee_id< :emp_id
SELECT SUM(salary)  FROM hr.employe b6b21tbyaf8aq             1 4238811478
es WHERE employee_id < :emp_id
SELECT SUM(salary) FROM hr.employee 4318cbskba8yh             1  615850960
s WHERE employee_id < :emp_id
select sum(salary) from hr.employee 633zpx3xm71kj             1 4214457937
s where employee_id < :emp_id
Select sum(salary) From hr.employee 1mqbbbnsrrw08             1  830205960
s Where employee_id < :emp_id

7 rows selected.

例20-18 バインド長の不一致

次のコードでは、異なる長さのバインド変数を定義し、同じバインド値を使用したテキスト的に同じ文を実行します。

VARIABLE lname VARCHAR2(20)
EXEC :lname := 'Taylor';
SELECT SUM(salary) FROM hr.employees WHERE last_name = :lname;
VARIABLE lname VARCHAR2(100)
EXEC :lname := 'Taylor';
SELECT SUM(salary) FROM hr.employees WHERE last_name = :lname;

次の問合せは、データベースはカーソルを共有しなかったことを示しています。

COL SQL_TEXT FORMAT a35
SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE
FROM   V$SQLAREA
WHERE  SQL_TEXT LIKE '%mployee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT                            SQL_ID	       VERSION_COUNT HASH_VALUE
----------------------------------- ------------- ------------- ----------
SELECT SUM(salary) FROM hr.employee buh8j4557r0h1             2 1249608193
s WHERE last_name = :lname

理由は、バインド長によるものです。

COL BIND_LENGTH_UPGRADEABLE FORMAT a15
SELECT s.SQL_TEXT, s.CHILD_NUMBER, 
       c.BIND_LENGTH_UPGRADEABLE
FROM   V$SQL s, V$SQL_SHARED_CURSOR c
WHERE  SQL_TEXT LIKE '%employee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%'
AND    s.CHILD_ADDRESS = c.CHILD_ADDRESS;

SQL_TEXT                            CHILD_NUMBER BIND_LENGTH_UPG
----------------------------------- ------------ ---------------
SELECT SUM(salary) FROM hr.employee            0 N
s WHERE last_name = :lname
SELECT SUM(salary) FROM hr.employee            1 Y
s WHERE last_name = :lname

20.4.4 オプティマイザ環境へのセッション・レベルの変更の最小化

ベスト・プラクティスは、アプリケーションのユーザーが最適化アプローチと目標を各セッションに対して変更しないようにすることです。オプティマイザ環境に変更を行うと、他の同一の文がカーソル共有されることを回避できます。

例20-19 環境の不一致

この例では、テキスト的に同じだがカーソルを共有していない2つの文を示しています。

VARIABLE emp_id NUMBER

EXEC :emp_id := 110;

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
SELECT salary FROM hr.employees WHERE employee_id < :emp_id;
ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS;
SELECT salary FROM hr.employees WHERE employee_id < :emp_id;

問合せのV$SQL_SHARED_CURSORは、オプティマイザ・モードでの不一致を示しています。 

SELECT S.SQL_TEXT, S.CHILD_NUMBER, s.CHILD_ADDRESS, 
       C.OPTIMIZER_MODE_MISMATCH
FROM   V$SQL S, V$SQL_SHARED_CURSOR C
WHERE  SQL_TEXT LIKE '%employee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%'
AND S.CHILD_ADDRESS = C.CHILD_ADDRESS;

SQL_TEXT                            CHILD_NUMBER CHILD_ADDRESS    O
----------------------------------- ------------ ---------------- -
SELECT salary FROM hr.employees WHE            0 0000000080293040 N
RE employee_id < :emp_id
SELECT salary FROM hr.employees WHE            1 000000008644E888 Y
RE employee_id < :emp_id