Tracing PHP Oracle Applications, part 1

(This was an article I wrote for a newsletter).

In December 2009, PHP reached the number three position in the TIOBE
language index. Analysts are recommending PHP be considered in
your technology portfolio. Oracle Enterprise Linux users are well
placed to do this.

Oracle contributes to the PHP project by, among other things,
maintaining the OCI8 extension for the Oracle Database. It is
available with PHP source code and is also bundled on the PHP
Extension C Library (PECL), where it can be used to update existing
versions of PHP.

OCI8 RPMs for the default Linux PHP are available for Unbreakable Linux Network
subscribers in the “Oracle Software” channel. However, most users will
want a recent version of PHP. Our partner, Zend, has worked with
Oracle to make their Zend Server available through ULN. This product
was highlighted in the November Linux newsleter. More information is
at: http://www.oracle.com/technology/tech/php/zend-server.html

No matter how it was installed, the PHP OCI8 extension provides a
procedural API with familiar calls allowing efficient SQL and PL/SQL
execution. The extension also supports some advanced Oracle features
such as connection pooling.

Recently some new OCI8 functions were checked into the PHP source
code. These will become available as the packages pick up the latest
changes.

The new functions include:

• oci_set_module_name
• oci_set_action
• oci_set_client_info

These set values that are can be used by the database. By
transparently “piggy-backing” onto SQL statements sent to the
database, they efficiently provide a way to enhance the tracability,
authentication and auditing of applications.

A quick example using these new functions is:

    <?php 

    $c = oci_connect('hr', 'hrpwd', 'localhost/orcl'); 

    oci_set_client_info($c, 'My Application Version 2');
    oci_set_module_name($c, 'Home Page');
    oci_set_action($c, 'Friend Lookup'); 

    // Do some action which touches the database
    // The three attribute values will be "piggy backed"
    // and sent to the database
    $s = oci_parse($c, 'select * from dual');
    oci_execute($s);
    oci_fetch_all($s, $res); 

    ?>

The values are used in a number of data dictionary views in the
database, such as V$SESSION. You can also architect your code to make
use of the values. For example they can be viewed and tested in SQL
queries using the SYS_CONTEXT() function.

One key use for the Module and Action values is to track exactly
what SQL statements each part of your application executes. These
values are recorded along with the SQL statement in the V$SQLAREA
view the first time a statement is executed:

   SQL> select sql_text, module, action
        from v$sqlarea
        where module = 'Home Page'; 

   SQL_TEXT                    MODULE          ACTION
   --------------------------- --------------- ----------------
   select * from dual          Home Page       Friend Lookup

This quickly allows you to narrow down problematic statements to
resolve performance issues.

In a future post I’ll talk about some of the other changes in OCI8
1.4 and go into more depth on how to use the attributes for
tracing.