Oracle Databaseでは、共有プール内のプライベートSQL領域へのポインタであるカーソルを共有できます。カーソル共有により、データベース・アプリケーションのパフォーマンスを大幅に向上させることができます。
この項の内容は次のとおりです。
プライベートSQL領域には、解析済のSQL文に関する情報と、処理に使用するその他のセッション固有の情報が保持されます。
サーバー・プロセスでSQLまたはPL/SQLのコードが実行される場合、プロセスではプライベートSQL領域を使用してバインド変数値、問合せ実行状態情報、問合せ実行作業領域が格納されます。文の各実行におけるプライベートSQL領域は共有されず、異なる値やデータが含まれている可能性があります。
カーソルは、特定のプライベートSQL領域の名前またはハンドルです。カーソルには、バインド変数の値や結果セットなどのセッション固有の状態の情報が含まれています。
次の図に示すように、カーソルはクライアントから見るとポインタ、サーバーから見ると状態のようなものです。カーソルはプライベートSQL領域と密接に関連しているため、これらの用語は同じ意味で使用されることがあります。
プライベートSQL領域のカーソルは、ライブラリ・キャッシュの共有SQL領域を指します。セッション状態の情報を含むプライベートSQL領域とは異なり、共有SQL領域には、文の解析ツリーおよび実行計画が含まれています。
たとえば、SELECT * FROM employees
を実行すると、計画および解析ツリーはある共有SQL領域に格納されます。構文的にも意味的にも異なるSELECT * FROM departments
を実行すると、計画および解析ツリーは別の共有SQL領域に格納されます。
同じセッションまたは異なるセッションにある複数のプライベートSQL領域は、単一の共有SQL領域を参照でき、これはカーソル共有と呼ばれる現象です。たとえば、あるセッションにおけるSELECT * FROM employees
の実行と、別のセッションにおける(同じ表にアクセスする)SELECT * FROM employees
の実行では、同じ解析ツリーおよび計画を使用できます。複数の文によってアクセスされる共有SQL領域は、共有カーソルと呼ばれます。
Oracle Databaseでは、次の手順を使用して、SQL文またはPL/SQLブロックを発行する際に、ライブラリ・キャッシュにテキスト的に同じ文が存在するかどうかを自動的に確認します。
文のテキストがハッシュされます。
共有プール内の既存のSQL文で、一致するハッシュ値が検索されます。次のオプションが可能です。
一致するハッシュ値が存在しない場合。
この場合、SQL文は共有プール内に現在存在せず、ハード解析が実行されます。これにより共有プール・チェックが終了されます。
一致するハッシュ値が存在する場合。
この場合、データベースは、次の手順であるテキスト一致に進みます。
一致する文のテキストとハッシュ文のテキストが同一であるかどうか比較されます。次のオプションが可能です。
テキスト一致が失敗した場合。
この場合、テキスト一致プロセスは停止され、ハード解析が実行されます。
テキスト一致が成功した場合。
この場合、データベースは次の手順に進みます。「親カーソルと子カーソル」で説明されているように、既存の親カーソルを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領域を共有できます。CURSOR_SHARING
を使用する場合のコストについては、「CURSOR_SHARING = FORCEを永続的な修正として使用しない」で説明します。
関連項目:
CURSOR_SHARING
初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。
すべての解析済のSQL文には、1つの親カーソルと1つ以上の子カーソルがあります。親カーソルにはSQL文のテキストが格納されます。2つの文のテキストが同一である場合、これらの文は同じ親カーソルを共有します。しかし、テキストが異なる場合、データベースは別の親カーソルを作成します。
例15-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
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
)となります。
すべての親カーソルには1つ以上の子カーソルがあります。子カーソルには、実行計画、バインド変数、問合せで参照されるオブジェクトに関するメタデータ、オプティマイザ環境およびその他の情報が含まれています。親カーソルとは異なり、子カーソルにはSQL文のテキストは格納されません。
文で親カーソルを再利用できる場合、既存の子カーソルを再利用できるかどうかがチェックされます。次のような複数のチェックが実行されます。
発行された文で参照されたオブジェクトは、共有プール内の文によって参照されたオブジェクトと比較され、それらがすべて同一であるかどうかが確認されます。
SQL文やPL/SQLブロック内でスキーマ・オブジェクトを参照する際には、同じスキーマ内の同じオブジェクトである必要があります。たとえば、2人のユーザーが次のSQL文を発行し、各ユーザーに独自のemployees
表がある場合、次の文はユーザーごとに異なるemployees
表を参照するので、この文は同一とみなされません。
SELECT * FROM employees;
オプティマイザ・モードが同一であるかどうかが判定されます。
たとえば、SQL文は同じオプティマイザの目標を使用して最適化する必要があります(「オプティマイザの目標の選択」を参照)。
例15-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文では、同じ実行計画が使用されています。
親カーソルに複数の子カーソルがある場合、V$SQL_SHARED_CURSOR
ビューは、カーソルが共有されなかった理由に関する情報を提供します。複数のタイプの非互換性について、TRANSLATION_MISMATCH
列では値Y
またはN
で不一致を示します。
例15-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
アプリケーションが文を発行し、Oracle Databaseがカーソルを再利用できない場合、新しく実行可能なバージョンのアプリケーション・コードを作成する必要があります。この操作はハード解析と呼ばれています。
ソフト解析はハード解析ではない解析で、データベースが既存のコードを再利用できる場合に発生します。ソフト解析には、ハード解析よりもリソース集中型ではないものがあります。たとえば、文の親カーソルがすでに存在する場合、Oracle Databaseは、様々な最適化を実行し、共有SQL領域に子カーソルを格納できます。しかし、親カーソルが存在しない場合、Oracle Databaseは、共有SQL領域に親カーソルも格納する必要があり、追加のメモリー・オーバーヘッドが作成されます。
実質的に、ハード解析では文を実行する前に再コンパイルします。すべての実行前にSQL文をハード解析することは、すべての実行前にCプログラムを再コンパイルすることに似ています。ハード解析では次のような操作を実行します。
SQL文の構文のチェック
SQL文のセマンティクスのチェック
文を発行するユーザーのアクセス権のチェック
実行計画の作成
データ・ディクショナリをチェックするための、ライブラリ・キャッシュおよびデータ・ディクショナリ・キャッシュへの多くのアクセス
ハード解析の特にリソース集中型の側面は、データ・ディクショナリをチェックするために、ライブラリ・キャッシュおよびデータ・ディクショナリ・キャッシュに何度もアクセスすることです。データベースはこれらの領域にアクセスするとき、チェック中に定義が変更されないように、必要なオブジェクト上にラッチと呼ばれるシリアライズ・デバイスを使用します。ラッチの競合が発生すると、文の実行時間が長くなり、同時実行性が低下します。
前述のすべての理由で、ハード解析のCPUおよびメモリーのオーバーヘッドにより深刻なパフォーマンスの問題が発生します。この問題が特に明白なのは、フォームからユーザー入力を受け入れ、SQL文を動的に生成するWebアプリケーションです。Real-World Performanceグループでは、ハード解析をできるだけ減らすことを強くお薦めします。
ビデオ:
例15-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
例15-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 elapsed 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)
関連項目:
この項の内容は次のとおりです。
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は共有プールおよびライブラリ・キャッシュをラッチする必要があります。ハード解析の数が増加すると、共有プールのラッチを待機するプロセスの数も増加します。この状況では、同時実行性が低下し、競合が増加します。
ビデオ:
例15-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を永続的な修正として使用しない」を参照してください
リテラルではなくバインド変数を使用するようにOracleアプリケーションを開発できます。
バインド変数は、問合せ内のプレースホルダです。たとえば、文SELECT SUM(salary) FROM hr.employees WHERE employee_id < :emp_id
では、従業員IDにバインド変数:emp_id
を使用しています。
Real-World Performanceグループでは、バインド変数を使用するアプリケーションは、より適切に実行され、スケーラブルかつセキュアであることがわかりました。バインド変数の使用によって生じる主な利点は、次のとおりです。
バインド変数を使用するアプリケーションは、SQLインジェクション攻撃に対する脆弱性が、リテラルを使用するアプリケーションと同じではありません。
同一の文でバインド変数を使用する場合、Oracle Databaseはカーソル共有を利用でき、同じ文に異なる値がバインドされると、計画およびその他の情報を共有します。
Oracle Databaseでは、ハード解析に必要な共有プールおよびライブラリ・キャッシュのラッチのオーバーヘッドが回避されます。
ビデオ:
例15-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つの個別の子カーソルを作成するデータベースではなく、同じ子カーソルを再利用したデータベースを示しています。バインド変数を使用して、この再利用が可能になりました。
バインド変数の照合(バインド照合とも呼ばれる)では、オプティマイザはデータベースが文のハード解析を実行しているときにバインド変数の値を参照します。オプティマイザは、すべての解析の前にバインド変数の値を参照しません。かわりに、オプティマイザの照合が行われるのは、オプティマイザが最初に起動されたときのみで、これはハード解析中です。
問合せがリテラルを使用する場合、オプティマイザはリテラル値を使用して最良の計画を見つけることができます。ただし、問合せがバインド変数を使用する場合は、オプティマイザはSQLテキストにリテラルが存在しない状態で最良の計画を選択する必要があります。このタスクは極めて困難になる可能性があります。最初のハード解析中にバインド値を照合することにより、オプティマイザは、まるでリテラルが使用されたかのようにWHERE
句条件のカーディナリティを判断することができ、これによって計画が改善されます。
オプティマイザはハード解析中にバインド値でのみ照合するため、計画は可能なすべてのバインド値に対して最適ではない可能性があります。次の例に、この原則を示します。
例15-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)
前述の出力では、多くの行を戻す問合せには、索引スキャンより全表スキャンのほうが効率的であるとオプティマイザがみなしていることを示しています。
例15-9 カーソル再利用でのバインド変数の結果
この例では、例15-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
のときは、オプティマイザはコストの低い全表スキャンを選択します。これは、適応カーソル共有によって解決される問題です(「適応カーソル共有」を参照)。
このトピックでは、CURSOR_SHARING
初期化パラメータとは何か、このパラメータを異なる値に設定した場合のOracle Databaseによるバインド変数の使用方法への影響について説明します。
この項の内容は次のとおりです。
CURSOR_SHARING
初期化パラメータは、データベースによるバインド変数を使用した文の処理方法を制御します。
Oracle Database 12cでは、このパラメータは次の値をサポートします。
EXACT
これはデフォルト値です。データベースでは、テキスト的に同じ文のみがカーソルを共有できます。データベースは、リテラル値をシステム生成のバインド変数に置換しようとしません。この場合、オプティマイザは、リテラル値に基づいて各文の計画を生成します。
FORCE
データベースは、すべてのリテラルをシステム生成のバインド変数に置換します。バインド変数でリテラルが置換された後に同一となった文に対して、オプティマイザは同じ計画を使用します。
注意:
CURSOR_SHARING
のSIMILAR
値は非推奨です。
CURSOR_SHARING
はシステム・レベルまたはセッション・レベルで設定でき、CURSOR_SHARING_EXACT
ヒントは文レベルで使用できます。
SQL文がバインド変数よりもリテラルを使用する場合、CURSOR_SHARING
初期化パラメータをFORCE
に設定すると、データベースはリテラルをシステム生成のバインド変数と置き換えることができます。この方法を使用して、データベースは共有SQL領域の親カーソルの数を減らせることがあります。
注意:
文でORDER BY
句が使用されている場合、定数列番号をリテラルと解釈することは意味的に正しくないため、データベースはこの句ではリテラル置換を行いません。ORDER BY
句の列番号は問合せ計画と実行に影響するため、データベースは異なる列番号を持つ2つのカーソルを共有することはできません。
CURSOR_SHARING
がFORCE
,に設定されている場合、データベースは解析中に次の手順を実行します。
文のすべてのリテラルを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
共有プールで同一文(同じSQLハッシュ値)を検索します
同一文が見つからない場合は、データベースはハード解析を実行します。そうでない場合は、データベースは次の手順に進みます。
文のソフト解析を実行します
前述の手順に示すように、CURSOR_SHARING
初期化パラメータをFORCE
に設定しても、解析数は減りません。かわりに、場合によっては、FORCE
を使用するとデータベースでハード解析ではなくソフト解析を実行できます。また、インジェクションがすでに発生した後でOracle Databaseで値をバインドするため、FORCE
によってSQLインジェクション攻撃が回避されません。
例15-10 システムのバインド変数へのリテラルの置換
この例では、セッション・レベルで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"
関連項目:
実行される各種チェックの詳細は、「プライベートSQL領域と共有SQL領域」を参照してください
CURSOR_SHARING
初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください
適応カーソル共有機能により、バインド変数を含む1つの文が複数の実行計画を使用できるようになります。カーソル共有には「適応」機能があります。つまり、データベースが各実行または各バインド変数値に常に同じ計画を使用することがないように、カーソルがその動作を適応させます。
この項の内容は次のとおりです。
バインド照合では、オプティマイザは、カーソルの最初の起動時にユーザー定義のバインド変数の値を照合します。オプティマイザは、バインド変数のかわりにまるでリテラルが使用されたかのように、WHERE
句条件のカーディナリティを判断します。ただし、WHERE
句の列に偏ったデータがある場合、この列にヒストグラムが存在する可能性があります。オプティマイザがユーザー定義のバインド変数の値を照合し、計画を選択したとき、その計画がすべての値にとって最適ではない可能性があります。
適応カーソル共有では、データベースは、様々なバインド値に対して時間の経過とともにアクセスされるデータを監視し、特定のバインド値に対して最適なカーソルを確実に選択できるようにします。たとえば、オプティマイザは、バインド値10
とバインド値50
に対して、それぞれ異なる計画を選択することもあります。カーソル共有には「適応」機能があります。つまり、オプティマイザが各実行または各バインド変数値に常に同じ計画を選択することがないように、カーソルがその動作を適応させます。そのため、オプティマイザは、文の異なる実行により異なる実行計画の利点を得るタイミングを自動的に検出します。
注意:
適応カーソル共有は、CURSOR_SHARING
初期化パラメータとは独立しています。適応カーソル共有は、ユーザー定義のバインド変数やシステム生成のバインド変数を含む文にも同様に適用できます。適応カーソル共有は、リテラルのみを含む文には適用されません。
適応カーソル共有では、バインド変数を使用する文を監視して、新しい計画が効率的であるかどうかを判断します。
アプリケーションで、毎回異なる値をバインドする次の文を5回実行するとします。
SELECT * FROM employees WHERE salary = :sal AND department_id = :dept
また、この例では、述語の少なくとも1つの列にヒストグラムが存在するとします。データベースは、この文を次のように処理します。
アプリケーションが文を最初に発行すると、ハード解析が行われます。解析中、データベースは次のタスクを実行します。
バインド変数を照合して初期計画を生成します。
カーソルをバインド依存とマークします。バインド依存カーソルは、最適な計画がバインド変数の値に依存するカーソルです。別の計画が有効であるかどうかを判断するために、データベースは、別のバインド値を使用するバインド依存カーソルの動作を監視します。
バインドされた値のカーディナリティなど、述語に関するメタデータを格納します(この例では、5行のみが戻されたとします)。
照合された値に基づいて実行計画(この例では索引アクセス)を作成します。
データベースはカーソルを実行し、バインド値および実行統計をカーソルに格納します。
アプリケーションは、異なるバインド変数を使用して、2回目に文を発行します。データベースはソフト解析を実行し、ライブラリ・キャッシュで一致するカーソルを検出します。
データベースはカーソルを実行します。
データベースは、次の実行後タスクを実行します。
データベースは、2回目の実行の実行統計を1回目の実行統計と比較します。
データベースは、前のすべての実行にわたって統計のパターンを監視して、カーソルをバインド対応カーソルとマークするかどうかを判断します。この例では、データベースはカーソルをバインド対応と判断すると仮定します。
アプリケーションが異なるバインド変数を使用して3回目に文を発行すると、ソフト解析が行われます。カーソルがバインド対応であるため、データベースは次のことを行います。
新しい値のカーディナリティが、格納されたカーディナリティと同じ範囲内にあるかどうかを判断します。この例では、カーディナリティは5行ではなく8行です。
既存の子カーソルの実行計画を再利用します。
データベースはカーソルを実行します。
アプリケーションが異なるバインド変数を使用して4回目に文を発行すると、ソフト解析が行われます。カーソルがバインド対応であるため、データベースは次のことを行います。
新しい値のカーディナリティが、格納されたカーディナリティと同じ範囲内にあるかどうかを判断します。この例では、カーディナリティは大きく異なります。5行ではなく102行(107行が含まれる表)です。
一致する子カーソルは見つけません。
データベースはハード解析を実行します。その結果、データベースは次を行います。
2回目の実行計画(この例では全表スキャン)で新しい子カーソルを作成します
バインドされた値のカーディナリティなど、述語に関するメタデータをカーソルに格納します
データベースは新しいカーソルを実行します。
データベースは、新しいバインド値および実行統計を新しい子カーソルに格納します。
アプリケーションが異なるバインド変数を使用して5回目に文を発行すると、ソフト解析が行われます。カーソルがバインド対応であるため、データベースは次のことを行います。
新しい値のカーディナリティが、格納されたカーディナリティと同じ範囲内にあるかどうかを判断します。この例では、カーディナリティは20です。
一致する子カーソルは見つけません。
データベースはハード解析を実行します。その結果、データベースは次を行います。
3回目の実行計画(この例では索引アクセス)で新しい子カーソルを作成します。
この索引アクセスの実行計画は、文の最初の実行に使用された索引アクセスの実行計画と同じであると判断します。
索引アクセスの計画を含む2つの子カーソルをマージします。組み合せたカーディナリティ統計が1つの子カーソルに格納され、もう1つの子カーソルは削除されます。
データベースは、索引アクセス実行計画を使用してカーソルを実行します。
バインド依存カーソルは、最適な計画がバインド変数の値に依存するカーソルです。
データベースは、カーディナリティの計算時にバインド値を調べ、異なるバインド値に基づいた計画変更に対して問合せ依存であるとみなします。データベースは、別の計画が有効であるかどうかを判断するために、別のバインド値を使用するバインド依存カーソルの動作を監視します。
オプティマイザは、次の基準を使用して、カーソルがバインド依存であるかどうかを判断します。
オプティマイザはバインド値を照合してカーディナリティ予測を生成しました。
バインドが等価述語または範囲述語で使用されます。
新しいバインド値で問合せを実行するごとに、データベースは新しい値の実行統計を記録し、それらを前の値の実行統計と比較します。実行統計が大幅に異なると、データベースはカーソルをバインド対応としてマークします。
例15-11 大きなデータの偏りがある列
この例で、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; EXEC DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'hr', tabname => 'employees'); 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
例15-12 カーディナリティの低い問合せ
この例は、例15-11の例の続きです。次の問合せでは、列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
の選択性を計算したため、データベースはこのカーソルをバインド依存とマークしました。ヒストグラムの存在は列が偏っていることを示すため、バインド変数の異なる値に異なる計画が必要になる可能性があります。
例15-13 カーディナリティの高い問合せ
この例は、例15-12の例の続きです。次のコードでは、値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
この段階では、オプティマイザはカーソルをバインド対応とまだマークしていません。
関連項目:
V$SQL
の詳細は、『Oracle Databaseリファレンス』を参照してください
バインド対応カーソルは、バインド値ごとに異なる計画を使用するのに適したバインド依存カーソルです。
カーソルをバインド対応にした後で、オプティマイザは、バインド値とそのカーディナリティの見積りを基に、今後の実行のための計画を選択します。そのため、「バインド対応」は基本的に、現在のバインド値に最適な計画を意味します。
バインド依存カーソルを含む文を実行する場合、オプティマイザは、内部アルゴリズムを使用して、カーソルをバインド対応としてマークするかどうかを判断します。これは、カーソルが生成するデータ・アクセス・パターンが、バインド値によって大幅に異なるかどうかによって判断され、結果としてパフォーマンス・コストが予測とは異なります。
データベースがカーソルをバインド対応としてマークすると、次回そのカーソルが実行されたときに、データベースは次のことを行います。
バインド値に基づいて、新規計画を生成します
その文に生成された元のカーソルを共有不可(V$SQL.IS_SHAREABLE
がN
)としてマークします。元のカーソルは使用できなくなり、ライブラリ・キャッシュからのエージ・アウトの対象となります
同じ問合せが別のバインド値を使用して繰り返し実行されると、データベースは新しいバインド値をSQL文のシグネチャ(オプティマイザ環境、NLS設定などを含む)に追加し、値を分類します。データベースはバインド値を調べ、現在のバインド値のデータ量が大幅に異なるか、または既存の計画が十分であるかを検討します。データベースは、新しい値ごとに新しい計画を作成する必要はありません。
12個の異なるバインド値を含む文を実行するシナリオを考えてみます(それぞれの異なる値を2回実行します)これにより、データベースは5つのハード解析をトリガーし、2つの追加の計画を作成します。データベースは5つのハード解析を実行するため、一部のカーソルに既存のカーソルと同じ実行計画がある場合でも、5つの新しい子カーソルが作成されます。データベースは余分なカーソルを使用不可とマークし、これらのカーソルは最終的にライブラリ・キャッシュからエージ・アウトされます。
最初のハード解析中に、オプティマイザは、基本的にはバインド値と適切な実行計画との間の関係をマップしています。この最初の期間の後、データベースは最終的には安定した状態になります。新しいバインド値で実行すると、ハード解析を必要とせずに、キャッシュ内の最適な子カーソルが選択されます。そのため、解析の数は異なるバインド値の数と対応しません。
例15-14 バインド対応カーソル
この例は、「バインド依存カーソル」の例の続きです。次のコードでは、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
よりバッファ取得の数が少ないことを示し、バインド依存とバインド対応の両方としてマークされています。バインド対応カーソルは、バインド変数を含む述語の選択性に応じて、異なるバインド値に異なる計画を使用することがあります。
例15-15 バインド対応カーソル: 最適な計画の選択
この例は、例15-14の例の続きです。次のコードでは、極端にカーディナリティが低い(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リファレンス』を参照してください
オプティマイザがバインド対応カーソルの計画を作成し、この計画が既存のカーソルと同じである場合、オプティマイザはカーソルのマージを実行できます。
この場合、データベースはライブラリ・キャッシュの領域を節約するためにカーソルをマージします。データベースは、新しいバインド値の選択性を含めるために、カーソルの選択性の範囲を拡大します。
問合せが新しいバインド変数を使用すると、オプティマイザは、バインド値の選択性の類似に基づいて適合すると思われるカーソルを見つけようとします。データベースでこのようなカーソルが見つからない場合、新たに作成します。新しいカーソルの計画が既存のカーソルの計画と同じ場合、データベースはライブラリ・キャッシュの領域を節約するために2つのカーソルをマージします。マージによって、データベースは1つのカーソルを共有不可とマークします。ライブラリ・キャッシュが領域削減の必要に迫られている場合、データベースは共有不可のカーソルを最初にエージ・アウトします。
関連項目:
適応カーソル共有では、V$
ビューを使用して選択性の範囲、カーソル情報(カーソルがバインド対応かバインド依存かなど)および実行統計を表示できます。
V$SQL
は、カーソルがバインド依存とバインド対応のどちらであるかを示します。
V$SQL_CS_HISTOGRAM
は、3バケットの実行履歴ヒストグラムにより、実行カウントの配分を示します。
V$SQL_CS_SELECTIVITY
は、カーソル共有をチェックするために選択性が使用された場合に、バインド変数を含むすべての述語に格納されている選択性の範囲を示します。これには、述語のテキスト、および選択性の範囲の下限値と上限値が含まれています。
V$SQL_CS_STATISTICS
は、カーソルをバインド対応としてマークするかどうかを判断するためにオプティマイザが使用する情報を示します。実行のサンプルでは、データベースは、処理された行、取得するバッファ、およびCPU時間を追跡します。バインド・セットがカーソルの作成に使用された場合、PEEKED
列はYES
を示します。それ以外の場合、この値はNO
です。
関連項目:
V$SQLおよび関連するビューの詳細は、『Oracle Databaseリファレンス』を参照してください。
この項の内容は次のとおりです。
Real-World Performanceグループでは、すべてのエンタープライズ・アプリケーションでバインド変数を使用することを強くお薦めします。Oracle Databaseアプリケーションは、バインド変数を使用して記述されることを目的としていました。多数のユーザーが動的な非共有のSQL文を発行するようなアプリケーションを設計しないようにしてください。
Oracle Databaseがライブラリ・キャッシュ内の文の一致の検出に失敗するたびに、ハード解析を実行する必要があります。リテラルを使用してアプリケーションを開発すると危険性があるにもかかわらず、実在のアプリケーションのすべてにバインド変数が使用されているわけではありません。開発者は、リテラルを使用したプログラムを記述するほうが速くて簡単であることに気付く場合があります。しかし、開発時間を削減しても、開発後のパフォーマンスおよびセキュリティが向上するわけではありません。
ビデオ:
ハード解析によるパフォーマンスの問題を説明しているデモは、「RWP #3: 接続プールおよびハード解析」を参照してください
バインド変数を使用する主な利点は、次のとおりです。
リソースの効率
すべての実行前にプログラムをコンパイルすると、リソースが効率的に使用されませんが、Oracle Databaseがハード解析を実行する際には、これは基本的に行われています。カーソルの作成、実行計画の生成および評価などを行うために、データベース・サーバーは、大量のCPUとメモリーを消費する必要があります。データベースでカーソルの共有を有効にすることによって、ソフト解析でのリソースの消費がはるかに少なくなります。アプリケーションでバインド変数ではなくリテラルを使用しているが、毎日少数の問合せしか実行されない場合、DBAでは、追加のオーバーヘッドがパフォーマンスの問題として認識されない場合があります。しかし、アプリケーションが1秒当たり何百または何千もの問合せを実行する場合、追加のリソース・オーバーヘッドにより、パフォーマンスが許容不可能なレベルにまで簡単に低下することがあります。バインド変数を使用すると、文が何回実行されても、データベースはハード解析を1回のみ実行できます。
スケーラビリティ
データベースがハード解析を実行すると、共有プールおよびライブラリ・キャッシュでラッチの取得および保持に時間がかかります。ラッチは、低レベルのシリアライズ・デバイスです。データベースによる共有メモリー内の構造のラッチが長く頻繁になるほど、これらのラッチのキューも長くなります。複数の文が同じ実行計画を共有する場合、ラッチに対するリクエストおよびラッチの存続期間が停止します。この動作によってスケーラビリティが向上します。
スループットおよびレスポンス時間
データベースがカーソルの再解析および作成を常に回避すると、ユーザー領域でより多くの時間が費やされます。Real-World Performanceグループでは、リテラルをバインドを使用するように変更すると、スループットおよびユーザーのレスポンス時間を桁違いに向上させることが多いことがわかりました。
ビデオ:
バインド変数のパフォーマンスの利点を示しているデモは、「RWP #4: バインド変数およびソフト解析」を参照してください
セキュリティ
SQLインジェクション攻撃を防ぐ唯一の方法は、バインド変数を使用することです。悪意のあるユーザーが、アプリケーションにコードを挿入することによって文字列を連結するアプリケーションを利用する可能性があります。
関連項目:
リテラルによるセキュリティの脆弱性を修正するアプリケーションの例は、『Oracle Database PL/SQL言語リファレンス』を参照してください
ベスト・プラクティスは、共有可能なSQLを書き込み、CURSOR_SHARING
にデフォルトのEXACT
を使用することです。ただし、多くの類似文のあるアプリケーションでは、CURSOR_SHARING
をFORCE
に設定することでカーソル共有が大幅に改善されます。リテラルをシステム生成のバインド値で置き換えると、メモリー使用量が減少し、解析が高速になり、ラッチの競合が減少します。ただし、FORCE
は、永続的な開発ソリューションとなることを意図しているわけではありません。
一般的なガイドラインとして、Real-World Performanceグループは、まれな状況の場合を除きCURSOR_SHARING
をFORCE
に設定しないことをお薦めします。次のすべての条件を満たす場合のみ設定します。
共有プール内の文は、リテラルの値のみが異なります。
非常に多くのライブラリ・キャッシュ・ミスが発生するため、レスポンス時間が最適ではありません。
既存のコードには、セキュリティおよびスケーラビリティの深刻な不具合(バインド変数がない)があり、ソース・コードが修正されるまで、一時的な対策を講じる必要があります。
この初期化パラメータは、インスタンス・レベルではなくセッション・レベルで設定します。
CURSOR_SHARING
をFORCE
に設定する場合、次の短所があります。
アプリケーションではユーザー定義のバインド変数が使用されません。これはSQLインジェクションに対してオープンになっていることを意味します。CURSOR_SHARING
をFORCE
に設定しても、SQLインジェクション・バグが修正されたり、コードがよりセキュアにレンダリングされることはありません。悪意のあるSQLテキストがすでに挿入された後でのみ、データベースは値をバインドします。
ビデオ:
FORCE
を使用するアプリケーションのReal-World PerformanceグループによるAWR分析については、「RWP #4: バインド変数およびソフト解析」を参照してください。
データベースは、共有プール内で類似文を検索するために、ソフト解析の間に追加作業を実行する必要があります。
データベースはすべてのリテラルを削除します。これは有効な情報も削除する可能性があることを意味します。たとえば、データベースがSUBSTR
関数およびTO_DATE
関数のリテラル値を削除するとします。リテラルのほうが最適な場合にシステム生成のバインド変数を使用すると、実行計画に悪影響を及ぼす可能性があります。
SELECT
文にリテラルを含む選択された式の最大長(DESCRIBE
からの戻り値)が増加します。ただし、戻されたデータの実際の長さは変わりません。
スター型変換はサポートされません。
関連項目:
CURSOR_SHARING初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。
デフォルトでは、バインド変数の名前など、2つのSQL文のテキストの違いによって、カーソルの共有がデータベースで行われなくなります。また、バインド変数のサイズ変更によって、カーソルの不一致が発生する可能性があります。このため、アプリケーション・コードでバインド変数を使用しても、カーソル共有が十分に保証されるわけではありません。
Real-World Performanceグループでは、SQL文およびPL/SQLブロックの空白設定および大文字/小文字の区別の規則を標準化することをお薦めします。また、バインド変数の命名規則および定義を確立します。データベースが予想どおりにカーソルを共有しない場合、V$SQL_SHARED_CURSOR
を問い合せることによって診断を開始します。
例15-16 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.
例15-17 バインド長の不一致
次のコードでは、異なる長さのバインド変数を定義し、同じバインド値を使用したテキスト的に同じ文を実行します。
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
ベスト・プラクティスは、アプリケーションのユーザーが最適化アプローチと目標を各セッションに対して変更しないようにすることです。オプティマイザ環境に変更を行うと、他の同一の文がカーソル共有されることを回避できます。
例15-18 環境の不一致
この例では、テキスト的に同じだがカーソルを共有していない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