Database
技術記事
Ask Tom
テクノロジー： Ask Tom
接続プール、カーソルの違い、最適な順序についてTom Kyteオラクルの技術者が、プールを整理し、カーソルの種類について探求し、表作成の順序を検討します。 Oracle Database 10g Release 2を実行するサーバー（2CPU、12GB RAM）で重大なパフォーマンス問題が発生しています。 Automatic Workload Repositoryレポートには、SQL文のチューニングが必要であると示されていますが、人的リソースが不足しているためアプリケーションレベルのチューニングを行うことは不可能です。 このAutomatic Workload Repositoryレポートの冒頭を参照の上、パフォーマンスを改善する方法（SQLチューニング以外の方法）を教えてください。 困り果てています。 注：提供されたOracle Database Automatic Workload Repositoryツールのレポートはサイズが大きいため、ここには掲載していません。 レポートを確認したい場合は、オンライン（bit.ly/zN0vK3）でダウンロードできます。 この質問に対する私の最初の回答は、以下のとおりです。 さらに、この質問を投稿した方から返答のコメントが投稿されたため、その内容についてもここに記載しました。 フィードバック（良い、悪い、または関心がない）を得て状況を確認できることはすばらしいことです。 今回のフィードバックは良い内容であり、以下に説明する私のポイントが正しかったことを示してくれました。 質問者によると“アプリケーションレベルのチューニングは不可能”であり、それを行う人的リソースがないとのことですが、私はアプリケーションのチューニングが必要であると断言します。 “データベースのチューニング”によって、アプリケーションの実行時間を数%、もしくは数十%削減することは可能です。しかし、投稿されたようなパフォーマンス問題が発生した場合は、大幅なアプリケーション実行時間の改善が必要です。 ここには魔法のような解決方法はありません。 求める水準の応答を得るためにアプリケーションやその全体的な設計と実装を調査する必要がないとすれば、アプリケーションやその設計、実装の調査は誰もしないでしょう。 実行時間を大幅に削減するためには、アプリケーションやその設計、実装を調査することになります。 10%の高速化なら、運が良ければアプリケーションを変更することなくデータベースのチューニングができるかもしれません（ここではかもしれませんを強調しておきます）。 しかし、そのようなことは非常に稀であり、データベースがセルフチューニングされると、その可能性はさらに少なくなります。 質問は“パフォーマンスを改善する方法（SQLチューニング以外の方法）を教えてください。 困り果てています”という言葉で締めくくられています。 質問者が困り果てていることは想像できます。 しかし、この種の要望は、まるで目隠しをしてヨーロッパからアメリカまで1日で歩いて行く方法を求めるようなものです。 アプリケーションを変更せずにアプリケーションをチューニングすることは不可能です。 まずは、チューニングに対するそのような考え方を捨て、ほぼ確実にアプリケーションを変更する必要があると認識してください。 提供されたAutomatic Workload Repositoryレポートを見ると、システム・レベルでもっとも長く実行しているイベントはラッチ関連のもの、 つまりキャッシュ・バッファ・チェーンとライブラリ・キャッシュであることが分かります。 さらに、CPU時間の値が非常に大きくなっています。 並行処理に基づく待機時間の原因は1つしかありません。それは、多数のセッションが同時にアクティブになっていることです。 同時にアクティブになるセッションを少なくすれば、並行処理に基づく待機時間は確実に減少します（競争する人数が減るようなものです）。 このレポートによると、データベース内の134のセッションを、合計4つのCPUコアで実行しています。 4つのCPUコアで134のセッションを同時にアクティブにすることはまったく不可能であるため、接続プールのサイズを大幅に減らして、セッションの同時実行数を減らすことをお勧めします。 接続プールのサイズを32程度にまで減らしましょう。 4コアで同時に134の作業を実行することは不可能です。厳密に言えば、4コアで同時に実行できる作業は4つだけです。 同時実行ユーザー・ロードを減らせば、待機時間が減少し、応答時間が減少し、トランザクション速度が向上します。 要するに、セッションを多くするよりも少なくした方が、実際にできる作業は多くなります。 このような、より少ないものでより多くを行うという助言は、直感的でないと感じられることでしょう。これを説明しているReal World Performanceのビデオをぜひご覧ください。 このビデオでは、12コアのマシンでトランザクションを実行するテストで、接続プールのサイズを過度に大きい値（数千）から適当な値（96）に減らしたときの結果を確認できます。 この特定のマシンで接続数を96にした場合、1秒あたりのトランザクション数が以前の150%に増加し、これらのトランザクションの応答時間が以前の100ミリ秒程度から、5ミリ秒程度まで減少しました。 接続プール・サイズを減らす（つまり、アプリケーションによるデータベースの利用方法を変え、データベース内で数百のアクティブ・セッションをキューイングするのではなく、中間層の接続プールでキューイングする）方法以外では、キャッシュ・バッファ・チェーン・ラッチを要求する頻度を下げるように問合せを変更する必要があります。 つまり、アプリケーション内で問合せとアルゴリズムのチューニングを行います。 魔法のような解決方法は文字通りありません。 システム・レベルでの微調整はできないかもしれず、 アプリケーションの変更を行う必要があるかもしれません。 以上が私の回答でした。この回答を投稿してまもなく、質問の投稿者から次のような返答がありました。 ご回答ありがとうございました。 . . . 接続プールのサイズを減らしたところ、パフォーマンスが改善されました。 この回答から、リソースの管理方法が実行時のパフォーマンスに大きく影響するという点を痛感させられます。 接続済みの同時実行セッション数が数百、もしくは数千に及ぶデータベースをあまりにも多く見かけます。 数百、もしくは数千のCPUがない限り、このような同時実行セッション数は3層アーキテクチャにおいては適切ではありません。 中間層に接続プールがあるのですから、データベースに実行させる同時実行ワークロードを、接続プールを使用して制限するべきです。 結局、コアがN個あればN個のものを同時に“アクティブ”にできるという単純な数学に行き着きます。 アクティブにしようとしたものがブロックされてI/Oを待機する場合、アクティブ・セッションの数がマシン上のコア数より大きくなることができますが、その係数は1桁程度です。 たとえば、前述のビデオのデモでは、接続数8×コア数12=合計接続数96を実行しています。 1コアあたり20、30、50、あるいはそれ以上（中には非常に多く）の接続を実行するケースを多く見かけますが、 その場合に起こりうることは1つだけです。すなわち、最終的に、マシンを制御するためにはコンセントを抜かなければならないという状態になります。 接続数に気をつけて、 適切な接続数を維持しましょう。 その効果は絶大です。 カーソルか参照カーソルかOracle PL/SQL開発者の求職に対する面接で、カーソルと参照カーソルの違い、およびそれぞれを使用すべき適切な状況について尋ねられました。 この質問に対する適切な回答を教えていただけますか。 技術的、内部的、基本的なレベルでは、両者は同じものです。 両方ともカーソルであり、同じように扱うことができます。 これ以降の回答では、参照カーソルと“通常の”PL/SQLカーソルについてのみ説明します（たとえばDBMS_SQLカーソルについては取り上げません）。 手短に言えば、通常のカーソルと参照カーソルのおもな違いは次のとおりです。
これらのポイントについて1つずつ説明します。 まず、参照カーソルは動的にオープンでき、実行時に定義される一方、通常のカーソルは静的であり、コンパイル時に定義されます。 リスト1では、参照カーソルと通常のカーソルを作成しています。 コード・リスト1：参照カーソルと通常のカーソルの作成
declare
type rc is ref cursor;
cursor c is select * from dual;
l_cursor rc;
begin
--ここでは、実行時に定義される参照カーソルを使用します。
if ( to_char(sysdate,'dd') = 30 ) then
open l_cursor for 'select * from emp';
elsif ( to_char(sysdate,'dd') = 29 ) then
open l_cursor for select * from dept;
else
open l_cursor for select * from dual;
end if;
--ここでは、通常のカーソルを使用します。 これは
--コンパイル時に定義され、変更できません。
open c;
end;
/
リスト1のコード・ブロックを見れば、2つのカーソルでもっとも際立った違いが分かるでしょう。 このブロックを何度実行しても、カーソルCは常にSELECT * FROM DUALとなりますが、 参照カーソル（L_CURSOR）はさまざまな文になります。 また、このコード・ブロックが示しているもう1つの点は、参照カーソルは実行時に構成される問合せでオープンすることも、コンパイル時に定義される問合せでオープンすることもできるという点です。 次の文を見てください。 open l_cursor for 'select * from emp'; この文は、参照カーソルL_CURSORを定義する問合せが真に動的であり、実行時に完全に構成することができることを示しています。 'select * from emp'の部分を任意の文字列（文字列型の任意のPL/SQL変数）に置き換えて、参照カーソルを実行時にオープンすることができます。 リスト1のL_CURSORに対する他の2つのopenコールは、参照カーソルに関連付けられる問合せを実行時に動的に指定できることを示しています。 この参照カーソルでは、今日が何日であるかによって、SELECT * FROM DEPTかSELECT * FROM DUALのいずれかの静的な問合せがオープンされます。 この参照カーソルは、通常のカーソルCと異なり、コンパイル時に決定される定義に縛られることがありません。 カーソルCに関連付けられた問合せは静的であり、このコードを何度実行しても同一です。 次に、2つ目のポイントである、参照カーソルは別のPL/SQLルーチンに渡すか、クライアントに返すことができる一方、通常のカーソルは（渡さずに）直接アドレッシングする必要があり、クライアント・アプリケーションに返すことはできない、という点について説明します。 この点の参照カーソルにとっての意味は、簡単に理解できます。 次のコードは、参照カーソルを入力として受け取り、そのカーソルから1行フェッチして、その行を出力するプロシージャです。
create or replace
procedure
p( l_cursor in out sys_refcursor )
is
l_rec all_users%rowtype;
begin
fetch l_cursor into l_rec;
if (sql%notfound)
then
return;
end if;
dbms_output.put_line
( 'data in procedure = ' ||
l_rec.username );
end;
/
SQL> variable x refcursor 最後にリスト2のコードを実行します。このコードでは、参照カーソルを動的にオープンし、PL/SQLサブルーチンに渡して、その後の処理のためにクライアント・プログラムに参照カーソルを返しています。 コード・リスト2：参照カーソルのオープン、受け渡し、返却 SQL> declare 2 l_cursor sys_refcursor; 3 l_rec all_users%rowtype; 4 l_query varchar2(300) := 5 'select * 6 from all_users 7 where rownum <= 5'; 8 begin 9 open l_cursor for l_query; 10 p( l_cursor ); 11 :x := l_cursor; 12 end; 13 / data in procedure = SYS PL/SQL procedure successfully completed. SQL> print x USERNAME USER_ID CREATED ————————————— —————————— ————————————— SYSTEM 5 05-SEP-10 OUTLN 9 05-SEP-10 DIP 14 05-SEP-10 ORACLE_OCM 21 05-SEP-10 この出力から分かるように、最初の行はPL/SQLルーチンによってフェッチされ出力されており、その後の4行はPL/SQLではなくクライアント・プログラムによってフェッチされ出力されています。 以上の2つの例により、通常のカーソルよりも参照カーソルの方が柔軟であると分かります。そこで、“そもそもなぜ通常のカーソルを使用するのか”という疑問が生じるかもしれません。 この答えは2つあります。 パフォーマンスとプログラミングの容易さです。 まず、パフォーマンスについて説明します。通常のカーソルは、開いた状態でPL/SQLによってキャッシュされますが、参照カーソルは異なります。 言いかえれば、参照カーソルを使用する場合、解析のペナルティが生じます。 この点も、単純なルーチンをコーディングすることで簡単に確認できます。このルーチンでは、通常のカーソルをオープンし、そのカーソルからフェッチしてカーソルを閉じます。さらに、参照カーソルでも同じことを実行します。 これを実行するルーチンは次のとおりです。
SQL> create or replace
2 procedure p
3 is
4 l_cursor sys_refcursor;
5 l_rec dual%rowtype;
6 cursor c is select *
from dual d1;
7 begin
8 open c;
9 fetch c into l_rec;
10 close c;
11
12 open l_cursor for select *
from dual d2;
13 fetch l_cursor into l_rec;
14 close l_cursor;
15 end;
16 /
Procedure created.
SQL*PlusでSQLトレースを有効にし、“exec p;”を使用してこのプロシージャを10回実行した場合、次のようなTKPROFレポートが表示されます。 SELECT * FROM DUAL D1 call count ————————— ———————— Parse 1 Execute 10 Fetch 10 ————————— ———————— total 21 SELECT * FROM DUAL D2 call count ————————— ———————— Parse 10 Execute 10 Fetch 10 ————————— ———————— total 30 両方のSQL文が10回ずつ実行されていますが、これはコードを10回実行したため期待どおりの動作です。しかし、参照カーソルに関連付けられた問合せでは、解析も10回実行されています。一方、通常のカーソルの問合せでは、解析は1回のみです。 PL/SQLでは、通常のカーソルは、再度実行されることを見越して、開いた状態でキャッシュされます。 これにより、PL/SQLが解析プロセス全体をスキップできるため、CPU利用率が大幅に削減されます （この説明で納得できない場合は、bit.ly/zPMeVwのビデオをご覧ください）。 このPL/SQLカーソルのキャッシュは、SESSION_CACHED_CURSORSデータベース初期化パラメータによって制御されます。開発者にとっては完全に透過的で、まったく気にする必要がありません。 つまり、PL/SQLでカーソルが開いた状態でキャッシュされることによってプログラミングが影響を受けることはまったくありません。 現在空いているオープン・カーソル・スロット数よりも多くのオープン・カーソル（PL/SQLでキャッシュされたカーソルではないもの）が必要になった場合、PL/SQLはキャッシュされたオープン・カーソルを通知なしに閉じ始めます。 PL/SQLがオープン・カーソル・スロットを大量に消費することはなく、単純に現在使用されていないオープン・カーソル・スロットを透過的に使用します。 次に説明する、通常のカーソルにおけるパフォーマンス関連のメリットも、使いやすさに関わるものです。 通常のカーソルでは、一度に100行の暗黙的な配列フェッチが可能である一方、参照カーソルでは明示的な配列フェッチを使用する必要があります。 言いかえれば、通常のカーソルではコード量を大幅に削減して、より効率的にデータを取得できます。 たとえば、ALL_OBJECTSビューを表Tにコピーし、トレースを有効にしてリスト3のコードを実行した場合、リスト3のTKPROFレポートのようなレポートが表示されます。 コード・リスト3：通常のカーソルと参照カーソルのパフォーマンスを比較したレポート
SQL> declare
2 l_rec t%rowtype;
3 l_cursor sys_refcursor;
4 begin
5 for x in
6 ( select * from t regular )
7 loop
8 null;
9 end loop;
10
11 open l_cursor
12 for
13 select *
14 from t refcur;
15
16 loop
17 fetch l_cursor
18 into l_rec;
19 exit when
20 l_cursor%notfound;
21 end loop;
22 close l_cursor;
23 end;
24 /
PL/SQL procedure successfully completed.
TKPROF report
SELECT * FROM T REGULAR
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 722 0.23 0.23 0 1748 0 72198
————————— ——————— ————— ———————— ———————— ———————— ———————— ——————
total 724 0.23 0.23 0 1748 0 72198
SELECT * FROM T REFCUR
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 72199 0.40 0.42 0 72203 0 72198
————————— ——————— ————— ———————— ———————— ———————— ———————— ——————
total 72201 0.40 0.42 0 72203 0 72198
通常のカーソルのフェッチ回数は722であり、これは全体の行数の約100分の1です。 PL/SQLでは、暗黙的な通常のカーソルの場合、100行の暗黙的配列フェッチが使用されます。 参照カーソルの場合、フェッチ数は行数に1を足した値です（最終行がフェッチされたことを確認するために、最終行の後にもう1度フェッチを実行する必要があります）。 これは問合せのCPU利用率に大きく影響します。また、この例のように、実行されるI/Oの回数（TKPROFレポートのquery列の値）にも影響する場合があります （I/Oが削減される理由について興味がある場合は、bit.ly/ww0Wklをご覧ください。例を使用してその理由を説明しています）。 最後に、通常のカーソルはプロシージャまたは関数の外部で、グローバル・パッケージ変数として定義できるという点について考察します。 参照カーソルではそれはできず、PL/SQLコードの特定ブロックのスコープでローカルに定義する必要があります。 この点については、どちらのカーソルにとっても、メリットでもデメリットでもないと思います。 そもそも、私はグローバル変数があまり好きではなく、一般的には不適切な慣行だと考えています。そのため、個人的には、通常のカーソルをグローバルにすることができるという利点を利用することはありません。 このカーソルの違いの意味は、次のような短いコードで示すことができます。
SQL> create or replace package my_pkg
2 as
3 cursor global_cursor is
select * from dual;
4 end;
5 /
Package created.
SQL> create or replace package my_pkg
2 as
3 global_cursor sys_refcursor;
4 end;
5 /
Warning: Package created with
compilation errors.
SQL> show err
Errors for PACKAGE MY_PKG:
LINE/COL ERROR
———————— ————————————————————————————
3/16 PL/SQL: Declaration ignored
3/16 PLS-00994: Cursor Variables
cannot be declared as part
of a package
ここでは、プロシージャや関数の外部で定義された通常のカーソルを含むパッケージはコンパイルに成功していますが、同じように定義された参照カーソルを含むパッケージはコンパイルに失敗しています。 最適な順序表の内部に列を作成するための最適な順序はありますか。 私の店舗のDBAは、VARCHAR2列を最後に配置するという標準を主張しており、NULLとなる可能性の大きさは考慮していません。 この順序に実際のメリットはあるのでしょうか。 まずは、背景知識について少し説明しましょう。 データベース・ブロック内の行は、次のように保存されます。 [NULLフラグ][長さ][データ][NULLフラグ][長さ][データ]. . . . 表の3つ目の列に到達するためには、データベースが最初の2つの列を解析する必要があります（ポインタではありません。データベースでは行を1バイトずつ読み取る必要があります）。数値、日付など、どのような種類の列であっても同様です。 列では、長さフィールドの後にデータが保存されるため、次の列に到達するためには、両方を解析しなければなりません。 そのため、一般的に、最適なパフォーマンスを得るには、もっとも頻繁にアクセスされる列を最初に配置します。 ただし、NULLが0バイトであるため、NULLになる可能性のもっとも高い列を最後に配置するとよいという意見もあります。 N個目の列が見つかる前に行の末尾に到達した場合は、その列がNULLであるかデフォルトの列の値（Oracle Database 11gの高速追加機能によって表に追加されたNOT NULL列の場合）であることが分かります。 ただし、すべての場合で主キー列を最初に配置するという慣例もあります。 ただし . . ただしが多すぎますね。 一般的には、これらはそれほど重大な問題にはなりません。 もっとも頻繁にアクセスされる列を最初に配置するという考え方は、回答を得るために何百万もの行をスキャンするようなデータウェアハウスでもっとも重視されるでしょう（必要のない多くの列を繰り返し解析するオーバーヘッドが積み重なります）。 しかし、数十行を処理するようなオンライン・トランザクション処理（OLTP）システムでは、それほど大きな問題とはなりません。 通常にはないような例として、数十の列がほぼ常にNULLとなるケースを考えてみましょう。それらの列を表の最後に配置すれば、表の行数が非常に多い場合、かなりのバイト数を節約できます。しかし、そのためには、表が大規模であって、すべての列が高い頻度でNULLとなる必要があります。 したがって、美しさと設計の観点からもっとも意味のあるような順序で列を配置することをお勧めします。 前述のDBAのルール（VARCHAR2列を最後に配置し、NULLとなる可能性は考慮しない）に関して言えば、これはパフォーマンスや管理性とは何の関係もないでしょう。 多くの場合、パフォーマンスに悪影響を及ぼします。 次のように、ほぼすべてのデータベースのデータが文字列と同様の方法で保存されることを覚えておいてください。
データベースでは、長さバイトを読み取って、その列が行の中で占めるバイト数を把握し、列のデータを読み取り、次の長さバイトに到達するという処理を繰り返す必要があります。 頻繁にアクセスする列を表の最初に配置すれば、アクセスに必要となるCPU負荷を削減できると思うかもしれません。 しかし、そうなるためには、多数の行にアクセスする必要があります。 1,000列を含む表があるとして、最初の列と最後の列に問合せを行い、それぞれのTKPROFレポートを比較してみます。 リスト4は、そのような表を作成して問合せのレポートを表示するコードです。 コード・リスト4：1,000列の表の作成、問合せ、レポート
declare
l_create long := 'create table t ( c1 number';
begin
for i in 2 .. 1000
loop
l_create := l_create || ',c'||i||' number default ' || i;
end loop;
execute immediate l_create || ')';
end;
/
insert into t (c1, c1000 ) select rownum, rownum from all_objects;
exec dbms_stats.gather_table_stats( user, 'T' );
SELECT C1 FROM T
call count cpu elapsed disk query current rows
————————— ——————— ————— ———————— ———————— ———————— ———————— ——————
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 7230 6.41 15.72 414610 420920 0 722790
————————— ——————— ————— ———————— ———————— ———————— ———————— ——————
total 7241 6.41 15.72 414610 420920 0 722790
SELECT C1000 FROM T
call count cpu elapsed disk query current rows
————————— ——————— ————— ———————— ———————— ———————— ———————— ——————
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 7230 8.66 17.93 421260 3304860 0 722790
————————— ——————— ————— ———————— ———————— ———————— ———————— ——————
total 7241 8.66 17.94 421260 3304860 0 722790
この場合、1,000列の解析のほか、連鎖された行断片の追跡もCPUのオーバーヘッドの原因となっています（255列以上を含む行は複数の行断片として保存されます）。 レポートでは、この行連鎖による副作用として、query列の値が増加しています。これは、バッファが処理される回数によるものです。 連鎖された行の追跡を回避するために、リスト4の1,000列を250列に変更した場合の結果は、リスト5のようになります。 コード・リスト5：250列のみを含む表のレポート SELECT C1 FROM T call count cpu elapsed disk query current rows ————————— ——————— ————— ———————— ———————— ———————— ———————— —————— Parse 1 0.00 0.00 0 0 0 0 Execute 10 0.00 0.00 0 0 0 0 Fetch 7230 0.62 0.62 1117 94520 0 722790 ————————— ——————— ————— ———————— ———————— ———————— ———————— —————— total 7241 0.62 0.62 1117 94520 0 722790 SELECT C250 FROM T call count cpu elapsed disk query current rows ————————— ——————— ————— ———————— ———————— ———————— ———————— —————— Parse 1 0.00 0.00 0 0 0 0 Execute 10 0.00 0.00 0 0 0 0 Fetch 7230 0.96 0.97 7 94520 0 722790 ————————— ——————— ————— ———————— ———————— ———————— ———————— —————— total 7241 0.96 0.97 7 94520 0 722790 このレポートで分かるように、列がCREATEリストの後ろに来るほど、その列を取得するためには時間がかかります。 “サイズの大きな列ほどCREATE TABLE文の後ろに配置しなければならない”というルールは意味をなさないようです。 NULLになる可能性のある列を（領域の節約のために）最後に配置すべきだという議論や、あまり頻繁にアクセスされない列を（パフォーマンス向上のために）最後に配置すべきだという議論はありますが、サイズの大きな列を最後に配置すべきだと言っている人は、私の知る限り誰もいません。
Tom KyteはオラクルのServer Technologies部門に籍を置くデータベース・エバンジェリストで、1993年からオラクルに勤務しています。Expert Oracle Database Architecture（Apress、2005年/2010年）、Effective Oracle by Design（Oracle Press、2003年）などの著書があります。 |
Printer View