Tracing PHP Oracle Applications, part 1
By cj on Jan 19, 2010
(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:
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.
Update: also see PHP Web Auditing, Authorization and Monitoring with Oracle Database.