ORACLEで最速の性能を引き出せるプログラム言語はPLSQL
掲題の通り、ORACLEデータベースを使用するプログラムを作成する場合にほとんどの場合最高の性能を引き出せる言語はPL/SQLとなります。 本ページではなぜPL/SQLが早いのかをアーキテクチャから考察します。
何故PL/SQLは早いのか
理由はSQL処理だけでなくロジックも含めてプログラム全体が単一サーバプロセスに閉じて動作可能な為です。
PL/SQL以外の言語でORACLEを利用するプログラムを動かすとクライアント側プロセス(プログラム言語でコーディング、コンパイルしたクライアントモジュールのプロセス)と、DBサーバ側のサーバプロセスの2つが最低でも起動します。 この構成ではDBに処理を投げるたびにクライアントとサーバプロセス間のプロセス間通信のオーバーヘッドが発生してしまうためどうしてもPLSQLよりも遅くなってしまいます。
クライアントとDBサーバが別個のサーバでありネットワークを介して接続する構成の場合通信というそれなりに時間のかかるオーバーヘッドが発生します。 現状主流となっているTCP/IPでの接続だと環境にもよりますが通信毎におおよそ0.1ms～0.3ms程度(N/W通信だけでなくその他もろもろのオーバーヘッドを含めた時間)劣化します。 つまり100万回SQLを実行した場合PLSQLと比較して100～300秒程度遅くなってしまうということです。
以下の例ではpro*cで10万回dual表にアクセスするプログラムを実行した場合のログとvmstatとなります。 処理時間は15秒で、クライアント側は25%程度、DB側は40%(8coreなので約5%*8)の合計65%程度しかCPUを使えていません。 またコンテキストスイッチ(cs)がクライアントとDBで毎秒1.3万回程度発生してしまっています。
2015年 4月 19日 日曜日 16:50:56 JST 接続成功: scott real 0m14.958s user 0m1.410s sys 0m1.062s 2015年 4月 19日 日曜日 16:51:11 JST --クライアント(1core) 2015/04/19 16:50:55 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------ 2015/04/19 16:50:55 r b swpd free buff cache si so bi bo in cs us sy id wa st 2015/04/19 16:50:55 1 0 0 707056 124904 1011740 0 0 36 15 108 250 2 1 97 1 0 2015/04/19 16:50:56 0 0 0 706776 124904 1011744 0 0 0 0 9 211 1 0 99 0 0 2015/04/19 16:50:57 1 0 0 704688 124904 1011744 0 0 0 0 9782 9977 8 12 80 0 0 2015/04/19 16:50:58 1 0 0 704544 124904 1011744 0 0 0 48 13469 13646 9 15 76 0 0 2015/04/19 16:50:59 1 0 0 704544 124904 1011744 0 0 0 32 13388 13570 10 14 76 0 0 2015/04/19 16:51:00 1 0 0 704544 124920 1011728 0 0 4 196 13368 13558 10 16 74 0 0 2015/04/19 16:51:01 1 0 0 704544 124920 1011748 0 0 0 48 13395 13573 9 14 77 0 0 2015/04/19 16:51:02 1 0 0 704544 124928 1011740 0 0 0 12 13467 13660 9 14 77 0 0 2015/04/19 16:51:03 1 0 0 704544 124928 1011748 0 0 0 0 13457 13630 10 13 77 0 0 2015/04/19 16:51:04 1 0 0 704544 124928 1011748 0 0 0 48 13617 13800 10 16 74 0 0 2015/04/19 16:51:05 1 0 0 704544 124936 1011740 0 0 0 12 13414 13596 9 13 78 0 0 2015/04/19 16:51:06 1 0 0 704544 124944 1011748 0 0 0 72 13437 13617 10 15 75 0 0 2015/04/19 16:51:07 1 0 0 704544 124944 1011748 0 0 0 48 13452 13632 9 16 75 0 0 2015/04/19 16:51:08 1 0 0 704544 124944 1011748 0 0 0 8 13473 13648 11 13 76 0 0 2015/04/19 16:51:09 1 0 0 704544 124952 1011740 0 0 0 12 13459 13634 9 16 75 0 0 2015/04/19 16:51:10 1 0 0 704420 124952 1011748 0 0 0 48 13455 13634 10 14 75 1 0 2015/04/19 16:51:11 2 0 0 704420 124960 1011748 0 0 0 52 13462 13641 9 14 77 0 0 --DB(8core) 2015/04/20 01:49:57 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu----- 2015/04/20 01:49:57 r b swpd free buff cache si so bi bo in cs us sy id wa st 2015/04/20 01:49:57 1 0 0 22927488 87716 9109384 0 0 4 7 68 87 0 0 100 0 0 2015/04/20 01:49:58 0 0 0 22927720 87716 9109384 0 0 0 0 674 555 0 0 100 0 0 2015/04/20 01:49:59 1 0 0 22925084 87724 9109376 0 0 0 16 1956 2024 1 1 99 0 0 2015/04/20 01:50:00 0 0 0 22924960 87724 9109384 0 0 0 32 11320 14079 3 2 95 0 0 2015/04/20 01:50:01 0 0 0 22924596 87732 9109376 0 0 0 40 11777 14316 3 2 94 0 0 2015/04/20 01:50:02 1 0 0 22923860 87732 9109388 0 0 0 0 11321 14073 3 2 95 0 0 2015/04/20 01:50:03 0 0 0 22923984 87732 9109388 0 0 0 32 11314 14099 3 2 95 0 0 2015/04/20 01:50:04 3 0 0 22924108 87732 9109388 0 0 0 0 11584 14193 3 2 95 0 0 2015/04/20 01:50:05 1 0 0 22923488 87740 9109380 0 0 0 28 11447 14165 3 2 95 0 0 2015/04/20 01:50:06 2 0 0 22923860 87740 9109380 0 0 0 32 11394 14115 3 2 95 0 0 2015/04/20 01:50:07 1 0 0 22923860 87740 9109388 0 0 4 8 11585 14360 3 2 95 0 0 2015/04/20 01:50:08 2 0 0 22923992 87740 9109392 0 0 0 0 11393 14098 3 2 95 0 0 2015/04/20 01:50:09 0 0 0 22923868 87740 9109392 0 0 0 32 11441 14152 3 2 95 0 0 2015/04/20 01:50:10 1 0 0 22924116 87740 9109392 0 0 0 80 11437 14222 3 2 95 0 0 2015/04/20 01:50:11 1 0 0 22924116 87748 9109384 0 0 0 16 11490 14195 3 2 95 0 0 2015/04/20 01:50:12 0 0 0 22923744 87748 9109384 0 0 0 48 11365 14162 3 2 95 0 0 2015/04/20 01:50:13 2 0 0 22924116 87748 9109392 0 0 0 4 11491 14208 3 2 95 0 0 2015/04/20 01:50:14 0 0 0 22926348 87748 9109392 0 0 4 4 9115 11168 2 2 96 0 0 2015/04/20 01:50:15 1 0 0 22926604 87748 9109396 0 0 0 32 318 401 0 0 100 0 0
ネットワークを介さない構成(DBサーバ上でクライアントを起動)であれば上記のネットワークのオーバーヘッドはなくなり早くなりますが、 プロセス間でのデータ通信は発生するためこれでもPLSQLと同等の性能は得られません。 理由としてはクライアントとサーバプロセスのデータのやり取りが発生するという以外に、 DBに処理を投げるたびにクライアントとサーバプロセスでコンテキストスイッチが発生してしまう為です。
以下の例では上記と同じプログラムをDBサーバ(1core)上で動作させた場合のログとなります。 ネットワークのオーバーヘッドがなくなったことで処理時間が6秒と半分以下となり、CPUも100%使えています。 (2core以上あれば100%＋アルファ程度までCPU使用率が計上されるのでこれよりもう少し早くなる) またコンテキストスイッチ(cs)が毎秒3万回程度発生してしまっています。
2015年 4月 19日 日曜日 21:35:57 JST 接続成功: scott real 0m6.772s user 0m1.236s sys 0m0.527s 2015年 4月 19日 日曜日 21:36:03 JST --クライアント兼DB 2015/04/19 21:35:54 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------ 2015/04/19 21:35:54 r b swpd free buff cache si so bi bo in cs us sy id wa st 2015/04/19 21:35:54 1 0 0 596820 163076 1083700 0 0 21 22 68 240 1 0 98 1 0 2015/04/19 21:35:55 0 0 0 596820 163076 1083708 0 0 0 48 15 224 0 1 99 0 0 2015/04/19 21:35:56 0 0 0 596820 163084 1083708 0 0 0 60 11 211 1 0 99 0 0 2015/04/19 21:35:57 1 0 0 593472 163092 1083700 0 0 0 72 26 16622 38 21 40 0 0 2015/04/19 21:35:58 1 0 0 593348 163092 1083708 0 0 0 48 5 29863 64 36 0 0 0 2015/04/19 21:35:59 1 0 0 593348 163096 1083704 0 0 0 68 18 30029 67 33 0 0 0 2015/04/19 21:36:00 1 0 0 593224 163096 1083708 0 0 0 0 2 29971 65 35 0 0 0 2015/04/19 21:36:01 1 0 0 593224 163104 1083708 0 0 0 132 7 29970 72 28 0 0 0 2015/04/19 21:36:02 1 0 0 593224 163120 1083692 0 0 0 36 6 30005 65 35 0 0 0 2015/04/19 21:36:03 1 0 0 593224 163120 1083708 0 0 0 0 2 29947 68 32 0 0 0 2015/04/19 21:36:04 0 0 0 596572 163120 1083708 0 0 0 88 22 5224 12 6 82 0 0
最後にPLSQLでほぼ同じ処理を実行した場合のログが以下です。 処理時間は3秒以下とさらに速くなりました。 また、コンテキストスイッチ(cs)が上記構成のように増加していないのでCPUの切り替えがほとんど発生していないことがわかります。
2015年 4月 19日 日曜日 21:40:40 JST SQL*Plus: Release 11.1.0.7.0 - Production on Sun Apr 19 21:40:40 2015 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options PL/SQL procedure successfully completed. Elapsed: 00:00:02.32 Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options real 0m2.385s user 0m0.015s sys 0m0.013s 2015年 4月 19日 日曜日 21:40:43 JST 2015/04/19 21:40:36 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------ 2015/04/19 21:40:36 r b swpd free buff cache si so bi bo in cs us sy id wa st 2015/04/19 21:40:36 1 0 0 592924 163916 1083960 0 0 21 22 67 245 1 0 98 1 0 2015/04/19 21:40:37 0 0 0 592916 163916 1083960 0 0 0 0 9 201 0 0 100 0 0 2015/04/19 21:40:38 0 0 0 592916 163916 1083960 0 0 0 48 5 208 1 0 99 0 0 2015/04/19 21:40:39 0 0 0 592916 163916 1083960 0 0 0 0 7 206 0 0 100 0 0 2015/04/19 21:40:40 0 0 0 592916 163920 1083960 0 0 0 12 7 205 0 0 100 0 0 2015/04/19 21:40:41 1 0 0 589100 163928 1083960 0 0 4 256 41 306 24 9 67 0 0 2015/04/19 21:40:42 1 0 0 588948 163932 1083964 0 0 0 92 4 213 76 24 0 0 0 2015/04/19 21:40:43 1 0 0 588948 163932 1083964 0 0 0 0 2 196 79 21 0 0 0 2015/04/19 21:40:44 0 0 0 592676 163932 1083964 0 0 0 48 18 226 5 3 92 0 0 2015/04/19 21:40:45 0 0 0 592676 163932 1083964 0 0 0 0 2 194 0 0 100 0 0
PL/SQLの欠点
PLSQLはORACLEのDB処理に最適化された言語ですが、他のプログラム言語と比較すると以下のような欠点があります。
- シングルスレッドである(マルチスレッド不可)為1サーバプロセスはCPUを1core分までしか使用することができない。 (パラレル処理を使うと複数プロセスで処理可能なので1core分以上CPUを使うことができるが、エンタープライズエディションの機能であり、一回当たりの処理時間が短いSQLを多数実行するようなプログラムには不向き)
- 処理が終わるまで標準出力が戻らない(dbms_outputによる出力をリアルタイムに出力できない)
本ページで利用したプログラムとコンパイル、テスト手順
s1.pc(Pro*Cで10万回dual表アクセスする)
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
/* VARCHAR型変数のバッファサイズ. */
#define UNAME_LEN 20
#define PWD_LEN 40
#define TNSNAME_LEN 40
/* プリコンパイラオプションでMODE=ORACLEの場合は以下のようにdeclareセクションが不要になる。 */
VARCHAR username[UNAME_LEN]; /* VARCHAR型はプリコンパイラによってarrとlenをメンバに持つ構造体に変換される */
varchar password[PWD_LEN]; /* 小文字でvarcharとしてもよい */
varchar tnsname[TNSNAME_LEN];
/* 変数 */
int i;
int dummy; //フェッチデータ格納用
short dummy_ind; //標識変数
//「#include <sqlca.h>」でもよい
EXEC SQL INCLUDE SQLCA;
////////////////////////////////////////////////////////////////////////////////////////////////////
// エラー発生時に呼ばれる関数。エラーメッセージを標準出力に吐き終了する
////////////////////////////////////////////////////////////////////////////////////////////////////
void sql_error(msg)
char *msg;
{
char err_msg[128];
size_t buf_len, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\n%s\n", msg);
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s\n", msg_len, err_msg);
EXEC SQL ROLLBACK RELEASE;
exit(EXIT_FAILURE);
}
////////////////////////////////////////////////////////////////////////////////////////////////////
// メイン関数
// 10万回dual表のセレクトを繰り返す。
////////////////////////////////////////////////////////////////////////////////////////////////////
int main()
{
// ORACLEへの接続
// ユーザ名の設定。VARCHAR構造体メンバであるarrにデータを、lenに文字列長を設定する
strncpy((char *) username.arr, "scott", UNAME_LEN);
username.len = (unsigned short) strlen((char *) username.arr);
// パスワード設定
strncpy((char *) password.arr, "tiger", PWD_LEN);
password.len = (unsigned short) strlen((char *) password.arr);
// 接続識別子設定
strncpy((char *) tnsname.arr, "ORCL112", TNSNAME_LEN);
tnsname.len = (unsigned short) strlen((char *) tnsname.arr);
//エラー発生時にsql_error関数にジャンプする
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");
// ORACLEへの接続。ローカルDBに接続する場合はUSING句は不要
EXEC SQL CONNECT :username IDENTIFIED BY :password USING :tnsname;
printf("\n接続成功: %s\n", username.arr);
//10万回ループ
for (i = 1; i <= 100000; i++)
{
//レコードが存在しなかった場合notfoundにジャンプする
EXEC SQL WHENEVER NOT FOUND GOTO notfound;
//SELECTの実行。INTO句を使った記述は単一レコードが戻るクエリに使用する。
//フェッチしたデータは「:dummy」に設定される。
//「:dummy_ind」は標識変数。標識変数を使うことでFETCHデータが
//正常であるか(NULLや切り捨てが発生していないか)判断できる。
EXEC SQL
SELECT 1
INTO :dummy:dummy_ind
FROM dual;
// 標識変数(dummy_ind)でFETCHしたデータがNULLであるか判断する
if (dummy_ind == -1)
printf("NULL\n");
else
//printf("%d\n", dummy);
continue;
notfound:
printf("\n データが見つかりませんでした.\n");
} /* end for */
// 切断
EXEC SQL ROLLBACK WORK RELEASE;
exit(EXIT_SUCCESS);
}
s1.pcのコンパイル(linux x86-64のORACLE 11gR1でコンパイル)
make -f /app/oracle/product/11.1.0/db_1/precomp/demo/proc/demo_proc.mk OBJS=s1.o EXE=s1 build 上記makeコマンドを実行すると以下が実行される。 #プリコンパイル proc iname=s1 include=. include=/app/oracle/product/11.1.0/db_1/precomp/public include=/app/oracle/product/11.1.0/db_1/rdbms/public include=/app/oracle/product/11.1.0/db_1/rdbms/demo \ include=/app/oracle/product/11.1.0/db_1/plsql/public include=/app/oracle/product/11.1.0/db_1/network/public #コンパイル /usr/bin/gcc -O2 -fPIC -DPRECOMP -I. \ -I/app/oracle/product/11.1.0/db_1/precomp/public \ -I/app/oracle/product/11.1.0/db_1/rdbms/public \ -I/app/oracle/product/11.1.0/db_1/rdbms/demo \ -I/app/oracle/product/11.1.0/db_1/plsql/public \ -I/app/oracle/product/11.1.0/db_1/network/public \ -DLINUX -D_GNU_SOURCE -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -DSLTS_ENABLE -DSLMXMX_ENABLE -D_REENTRANT -DNS_THREADS \ -c s1.c #リンク /usr/bin/gcc -o s1 s1.o -L/app/oracle/product/11.1.0/db_1/lib/ -lclntsh `cat /app/oracle/product/11.1.0/db_1/lib/ldflags` `cat /app/oracle/product/11.1.0/db_1/lib/sysliblist` -lmPL/SQL無名プロシージャ
set timing on
declare
wk_dummy number;
begin
for i in 1..100000 loop
select 1 into wk_dummy from dual;
end loop;
end;
/
テスト手順
date;time ./s1;date ※vmstatの取得方法はvmstatの出力にタイムスタンプを並べて表示させる方法を参照
折りたたみ
展開