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`  -lm
PL/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の出力にタイムスタンプを並べて表示させる方法を参照
カスタム検索

★ORACLE案件承ります
▼ORACLE掲示板