Query Performance and Prefetching

8.1 Prefetching Overview

This section shows how the performance of fetching query rows can be tuned in PHP.

Prefetching is the way that PHP OCI8 reduces network roundtrips to the database when fetching query results. By retrieving batches of rows, there is better database and network efficiency.

Prefetching is enabled by default in PHP OCI8. When the first row is initially retrieved from the database, up to the configured limit (100 by default) extra rows up will be returned and stored in an internal buffer local to the PHP process. Any of the PHP OCI8 oci_fetch_* functions called in a script will internally use data from that buffer until it is exhausted, at which point another round trip to the database occurs and a further batch of rows is returned. The way the oci_fetch_* functions return data to the caller does not change regardless of the prefetch value in effect.

The default prefetch value can be set with oci8.default_prefetch in the php.ini configuration file, or it can be set at run time with oci_set_prefetch().

So far the AnyCo application has used oci_fetch_all(). For a change, this chapter will show the other commonly used function, oci_fetch_array(). When this is called in a loop, it iterates through all rows in the query result set. For bigger data sets, fetching one row at a time prevents a large amount of memory being needed to hold the whole result set.

The action and benefits of prefetching would not be changed if oci_fetch_all() was used. Prefetching is handled in the Oracle client libraries at a layer below PHP.

8.2 Creating the Employee Report Page

Create a new PHP file ac_report.php that generates a report of all employees and the equipment issued to them. The file initially looks like:

<?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
 
?>

In the Functions section add the printcontent() function:

/**
 * 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
}

The structure is basically similar to the layout shown in previous chapters.

The $db->setPrefetch() call is used to set the prefetch value. The microtime() calls are used to show how long the report took to generate.

A new Db::fetchRow() method is used to get one row at a time. It is called in a loop after the query has been run.

Edit ac_db.inc.php and add the setPrefetch() and fetchRow() methods to the Db class:

    /**
     * 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);
    }

The OCI_ASSOC flag tells PHP to return the results in an associative array, using the column names as the array keys. The OCI_RETURN_NULLS flag tells PHP to return an array entry for null data values. The value will be an empty string. This ensures that the array for each row has the same number of entries.

8.3 Running the Equipment Report

Save all the files and run the Application as Administrator. From the left hand navigation menu select Equipment Report. It shows all employees and the equipment they have been issued.

At the bottom is the amount of time taken to generate the query output. For this amount of data and because PHP and the database are not separated by a network, the time will be small:

To show the effect of turning off prefetching, edit ac_report.php and change the prefetch setting to 0:

  $db->setPrefetch(0);

This means that each row of data that PHP OCI8 gets from the Oracle Client libraries initiates a roundtrip request to the database server. No extra rows are prefetched.

Re-run the report. The elapsed time should be longer.

For a small system like this there might be some test variability and the values may be too small to be reliable. Re-run several times or change the query to return more rows if this is the case.

8.4 Prefetching with a REF CURSOR

Prefetching can also be used when fetching records with a REF CURSOR. To make the REF CURSOR prefetch value changeable in the Db class, edit ac_db.inc.php and add the following lines before the REF CURSOR execution in Db::refcurExecFetchAll():

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

This prefetch size is set on the REF CURSOR, not the top level statement. The function will look as follows:

    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);
    }

With your own applications, testing will show the optimal prefetch size for your queries. There is no benefit in using too large a value. Conversely, because Oracle dynamically allocates space, there is little to be gained by making the value too small.

It is unlikely that you want to turn pre-fetching completely off. The only case would be in PHP code that gets a REF CURSOR, fetches some data from it, and then passes the cursor back to a PL/SQL procedure, which fetches the remaining data. If prefetching occurred when PHP fetches records from the REF CURSOR, but those prefetched rows were not returned to the script via an oci_fetch_* call, those rows would be "lost" and would not be available to the second PL/SQL procedure.

Note:

PHP must be linked with Oracle Database 12cR1 libraries for prefetching from REF CURSOR to work. When using earlier versions each requested REF CURSOR row required a roundtrip to the database, reducing performance of the system.