Oracle and PHP Performance

by Mike Willbanks on September 16th, 2005

Well strangely enough I have been working with Oracle databases the last 4 months and have been researching on how to speed them up. Currently the business that I am doing it for is running a highly outdated PHP installation (PHP Version 4.2.3 64-bit). However, there has not been a PHP 4 update from HP-UX which is what they are currently utilizing. There is a PHP 5 update but they do not want to break functionality or start a migration to upgrade old programs that may not work.

With that being said here are a few tips of the trade for speeding up your oracle queries:

1. Utilizing ocisetprefetch (oci_set_prefetch)
Setting the prefetch amount can dramatically speed up queries… By default PHP prefetches one row at a time. This is not efficent when selecting more than one row at a time. If you are fetching consistantly above 10+ records set the prefetch to the average amount. This will cause it to buffer in that amount of prefetch so those rows are accessible much faster.

2. Statement parsing ociparse (oci_parse)
In oracle parsing a statement is one of the more intensive operations. When you are utilizing a query, make sure that you parse your statements outside of a loop and utilize binding variables in order to be able to reuse that query. We will talk more about binding variables later.

An example of this is:
$stmt = ociparse($conn, select * from dual where id=:id);
while ([loop condition]) {
ocibindbyname($stmt, ':id', $id);

This way you are always reusing your parsed statement and are not increasing your loading time.

3. Fetching one row at a time instead of all rows with ocifetchinto and ocifetchstatement (oci_fetch_into, oci_fetch_statement)
While you should realize the amount of memory needed and the unefficency of ocifetchstatement, some people would still like to get all of the records and throw them into an array before moving on to using the information.
An example of this would be:
$stmt = ociparse($conn, 'select id, name, email from users');
ocifetchstatement($stmt, $results, null, null, OCI_FETCHSTATEMENT_BY_ROW); //now we have all the rows in one array

//lets do a foreach to demonstrate more unefficency
foreach($results as $res) {
echo ($res['ID'] . ' ' . $res['NAME'] . ' ' . $res['EMAIL']);

Now what we actually did there was fetched every single row into one array which makes it have to query all the data before it can return it into that result set, then maximized our memory with fetching all the rows, then we decided to do a foreach which copies that result set to echo out each one. Therefore you have 2 copies of the same array now add that onto the time it takes to fetch that whole result set.

The more efficent way to do this would be:
$stmt = ociparse($conn, 'select id, name, email from users');
while(ocifetchinto($stmt, $res, OCI_ASSOC) {
echo ($res['ID'] . ' ' . $res['NAME'] . ' ' . $res['EMAIL']);

All better :)

4. Setting Indexs and giving oracles parser hits towards what you are optimizing for.
When querying information you might want to insure that you will get a fast return by making sure that all of your joins have an index on the joining query. This will infact be the most dramatic factor with getting high speeds out of oracle. Then to make it more speedy you can use parser rules to tell it which plan to use (oracle creates an explain plan to show you which might be the best one and automatically picks on if you don’t… Sometimes it is wrong in picking the best one.

The following you can use to help it choose which optimization plan to use:

The first one is what oracle usually does by default. But it can vary between queries, ALL_ROWS should be used if you are going to use everything from a query, if you are limiting the output use FIRST_ROWS. That will help make your query faster as well.

5. Binding variables ocibindbyname (oci_bind_by_name)
Binding variables are one of the nicest things to use. If you use a binding variable it helps with sql injection as the statement is prepared before you are inserting data. No more escaping quotes. However remember about XSS and the others. You still should always check your input so that it is correct.

Binding variables help you parse a regular statement and allow it to be used multiple times, you can also return things into a bind.

From PHP

Leave a Reply

Note: XHTML is allowed. Your email address will never be published.

Subscribe to this comment feed via RSS