Because the cost of creating a new shadow process is relatively large, we should try to avoid it when necessary. The easiest way to accomplish this is by using persistent connections. PHP is a stateless language by design. This means that by default any information created (or resource instantiated) during a request will be cleaned up and destroyed at the end of the request. For Oracle client connections, we want to avoid this behavior.

To enable connections that persist from one request to the next, you can use one of the two following connect variants:

OCIPLogin($username, $password [, $tnsname])

or

OCINLogin($username, $password [, $tnsname])

Both of these functions create persistent server connections, although OCINLogin() will create a new session handle for every request. If your application uses transactions and you want to separate simultaneous transactions into multiple sessions, use OCINLogin().

A side effect of using persistent connections is that you are more prone to process-starvation. When running a single Apache Web server (with a default maximum of 256 child processes) against a dedicated Oracle database, you are likely to never run into any problems. If you grow that to 4 Web servers, though, each running 256 children with persistent Oracle connections, you will now be making 1,024 connections to your Oracle database and soon will be running up against the Oracle instance's resource limits.

In your Oracle instance's configuration file (init.ora), there are two tunable parameters:

sessions = NNNN and processes = NNNN.

These control the maximum number of sessions and processes that the instance will support. If you need to support 1,024 simultaneous connections, sessions will need to be at least 1,024 (since OCINLogin() connects and certain recursive queries can require multiple sessions per connection), and processes will need to be higher still (since we need to account for the Oracle background processes as well). Unfortunately, setting these processes arbitrarily high is not an option. Oracle processes consume a non-trivial amount of private memory (2 to 3MB per process on most systems). Individually these are small, but when taken as a group and combined with the shared memory required for the server system global area (SGA), this will quickly cause you to bump your head against the database server's physical memory limits.

Users coming from a MySQL environment might be tempted to eschew persistent connections altogether (as is recommended for MySQL environments). The latch and file contention incurred by an Oracle shadow process startup makes using non-persistent connections extremely inefficient. So what are the solutions?

• Determine how many simultaneous sessions our database can sustain and set its limits accordingly. There are articles that detail how to do this, but it is mostly an arithmetic exercise: Take the total amount of physical memory in the system and subtract the memory used by the kernel, any support programs, and the Oracle background processes. Next, subtract all the memory configured as shared memory for Oracle (shared pool and buffer cache). What we have left over can be used for shadow processes. Divide that by the average amount of private process memory used by one of your shadow processes (you should measure this yourself, since it changes depending on the nature of the queries you run), and we have the number of processes sustainable.
• Configure our Web servers so that they can never make more connections than your process settings allow. This is achieved by setting each Web server's MaxChildren tunable low enough so that all the Web servers together will have fewer children than sustainable Oracle connections. This means no longer supporting 256 children per Apache instance.
• Restructure our application so that it won't miss the extra children. We'll come back to that later in the article.

How important is this? At one employer's, we experienced consistent latching issues even though we had persistent connections enabled. As anyone who has experienced serious latch contention can attest, it is a crippling problem where the server becomes largely unresponsive as it spends an excessive amount of time doing locking operations. During our investigation, we found that, while a good number of processes served hundreds of requests before terminating, a large number of processes served only a single request. What had happened was that we had set the Apache setting MaxSpareServers quite low. Some issues with the load-balancing equipment we were using caused "bursty" behavior, where a Web server was hit with a number of simultaneous requests and then left idle for a few seconds. Inside Apache, this caused additional children to be created to serve the high request level; but as soon as it subsided (almost instantly), most of the now-idle children were reaped (terminating the process and closing its Oracle connections). Globally this had a similar effect to running non-persistent processes. Setting MaxSpareServers higher eliminated this problem and eliminated the latch contention.

Executing SQL

The meat of any Oracle client-server relationship is executing queries. There isn't space here to talk about tuning queries—that is a topic that consumes entire books. Instead, we'll focus on how to make your already-tuned queries run as efficiently as possible.

The first step in writing good Oracle application code in PHP is to always use bind SQL. When we write a query like this:

SELECT * FROM USERS WHERE USERNAME = 'george'

Oracle must soft-parse the query to see if it has compiled it before. By default, the value 'george' is taken as a literal, meaning that if we execute this query with a different name ('bob'), Oracle will treat it as an entirely separate query. Oracle keeps a parsed copy of every query it executes in its shared pool, so if you execute this with thousands of names, there will be thousands of distinct copies of this query in your shared pool. On even a mildly active site, this will cause severe memory fragmentation and a proliferation of ORA-4031 errors.

The solution to this is to use bind SQL. Bind SQL allows us to replace the literal values in the WHERE clause with placeholders, as follows:

SELECT * FROM USERS WHERE USERNAME = :NAME

Here the query will have to be completely parsed (hard-parsed) only once; all subsequent parses will be so-called soft-parses, where the engine simply fetches the compiled query from the SGA. Also, only a single-parsed copy will be stored, drastically reducing the memory requirements for frequently executed queries.

Now we can execute this as follows:

<?php
  $db = OCIPLogin('scott', 'tiger', 'testdb');
  $stmt = OCIParse($db, 'SELECT * FROM USERS WHERE USERNAME = :NAME');
  $name = 'george';
  OCIBindByName($stmt, ':NAME', $name, -1);
  OCIExecute($stmt);
?>

In the pre-Oracle8i days, we had to manually bind queries; starting in 8i we can instruct the optimizer to do it for us by setting the init.ora parameter 'cursor_sharing = FORCE'. This setting tells the optimizer to look for literal values that could be bound and to perform the binding manually. As of 9i, we can use the setting 'cursor_sharing = SIMILAR', which instructs the optimizer to peek into the underlying table statistics to see if autobinding the literals is beneficial (it may not be, in the case where a field is highly skewed). While these settings should be enabled (FORCE in 8i, SIMILAR in 9i and above), the act of peeking into the query to analyze potential bindings is expensive for the optimizer, so you should manually bind your queries when possible.

SQLNet, the protocol that the Oracle client-server is carried over, is notoriously chatty. For example, if you execute a query that returns 100 rows, there will be a conversation exchange to parse the query, an exchange for each bind variable, a query to perform the execution, and a query for each row fetched. Each of these exchanges involves a network packet exchange (called a round-trip) between the client and the server. Reducing the number of round-trips can have profound performance impacts.

The first way to tackle this problem is to set up the client's pre-fetch buffer. Pulling a single row over the network is extremely inefficient, so you are much better off pulling them over aggressively and reading them out of a local buffer. The Oracle client library can perform this service automatically, if we set the following for our statement handles:

  $stmt = OCIParse($db, $query);
  OCISetPreFetch($stmt, 1000);
  //  execute and fetch

This instructs the OCI client library to internally buffer 1,000 rows at a time and will give you a positive return on any query that returns more than one row. 1,000 is a rather arbitrary number—the only limiting factors in selecting the pre-fetch buffer size are:

1. The buffer needs to be filled before the execute returns—if having immediate access to the partial result set is important, you will not want the buffer size to be too large.
2. The buffered result set is held in local memory until it is refreshed—if you have very large rows or very large result sets, you should not make the buffer size too large, to prevent the client from having a memory shortage.

Managing Your Interaction with Oracle

You have now seen a few techniques for improving the way you interact with your Oracle database through PHP. Making those sorts of structural changes to your programs is a management hassle, as it usually requires making nontrivial changes to a number of places in your code. The correct way to approach the problem is to encapsulate all your database access code in a wrapper library so that the internals of how queries are prepared and executed can be changed without having to audit your entire code base.

I say "wrapper library" instead of "abstraction layer" because many so-called abstraction layers make a point of hiding from you not only the minutiae of the low-level database calls but the SQL as well. They implement their own database-neutral syntax so that you can effortlessly change your application over to another database. I have three major problems with this philosophy:

1. SQL is a powerful and descriptive language that many developers know. Hiding it from them and making them learn a new, less flexible language is silly.
2. Every major database product has non-standard SQL syntaxes for accomplishing particular tasks. Forsaking these differences restricts your flexibility and removes some of the value of your platform of choice. By considering only the intersection of their supported features, you end up with the lowest common denominator.
3. Switching database vendors is an uncommon occurrence for most people and will involve considerable data migration efforts, regardless of whether your application is database-agnostic or not.

First, you need to set up the rewrite rules inside your httpd.conf. Here is a sample stanza:

RewriteEngine   On
RewriteConf     %{REQUEST_FILENAME}        ^/archive/[0-9]+\.html
RewriteConf     %{REQUEST_FILENAME}        !-f
RewriteRule     ^/archive/([0-9]+)\.html   /generate.php?id=$1

This first enables the rewriting engine and then says that any requested filename that matches the archive pattern (^/archive/[0-9]+\.html) that does not exist (!-f) will be rewritten to go to the generation page with the page identifier passed as a parameter.

The generator page is similarly simple:

<?php
  $id = $_GET['id'];
  $dbh =& new DB_Oracle_TestDB;
  $cursor =& $dbh->execute("SELECT content FROM news WHERE id = $id");
  $result = $cursor->fetch();
  if(!$result) {
    header("HTTP/1.0 404 Not Found");
    exit;
  }
  else {
    echo $result['CONTENT'];
    $outfile = $_SERVER['DOCUMENT_ROOT']."/archive/$id.html";
    if(($fp = fopen($outfile, "w")) === false) {
      exit;
    }
    fwrite($fp, $result['CONTENT']);
    fclose($fp);
  } 
?>

In this example, the generator assumes that the news table's content column contains fully formed content. In reality, you would most likely perform some formatting in the script as well, capturing the output using output buffering.

Conclusion

What you've seen here is just a brief description of techniques for scaling Oracle and PHP. While the examples have hopefully been useful, the key lessons I hope you've taken from this article are:

• Closely manage your Oracle connections to avoid resource exhaustion.
• Speed brings you efficiency, which aids scalability.
• When possible, use caching techniques to avoid database queries completely.