OracleとPostgresの両方でのSQLクエリの処理には、多くの共通点があります。 何らかの方法で、解析を実行し、セマンティクスを確認する必要があります（メタ情報が必要であり、「データディクショナリ」または「システムカタログ」と呼ばれるかどうかは関係ありません）、変換を実行し、最適な実行計画を構築しますコストがかかるため、事前にコンパイルされた統計が必要です）。

しかし、処理へのアプローチ全体を根本的に変える1つの重要な違いがあります。 もちろん、Oracleは解析されたリクエストのグローバルキャッシュを使用し、Postgresはリクエストをローカルに保存します。

この記事では、1つのアーキテクチャソリューションの違いにより、2つのDBMSでクエリを処理するというまったく異なるイデオロギーが論理的にどのように続くかを追跡しようとします。

上記の例（Oracle 11.2 XEおよびPostgreSQL 9.4のバージョンで実行された）には、クエリの実行時間が含まれています。 相対的な値にのみ関心があります。リクエストに変更を加えた後、実行時間が変更される回数です。 この場合、絶対数は、機器、負荷、設定に応じて桁違いに異なる場合があります。 それらに基づく無意味な結論の理由を与えないために、記事のすべての絶対値は、両方のシステムでクエリの1つが10秒になるようにスケーリングされます。

オラクル

Oracleは、解析されたリクエストのインスタンス全体（ライブラリキャッシュ、ライブラリキャッシュ）にグローバルキャッシュを使用します。 実行されたリクエストのプランは、キャッシュ内にあることが保証されます。リクエストは、キャッシュからすでに準備されたプランで実行されるか、新しいプランが構築されてキャッシュに保存されます。

単純化された一般的なクエリ実行スキームは、次のように表すことができます。

1. クエリの解析（SQLコマンドのスペルが正しいかどうか）。
2. セマンティック分析（これらのオブジェクトが存在し、それらにアクセスできるかどうか）。
3. 準備された計画がキャッシュにある場合は、それを使用します。 そうでなければ-さらに。
4. 変換（ヒューリスティックルールに従って要求を書き換える）。
5. 最適化（最小コストの実装計画の選択）。
6. 選択したプランをキャッシュします。

同じリクエストが連続して2回繰り返されると、処理方法が異なります。 初めて、いわゆる完全な分析（ハード解析）が行われます-最初の段落から最後の段落まで。 2回目は、構文解析と意味解析の部分的な解析のみが実行されます。その後、既製のプランが検索され、キャッシュで使用されます。これはより効率的です。

グローバルキャッシュの存在は、その中のエントリの数を最小限に抑えるためにプッシュされます。 1つの理由は、「1回限りの」リクエストの大きなストリームが有用なプランをキャッシュから押し出すことができる一方で、それらのリクエスト自体は決して繰り返されないからです。 しかし、最も重要なことは、並列プロセスが共有キャッシュにアクセスしているため、ロックで保護する必要があり、書き込みがボトルネックになる可能性があることです。

実際、多くの解析を行うプロセスは、インスタンス全体の問題になります。 次の例でこの状況を考慮してください。

create table t(
id number primary key,
n number not null
);
insert into t(id, n)
select level, 1
from dual
connect by rownum <= 100000;
exec dbms_stats.gather_table_stats(user,'T');
alter session set statistics_level=all;

ここで、テーブルを作成し、そこに10万行を挿入し（「rowid <= Nによるデュアル接続から」コンストラクトは、N行の選択を生成するイディオムです）、統計を収集します。

以下のPL / SQLコードを実行してみましょう。これは、動的に生成された更新クエリを使用して、テーブルをループで1行ずつ更新します（おそらくこの例は非常に手間がかかりますが、実際にはそうではありません）：

begin
for i in (select id from t) loop
execute immediate 'update t set n = n + 1 where id = '||i.id;
end loop;
commit;
end;
/

トレースする場合、ここにあります。

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 100003 92.63 95.40 0 2837 0 0
Execute 100003 13.57 14.29 0 200002 102225 100000
Fetch 1002 0.87 0.75 0 10173 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 201008 107.08 110.46 0 213012 102225 200000

Misses in library cache during parse: 100001

コードのブロックから開始されたすべてのSQLクエリに関する情報をここに示します。 経過列は合計経過時間（CPUとさまざまな期待値で構成されます）を示し、解析、実行、フェッチの行は、クエリ結果の解析、実行、受信の段階に対応します。 ご覧のとおり、メインの時間（110列のうち95秒、経過列）は、同じタイプのリクエスト10万（カウント列）の解析とその1回限りのプランのキャッシュへの投入に費やされました。 複数の同様のプロセスを同時に開始すると、「ラッチ：共有プール」や「ラッチ：行キャッシュオブジェクト」（バージョンごとに名前が変わる）などの期待が現れ始め、ライブラリキャッシュへのアクセスの競合を示します。

これを防ぐには、Oracleでバインド変数を使用するのが一般的です。 たとえば、次のように：

begin
for i in (select id from t) loop
execute immediate 'update t set n = n + 1 where id = :A' using i.id;
end loop;
commit;
end;
/

または、PL / SQLが変数をデータベースバインディング変数に自動的に変換するため、動的SQLを使用せずにシンプルになります。

begin
for i in (select id from t) loop
update t set n = n + 1 where id = i.id;
end loop;
commit;
end;
/

この場合、トレースは次のように表示されます。

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.02 0.03 0 297 0 0
Execute 100002 9.08 9.28 0 201694 102315 100000
Fetch 1001 0.77 0.68 0 10173 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101006 9.87 10.00 0 212164 102315 200000

解析時間は最小限に短縮されました。DBMSの更新要求はすべて同じになりました。 「アイデンティティ」、つまり実際にはキャッシュのキーは、2つの値によって決定されます。

• sql_id-要求テキストのハッシュコード（つまり、任意の文字が異なる要求は既に異なる要求です）
• child_number-少なくとも構文的に同一のクエリ（同じsql_idを使用）が意味的に異なる可能性があり、異なるプランを持たなければならないという事実によって、追加の必要性が生じます。

したがって、更新クエリは1回だけ解析されます（count列の3番はPL / SQLブロックの解析、for句のselectクエリ、ループの本体のupdateクエリに対応します）。 彼の計画はキャッシュされ、すべてが比較的迅速に機能します。

（なぜ「相対的」なのでしょうか？正しい方法は、1つのコマンド「update t set n = n + 1」で更新することです。これは1桁高速に実行されます。）

ただし、変数の値を考慮せずに構築された「一般的な」クエリプランは、均等に分散されたデータにのみ適しています。

テーブルを変更してみましょう。0.1％の行では「Y」、残りの99.9％では「N」に等しいフラグフィールドを追加してインデックスを作成します。

alter table t add (
flag char(1) check (flag in ('Y','N'))
);
update t
set flag = case when mod(id,1000)=0 then 'Y' else 'N' end;
create index t_flag on t(flag);

オプティマイザがフラグフィールドのデータの不均一性を考慮するためには、このフィールドのヒストグラムを収集する必要があります。 たとえば、次のように：

exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for columns flag size 2');

興味深いことに、explain planコマンド（その結果はdbms_xplan.display関数を使用して利用可能）は、オプティマイザーがテーブルの半分を受け取ることを期待するかのように、均一性の仮定から構築されたプランを引き続き表示します。

explain plan for select * from t where flag = :f;
select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 488K| 76 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 50000 | 488K| 76 (2)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("FLAG"=:F)

これは、概して、OracleのEXPLAIN PLANコマンドを使用できないことを意味します。 変数の値もその型も考慮されず、変数によって生成されたプランはキャッシュに入れられず、いかなる方法でも使用されません。

実際、クエリを実行すると、Oracleはバインディング変数の値を「覗き」（これを「バインドピーク」と呼びます）、これらの値に基づいてプランを構築します。 要求が既に実行のために送信されて解析されている場合、キャッシュで実際のプランを直接調べる必要があります。 これを行うには、dbms_xplan.display_cursor関数を使用します。 例で指定されたパラメーターを使用すると、最後に実行されたリクエストのプランとバインディング変数に関する情報が表示されます。

var f char(1)
exec :f := 'Y'
select * from t where flag = :f;
...
100 rows selected.

select * from table(dbms_xplan.display_cursor(format=>'typical +peeked_binds'));

SQL_ID 6pncxxhknwgqc, child number 0

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 135 | 1350 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_FLAG | 135 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :F (CHAR(30), CSID=873): 'Y'

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

2 - filter("FLAG"=:F)

これで、オプティマイザーが変数の値（ピークバインドセクション）を考慮し、行数を適切に推定し（135、エラーは結果に影響しません）、インデックスによるアクセスを選択したことが明らかです。

問題は、組み込みの「プライベート」プランがキャッシュに移動し、変数の値を考慮せずに同じリクエストに再利用されることです。 これは常に良いとは限りません。この例では、インデックスアクセスは「N」の値に対して非常に非効率的です。 従来、解決策は、クエリテキストにリテラルを貼り付けた動的SQLを使用することでしたが、解決策は失敗しました。上記の欠点に加えて、このアプローチはSQLインジェクションの可能性があるため危険です。 そのため（バージョン11g以降）、Oracleはバインディング変数の値に敏感なクエリを見つけて処理することができます（これを「適応カーソル共有」と呼びます）。 リクエストを実行するとき、すでにキャッシュにあるプランが使用されますが、実際に消費されたリソースは追跡され、以前の実行の統計と比較されます。

リクエストに応じて、ライブラリキャッシュからの情報の一部を見てみましょう。

select child_number, is_bind_sensitive, is_bind_aware, executions, buffer_gets from v$sql where sql_id='6pncxxhknwgqc';

CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE EXECUTIONS BUFFER_GETS
------------ ----------------- ------------- ---------- -----------
0 Y N 1 128

要求は、バインド依存としてマークされます。 Buffer_gets-読み取られたデータブロックの数。

クエリが他の値を使用して実行されたことが悪い場合、次に実行されたときに、異なるプランが必要であるとマークされます（バインド対応）。

異なるフラグフィールド値で同じリクエストを実行してみましょう。

exec :f := 'N'
select * from t where flag = :f;
...
99900 rows selected.

キャッシュからのプランでリクエストが実行されたことを確認し、同時に、プランの期待値だけでなく実際の値も出力する可能性を示します（このため、statistics_levelパラメーターが最初に設定されました）。

select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

SQL_ID 6pncxxhknwgqc, child number 0

-----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 99900 | 41368 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 135 | 99900 | 41368 |
|* 2 | INDEX RANGE SCAN | T_FLAG | 1 | 135 | 99900 | 6842 |
-----------------------------------------------------------------------------------

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

2 - access("FLAG"=:F)

予想される行数（135）と実際の行（99900）には矛盾があります。 さらに、実行するためには、初回（buffer_gets列）よりもはるかに多くのデータを読み取る必要があったことは明らかです。

select child_number, is_bind_sensitive, is_bind_aware, executions, buffer_gets from v$sql where sql_id='6pncxxhknwgqc';

CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE EXECUTIONS BUFFER_GETS
------------ ----------------- ------------- ---------- -----------
0 Y N 2 41496

リクエストを再度実行します。

select * from t where flag = :f;
...
99900 rows selected.

これで、バインド変数の新しい値用に構築された新しいプランが使用されます（変更された子番号とピークされたバインドセクションに注意してください）。

select * from table(dbms_xplan.display_cursor(format=>'typical +peeked_binds'));

SQL_ID 6pncxxhknwgqc, child number 1

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 77 (100)| |
|* 1 | TABLE ACCESS FULL| T | 99856 | 975K| 77 (3)| 00:00:01 |
--------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :F (CHAR(30), CSID=873): 'N'

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

1 - filter("FLAG"=:F)

今回、オプティマイザーは行数（99856、わずかなエラーのマージン）を正しく推定し、全表スキャンを選択しました。 また、ライブラリキャッシュには、同じリクエストに対する2つのバージョンのプランがあります。

select child_number, is_bind_sensitive, is_bind_aware, executions, buffer_gets from v$sql where sql_id='6pncxxhknwgqc';

CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE EXECUTIONS BUFFER_GETS
------------ ----------------- ------------- ---------- -----------
0 Y N 2 41496
1 Y Y 1 6922

キャッシュ内のプランの数を最小限に抑えるため、オプティマイザーは1つのリクエストに対して異なるプランを使用するかどうかを決定する前に「つまずき」ます。 これは、事前にオプティマイザーに手動でヒントを与えることで回避できることに注意してください。

ポストグレス

Postgresには、解析されたリクエストのグローバルキャッシュはありません。 さらに、特別な努力が行われない場合、リクエストはプロセスメモリにローカルに保存されません。

特に、同じリクエストを繰り返すと、毎回完全に逆アセンブルされます。 もちろん、この方法で記述されたプロセスは最適に動作しませんが、少なくとも他のプロセスに直接影響を与えることはありません。

例を考えてみましょう：

create table t(
id serial primary key,
n numeric not null
);
insert into t(n)
select 1 from generate_series(1,100000);
analyze t;

次のPL / pgSQLコードを実行します。

\timing on
do $$
declare
i record;
begin
for i in (select id from t) loop
execute 'update t set n = n + 1 where id = '||i.id;
end loop;
end;
$$ language plpgsql;
DO
Time: 36164,377 ms

解析の結果を保存するには、クエリを準備する必要があります。その場合にのみ、保存したクエリを再利用できます。

prepare u(integer) as update t set n = n + 1 where id = $1;
execute u(1);
execute u(2);
...
execute u(100000);

これは、最初の例のように、executeを使用せずにPL / pgSQLブロックでSQLコマンドを呼び出した場合に起こることです。 私たちの場合、これにより3.5倍の速度向上が得られます。

do $$
declare
i record;
begin
for i in (select id from t) loop
update t set n = n + 1 where id = i.id;
end loop;
end;
$$ language plpgsql;
DO
Time: 10000,000 ms

（そして、正しいオプション— 1つのSQLコマンド—は3倍速く実行されます。）

一般的なリクエスト解析スキームは、次の手順で構成されます。

1. 解析
2. セマンティック分析。
3. 書き換えを要求する（システムとユーザーの両方の規則に従って）。
4. 最適化。

要求を準備するとき、それは分析され、書き換えられます。 最適化は実行時に改めて実行されます。したがって、バインディング変数の各値に対して、独自の「プライベート」プランが構築されます。

不均一なデータ分布の例を考えてみましょう（文字変数の代わりにブール型を使用できます）：

alter table t add column
flag boolean;
update t
set flag = mod(id,1000)=0;
create index on t(flag);

テーブルの分析時に、必要なヒストグラムが自動的に作成されます。

analyze t;

リクエストを準備します。

prepare s1(boolean) as select * from t where flag = $1;

フラグの真の値に対してどの実行プランが選択されるかを調べるには、explainコマンドを使用する必要があります。 Postgresでは、バインド変数の意味とタイプを認識しており、コマンドが実行されるプランを正確に示しています。

explain execute s1(true);
QUERY PLAN
------------------------------------------------------------------------
Index Scan using t_flag_idx on t (cost=0.29..14.31 rows=110 width=10)
Index Cond: (flag = true)
Filter: flag

オプティマイザーは110行を選択することを期待し（これもわずかな誤差があります）、インデックスアクセスを使用します。

また、explainコマンドは、プランを作成できるだけでなく、コマンドを実行して、カーディナリティの期待値と現在値の両方をすぐに取得できるため、便利です。 別のフラグ値でこれを示します。

explain analyze execute s1(false);
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..2958.00 rows=99890 width=10) (actual time=0.043..265.272 rows=99900 loops=1)
Filter: (NOT flag)
Rows Removed by Filter: 100
Execution time: 385.455 ms

この場合、オプティマイザーは99890行（実際には99900）を取得することを期待し、テーブルの完全な読み取りを適切に選択します。

これにより、Oracleが直面している問題と反対の問題が発生します。プランがバインディング変数の値に依存していない場合はどうなりますか？ この場合、毎回リクエストを最適化しないことが有益です。

実際、Postgresは「プライベート」計画から「一般」計画（一般計画）に移行する方法を知っていますが、すぐには実行しません。 リクエストはいずれの場合でも最初の5回最適化され、そのコスト（オプティマイザーによる）がプライベートプランの平均コストを超えない場合、一般プランが優先されます。 ここでの5番は一種の妥協です。小さな値はバインディング変数の異なる値の値に関する十分な統計を提供せず、大きな値は最適化自体を無効にします。

データの均一な分布を使用した例を使用して、このメカニズムを検討してください。

prepare s2(integer) as select * from t where id = $1;
explain execute s2(1);
QUERY PLAN
-----------------------------------------------------------------
Index Scan using t_pkey on t (cost=0.42..8.44 rows=1 width=10)
Index Cond: (id = 1)

これはプライベートプランであり、「インデックス条件：（id = 1）」という条件で確認できます。特定の番号がここに示されています。

ただし、変数の任意の値を使用してExplainを呼び出すか、クエリをさらに4回実行する場合は、一般的なプランに切り替えます。

execute s2(2);
...
execute s2(3);
...
execute s2(4);
...
execute s2(5);
...
explain execute s2(6);
QUERY PLAN
-----------------------------------------------------------------
Index Scan using t_pkey on t (cost=0.42..8.44 rows=1 width=10)
Index Cond: (id = $1)

ここでは、「Index Cond：（id = $ 1）」という条件で、特定の値の代わりに、バインディング変数の番号が示されています-これは一般的な計画の兆候です。 この場合のその価値は、プライベートプランのコストと一致します。

これで、既成の計画がリクエストに使用され、実行の効率が向上します（ただし、コストの計算にエラーが発生した場合、または最初の5回が「指標ではない」場合に問題が発生する可能性があります）。

おわりに

解析されたリクエストのグローバルキャッシュをOracleで使用するという決定は、サイズに制限があり、有用なプランが混み合う危険性があるためと、キャッシュにアクセスするための並列プロセスの競合のために、絶対に必要なものよりも多く書き込みたくないという願望につながります。 したがって、Oracleは要求の1つの一般的な計画から始まり、必要な場合にのみいくつかの商に進みます。

それどころか、Postgresでグローバルキャッシュを使用しないという決定により、不必要な解析との関連付けが容易になります。 反対に、Postgresはプライベートプランから始まり、可能であれば一般的なプランに移行します。

Oracleはリクエストの計画を自動的にキャッシュします。 この点で、開発者はバインド変数を使用することを覚えておくだけでよく、これは主にグローバルキャッシュの制限によって決まります。 問題の重大度により、Oracleはcursor_sharingパラメーターを提供し、すべての定数を変数に置き換えます。

Postgresは、解析されたリクエストを開発者または開発ツールの手に保存する必要性を完全に判断します。 バインド変数の使用は、Postgresのパフォーマンスにそれほど劇的な役割を果たしません（ただし、SQLインジェクションのセキュリティ問題は両方のシステムに等しく関連します）。

複数のプロセスが同じクエリを使用する場合、Oracleで解析されるのは1回のみです。 残りのプロセスは、グローバルキャッシュ内の既製のプランを利用します。

Postgresでは、各プロセスはリクエスト自体を解析する必要があります。 ただし、1回限りの要求は、プランをキャッシュに入れるオーバーヘッドなしで実行されます。

各ソリューションには長所と短所があります。 いずれにしても、これらの機能は、アプリケーションシステムを設計、実装、および保守する開発者および管理者が考慮する必要があります。