今回はOracleのSQLの具体的な処理の流れについて調査しました。
SQLは主に下記の順番で処理されます。
SQLの発行→カーソルオープン→SQL文の解析(文法チェック、解析、実行計画の作成など)→SQL文の実行→(フェッチ)→カーソルクローズ
※フェッチはSELECTのみ
SQL文の発行から実行されるまでの流れを見てみます。
前提として「専用サーバモード」、「コストベースオプティマイザ」を使用する場合です。
SQLの解析
SQL文の各単語を各チェック処理で処理できるデータ構造に分解し、構文チェック、意味チェック、共有プールチェックを行います
アプリケーションから発行されたときに初めてSQL文を解析するため、解析回数を削減できるのはアプリケーション側のみです(Oracle側ではできません)
構文チェック
スペルミスなどSQL文の文法をチェックします
一般的なコンパイルでいう「構文解析」
いわゆる「字句解析」もこのフェーズでやっているのでしょうか？
(例)
SQL> SELECT * FORM employees;
SELECT * FORM employees
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
意味チェック
SQL文で指定されたオブジェクトが存在するかなどSQL文の意味が有効かチェックします
一般的なコンパイルでいう「意味解析」
SQL文を実行する権限チェックもこのフェーズでやっているのでしょうか？
(例)
SQL> SELECT * FROM nonexistent_table;
SELECT * FROM nonexistent_table
*
ERROR at line 1:
ORA-00942: table or view does not exist
共有プールチェック
発行されたSQL文がライブラリキャッシュの共有SQL領域(カーソルキャッシュ)の既存のSQL文と同じか確認します
厳密にはSQL文ごとに13桁のハッシュ値(SQL ID)を生成し、既存の解析済みのSQL文に同じハッシュ値があるか確認します
ハッシュ値を見てソフトパースを実行するかハードパースを実行するか判断します
ハッシュ値が異なる場合(共有SQL領域に同じSQL文が存在しなかった場合)
ハードパース(ハード解析)が実行され、「SQLの最適化」へ遷移する
ハードパースは実行するアプリケーションコード(プログラム)を新しく生成する(ライブラリ・キャッシュ・ミス)
※再利用できるSQL文がない(＝再利用できるアプリケーションコード(プログラム)がない)ため、「SQLの実行」で実行するアプリケーションコード(プログラム)を新しく生成する必要がある
ライブラリキャッシュやデータディクショナリキャッシュへ何度もアクセス(メタデータのフェッチ)するため、大量のラッチ(Oracleの内部ロック)が発生する
ソフトパースに比べ、ディスクI/OやCPU使用量が増えるため、処理時間が掛かる
ハッシュ値が同じ場合(共有SQL領域に同じSQL文が存在した場合)
ソフトパース(ソフト解析)が実行され、「SQLの実行」へ遷移する
ソフトパースではSQL文(実行するアプリケーションコード(プログラム))を再利用する(ライブラリ・キャッシュ・ヒット)
なお、同じSELECT文でも「select」、「SELECT」ではハッシュ値が異なるため、異なるSQL文とみなされる
また、下記のようにまったく同じSQL文でもスキーマが異なる場合、別の意味になってくるため、ソフトパースにならないケースもある(ハードパースに切り替わる)
(例)
hogeスキーマ内で select * from emp;
fugaスキーマ内で select * from emp;
SQLの最適化フェーズと行ソースジェネレータによる行ソース生成を省略できるため、ハードパースに比べて優れている
同じセッションまたは異なるセッションにある複数のプライベートSQL領域は、単一の共有SQL領域を参照できる(カーソル共有)
SQLの最適化
オプティマイザが複数の実行計画を作成し、複数の実行計画の中から最適(最小コスト)な実行計画を選択して行ソースジェネレータへ渡します
DMLはハード解析が実施される場合、必ず最適化されますが、DDLは一部を除いて最適化されません（DDLはDMLとは異なる手順で実行されるため）
DDLは最適化を要求する副問合せなどのDMLコンポーネントを含む（CTASなど）場合、最適化されます
検証中に実行計画の出力有無を試しました。
--実行計画の出力なし
TRUNCATE TABLE、DROP TABLE、CREATE TABLE、CREATE INDEX
--実行計画の出力あり
CTAS（CREATE TABLE XXX AS SELECT）、CREATE UNIQUE INDEX
SQLの行ソース生成
オプティマイザから受け取った最適な実行計画を基に行ソースジェネレータがSQLの実行に使用する実行計画(SQLの実行に必要なデータ構造(行ソースツリー))を出力します
行ソースツリーには下記の情報が含まれています
・SQL文によって参照される表の順序
→たとえば外部表はDEPT表、内部表はEMP表など
・SQL文で言及される各表へのアクセス方法
→たとえばDEPT表へのアクセスは索引スキャン(INDEX INIQUE SCAN)、EMP表へのアクセスは索引スキャン(INDEX RANGE SCAN)など
・SQL文の結合操作の影響を受ける表の結合方法
→たとえばDEPT表とEMP表はネステッドループ結合するなど
・フィルタ、ソートまたは集計などのデータ操作
→たとえばDEPT表のDEPTNO=10でフィルタし、DEPT表のDEPTNOとEMP表のDEPTNOで結合するなど
※これらの情報を保持つするのは行ソースツリーですが、表の順序やアクセス方法を決めるのはオプティマイザです
（例）行ソースジェネレータが出力する実行計画
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 05wg5gppgxj6w, child number 0
-------------------------------------
SELECT /* XPLAN_TEST1 */ E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME FROM DEPT
D, EMP E WHERE D.DEPTNO = E.DEPTNO AND D.DEPTNO = 10
Plan hash value: 1561127466
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | NESTED LOOPS | | 3 | 165 | 1 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 22 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 3 | 99 | 0 (0)| |
|* 5 | INDEX RANGE SCAN | IDX1_EMP_DEPTNO | 3 | | 0 (0)| |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
SQLの実行
行ソースジェネレータによって作成された行ソースツリー内の各行ソースをSQLエンジンが実行します
DDLやDMLはこのフェーズで処理を終了します
フェッチ(SELECTのみ)
データを取り出し、サーバプロセスへ渡します
【補足】
プライベートSQL領域
SQLを実行するために必要な情報が格納される
持続領域、ランタイム領域がある
・持続領域
バインド変数の値などSQL文の実行時、SQL文に指定されるものが格納される
カーソルクローズ時に解放される
ランタイム領域
問合せ実行状態情報(たとえば全表スキャンで現在までに取得された行数など)が格納される
DMLだとSQL文が終了するとランタイム領域が解放される
オープンカーソル
カーソルがオープンしている場合、解析処理しないでカーソルハンドルを使用できる
クローズカーソル
SQL処理が完了すると通常はカーソルをクローズする
クローズ後もプライベートSQL領域にはカーソルが残るため、再度オープンすれば即時にカーソルを使用できる
キャッシュするカーソル数はSESSION_CACHED_CURSORS初期化パラメータで設定できる
セッションメモリー
セッション変数(ログイン情報)やセッションに関する情報を格納する
作業領域
結合や並べ替えなどSQLの処理でメモリ操作が発生するときに必要に応じて使う
ソート領域、ハッシュ結合領域、スタック領域がある
・ソート領域
ORDER BY、GROUP BY、ROLLUPなどで使用する
・ハッシュ結合領域
ハッシュ結合で使用する
・ビットマップ操作領域
ビットマップマージ、ビットマップ作成などで使用する
スタック領域
ユーザーセッションで使用されるローカル変数を保持する領域
ライブラリキャッシュ
共有SQL、共有PL/SQL領域、オブジェクトのメタデータなどが格納される
SQL実行時、カーソル(ポインタ)を使用して操作が行われる
ディクショナリキャッシュ
データディクショナリから取得したレコードが格納される
結果キャッシュ(11g以降)
SELECT文の結果レコードを格納する
バッファキャッシュにアクセスせずに使用頻度の高いレコードを再利用できる
「SQLの最適化」でオプティマイザが実施する処理をもう少し詳しく見てみます。
問合せトランスフォーマ
パーサから受け取ったSQL(構文解析フェーズや意味解析フェーズで文法チェックや権限チェックの完了したSQL)に対し、選択可能な実行計画を増やすため、さらに効率的に実行できる可能性がある別のSQLに書き換えます
たとえばビューや副問合せを展開して通常の結合に変換するなど
エスティメータ
オプティマイザ統計や動的サンプリング(動的統計)で収集された統計情報をデータディクショナリから参照して変換済みSQLに対する実行計画の実行コスト値を算出します
プランジェネレータ
実行計画を生成します
ただし、データアクセスパス、テーブルの結合順序、テーブルの結合方法の組み合わせを見て、さらに実行コスト値の小さい実行計画が生成できる可能性がある場合、エスティメータへ遷移する
■参考資料
メモリー・アーキテクチャ
カーソル共有によるReal-World Performanceの改善
SQLの処理
V$SQLAREA
V$SQL
パフォーマンス改善と事前対策に役立つ Oracle SQLチューニング 本気で学ぶ実践的な考え方とテクニック
新・門外不出のOracle現場ワザ エキスパートが明かす運用・管理の極意
オラクルマスター教科書 ORACLE MASTER Expert パフォーマンス・チューニング編
■おわりに
共有プール内のカーソルは親カーソルと子カーソルに分けられます。(同じSQL文でも実行計画が異なるパターンに対応するため)
親カーソルにはSQLテキストが格納されます(V$SQLAREAで確認可能)
子カーソルには生成された解析結果(実行計画、バインド変数の値など)が格納されます(V$SQLで確認可能)
【検証環境】
OS：Oracle Linux 6.5
DB/GI：Oracle Database 12c Release 1 (12.1.0.2.0) Enterprise Edition
※2ノードRAC(管理者管理型DB)
【検証手順】
1. SCOTTユーザでEMPテーブル取得(HRユーザとは別セッション)
2. HRユーザでEMPテーブル取得(SCOTTユーザとは別セッション)
3. カーソル情報確認
【作業ログ】
SQL> SELECT /* TEST */ * FROM EMP WHERE EMPNO = 7369;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------------- --------------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
2. HRユーザでEMPテーブル取得(SCOTTユーザとは別セッション)
SQL> SELECT /* TEST */ * FROM EMP WHERE EMPNO = 7369;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------------- --------------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
3. カーソル情報確認
SQL> select SQL_ID, SQL_TEXT, VERSION_COUNT, PARSING_USER_ID, PARSING_SCHEMA_NAME, CPU_TIME, DISK_READS from v$sqlarea where sql_text like '%TEST%' ORDER BY FIRST_LOAD_TIME DESC;
SQL_ID SQL_TEXT VERSION_COUNT PARSING_USER_ID PARSING_SCHEMA_ CPU_TIME DISK_READS
--------------- ------------------------------------------------------------ ------------- --------------- --------------- ---------- ----------
731pcxytcvana select SQL_ID, SQL_TEXT, VERSION_COUNT, PARSING_USER_ID, PAR 1 0 SYS 9000 1
SING_SCHEMA_NAME, CPU_TIME, DISK_READS from v$sqlarea where
sql_text like '%TEST%' ORDER BY FIRST_LOAD_TIME DESC
7z1q049gfa0n6 SELECT /* TEST */ * FROM EMP WHERE EMPNO = 7369 2 102 HR 14000 24
※SQL_ID：SQL文の識別子
※SQL_TEXT：SQL文
※VERSION_COUNT：子カーソルの数
※PARSING_USER_ID：子カーソルの解析に使用されたスキーマID
※PARSING_SCHEMA_NAME：子カーソルの解析に使用されたスキーマ名
※CPU_TIME：CPU時間(マイクロ秒)
※DISK_READS：ディスク読取り回数
SQL> select SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, PARSING_USER_ID, PARSING_SCHEMA_NAME, CPU_TIME, DISK_READS from v$sql where sql_id= '7z1q049gfa0n6';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE PARSING_USER_ID PARSING_SCHEMA_ CPU_TIME DISK_READS
--------------- ------------ --------------- --------------- --------------- ---------- ----------
7z1q049gfa0n6 0 2949544139 110 SCOTT 8000 15
7z1q049gfa0n6 1 2949544139 102 HR 6000 9
※CHILD_NUMBER：子カーソルの番号
※PLAN_HASH_VALUE：実行計画の識別子
★SCOTTユーザとHRユーザで同じSQL文を実行したが、スキーマが異なるため、子カーソルが別々で作成された
