8 問合せのパフォーマンスとプリフェッチ

この章のトピックは、次のとおりです。

プリフェッチの概要
「Employee Report」ページ
備品レポートの実行
REF CURSORのプリフェッチ

プリフェッチの概要

この項では、問合せ行のフェッチのパフォーマンスをPHPで調整する方法について説明します。

PHP OCI8では問合せ結果をフェッチするときに、プリフェッチという方法でデータベースへのネットワーク・ラウンドトリップを減らすことができます。行をバッチとして取得すると、データベースとネットワークの効率が上がります。

PHP OCI8ではデフォルトでプリフェッチが有効になっています。データベースから最初の行を取得するときに、設定した上限までの行(デフォルトで100)も一緒に返され、PHPプロセスから見てローカルにある内部バッファに格納されます。その後スクリプトでコールされるPHP OCI8 oci_fetch_*関数は、バッファからのデータを内部的に使用し続け、データがすべて消費されると、データベースへのラウンドトリップが新たに開始され、新しい行のバッチが返されます。oci_fetch_*関数がコール元にデータを返す方法は、有効なプリフェッチ値にかかわらず同じです。

デフォルトのプリフェッチ値はphp.ini構成ファイルのoci8.default_prefetchで設定するか、実行時にoci_set_prefetch()で設定できます。

現状のAnyCoアプリケーションでは、oci_fetch_all()を使用しています。この章では、よく使用される別の関数oci_fetch_array()について説明します。これがループでコールされると、問合せ結果セット内のすべての行が反復処理されます。データ・セットが大きい場合は、一度に1行フェッチする方法をとることで、結果セット全体を維持するための大量のメモリーを確保する必要がなくなります。

oci_fetch_all()を使用した場合でも、プリフェッチの動作と利点に変わりはありません。プリフェッチの処理は、PHPの下層にあるOracleクライアント・ライブラリで行われます。

「Employee Report」ページ

すべての従業員と支給備品のレポートを生成する新規のPHPファイルac_report.phpを作成します。ファイルの初期の状態は次のようになります。

<?php
 
/**
 * ac_report.php: Full report of all employees and their equipment
 * @package Report
 */
 
session_start();
require('ac_db.inc.php');
require('ac_equip.inc.php');
 
$sess = new \Equipment\Session;
$sess->getSession();
if (!isset($sess->username) ││ empty($sess->username)
        ││ !$sess->isPrivilegedUser()) {
    header('Location: index.php');
    exit;
}
 
$page = new \Equipment\Page;
$page->printHeader("AnyCo Corp. Equipment Report");
$page->printMenu($sess->username, $sess->isPrivilegedUser());
printcontent($sess);
$page->printFooter();
 
// Functions
 
?>

Functionsセクションにprintcontent()関数を追加します。

/**
 * Print the main body of the page
 *
 * @param Session $sess
 */
function printcontent($sess) {
    echo "<div id='content'>";
    $db = new \Oracle\Db("Equipment", $sess->username);
 
    $sql = "select first_name ││ ' ' ││ last_name as emp_name, equip_name
        from employees left outer join equipment
        on employees.employee_id = equipment.employee_id
        order by emp_name, equip_name";
 
    // Change the prefetch value to compare performance.
    // Zero will be slowest. The system default is 100
    $db->setPrefetch(200);
 
    $time = microtime(true);
    $db->execute($sql, "Equipment Report");
    echo "<table>";
    while (($row = $db->fetchRow()) != false) {
        $empname = htmlspecialchars($row['EMP_NAME'], ENT_NOQUOTES, 'UTF-8');
        $equipname = htmlspecialchars($row['EQUIP_NAME'], ENT_NOQUOTES, 'UTF-8');
        echo "<tr><td>$empname</td><td>$equipname</td></tr>";
    }
    echo "</table>";
    $time = microtime(true) - $time;
    echo "<p>Report generated in " . round($time, 3) . " seconds\n";
    echo "</div>";  // content
}

基本的にこの構造は前の章のレイアウトと同じです。

$db->setPrefetch()コールを使用して、プリフェッチ値を設定します。microtime()コールは、レポートの生成時間を示すものです。

新しいDb::fetchRow()メソッドを使用して、一度に1行ずつ取得しています。これは問合せが実行された後、ループ内でコールされます。

ac_db.inc.phpを編集し、setPrefetch()メソッドとfetchRow()メソッドをDbクラスに追加します。

    /**
     * Set the query prefetch row count to tune performance by reducing the
     * number of round trips to the database.  Zero means there will be no
     * prefetching and will be slowest.  A negative value will use the php.ini
     * default value.  Some queries such as those using LOBS will not have
     * rows prefetched.
     *
     * @param integer $pf The number of rows that queries should prefetch.
     */
    public function setPrefetch($pf) {
        $this->prefetch = $pf;
    }
 
    /**
     * Fetch a row of data.  Call this in a loop after calling Db::execute()
     *
     * @return array An array of data for one row of the query
     */
    public function fetchRow() {
        $row = oci_fetch_array($this->stid, OCI_ASSOC + OCI_RETURN_NULLS);
        return($row);
    }

OCI_ASSOCフラグは、列名を配列キーとする連想配列に結果を返すようPHPに指示しています。OCI_RETURN_NULLSフラグは、nullのデータ値について配列エントリを返すようPHPに指示しています。値は空の文字列になります。これにより、各行の配列に含まれるエントリの数が同じになります。

備品レポートの実行

すべてのファイルを保存し、アプリケーションをAdministratorとして実行します。左側のナビゲーション・メニューから「Equipment Report」を選択します。すべての従業員と支給備品が表示されます。

下部に問合せの出力の生成にかかった時間が表示されています。使用したデータの量と、PHPとデータベースがネットワークで分断されていないという状況から、所要時間は少なくなっています。

プリフェッチをオフにしたときの効果を確認するため、ac_report.phpを編集してプリフェッチ設定を0に変更します。

  $db->setPrefetch(0);

これは、PHP OCI8がOracleクライアント・ライブラリからデータを1行取得するたびに、データベース・サーバーへのラウンドトリップ・リクエストが開始されるという意味です。行が余計にプリフェッチされることはありません。

レポートを再実行します。経過時間が長くなります。

このような小規模のシステムでは、テストによりばらつきが生じる可能性があり、値が小さすぎて信頼性に欠けることもあります。このような場合は、何度か実行するか、多くの行が返されるように問合せを変更します。

REF CURSORのプリフェッチ

プリフェッチはREF CURSORSからレコードをフェッチするするときにも使用できます。Dbクラス内でREF CURSORプリフェッチ値を変更できるようにするには、ac_db.inc.phpを編集し、次の行をDb::refcurExecFetchAll()のREF CURSOR実行の前に追加します。

        if ($this->prefetch >= 0) {
            oci_set_prefetch($rc, $this->prefetch);  // set on the REFCURSOR
        }

このプリフェッチ・サイズは、トップ・レベルの文ではなくREF CURSORで設定します。関数は次のようになります。

    public function refcurExecFetchAll($sql, $action, $rcname, 
    $otherbindvars = array()) {
        $this->stid = oci_parse($this->conn, $sql);
        $rc = oci_new_cursor($this->conn);
        oci_bind_by_name($this->stid, $rcname, $rc, -1, OCI_B_CURSOR);
        foreach ($otherbindvars as $bv) {
            // oci_bind_by_name(resource, bv_name, php_variable, length)
            oci_bind_by_name($this->stid, $bv[0], $bv[1], $bv[2]);
        }
        oci_set_action($this->conn, $action);
        oci_execute($this->stid);
        if ($this->prefetch >= 0) {
            oci_set_prefetch($rc, $this->prefetch);  // set on the REFCURSOR
        }
        oci_execute($rc); // run the ref cursor as if it were a statement id
        oci_fetch_all($rc, $res);
        return($res);
    }

アプリケーションでテストを行うことで、問合せの最適なプリフェッチ・サイズが明らかになります。あまり大きい値を使用しても利点はありません。Oracleでは動的にスペースが割り当てられるため、逆に値を小さくしすぎても得られるものは多くありません。

プリフェッチを完全にオフにしたほうがよい状況はあまりありません。考えられるケースとしては、PHPコードがREF CURSORを取得し、そこから一部のデータをフェッチした後、残りのデータをフェッチするPL/SQLプロシージャにカーソルを戻すような場合です。PHPがREF CURSORからレコードをフェッチするときにプリフェッチが行われたのに、それらのプリフェッチされた行がoci_fetch_*コールをとおしてスクリプトに戻されなかった場合、それらの行は失われ、次のPL/SQLプロシージャで使用できません。

注意:

REF CURSORからプリフェッチを行うには、PHPをOracle Database 11gR2ライブラリにリンクする必要があります。以前のバージョンを使用している場合、リクエストした各REF CURSOR行でデータベースへのラウンドトリップが必要になるため、システムのパフォーマンスが低下します。