The DeployPHP Series, Part 1: Optimizing PHP and Oracle
by John Lim

In this first installment of the DeployPHP Series, get a checklist for developing and deploying powerful, scalable PHP applications on Oracle.

Downloads for this article: Oracle Database 10g Oracle Instant Client Oracle JDeveloper PHP Extension

The open-source language PHP: Hypertext Preprocessor (PHP) powers some of the most popular Web sites in the world, such as Yahoo!, Lufthansa, and Disney Online. This fact is even more remarkable when you consider that PHP does so much with so little.

Imagine that your Web server is a gigantic virtual factory processing HTTP requests. Now imagine your PHP interpreters as factory workers in hard hats handling the requests. Each worker is enclosed in his/her own cell, unable to talk or coordinate with the others. The workers are absent-minded and have no memory of previous transactions; before any work can commence on the incoming HTTP request, they have to recall all session information from a data store. And after finishing a request, workers have to scramble to tuck away all session information back into the store.

Paradoxically, this stateless environment where workers are isolated in cells and have poor memories is extremely scalable. PHP processes that are totally independent of each other and do not maintain state are highly parallelizable. Of course you need to perform data synchronization and session storage, but you can outsource these parts to a specialized external module, typically a relational database such as Oracle. As Rasmus Lerdorf points out in his OTN article "Do You PHP?", "PHP encourages you to push scalability issues to the layers that require it. If you need a shared data store, use a database that supports replication and can scale to the levels you need."

As a PHP developer, you know that the database is probably the most important component with which you will be interacting. In this first installment in the DeployPHP Series, I'll discuss this checklist for developing and deploying powerful and scalable PHP applications on Oracle (I will assume that you have some knowledge of PHP and SQL, but not much of Oracle):

1. Choose the right extension API.
2. Know when to fetch and hint.
3. Be smart about binding.
4. Use a class library.
5. Learn the art of creating optimizer hints and indexes.
6. Steward your data and manage your business logic.
7. Keep in touch with "mission control."

1. Choose the Right Extension API

PHP offers several application-programming interfaces (APIs) for connecting to Oracle—namely, the Oracle, OCI8, and in PHP 5.1, the PDO, extensions. As you will see shortly, PHP offers great functionality but awfully inconsistent naming conventions. Here is some guidance about which extension to deploy:

• Strangely enough, the "Oracle" extension is designed for Oracle7 and should be avoided at all costs. It is obsolete and lacks much functionality such as Large Object (LOB) support.
• PHP Data Objects (PDO) is the new portable database API in the PHP 5.1 release. However, it is still immature.
• On Windows, you can choose to query Oracle via the Open Database Connectivity (ODBC) extension. ODBC has some merits, as it provides built-in connection pooling. But the limited ODBC API means that handling of LOBs and IN OUT parameters in stored procedures can be problematic.
• The OCI8 extension provides the greatest functionality, as it maps very closely to the Oracle Call Interface (OCI). The "8" in OCI8 is misleading; the API can be used with Oracle8/8i, Oracle9i, and Oracle 10g. For best performance, I recommend this API.

2. Know When to Fetch and Hint

As a PHP developer, your most common task is retrieving and displaying data from Oracle. Before you start coding or deploying your PHP applications, you need to know a few important techniques.

First, some theory. When you send your SQL to Oracle, Oracle's parser will examine the SQL to see if it can use an existing compiled query. If there is a match, it will use the compiled query (a soft-parse). If no match occurs, then it will compile the query (a hard-parse), running the optimizer to select the best execution plan for the query.

Pagination. Typically you would break up the records you retrieve into pages and retrieve only the current page. You can do this using nested selects and the special ROWNUM variable. The following code shows you how to retrieve a range of records for pagination. Assume that $currentPage starts with page 0:

$firstRec = $currentPage * $recordsPerPage + 1;
$lastRec = ($currentPage+1) * $recordsPerPage;

$myquery = "SELECT * FROM customer WHERE type='HIGHVAL' ORDER BY name";

$sql = " SELECT * FROM 
     ( SELECT a.*, RowNum as rNum 
FROM ( $myquery ) a
            WHERE RowNum <= $lastRec )
     WHERE $firstRec <= rNum";

Counting records. Another common task that is harder than it looks is maintaining a count of the number of records for specific areas of interest. Oracle, like all high-performance databases, does not return a count of all the records in a returned recordset. This is a problem if want to find out how many records a query will return without running the potentially expensive query.

One example where this is useful is when you want to display a set of scrolling links when paging through a recordset, as shown below:

SELECT id, msg_subject, msg_contents FROM largetable

SELECT COUNT(*) FROM (SELECT id, msg_subject, msg_contents FROM largetable)

Hints. Oracle's query optimizer tries to be intelligent, but it won't always choose the best plan. To minimize these situations, you can supply hints to the optimizer. For example:

$sql = "SELECT * FROM (
SELECT /*+ FIRST_ROWS INDEX(customer ctype) */ * FROM customer 
WHERE type='High Value' ORDER BY name
) WHERE ROWNUM <= $lastRecordToDisplay";

FIRST_ROWS	Tells the optimizer that we don't want to retrieve all rows, so it optimizes for speed of retrieval a small fraction of the total records required.
INDEX(tablename indexname)	Asks the optimizer to use this index.

These are the two most common hints I use; you can find many others in the Oracle Database SQL Reference, under "Comments" in Chapter 2.

Prefetch. Finally, there is an OCI8 function that allows you to significantly reduce network traffic by setting the number of records to prefetch over the network into your Oracle client buffers. The default of 1 is far too low, as this means that every single record retrieved generates substantial network traffic. You should set it to your typical page size—for example, 20 to 50 records. This optimization alone can speed up queries by 100 to 200 percent.

In PHP:

$conn = OCILogon($user,$pwd);
$stmt = OCIParse($conn,$sql);
OCISetPrefetch($stmt, 20);
OCIExecute($stmt);
While (OCIFetchInto($stmt,$arr)) {
	/* Process $arr */
}

Have a look at the following SQL statements.

SELECT * FROM customer WHERE custid=1
SELECT * FROM customer WHERE custid=249
SELECT * FROM customer WHERE custid=6380

Bind value to variable	SQL
bind 1 to :var
bind 249 to :var	SELECT * FROM customer WHERE custid=:var
bind 6380 to :var

Translating this to PHP code, binding $value to :var, you'll get:

OCIParse($conn, 'SELECT * FROM customer WHERE custid=:var');
OciBindByName($stmt, ":var", $value,32); # 32 is an arbitrary large size

$value = 1; OCIExecute($stmt);
DisplayResults($stmt);

$value = 249; OCIExecute($stmt);
DisplayResults($stmt);

$value = 6380; OCIExecute($stmt);
DisplayResults($stmt);

Cursor sharing. If you have lots of legacy SQL that does not use binding, you can still reduce the compilation overhead using the CURSOR_SHARING parameter, available in Oracle8 and later. Executing the following SQL statement will cause Oracle to rewrite all your queries to use bind variables:

ALTER SESSION SET CURSOR_SHARING='FORCE'

SELECT * FROM customer WHERE custid=6380

SELECT * FROM customer WHERE custid=:SYS_B_0

On Oracle9i or later, you can also set CURSOR_SHARING='SIMILAR'. This parameter uses a more intelligent algorithm, taking table statistics into consideration to choose whether to convert a literal to a bind variable.

ALTER SESSION affects only the current session, so you will need to call it every time you connect to the database. To make it permanent, ask your DBA to place it in your database parameter file (init.ora or spfile).

In the next section, I'll show you some benchmarks with and without bind variables. The benchmarks will illustrate how using bind variables improved performance by over 100% in an INSERT statement scenario.

4. Use a Class Library

Before you start coding your application, you will find it useful to create a set of PHP functions to encapsulate the low-level functionality of the OCI8 extension. You can choose to either roll your own or use one of the popular open source libraries. The two most popular are PEAR DB and ADOdb. (I am the lead developer of the latter.)

As an example, consider the following code to update a table consisting of three fields: a, b, and c.

$conn = ocilogon('scott','tiger'); 

$stmt = OCIParse("insert into abc (a,b,c) values (?,?,?)");
OCIBindByName($stmt, ":a", $a, 32);
OCIBindByName($stmt, ":b", $b, 32);
OCIBindByName($stmt, ":c", $c, 32);
for ($i=0; $i<TIMES; $i++) {
	$a = $i;
	$b = "b".rand();
	$c = "c".rand();
	ociexecute($stmt,OCI_DEFAULT);
}
ocicommit($conn);

include('DB.php');
$DB = &DB::Connect('oci8://scott:tiger@/'); 

$DB->autoCommit(false);
$stmt = $DB->prepare("insert into abc (a,b,c) values (?,?,?)");
for ($i=0; $i<TIMES; $i++) {
	$DB->execute($stmt,array($i,"b".rand(),"c".rand()));
}
$DB->commit();

include('/path/to/adodb/adodb.inc.php');
$DB = NewADOConnection('oci8://scott:tiger@/');

$DB->BeginTrans();
$stmt = $DB->Prepare("insert into abc (a,b,c) values (:0,:1,:2)");
for ($i=0; $i<TIMES; $i++) {
	$DB->_Execute($stmt,array($i,"b".rand(),"c".rand()));
}	
$DB->CommitTrans();

Here are some benchmarks I did with the above code where TIMES = 1000. I measured the time from the beginning to the end of the transaction. I used PHP 4.3.11 and Oracle Database version 9.2 on Windows XP. Lower times are better.

OCI8 with binds	0.18s
ADOdb	0.27s
PEAR DB	0.35s
OCI8 without binds	0.41s

As you can see, there is a significant overhead to not using bind variables, particularly with INSERT and UPDATE statements that modify single records. There is some overhead to using a library; this performance hit has to be weighed against the improved ease-of-use and maintainability of your code when using a high-level library.

Another interesting point is that if the benchmarks are done with autocommit enabled, where a commit is silently executed after every INSERT statement, performance is substantially slower. The OCI8 with binds benchmark took 2.92 seconds, or was 16 times slower!

There are several additional advantages to using ADOdb. ADOdb has been heavily tuned for Oracle; for example:

• ADOdb sets OCISetPreFetch for you automatically.
• ADOdb inserts Oracle optimizer hints where possible to speed up your code.
• ADOdb supports IN OUT parameters in stored procedures and LOBs.
• ADOdb allows you to cache recordsets of frequently used queries. This is very useful for reducing your database load by caching lookup values that rarely change.
• ADOdb provides session-management utilities, allowing you to store your session information in a character LOB.
• ADOdb provides a tuning infrastructure that goes beyond SGA Trace. All SQL queries can be captured and cross-referenced by HTTP request, by cost, and by frequency of execution. This approach allows you to pinpoint performance problems in your SQL queries very quickly. (More on this later.)
• The default ADOdb install is purely portable PHP. For better performance, you can install the optional ADOdb extension, where parts of ADOdb are rewritten in C for speed. This extension is not required in the default install.

Although this is not an Oracle-specific optimization, you should also install a PHP accelerator cache such as Zend Accelerator, eAccelerator, or APC to speed up the compilation and loading of PHP code libraries.

$db->Execute("insert into aTable (id, name, ablob) 
                                values (999, 'Name', null)");

$db->UpdateBlob('aTable', 'ablob', $blobValue, 'id=999');

The above code inserts a new record into table ATABLE and then calls UpdateBlob to store the $blobValue into the field ablob where id=999.

5. Learn the Art of Creating Optimizer Hints and Indexes

Once you start testing your application and in the initial phase of deployment, you'll probably discover unexpected bottlenecks and problems. End users have a funny ability to generate the weirdest bugs in the most unexpected places. One of the best ways of working out why your SQL is slow is to log all SQL statements and execution times and then view the execution plans of the most expensive SQL.

$id = 'MyID';
$explainSQL = "EXPLAIN PLAN SET STATEMENT_ID='$id' FOR $sql";
$stmt = OCIParse($conn, $explainSQL);
OCIExecute($stmt);

CREATE TABLE PLAN_TABLE (
  STATEMENT_ID                    VARCHAR2(30),
  TIMESTAMP                       DATE,
  REMARKS                         VARCHAR2(80),
  OPERATION                       VARCHAR2(30),
  OPTIONS                         VARCHAR2(30),
  OBJECT_NODE                     VARCHAR2(128),
  OBJECT_OWNER                    VARCHAR2(30),
  OBJECT_NAME                     VARCHAR2(30),
  OBJECT_INSTANCE                 NUMBER(38),
  OBJECT_TYPE                     VARCHAR2(30),
  OPTIMIZER                       VARCHAR2(255),
  SEARCH_COLUMNS                  NUMBER,
  ID                              NUMBER(38),
  PARENT_ID                       NUMBER(38),
  POSITION                        NUMBER(38),
  COST                            NUMBER(38),
  CARDINALITY                     NUMBER(38),
  BYTES                           NUMBER(38),
  OTHER_TAG                       VARCHAR2(255),
  PARTITION_START                 VARCHAR2(255),
  PARTITION_STOP                  VARCHAR2(255),
  PARTITION_ID                    NUMBER(38),
  OTHER                           LONG,
  DISTRIBUTION                    VARCHAR2(30)
);

$DB->LogSQL(true);

<?php
include_once('/path/to/adodb.inc.php');
session_start(); # session variables required for monitoring
$conn = ADONewConnection('oci8');
$conn->Connect($tnsname,$user,$pwd);
$perf =& NewPerfMonitor($conn);
$perf->UI($pollsecs=5);
?>

select * from (SELECT /*+FIRST_ROWS*/ * FROM myhist WHERE accno ='7314028790' 
ORDER BY POSTDT desc) where rownum <= :adodb_offset

  

    
OPERATION
    
OBJECT_NAME
    
COST
    
CARDINALITY
    
BYTES
  
  

    
SELECT STATEMENT
    
 
    
63
    
456
    
39216
  
  

    
--COUNT STOPKEY
    
 
    
 
    
 
    
 
  
  

    
----VIEW
    
 
    
63
    
456
    
39216
  
  

    
------SORT ORDER BY STOPKEY
    
 
    
63
    
456
    
39216
  
  

    
--------TABLE ACCESS FULL
    
 
    
55
    
456
    
39216
  

Scripts Affected:
4   localhost/juris/z/tloan/myloan.php

CREATE INDEX ON myhist(accno)

  

    
OPERATION
    
OBJECT_NAME
    
COST
    
CARDINALITY
    
BYTES
  
  

    
SELECT STATEMENT
    
 
    
10
    
456
    
39216
  
  

    
--COUNT STOPKEY
    
 
    
 
    
 
    
 
  
  

    
----VIEW
    
 
    
10
    
456
    
39216
  
  

    
------SORT ORDER BY STOPKEY
    
 
    
2
    
456
    
39216
  
  

    
--------TABLE ACCESS BY INDEX ROWID
    
MYHIST
    
2
    
456
    
39216
  
  

    
----------INDEX RANGE SCAN
    
ACCNO
    
1
    
182
    
 
  

CREATE SEQUENCE aSEQUENCE;

CREATE OR REPLACE TRIGGER A_ID
BEFORE INSERT ON ATABLE
FOR EACH ROW
BEGIN
 SELECT aSEQUENCE.nextVal INTO :NEW.A_ID FROM dual;
END;

$id = $DB->GetOne('SELECT aSEQUENCE.currVal FROM DUAL');

AUTHOR: AuthorName Address Title1 ISBN1 Title2 ISBN2 Title3 ISBN3

AUTHOR: AuthorName Address
BOOK:   ISBN AuthorName Title

(id of the first post of the thread, the actual id of the post)

• Centralized control of key business logic. If you run a server farm, you don't need to update all PHP scripts on all servers to roll out a change.
• Possibly better performance compared to coding it in PHP, due to tighter coupling between PL/SQL and Oracle's database
• The same function is now accessible to other languages that have Oracle interfaces, such as Java, Python, and Visual Basic.

CREATE OR REPLACE FUNCTION CalcShoppingCartTotal(
cartid IN number) RETURN number
IS
begin
/* some code */
end;

$total = $DB->GetOne("SELECT CalcShoppingCartTotal(:0) FROM DUAL",array($cartid));

CREATE OR REPLACE PROCEDURE CalcShoppingCartTotal(
cartid IN number, 
shipcode OUT number,
total OUT number)
IS
begin
/* some code */
end;

$sql = "begin CalcShoppingCartTotal(:cartid, :shipcode, :total); end;";
$stmt = $DB->PrepareSP($sql);
$DB->InParameter($stmt, $cartid, 'cartid');
$DB->OutParameter($stmt, $shipCode, 'shipcode');
$DB->OutParameter($stmt, $cartTotal, 'total');
$DB->Execute($stmt);
print "<p>total = $cartTotal, shipping code=$ shipCode</p>";

number of Apache or FastCGI processes * number Oracle user id's used