津島博士のパフォーマンス講座 
第62回 Oracle Database 12cR2のSQL*Plusについて

■津島博士による解説が動画でも! セミナー動画公開中です。
津島博士のパフォーマンス講座「パフォーマンス問題はなぜ起きるのか」  【WMV】 【MP4】 【PDF

皆さんこんにちは、今年の夏も猛暑かと思っていたら、雨が多く逆に涼しい感じですね。9月になっても例年のような厳しい残暑もなく、このまま秋になると少し寂しい気がしますね。
今回は、Oracle Database 12cR2(Oracle12cR2)で追加されたSQL*Plusのパフォーマンス向上に関係する機能について説明します。後半に、Oracle12cR2で追加された便利な機能についても説明していますので、参考にしてください。

1. パフォーマンス向上の機能
SQL*Plusは、簡単なSQLのテストなどでよく使用しますが、第41回で説明したように大量データを検索する場合、CPUオーバーヘッドなどにより思うように実行することができません。そのため、それを改善する機能がOracle12cR2からいくつか追加されているので、そのような機能の以下について説明します(プリフェッチ・サイズと文キャッシュ・サイズは、OCI(Oracle Call Interface)やJDBCで可能だったことを、SQL*Plusでも簡単に使用できるようにした機能になります)。

  • データ出力の抑止
  • プリフェッチ・サイズ
  • 文キャッシュ・サイズ
  • FASTオプション

(1)データ出力の抑止
まずは、大量データを検索するときにデータ出力を抑止する機能から説明します。
データ出力を抑止したいときには、第41回で説明した'SET AUTOTRACE TRACEONLY STATISTICS'を使用して行っていましたが、これでもオーバーヘッドはそれなりにありました。それを改善するために、Oracle12cR2から'SET FEEDBACK'が拡張されて、問合せ結果の行数だけを出力できるようになりました。'SET FEEDBACK'は、これまで問合せ行数を出力するかどうかを指定するSETシステム変数でしたが、Oracle12cR2からは以下のようにONLYが追加されて、データを出力せずに問合せ行数だけを出力することが可能になります(デフォルトは、これまでと同じく'SET FEEDBACK 6'になります)。このシステム変数は、小文字を省略して'SET FEED ONLY'と指定することも可能です(このようにSETシステム変数やコマンドなどの小文字は、省略できることを意味しています)。

SET FEEDback {6 | n | ON | OFF | ONLY}

効果を確認するために、200万件のデータに対する検索処理を、'SET AUTOTRACE TRACEONLY STATISTICS'と'SET FEEDBACK ONLY'のそれぞれで測定してみました(処理時間の出力は、'SET TIMING ON'を使用しています)。以下のように'SET FEEDBACK ONLY'(右側)の方が短時間で処理できるようになっています(200万件のデータを検索する処理が、22.26秒から10.52秒に改善されています)。

SQL> SET TIMING ON
SQL> SET AUTOTRACE TRACEONLY STATISTICS
SQL> SELECT * FROM t1;

2000000 rows selected.

Elapsed: 00:00:22.26
SQL> SET TIMING ON
SQL> SET FEEDBACK ONLY
SQL> SELECT * FROM t1;

2000000 rows selected.

Elapsed: 00:00:10.52

 

また、'SET AUTOTRACE TRACEONLY STATISTICS'と併用すると、SQL文統計も出力することができます。以下のようにSQL文統計の'bytes sent via SQL*Net to client'(クライアントへ送信したバイト数)が同じ値になっているので、データは問題なくSQL*Plusに転送されています。ただし、EXPLAINを指定しても実行計画は出力されないので注意してください。

SQL> SET AUTOTRACE TRACEONLY STATISTICS
SQL> SELECT * FROM t1;

2000000 rows selected.

Elapsed: 00:00:22.26

Statistics
-----------------------------------------------------
…
   52237052  bytes sent via SQL*Net to client
    1467271  bytes received via SQL*Net from client
     133335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    2000000  rows processed

SQL> SET AUTOTRACE TRACEONLY STATISTICS
SQL> SET FEEDBACK ONLY
SQL> SELECT * FROM t1;

2000000 rows selected.

Elapsed: 00:00:10.52

Statistics
-----------------------------------------------------
…
   52237052  bytes sent via SQL*Net to client
    1467271  bytes received via SQL*Net from client
     133335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    2000000  rows processed

 

Oracle12cR2からは、大量データを検索するときのオーバーヘッドが少ない、こちらの方を使用するようにしてください。ただし、第41回でも説明したように、大量データを検索する処理は、SQL*Plusやネットワークの負荷が大きく、正確なデータベースの性能は測定できないことを忘れないでください(あくまでもオーバーヘッドが少なくなったというレベルです)。

(2)プリフェッチ・サイズ
次に、データのプリフェッチ・サイズについて説明します。
これまで大量の行数を検索するときには、ARRAYSIZEを使用してチューニングを行っていましたが、それを拡張する機能として、データベースから一度にプリフェッチするサイズを、以下のROWPREFETCH(行数)とLOBPREFETCH(LOBデータ量)で指定できるようになりました(デフォルトは、ROWPREFETCHが1、LOBPREFETCHが0で、どちらも動作しません)。最大値は、2Gバイトになります(ROWPREFETCHは、データ量が2Gバイトを超えないようにする必要があります)。

SET ROWPREFETCH {1 | n}
SET LOBPREFETCH {0 | n}

LOBPREFETCHは分かりやすいですが、ROWPREFETCHは分かりづらいので、ARRAYSIZEとの違いも含めて説明します。ROWPREFETCHは、これまで1で変更できなかった値のため、最初のFETCHの行数が常に1行になっていました。以下は、デフォルト(ARRAYSIZE=15、ROWPREFETCH=1)のときのSQLトレースの一部(1回目と2回目のFETCHの部分)になります。これからFETCHで処理された行数(rの値)を見ると、最初のFETCHの行数が1になっているのが分かります。そのため、1行だけのデータでも、2回のFETCH(r=1とr=0)を行う必要がありましたが、これがROWPREFETCH=2にすると1回のFETCHで行うことができるようになります。

FETCH #140221255023848:c=1000,e=1288,p=0,cr=9,cu=3,mis=0,r=1,dep=0,og=1,plh=3617692013,tim=25200697196703
WAIT #140221255023848: nam='SQL*Net message from client' ela= 390 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=25200697197154
WAIT #140221255023848: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=25200697197232
FETCH #140221255023848:c=0,e=65,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=3617692013,tim=25200697197271

以下は、ARRAYSIZE=100、ROWPREFETCH=2のときになります(今度は、最初のFETCHの行数が2になっています)。

FETCH #140705822260456:c=1000,e=1195,p=0,cr=9,cu=3,mis=0,r=2,dep=0,og=1,plh=3617692013,tim=25200546802985
WAIT #140705822260456: nam='SQL*Net message from client' ela= 400 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=25200546803461
WAIT #140705822260456: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=25200546803536
FETCH #140705822260456:c=0,e=102,p=0,cr=0,cu=0,mis=0,r=100,dep=0,og=1,plh=3617692013,tim=25200546803612

また、このように2回目は、ARRAYSIZEになりますが、ROWPREFETCHがARRAYSIZEより大きいまたは等しいと、2回目はARRAYSIZE+ROWPREFETCHになります(以下は、ARRAYSIZE=100、ROWPREFETCH=200のときです)。

FETCH #140252830977256:c=2000,e=1372,p=0,cr=9,cu=3,mis=0,r=200,dep=0,og=1,plh=3617692013,tim=25201133144080
WAIT #140252830977256: nam='SQL*Net message from client' ela= 715 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=25201133144860
WAIT #140252830977256: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=25201133144935
FETCH #140252830977256:c=0,e=196,p=0,cr=0,cu=0,mis=0,r=300,dep=0,og=1,plh=3617692013,tim=25201133145105

これによりラウンドトリップ(送受信されたメッセージ数)を削減することができます。ただし、あまり大きい値にしても効果が変わらない場合があります。

(3)文キャッシュ・サイズ
次に、文キャッシュ・サイズについて説明します。
キャッシュされるSQL文の数を以下のように指定します(デフォルトは、0でキャッシュされません)。最大値は、32767になりますが、初期化パラメータOPEN_CURSORSより多くなると、エラー(ORA-01000: 最大オープン・カーソル数を超えました。)になるので注意してください。

SET STATEMENTCACHE {0 | n}

このキャッシュ・サイズの値を指定すると、繰り返されるSQL文をキャッシュすることが可能になり、これらのSQL文は再度解析(パース・コール)する必要がないため、パフォーマンスを向上することができます。
これまでは、第7回で説明したセッション・カーソル・キャッシュを使用しますが、このときソフト・パースが動作する必要がありました。これが文キャッシュを使用すると、ソフト・パースも行う必要がなくなります(第7回では、違いが分かりやすいように、ソフト・パースの前に実行されると説明しましたが、正確にはライブラリ・キャッシュ内からカーソルを探すオーバーヘッドが削減されますが、それ以外はソフト・パースとして動作します)。
違いが分かるように、文キャッシュを設定した場合としない場合で、V$MYSTATの統計'parse count (total)'(解析コールの合計数)を検索するSQLを3回実行してみました。以下のように、STATEMENTCACHEが0(左側)だと'parse count (total)'がカウントアップされているのに、STATEMENTCACHEを1(右側)にすると'parse count (total)'の値がカウントアップされなくなります。

SQL> SET STATEMENTCACHE 0
SQL> SELECT name, value 
2    FROM v$mystat JOIN v$statname USING(statistic#)
3   WHERE name='parse count (total)';

NAME                                VALUE
------------------------------ ----------
parse count (total)                    14

SQL> SELECT name, value FROM v$mystat … ;

NAME                                VALUE
------------------------------ ----------
parse count (total)                    15

SQL> SELECT name, value FROM v$mystat … ;

NAME                                VALUE
------------------------------ ----------
parse count (total)                    16
SQL> SET STATEMENTCACHE 1
SQL> SELECT name, value
2    FROM v$mystat JOIN v$statname USING(statistic#)
3   WHERE name='parse count (total)';

NAME                                VALUE
------------------------------ ----------
parse count (total)                    14

SQL> SELECT name, value FROM v$mystat … ;

NAME                                VALUE
------------------------------ ----------
parse count (total)                    14

SQL> SELECT name, value FROM v$mystat … ;

NAME                                VALUE
------------------------------ ----------
parse count (total)                    14

 

全ての文キャッシュが使用済みのときは、セッション・カーソル・キャッシュと同じように、最も古いものが上書きされます。そのため、繰り返されるSQLより多い数を指定すると、繰り返してパース・コールされなくすることができます。

(4)FASTオプション
最後に、新しいSQL*Plusコマンドのオプションについて説明します。
SQL*Plusの起動時に、以下のようなFASTオプションが追加されて、パフォーマンスを測定するときに、簡単に行うことができるようになりました。

sqlplus -Fast …

Oracle12cR2から追加されたものも含めて、パフォーマンスに関係するSETシステム変数が多くなり、設定するのが大変になっています。そのため、パフォーマンスを向上するためのデフォルト値が設定されるように、このオプションが追加されました。これを指定すると、パフォーマンスに関係するSETシステム変数(ARRAYSIZE、LOBPREFETCH、PAGESIZE、ROWPREFETCH、STATEMENTCACHE)の値が、パフォーマンスが向上するように変更されます。以下のような値に設定されるので、通常はこの設定で問題ないと思います(ただし、'FEEDBACK ONLY'は設定されないので注意してください)。

sqlplus …
SQL> SHOW ARRAYSIZE LOBPREFETCH PAGESIZE ROWPREFETCH STATEMENTCACHE
arraysize 15
lobprefetch 0
pagesize 14
rowprefetch 1
statementcache is 0

sqlplus -Fast …
SQL> SHOW ARRAYSIZE LOBPREFETCH PAGESIZE ROWPREFETCH STATEMENTCACHE
arraysize 100
lobprefetch 16384
pagesize 50000
rowprefetch 2
statementcache is 20

これも効果を確認するために、先程の'FEEDBACK ONLY'を使用した200万件の検索処理に対して、FASTオプションを使用したとき(右側)としないとき(左側)で実行してみました。以下のようにFASTオプションを使用した方の処理時間が速くなっています(10.52秒が5.23秒に改善されています)。

sqlplus …
SQL> SET FEEDBACK ONLY
SQL> SELECT * FROM t1;

2000000 rows selected.

Elapsed: 00:00:10.52
sqlplus -Fast …
SQL> SET FEEDBACK ONLY
SQL> SELECT * FROM t1;

2000000 rows selected.

Elapsed: 00:00:05.23

 

以下のSQL文統計のように、ラウンドトリップ(SQL*Net roundtrips to/from client)が「(1)データ出力の抑止」のときより減少しています(133335から20001に少なくなっています)。これからも、通常はこれで問題ないことが分かります。

sqlplus -Fast …
SQL> SET AUTOTRACE TRACEONLY STATISTICS
SQL> SET FEEDBACK ONLY
SQL> SELECT * FROM t1;

2000000 rows selected.

Elapsed: 00:00:05.11

Statistics
----------------------------------------------------------
   …
   30590255  bytes sent via SQL*Net to client
     220597  bytes received via SQL*Net from client
      20001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    2000000  rows processed

2. 便利な機能
パフォーマンスには影響しませんが、いくつかの便利な機能が追加または拡張されているので、その機能の中から以下について紹介します。

  • CSV出力
  • コマンド履歴
  • バインド変数の拡張

(1)CSV出力
検索結果をCSV形式で出力するような場合もあるので、そのような処理が簡単に行えるように、以下の'SET MARKUP CSV'が追加になりました。DELIMITERオプションで、セパレータ文字の指定もできます(セパレータ文字のデフォルトは','になります)。

SET MARKup CSV {ON | OFF} [DELIMIter <セパレータ文字> [QUOTE {ON | OFF}]

以下の例は、セパレータ文字をスラッシュ'/'にしたときになります。また、以下のようにQUOTEオプションで、テキストに対する二重引用符の付加を、オン(右側)またはオフ(左側)にすることもできます(デフォルトは、二重引用符ありで出力されます)。

SQL> SET MARKUP CSV ON DELIMITER "/" QUOTE OFF
SQL> SELECT * FROM t1 WHERE id < 50;

ID/COL1/COL2
1/1/********************
2/2/********************
…
SQL> SET MARKUP CSV ON DELIMITER "/" QUOTE ON
SQL> SELECT * FROM t1 WHERE id < 50;

"ID"/"COL1"/"COL2"
1/1/"********************"
2/2/"********************"
…

 

このSETシステム変数を設定しても、次のCOLUMNコマンドは有効なままとなります。

  • COLUMN FORMAT(列の表示形式)
  • COLUMN HEADING(列ヘッダーのテキスト)
  • COLUMN NULL(NULL値として表示するテキスト)

(2)コマンド履歴
コマンドの履歴管理が行えるように、以下のSETシステム変数'SET HISTORY'とHISTORYコマンドが追加になりました。そのため、SQLを再実行するときなどは簡単に行うことができるようになります。

SET HISTory {ON | OFF | n} 
HISTory [n RUN | n EDIT | n DELete | CLEAR | LIST]

'SET HISTORY'は、履歴管理を行うかどうか指定します(デフォルトは、OFFで行われません)。ONのときは、100件のコマンドが格納されますが、格納するコマンド数(n)を指定することもできます(最大100000個のコマンドを格納できます)。このときすべての件数のコマンドを格納済みのときは、古いコマンド履歴から消去されます。
HISTORYコマンドは、コマンド履歴に対する処理になり、以下の5つの指定が可能です(デフォルトはLISTです)。

  • RUN(コマンド履歴リストのn番目のエントリを実行します)
  • EDIT(コマンド履歴リストのn番目のエントリを編集します)
  • DELETE(コマンド履歴リストのn番目のエントリを削除します)
  • CLEAR(コマンド履歴リストのすべてのエントリを消去します)
  • LIST(コマンド履歴リストのすべてのエントリをリストします)

例えば、以下のようにコマンド履歴のエントリ・リストを出力して、目的のコマンドの番号を指定して実行します。

SQL> SET HISTORY ON
SQL> HISTORY
  1  SELECT * FROM t1 WHERE id < 20;
  2  SELECT * FROM t1 WHERE id < 10;
…
SQL> HISTORY 2 RUN

(3)バインド変数の拡張
バインド変数は、VARIABLEコマンドで定義し、PL/SQLプログラムで値を代入する必要がありましたが、以下の右側のようにOracle12cR2からは初期値を設定できるようになり、一度で行うことができるようになりました。バインド変数を使用するスクリプトが見やすくなると思います。

-- 12.1以前
SQL> VARIABLE text char
SQL> exec :text:='X'
-- 12.2
SQL> VARIABLE text char='X'

 

 

 

3. おわりに
今回はOracle Database 12cR2のDatabase In-Memoryについて説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします。
それでは、次回まで、ごきげんよう。