3 SQLの処理
この章では、データベースがDDL文を処理してオブジェクトを作成する仕組み、DMLを使用してデータを変更する仕組み、問合せを使用してデータを取得する仕組みについて説明します。
3.1 SQL処理について
SQL処理とは、SQL文の解析、最適化、行ソース生成および実行を行うことです。
次の図は、SQL処理の全体的な段階を示しています。文によっては、データベースがこれらの段階の一部を省略する場合があります。
3.1.1 SQLの解析
SQL処理の最初の段階は、解析です。
解析段階では、SQL文の各部分を他のルーチンで処理できるデータ構造に分解する処理が行われます。Oracle Databaseは、アプリケーションから指示されたときに文を解析するため、解析回数を削減できるのはアプリケーションのみです(Oracle Database自体ではありません)。
アプリケーションがSQL文を発行すると、アプリケーションはOracle Databaseに解析コールを出して、文の実行を準備します。解析コールによって、解析済のSQL文と処理に使用するその他の情報が保持されるセッション固有のプライベートSQL領域のハンドルであるカーソルがオープンまたは作成されます。カーソルおよびプライベートSQL領域は、プログラム・グローバル領域(PGA)内にあります。
解析コール中に、データベースは、文の実行前に検出できるエラーを特定するチェックを実行します。解析では捕捉できないエラーもあります。たとえば、データベースで、文の実行時にのみ、データ変換に関するデッドロックまたはエラーが発生する場合があります。
関連項目:
デッドロックについて学習するには、『Oracle Database概要』を参照してください。
3.1.1.1 構文チェック
Oracle Databaseは各SQL文の構文上の妥当性を必ずチェックします。
SQL構文の形式が整っていても、規則に違反している文はチェックが失敗します。たとえば、次の文ではキーワードFROM
がFORM
という誤ったスペルになっているため、失敗します。
SQL> SELECT * FORM employees;
SELECT * FORM employees
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
3.1.1.2 セマンティクス・チェック
文のセマンティクスとは、文の意味のことです。セマンティクス・チェックでは、文内のオブジェクトおよび列が存在するかどうかなど、文の意味が有効かどうかを判断します。
次に示す存在しない表の問合せの例のように、構文的に正しい文でも、セマンティクス・チェックに失敗します。
SQL> SELECT * FROM nonexistent_table;
SELECT * FROM nonexistent_table
*
ERROR at line 1:
ORA-00942: table or view does not exist
3.1.1.3 共有プール・チェック
解析中、データベースは共有プール・チェックを実行して、文の処理のうちリソース集中型のステップを省略できるかどうかを判断します。
これを行うために、データベースはハッシング・アルゴリズムを使用してSQL文ごとにハッシュ値を生成します。文のハッシュ値は、V$SQL.SQL_IDに示されるSQL IDです。このハッシュ値は、同一バージョンのOracle Databaseにおいて一定であるため、単一インスタンスまたは別のインスタンスにおける同じ文は同じSQL IDになります。
ユーザーがSQL文を発行すると、データベースは共有SQL領域を検索して、既存の解析済の文に同じハッシュ値があるかどうかを確認します。SQL文のハッシュ値は、次の値とは異なります。
-
文のメモリー・アドレス
Oracle Databaseでは、SQL IDをキーとして使用して検索対象の表を読み取ります。これにより、Oracle Databaseは文のメモリー・アドレスを取得します。
-
文の実行計画のハッシュ値
1つのSQL文で、複数の計画を共有プールに設定できます。通常、各計画には、異なるハッシュ値を設定します。同じSQL IDに複数の計画ハッシュ値が設定されていると、Oracle Databaseでは、そのSQL IDに対して複数の計画が存在することが認識されます。
発行される文のタイプとハッシュ・チェックの結果に応じて、解析操作は次のカテゴリに分類されます。
-
ハード解析
Oracle Databaseは、既存のコードを再利用できない場合、新しく実行可能なバージョンのアプリケーション・コードを作成する必要があります。この操作はハード解析またはライブラリ・キャッシュ・ミスと呼ばれます。
ノート:
データベースは常にDDLのハード解析を実行します。
ハード解析時には、データベースはライブラリ・キャッシュおよびデータ・ディクショナリ・キャッシュに何度もアクセスして、データ・ディクショナリをチェックします。データベースはこれらの領域にアクセスするとき、定義が変更されないように、必要なオブジェクト上にラッチと呼ばれるシリアライズ・デバイスを使用します。ラッチの競合が発生すると、文の実行時間が長くなり、同時実行性が低下します。
-
ソフト解析
ソフト解析は、ハード解析以外のすべての解析です。発行される文が共有プール内の再利用可能なSQL文と同じ場合、Oracle Databaseは既存のコードを再利用します。コードの再利用は、ライブラリ・キャッシュ・ヒットとも呼ばれます。
ソフト解析によって実行される作業の量は、解析ごとに異なる場合があります。たとえば、セッションの共有SQL領域の構成によって、ソフト解析はラッチ量が減少し、よりソフト解析らしくなる場合があります。
データベースが最適化および行ソース生成のステップを省略してそのまま実行に進むため、通常ソフト解析はハード解析よりも優れています。
次の図に、専用サーバー・アーキテクチャのUPDATE
文の共有プール・チェックを簡単に示します。
チェックにより、共有プール内の文に同じハッシュ値があると判断された場合、データベースはセマンティクス・チェックおよび環境チェックを実行して、文の意味が同一かどうかを判断します。構文が同一であるだけでは不十分です。たとえば、データベースに異なる2人のユーザーがログインし、次のSQL文を発行したと想定します。
CREATE TABLE my_table ( some_col INTEGER );
SELECT * FROM my_table;
2人のユーザーのSELECT
文は構文上同一ですが、2つの別々のスキーマ・オブジェクトがmy_table
という名前になっています。この意味の相違によって、2番目の文では最初の文のコードを再利用できません。
2つの文が意味的に同一であったとしても、環境の違いによりハード解析が強制的に行われることがあります。この場合のオプティマイザ環境とは、作業領域サイズまたはオプティマイザの設定(たとえば、オプティマイザ・モード)など、実行計画の生成に影響する可能性があるすべてのセッション設定です。単一のユーザーによって実行される次の一連のSQL文を考えてみます。
ALTER SESSION SET OPTIMIZER_MODE=ALL_ROWS;
ALTER SYSTEM FLUSH SHARED_POOL; # optimizer environment 1
SELECT * FROM sh.sales;
ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS; # optimizer environment 2
SELECT * FROM sh.sales;
ALTER SESSION SET SQL_TRACE=true; # optimizer environment 3
SELECT * FROM sh.sales;
この例では、同じSELECT
文が3つの異なるオプティマイザ環境で実行されます。結果として、データベースはこれらの文に対して3つの個別の共有SQL領域を作成し、各文にハード解析を強制的に実行します。
関連項目:
-
プライベートSQL領域および共有SQL領域について学習するには、『Oracle Database概要』を参照してください。
-
共有プールの構成方法を学習するには、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
-
ラッチについて学習するには、『Oracle Database概要』を参照してください。
3.1.2 SQLの最適化
最適化では、Oracle Databaseは、一意のDML文ごとに必ず1回以上のハード解析を実行して、この解析中に最適化を実行します。
データベースはDDLを最適化しません。唯一の例外は、最適化が必要な副問合せなどのDMLコンポーネントがDDLに含まれる場合です。
3.1.3 SQLの行ソース生成
行ソース・ジェネレータは、オプティマイザから最適な実行計画を受け取り、データベースのその他の部分で使用可能な反復実行計画を作成するソフトウェアです。
反復計画は、SQLエンジンで実行される場合に、結果セットを作成するバイナリ・プログラムです。計画では、ステップの組合せ形式を使用します。各ステップでは行セットが戻されます。次のステップでは、このセットの行を使用するか、最後のステップで、SQL文を発行しているアプリケーションに行を戻します。
行ソースは、行を反復的に処理できる制御構造に沿って、実行計画のステップから戻された行セットです。表、ビュー、または結合処理あるいはグループ化処理の結果が行ソースになる可能性があります。
行ソース・ジェネレータは、行ソースのコレクションである行ソース・ツリーを作成します。行ソース・ツリーには次の情報が表示されます。
-
文によって参照される表の順序
-
文で言及される各表へのアクセス方法
-
文の結合操作の影響を受ける表の結合方法
-
フィルタ、ソートまたは集計などのデータ操作
例3-1 実行計画
この例に、AUTOTRACE
が有効な場合のSELECT
文の実行計画を示します。この文では、姓が文字A
で始まるすべての従業員の姓、職種、部門名を選択します。この文の実行計画は行ソース・ジェネレータから出力されます。
SELECT e.last_name, j.job_title, d.department_name
FROM hr.employees e, hr.departments d, hr.jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
AND e.last_name LIKE 'A%';
Execution Plan
----------------------------------------------------------
Plan hash value: 975837011
---------------------------------------------------------------------------
| Id| Operation | Name |Rows|Bytes|Cost(%CPU)|Time|
---------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 3 |189 |7(15)|00:00:01 |
|*1| HASH JOIN | | 3 |189 |7(15)|00:00:01 |
|*2| HASH JOIN | | 3 |141 |5(20)|00:00:01 |
| 3| TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 60 |2 (0)|00:00:01 |
|*4| INDEX RANGE SCAN | EMP_NAME_IX | 3 | |1 (0)|00:00:01 |
| 5| TABLE ACCESS FULL | JOBS |19 |513 |2 (0)|00:00:01 |
| 6| TABLE ACCESS FULL | DEPARTMENTS |27 |432 |2 (0)|00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
2 - access("E"."JOB_ID"="J"."JOB_ID")
4 - access("E"."LAST_NAME" LIKE 'A%')
filter("E"."LAST_NAME" LIKE 'A%')
3.1.4 SQLの実行
実行時には、SQLエンジンが行ソース・ジェネレータによって作成されたツリー内の各行ソースを実行します。このステップのみがDML処理の必須ステップです。
図3-3は、例3-1の計画の各ステップ間の行ソースのフローを示す実行ツリー(解析ツリーとも呼ばれる)です。通常、実行時のステップの順序は計画時の順序とは逆になるため、計画の場合は下から上へと読み取ります。
実行計画の各ステップにはID番号があります。図3-3の番号は、例3-1に示されている計画のId
列に対応します。計画のOperation
列の最初のスペースは階層の関係を示します。たとえば、操作の名前の前に2つのスペースがある場合、この処理は1つのスペースの後続となる子処理です。前に1つのスペースがある操作は、SELECT
文自体の子です。
図3-3では、ツリーの各ノードが行ソースとして機能しており、例3-1の実行計画の各ステップがデータベースから行を取得するか、1つ以上の行ソースから入力として行を受け取ります。SQLエンジンは各行のソースを次のように実行します。
-
黒のボックスで示されたステップは、データベース内のオブジェクトから物理的にデータを取り出します。これらのステップはアクセス・パス、つまりデータベースからデータを取得する技法です。
-
ステップ6は全表スキャンを使用して
departments
表のすべての行を取得します。 -
ステップ5は全表スキャンを使用して
jobs
表のすべての行を取得します。 -
ステップ4は
emp_name_ix
索引を順番にスキャンして、文字A
で始まる各キーを検索し、該当のROWIDを取得します。たとえば、Atkinson
に対応するROWIDはAAAPzRAAFAAAABSAAe
です。 -
ステップ3は
employees
表からステップ4でROWIDが戻された行を取得します。たとえば、データベースはROWIDAAAPzRAAFAAAABSAAe
を使用して、Atkinson
の行を取得します。
-
-
透明のボックスで示されたステップは行ソースを操作します。
-
ステップ2はハッシュ結合を実行して、ステップ3および5から行ソースを受け取り、ステップ5の行ソースからの各行をステップ3の対応する行と結合して、ステップ1に結果の行を戻します。
たとえば、従業員
Atkinson
の行は職種Stock Clerk
に関連付けられています。 -
ステップ1は別のハッシュ結合を実行して、ステップ2および6から行ソースを受け取り、ステップ6のソースからの各行をステップ2の対応する行と結合して、クライアントに結果を戻します。
たとえば、従業員
Atkinson
の行は部門名Shipping
に関連付けられています。
-
実行計画には、ステップが反復して実行されるものと、連続して実行されるものがあります。例3-1に示されているハッシュ結合は連続して実行されます。データベースでは、結合順序に基づいてステップがすべて実行されます。データベースでは、emp_name_ix
の索引レンジ・スキャンから開始されます。索引から取得されるROWIDを使用して、データベースは、employees
表内の一致する行を読み取り、jobs
表をスキャンします。jobs
表から行を取得した後、データベースでは、ハッシュ結合が実行されます。
実行中、データがメモリー内にない場合は、データベースがディスクからメモリーへとデータを読み取ります。また、データベースはデータ整合性を保証するために必要なすべてのロックおよびラッチを取り除き、SQL実行時に行われたすべての変更を記録します。SQL文の処理の最後の段階は、カーソルのクローズです。
3.2 Oracle DatabaseによるDML処理方法
ほとんどのDML文には問合せコンポーネントがあります。問合せでは、カーソルを実行すると、問合せの結果が結果セットと呼ばれる行の集合に配置されます。
3.2.1 行セットのフェッチ方法
結果セットの行は、1行ごとにまたはグループ単位でフェッチできます。
フェッチ段階では、データベースで行が選択され、問合せで要求された場合には行が順序付けされます。最後の行がフェッチされるまで、毎回のフェッチで結果の行が連続して取得されます。
通常、データベースは、最後の行がフェッチされるまで問合せによって取得する確実な行数を判定できません。Oracle Databaseはフェッチ・コールに応じてデータを取得するため、データベースが読み取る行が多いほど、データベースが実行する作業が増加します。一部の問合せではデータベースが最初の行を可能なかぎり迅速に戻すのに対して、別の問合せでは最初の行を戻す前にデータベースは全結果セットを作成します。
3.2.2 読取り一貫性
一般に、問合せはOracle Databaseの読取り一貫性メカニズムを使用してデータを取得します。このメカニズムでは、問合せによって読み取られるすべてのデータ・ブロックが単一の時点のものと一貫していることが保証されます。
読取り一貫性では、UNDOデータを使用して過去のバージョンのデータを表示します。たとえば、1つの問合せで全表スキャンにより100個のデータ・ブロックを読み取る必要があるとします。問合せが最初の10個のブロックを処理している間に、別のセッションのDMLがブロック75を変更します。最初のセッションがブロック75に到達したとき、問合せは変更を認識し、変更前の古いバージョンのデータを取り出すためにUNDOデータを使用し、メモリー内に現時点のバージョンではないブロック75を構成します。
関連項目:
マルチバージョン読取り一貫性について学習するには、『Oracle Database概要』を参照してください。
3.3 Oracle DatabaseによるDDLの処理方法
Oracle DatabaseはDMLとは異なる方法でDDLを処理します。
たとえば、表を作成するとき、データベースはCREATE TABLE
文を最適化しません。かわりに、Oracle DatabaseはDDL文を解析してコマンドを実行します。
DDLはデータ・ディクショナリ内のオブジェクトを定義する手段であるため、データベースはDDLを異なる方法で処理します。通常、DDL文を実行するために、Oracle Databaseは多くの 再帰的SQL文を解析して実行する必要があります。次のような表を作成するとします。
CREATE TABLE mytable (mycolumn INTEGER);
通常、この文を実行するために、データベースは多数の再帰的な文を実行します。再帰的SQLでは、次のようなアクションが実行されます。
-
CREATE TABLE
文を実行する前にCOMMIT
を発行します。 -
ユーザー権限が表の作成に十分であることを検証します。
-
表が属する表領域を決定します。
-
表領域の割当てが超過していないことを確認します。
-
スキーマ内に同じ名前のオブジェクトがないことを確認します。
-
データ・ディクショナリに表を定義する行を挿入します。
-
DDL文が成功した場合は
COMMIT
を発行し、失敗した場合はROLLBACK
を発行します。
関連項目:
DDLの処理、トランザクション制御およびその他のタイプの文について学習するには、『Oracle Database開発ガイド』を参照してください。