Friday Sep 26, 2014

Oracle OpenWorld is Upon Us! Application Developers Unite!

It's a super, extra, enormously busy time of the year at Oracle because our big Oracle OpenWorld Conference is next week. We have all been working on new products and new presentations.

I have a full mix of events, meetings, presentations and booth duty. If you're at the conference drop by to say Hi. I'll be at the Application Development booth (Left - SLD-163) in Moscone South on Monday morning, Tuesday lunchtime and Wednesday morning (plus other odd times).

Also lookout for me onstage with Tom Kyte on Monday and with Steven Feuerstein on Wednesday.

The session details, and other sessions you might find interesting are listed on my Focus On Application Development page.

If you are a developer, I recommend coming to the Programming and Scripting "Meet the Expert" session at 6pm Tuesday evening (Moscone South - 307). I cannot stress enough how valuable it is to have a dialog and relationship with the developers who create the software and APIs you use.

Tuesday Aug 19, 2014

Routing PHP memcached calls to Oracle Coherence

A new post Getting Started with the Coherence Memcached Adaptor from David Felcey shows how PHP memcached calls can automatically be routed to store data in Oracle Coherence 12c. This is possible now Coherence 12.1.3 supports Memcached clients using the Binary Memcached protocol. David's post shows how the Coherence Memcached adaptor can be configured as a proxy service that runs in the Coherence cluster. There's nothing particular to configure in the PHP application, except to enable memcached.use_sasl = 1

So what is Coherence? It is an "in-memory data grid solution", with a number of advanced features. You can read more in the Oracle Coherence 12C Data Sheet.

Thursday Jul 31, 2014

New Oracle Technology Network PHP Forum URL

The Oracle Technology Network (which promotes the development community) is upgrading its software platform and reorganizing some content. The PHP Oracle forum is now at https://community.oracle.com/community/development_tools/php. The top level "PHP Developer Center" is at http://www.oracle.com/technetwork/database/database-technologies/php/whatsnew/index.html. I notice my old bookmarks for the Developer Center redirect to its current location, but this doesn't seem true of some very old URLs for the forum.

Thursday Apr 24, 2014

Installing PHP on Oracle HTTP Server 12c

OTN have posted an updated guide for installing PHP on Oracle HTTP Server. Check it out!

Using PHP-FPM is a great way to integrate with Oracle Fusion Middleware.

Friday Mar 14, 2014

Performance improvement for OCI_RETURN_LOBS in PHP OCI8 2.0.8

Reducing "round trips" to the database server is important for performance and ultimately for system scalability. A server round-trip is defined as the trip from PHP to the database server and back to PHP.

Reducing round trips makes everything more efficient: PHP is faster, there is less network overhead, the database doesn't have to do a context switch or do any processing for you. Applications have some control over round trips, for example by effectively using prefetching or by using an appropriate execute mode to minimize unneccessary rollbacks at the end of a script.

The bug filer of Bug 66875 noticed that PHP OCI8's implementation itself could reduce round trips if a particular LOB column meta data value was cached for the duration of a query instead of being re-requested for each row of the query result set.

So, now with OCI8 2.0.8, you should see a performance increase if you are doing multi-row queries involving LOB columns returned as OCI_RETURN_LOBS:

$s = oci_parse($c, "select mylobcol from mylobtab");
oci_execute($s);
while (($row = oci_fetch_array($s, OCI_ASSOC+OCI_RETURN_LOBS)) !== false) {
    echo $row['MYLOBCOL'], "\n";
}

The bug filer tested the patch and added his performance improvement benchmark results to the bug report. The benefit in your environment will vary greatly with the network setup, schema, and how many LOB columns are queried. Test it out and let me know how the new version helps you.

There is no immediate change for LOBs fetched with OCI-Lob::read() and OCI-Lob::load(). This would require a more complex patch than I want to apply at this time. Queries that don't use LOBs are not affected in any way by the patch.

OCI8 2.0 is included in the forthcoming PHP 5.6 code base. For PHP 5.2 to PHP 5.5 you can install it from PECL. PHP 5.5 RPMs with PHP OCI8 2.0.8 are available from oss.oracle.com.

Finally, if your LOB queries return multiple rows, you might also like this tip to reduce PHP memory usage.

Monday Mar 03, 2014

The Oracle Database Access Group is hiring

Over the years I've worked very closely with the Oracle Database Access Group and have a lot of respect for them. I'm happy to share that they are hiring C developers.

To apply, go to irecruitment.oracle.com and enter the code IRC2401606 or IRC2403582 (depending where you want to work) in the Keyword search box.

Here is an excerpt from the job posting:

The Database Access group at Oracle is responsible for providing functionally comprehensive, reliable, high performance, secure and highly available access to the Oracle Database from various client drivers, including proprietary, standards-based and open-source drivers. The group works on the high level drivers, the Oracle Call Interface (OCI) layer, the Oracle wire protocol (TTC) and highly scalable server side protocol handlers that together connect an application written in any language securely to the Oracle Database Server to provide full featured access to the Oracle Database.

Some of the listed requirements are:

  • Strong C programming experience.

  • Knowledge and experience with latest application development technologies including open source and web technologies (e.g. PHP, Ruby, Python, Node.js, HTML5, JavaScript)

I believe it when they also say "Work is non-routine and very complex, involving the application of advanced technical/business skills in area of specialization". Check out the postings for all the details. I look forward to working with you.

Addendum: IRC2401606 is also available.

Tuesday Feb 11, 2014

Support for binding Oracle PL/SQL BOOLEAN introduced in PHP OCI8 2.0.7

I've released PHP OCI8 2.0.7 which has oci_bind_by_name() support for binding PL/SQL's BOOLEAN type to PHP's boolean. The feature is available when PHP OCI8 2.0.7 is linked-with and connects-to Oracle Database 12c. (The necessary Oracle C library support for binding BOOLEAN was introduced in 12c).

Following the existing PHP OCI8 convention, there are two new constants usable with oci_bind_by_name(): SQLT_BOL and OCI_B_BOL. They have identical values. The former mirrors the underlying Oracle library constant's name (yes, it only has one "O"). The latter follows the PHP OCI8 style of having OCI_B_-prefixed names for bind constants. Note the constants can't be used for array binding with oci_bind_array_by_name().

An example usng the new PHP OCI8 2.0.7 feature is:

<?php

/*
  Precreate this PL/SQL function:
    create or replace function is_valid(p in number) return boolean as
    begin
      if (p < 10) then
        return true;
      else
        return false;
      end if;
    end;
    /

*/

$c = oci_connect('hr', 'welcome', 'localhost/pdborcl');

$sql = "begin :r := is_valid(40); end;";
$s = oci_parse($c, $sql);
oci_bind_by_name($s, ':r', $r, -1, SQLT_BOL);  // no need to give length
oci_execute($s);
var_dump($r);                                  // Outputs: bool(false)

?>

Prior to OCI8 2.0.7 you had to write a wrapper PL/SQL block that mapped the PL/SQL true or false values to 1 or 0.

Code without using OCI8 2.0.7:

$sql = "begin if (is_valid(40) = true) then :r := 1; else :r := 0; end if; end;";
$s = oci_parse($c, $sql);
oci_bind_by_name($s, ':r', $r, -1, SQLT_INT);
oci_execute($s);
echo "Result is " . ($r ? "true" : "false") . "\n";  // Outputs: Result is false

The new functionality removes one small pain point and makes your interaction with Oracle Database 12c PL/SQL cleaner and easier.

Tuesday Dec 10, 2013

Tracing PHP with DTrace - Five Minute Lightning Talk is on Youtube

I gave a 5 minute talk on DTrace in PHP at the SF PHP Meetup Lightning Talk session last night. You can watch it here. It starts at the 14:10 mark.

Tuesday Dec 03, 2013

DTrace and Perl from @GregoryGuillou

Grégory Guillou (@GregoryGuillou) has a post on using DTrace with Perl: Custom DTrace Probes for Perl on Oracle Linux 6. He uses libusdt developed by Chris Andrews (@chrisandrews). Check it out!

Monday Dec 02, 2013

PHP Examples in New "Oracle Linux 6 DTrace Tutorial"

My colleague, Gavin Bowe, has released a new Oracle Linux 6 DTrace Tutorial. It is available in HTML, PDF and ePub from http://docs.oracle.com/cd/E37670_01/index.html.

Chapter 3 on "Tracing User-Space Applications" has some PHP examples.

Tuesday Nov 05, 2013

Tracing Silex from PHP to the OS with DTrace

In this blog post I show the full stack tracing of Brendan Gregg's php_syscolors.d script in the DTrace Toolkit. The Toolkit contains a dozen very useful PHP DTrace scripts and many more scripts for other languages and the OS.

For this example, I'll trace the PHP micro framework Silex, which was the topic of the second of two talks by Dustin Whittle at a recent SF PHP Meetup. His slides are at Silex: From Micro to Full Stack.

Installing DTrace and PHP

The php_syscolors.d script uses some static PHP probes and some kernel probes. For Oracle Linux I discussed installing DTrace and PHP in DTrace PHP Using Oracle Linux 'playground' Pre-Built Packages. On other platforms with DTrace support, follow your standard procedures to enable DTrace and load the correct providers. The sdt and systrace providers are required in addition to fasttrap.

On Oracle Linux, I loaded the DTrace modules like:

# modprobe fasttrap
# modprobe sdt
# modprobe systrace
# chmod 666 /dev/dtrace/helper

Installing the DTrace Toolkit

I download DTraceToolkit-0.99.tar.gz and extracted it:

$ tar -zxf DTraceToolkit-0.99.tar.gz

The PHP scripts are in the Php directory and examples in the Examples directory.

Installing Silex

I downloaded the "fat" Silex .tgz file from the download page and extracted it:

$ tar -zxf silex_fat.tgz

I changed the demonstration silex/web/index.php so I could use the PHP development web server:

<?php

// web/index.php

$filename = __DIR__.preg_replace('#(\?.*)$#', '', $_SERVER['REQUEST_URI']);
if (php_sapi_name() === 'cli-server' && is_file($filename)) {
    return false;
}

require_once __DIR__.'/../vendor/autoload.php';

$app = new Silex\Application();

//$app['debug'] = true;

$app->get('/hello', function() {
        return 'Hello!';
    });

$app->run();

?>

Running DTrace

The php_syscolors.d script uses the -Z option to dtrace, so it can be started before PHP, i.e. when there are zero of the requested probes available to be traced. I ran DTrace like:

# cd DTraceToolkit-0.99/Php
# ./php_syscolors.d

Next, I started the PHP developer web server in a second terminal:

$ cd silex
$ php -S localhost:8080 -t web web/index.php

At this point, the web server is idle, waiting for requests. DTrace is idle, waiting for the probes in php_syscolors.d to be fired, at which time the action associated with each probe will run.

I then loaded the demonstration page in a browser:

http://localhost:8080/hello

When the request was fulfilled and the simple output of "Hello" was displayed, I ^C'd php and dtrace in their terminals to stop them.

DTrace output over a thousand lines long had been generated. Here is one snippet from when run() was invoked:

C    PID/TID   DELTA(us)              FILE:LINE TYPE     -- NAME
...
1   4765/4765         21   Application.php:487  func     -> run
1   4765/4765         29   ClassLoader.php:182  func       -> loadClass
1   4765/4765         17   ClassLoader.php:198  func         -> findFile
1   4765/4765         31                 ":-    syscall        -> access
1   4765/4765         26                 ":-    syscall        <- access
1   4765/4765         16   ClassLoader.php:198  func         <- findFile
1   4765/4765         25                 ":-    syscall      -> newlstat
1   4765/4765         15                 ":-    syscall      <- newlstat
1   4765/4765         13                 ":-    syscall      -> newlstat
1   4765/4765         13                 ":-    syscall      <- newlstat
1   4765/4765         22                 ":-    syscall      -> newlstat
1   4765/4765         14                 ":-    syscall      <- newlstat
1   4765/4765         15                 ":-    syscall      -> newlstat
1   4765/4765         60                 ":-    syscall      <- newlstat
1   4765/4765         13                 ":-    syscall      -> newlstat
1   4765/4765         13                 ":-    syscall      <- newlstat
1   4765/4765         20                 ":-    syscall      -> open
1   4765/4765         16                 ":-    syscall      <- open
1   4765/4765         26                 ":-    syscall      -> newfstat
1   4765/4765         12                 ":-    syscall      <- newfstat
1   4765/4765         17                 ":-    syscall      -> newfstat
1   4765/4765         12                 ":-    syscall      <- newfstat
1   4765/4765         12                 ":-    syscall      -> newfstat
1   4765/4765         12                 ":-    syscall      <- newfstat
1   4765/4765         20                 ":-    syscall      -> mmap
1   4765/4765         14                 ":-    syscall      <- mmap
1   4765/4765       3201                 ":-    syscall      -> mmap
1   4765/4765         27                 ":-    syscall      <- mmap
1   4765/4765       1233                 ":-    syscall      -> munmap
1   4765/4765         53                 ":-    syscall      <- munmap
1   4765/4765         15                 ":-    syscall      -> close
1   4765/4765         13                 ":-    syscall      <- close
1   4765/4765         34       Request.php:32   func         -> main
1   4765/4765         22       Request.php:32   func         <- main
1   4765/4765         31   ClassLoader.php:182  func       <- loadClass
1   4765/4765         33       Request.php:249  func       -> createFromGlobals
1   4765/4765         29       Request.php:198  func         -> __construct
1   4765/4765         24       Request.php:218  func           -> initialize
1   4765/4765         26   ClassLoader.php:182  func             -> loadClass
1   4765/4765         89   ClassLoader.php:198  func               -> findFile
1   4765/4765         43                 ":-    syscall              -> access
...

The output shows PHP functions being called and returning (and where they are located) and which system calls the PHP functions in turn invoked. The time each line took from the previous one is displayed in the third column.

The first column is the CPU number. In this example, the process was always on CPU 1 so the output is naturally ordered without requiring post-processing, or the D script requiring to be modified to display a time stamp.

On a terminal, the output of php_syscolors.d is color-coded according to whether each function is a PHP or system one, hence the file name.

Summary

With one tool, I was able to trace the interaction of a user application with the operating system. I was able to do this to an application running "live" in a web context.

The DTrace Toolkit provides a very handy repository of DTrace information. Even though the PHP scripts were created in the time frame of the original PHP DTrace PECL extension, which only had PHP function entry and return probes, the scripts provide core examples for custom investigation and resolution scripts. You can easily adapt the ideas and create scripts using the other PHP static probes, which are listed in the PHP Manual.

Because DTrace is "always on", you can take advantage of it to resolve development questions or fix production situations.

Friday Nov 01, 2013

DTracing a PHPUnit Test: Looking at Functional Programming

Here's a quick example of using DTrace Dynamic Tracing to work out what a PHP code base does.

I was reading the article Functional Programming in PHP by Patkos Csaba and wondering how efficient this type of programming is. I thought this would be a good time to fire up DTrace and see what is going on. Since DTrace is "always available" even in production machines (once PHP is compiled with --enable-dtrace), this was easy to do.

I have Oracle Linux with the UEK3 kernel and PHP 5.5 with DTrace static probes enabled, as described in DTrace PHP Using Oracle Linux 'playground' Pre-Built Packages

I installed the Functional Programming sample code and Sebastian Bergmann's PHPUnit. Although PHPUnit is included in the Functional Programming example, I found it easier to separately download and use its phar file:

cd ~/Desktop
wget -O master.zip \
  https://github.com/tutsplus/functional-programming-in-php/archive/master.zip
wget https://phar.phpunit.de/phpunit.phar
unzip master.zip

I created a DTrace D script functree.d:

#pragma D option quiet

self int indent;

BEGIN
{
  topfunc = $1;
}

php$target:::function-entry
/copyinstr(arg0) == topfunc/
{
  self->follow = 1;
}

php$target:::function-entry
/self->follow/
{
  self->indent += 2;
  printf("%*s %s%s%s\n", self->indent, "->", arg3?copyinstr(arg3):"", 
                            arg4?copyinstr(arg4):"", copyinstr(arg0));
}

php$target:::function-return
/self->follow/
{
  printf("%*s %s%s%s\n", self->indent, "<-", arg3?copyinstr(arg3):"", 
                            arg4?copyinstr(arg4):"", copyinstr(arg0));
  self->indent -= 2;
}

php$target:::function-return
/copyinstr(arg0) == topfunc/
{
  self->follow = 0;
}

This prints a PHP script function call tree starting from a given PHP function name. This name is passed as a parameter to DTrace, and assigned to the variable topfunc when the DTrace script starts. With this D script, choose a PHP function that isn't recursive, or modify the script to set self->follow = 0 only when all calls to that function have unwound.

From looking at the sample FunSets.php code and its PHPUnit test driver FunSetsTest.php, I settled on one test function to trace:

function testUnionContainsAllElements() {
  ...
}

I invoked DTrace to trace function calls invoked by this test with

# dtrace -s ./functree.d -c 'php phpunit.phar \
 functional-programming-in-php-master/FunSets/Tests/FunSetsTest.php' \
 '"testUnionContainsAllElements"'

The core of this command is a call to PHP to run PHPUnit on the FunSetsTest.php script. Outside that, DTrace is called and the PID of PHP is passed to the D script $target variable so the probes fire just for this invocation of PHP.

Note the quoting around the PHP function name passed to DTrace. The parameter must have double quotes included so DTrace knows it is a string.

The output is:

PHPUnit 3.7.28 by Sebastian Bergmann.

......-> FunSetsTest::testUnionContainsAllElements
  -> FunSets::singletonSet
  <- FunSets::singletonSet
  -> FunSets::singletonSet
  <- FunSets::singletonSet
  -> FunSets::union
  <- FunSets::union
  -> FunSets::contains
    -> FunSets::{closure}
      -> FunSets::contains
        -> FunSets::{closure}
        <- FunSets::{closure}
      <- FunSets::contains
    <- FunSets::{closure}
  <- FunSets::contains
  -> PHPUnit_Framework_Assert::assertTrue
    -> PHPUnit_Framework_Assert::isTrue
    <- PHPUnit_Framework_Assert::isTrue
    -> PHPUnit_Framework_Assert::assertThat
      -> PHPUnit_Framework_Constraint::count
      <- PHPUnit_Framework_Constraint::count
      -> PHPUnit_Framework_Constraint::evaluate
        -> PHPUnit_Framework_Constraint_IsTrue::matches
        <- PHPUnit_Framework_Constraint_IsTrue::matches
      <- PHPUnit_Framework_Constraint::evaluate
    <- PHPUnit_Framework_Assert::assertThat
  <- PHPUnit_Framework_Assert::assertTrue
  -> FunSets::contains
    -> FunSets::{closure}
      -> FunSets::contains
        -> FunSets::{closure}
        <- FunSets::{closure}
      <- FunSets::contains
      -> FunSets::contains
        -> FunSets::{closure}
        <- FunSets::{closure}
      <- FunSets::contains
    <- FunSets::{closure}
  <- FunSets::contains
  -> PHPUnit_Framework_Assert::assertTrue
    -> PHPUnit_Framework_Assert::isTrue
    <- PHPUnit_Framework_Assert::isTrue
    -> PHPUnit_Framework_Assert::assertThat
      -> PHPUnit_Framework_Constraint::count
      <- PHPUnit_Framework_Constraint::count
      -> PHPUnit_Framework_Constraint::evaluate
        -> PHPUnit_Framework_Constraint_IsTrue::matches
        <- PHPUnit_Framework_Constraint_IsTrue::matches
      <- PHPUnit_Framework_Constraint::evaluate
    <- PHPUnit_Framework_Assert::assertThat
  <- PHPUnit_Framework_Assert::assertTrue
  -> FunSets::contains
    -> FunSets::{closure}
      -> FunSets::contains
        -> FunSets::{closure}
        <- FunSets::{closure}
      <- FunSets::contains
      -> FunSets::contains
        -> FunSets::{closure}
        <- FunSets::{closure}
      <- FunSets::contains
    <- FunSets::{closure}
  <- FunSets::contains
  -> PHPUnit_Framework_Assert::assertFalse
    -> PHPUnit_Framework_Assert::isFalse
      -> {closure}
        -> main
        <- main
      <- {closure}
    <- PHPUnit_Framework_Assert::isFalse
    -> PHPUnit_Framework_Assert::assertThat
      -> PHPUnit_Framework_Constraint::count
      <- PHPUnit_Framework_Constraint::count
      -> PHPUnit_Framework_Constraint::evaluate
        -> PHPUnit_Framework_Constraint_IsFalse::matches
        <- PHPUnit_Framework_Constraint_IsFalse::matches
      <- PHPUnit_Framework_Constraint::evaluate
    <- PHPUnit_Framework_Assert::assertThat
  <- PHPUnit_Framework_Assert::assertFalse
<- FunSetsTest::testUnionContainsAllElements
...

Time: 1.85 seconds, Memory: 3.75Mb
OK (9 tests, 23 assertions)

The periods correspond to the successful tests before and after (and from) the test I was tracing.

You can see the function entry ("->") and return ("<-") points. Cross checking with the testUnionContainsAllElements() source code confirms the two singletonSet() calls, one union() call, two assertTrue() calls and finally an assertFalse() call. These assertions have a contains() call as a parameter, so contains() is called before the PHPUnit assertion functions are run. You can see contains() being called recursively, and how the closures are invoked.

If you want to focus on the application logic and suppress the PHPUnit function trace, you could turn off tracing when assertions are being checked by adding D clauses checking the entry and exit of assertFalse() and assertTrue().

But if you want to see all of PHPUnit's code flow, you can modify the functree.d code that sets and unsets self->follow, and instead change it to toggle the variable in request-startup and request-shutdown probes:

php$target:::request-startup
{
  self->follow = 1
}

php$target:::request-shutdown
{
  self->follow = 0
}

Be prepared for a large amount of output!

Thursday Oct 24, 2013

PHP PECL OCI8 2.0 Production Release Announcement

The PHP OCI8 2.0.6 extension for Oracle Database is now "production" status. The source code is available on PECL. This can be used immediately to update your OCI8 extension in PHP 5.2 and later versions. The extension compiles with Oracle 10.2 or later client libraries. Oracle's standard cross-version database connectivity applies.

OCI8 2.0 and PHP 5.5.5 RPMs for Oracle and Red Hat Linux are available from oss.oracle.com. Windows DLLs are available on PECL for PHP 5.3, PHP 5.4 and PHP 5.5.

OCI8 2.0 source code will also be automatically included in the next major version of PHP.

New Functionality

  • Oracle Database 12c Implicit Result Set support. IRS's make it easy to pass query results back from stored PL/SQL procedures or anonymous PL/SQL blocks. Individual IRS statement resources, each corresponding to a single query, can be obtained with the new function oci_get_implicit_resultset(). These 'child' statement resources can be passed to any oci_fetch_* function. See Using PHP and Oracle Database 12c Implicit Result Sets and the PHP Manual: oci_get_implicit_resultset().

  • DTrace Dynamic Trace static probes. This well respected DTrace tracing framework is available on a number of platforms, including Oracle Linux. PHP OCI8 static user-space probes can be enabled with PHP's --enable-dtrace configuration option. See Using PHP DTrace on Oracle Linux. Documentation is also available in the PHP Manual OCI8 and DTrace Dynamic Tracing

Improved Functionality

  • Using oci_execute($s, OCI_NO_AUTO_COMMIT) for a SELECT no longer unnecessarily initiates an internal ROLLBACK during connection close. This can improve overall scalability by reducing "round trips" between PHP and the database.

Changed Functionality

  • PHP OCI8 2.0's minimum pre-requisites are now PHP 5.2 and Oracle client library 10.2. Later versions of both are usable and, in fact, recommended. Use the older PHP OCI8 1.4.10 extension when using PHP 4.3.9 through to PHP 5.1.x, or when only Oracle Database 9.2 client libraries are available.

  • oci_set_*($connection, ...) meta data setting call error handling is fixed so that oci_error($connection) works for these calls.

Note: The old, deprecated function aliases like ocilogon still exist but are not recommended for new applications.

Phpinfo() Changes

Some cosmetic changes were made to the output of php --ri oci8 and the phpinfo() function.

  • The oci8.event and oci8.connection_class values are now shown only when the Oracle client libraries support the respective functionality.

  • Connection statistics are now in a separate phpinfo() table.

  • Temporary LOB and Collection support status lines in phpinfo() output were removed. These two features have always been enabled since 2007.

Oci_internal_debug() Changes

  • The oci_internal_debug() function is now a no-op. Use PHP's --enable-dtrace functionality with DTrace or SystemTap instead.

References

Wednesday Oct 02, 2013

DTrace PHP Using Oracle Linux 'playground' Pre-Built Packages

We've released DTrace-enabled PHP 5.5.4 RPMs to make testing DTrace on Oracle Linux easier. As a result, the manual PHP install steps listed in Using PHP DTrace on Oracle Linux can be skipped. There are updated Betas of the "UEK3" Linux Kernel 3.8.13-16 and the dtrace-utils tools available too. With these, you now can DTrace PHP applications under Apache or with php-fpm, as well as command line PHP scripts.

This post updates the install instructions, and show some of the probe changes in the latest PHP OCI8 2.0 extension.

Install Oracle Linux and the UEK3 Kernel

  1. Follow the steps to install Oracle Linux and the UEK3 Kernel given in the earlier blog post Using PHP DTrace on Oracle Linux

  2. Once the 3.8.13-16 Linux Kernel is installed and booted, enable user space tracing:

    # modprobe fasttrap
    # chmod 666 /dev/dtrace/helper
    

    Instead of the chmod, you could instead use an acl package rule to limit device access to a specific user.

Pre-installation for PHP

  1. Install the Oracle client libraries that PHP will use, oracle-instantclient12.1-basic-12.1.0.1.0-1.x86_64.rpm.

    Users with a ULN subscription can get this package from the "Oracle Software for Oracle Linux 6 (x86_64)" channel.

    If you don't have a ULN support subscription, and instead use public-yum.oracle.com, then manually download and install the free Instant Client RPM from OTN:

    # rpm -i oracle-instantclient12.1-basic-12.1.0.1.0-1.x86_64.rpm
    
  2. Add the "playground" channel via the ULN interface (for ULN subscribers), or for public-yum users, manually edit /etc/yum.repos.d/public-yum-ol6.repo and add:

    [public_ol6_playground_latest]
    name=Latest mainline stable kernel for Oracle Linux 6 ($basearch) - Unsupported
    baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/playground/latest/$basearch/
    gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6
    gpgcheck=1
    enabled=1
    

    Note "playground" is an unsupported channel and NOT for PRODUCTION use. It contains the DTrace-enabled PHP 5.5.4 RPMS.

Install PHP

  1. Install PHP and the PHP OCI8 extension:

    # yum install php55 php55-oci8-12cR1
    
    [. . .]
    
    ================================================================================
     Package            Arch     Version       Repository                      Size
    ================================================================================
    Installing:
     php55              x86_64   5.5.4-1.el6   public_ol6_playground_latest   1.4 M
     php55-oci8-12cR1   x86_64   5.5.4-3.el6   public_ol6_playground_latest   148 k
    Installing for dependencies:
     php55-cli          x86_64   5.5.4-1.el6   public_ol6_playground_latest   2.7 M
     php55-common       x86_64   5.5.4-1.el6   public_ol6_playground_latest   538 k
    
    Transaction Summary
    ================================================================================
    Install       4 Package(s)
    
    Total download size: 4.8 M
    Installed size: 17 M
    Is this ok [y/N]: y
    

    Other PHP packages are available too, but only core PHP and the PHP OCI8 extension have DTrace probes.

    Note that with the playground channel enabled, yum update will install a 3.11 kernel. If you reboot, make sure to select the 3.8.13 kernel for DTrace testing.

  2. Restart Apache:

    # service httpd restart

Use PHP and DTrace

  1. Create a PHP script, /var/www/html/oci8.php

    <?php
    
    error_reporting(E_ALL);
    ini_set('display_errors', 'On');
    
    function do_query($c, $sql)
    {
      $s = oci_parse($c, $sql);
      if (!$s)
        return;
      $r = oci_execute($s);
      if (!$r)
        return;
      echo "<table>\n";
      while (($row = oci_fetch_row($s)) != false) {
        echo "<tr>\n";
        foreach ($row as $item) {
          echo "<td>";
          echo $item!==null?htmlentities($item, ENT_QUOTES|ENT_SUBSTITUTE):"&nbsp;";
          echo "</td>\n";
        }
        echo "</tr>\n";
      }
      echo "</table>\n";
    }
    
    $c = oci_connect('hr', 'welcome', 'localhost/pdborcl');
    oci_set_client_identifier($c, "Chris");
    
    do_query($c, "select city from locations where rownum < 5 order by 1");
    
    ?>
    

    Change the connection credentials to those of your existing Oracle Database.

  2. Create a D script to trace the PHP OCI8 2.0.4 Probes, user_oci8.d:

    #!/usr/sbin/dtrace -Zqs
    
    php*:::oci8-connect-entry
    {
        printf("%lld: PHP connect-entry ", walltimestamp);
        printf("credentials=\"%s@", arg0 ? copyinstr(arg0) : "");
        printf("dbname=%s\" ", arg1 ? copyinstr(arg1) : "");
        printf("charset=\"%s\" ", arg2 ? copyinstr(arg2) : "");
        printf("session_mode=%ld ", (long)arg3);
        printf("persistent=%d ", (int)arg4);
        printf("exclusive=%d\n", (int)arg5);
    }
    
    php*:::oci8-connect-return
    {
        printf("%lld: PHP oci8-connect-return ", walltimestamp);
        printf("connection=0x%p\n", (void *)arg0);
    }
    
    php*:::oci8-connection-close
    {
        printf("%lld: PHP oci8-connect-close ", walltimestamp);
        printf("connection=0x%p\n", (void *)arg0);
    }
    
    php*:::oci8-error
    {
        printf("%lld: PHP oci8-error ", walltimestamp);
        printf("status=%d ", (int)arg0);
        printf("errcode=%ld\n", (long)arg1);
    }
    
    php*:::oci8-check-connection
    {
        printf("%lld: PHP oci8-check-connection ", walltimestamp);
        printf("connection=0x%p ", (void *)arg0);
        printf("client_id=\"%s\" ", arg1 ? copyinstr(arg1) : "");
        printf("is_open=%d ", arg2);
        printf("errcode=%ld ", (long)arg3);
        printf("server_status=%lu\n", (unsigned long)arg4);
    }
    
    php*:::oci8-sqltext
    {
        printf("%lld: PHP oci8-sqltext ", walltimestamp);
        printf("connection=0x%p ", (void *)arg0);
        printf("client_id=\"%s\" ", arg1 ? copyinstr(arg1) : "");
        printf("statement=0x%p ", (void *)arg2);
        printf("sql=\"%s\"\n", arg3 ? copyinstr(arg3) : "");
    }
    
    php*:::oci8-execute-mode
    {
        printf("%lld: PHP oci8-execute-mode ", walltimestamp);
        printf("connection=0x%p ", (void *)arg0);
        printf("client_id=\"%s\" ", arg1 ? copyinstr(arg1) : "");
        printf("statement=0x%p ", (void *)arg2);
        printf("mode=0x%x\n", arg3);
    }
    

    This prints each probe's arguments on a single, timestamp-prefixed line, which helps post-sorting the output in time order.

    The PHP OCI8 2.0.4 probe arguments have been enhanced from the PHP OCI8 2.0.2 probes described in Using PHP DTrace on Oracle Linux .

    If you want to trace the core PHP probes, they are described here.

  3. Start the D script:

    # ./user_oci8.d
    

    This will wait for probes to be fired. When you have finished testing, you can ^C this window.

  4. In a browser, load the PHP file: http://localhost/oci8.php. The web page will show query results. The probes that the D script used will fire and generate output (which I've manually wrapped to display cleanly in this blog post):

    # ./user_oci8.d
    1380749137317414976: PHP connect-entry
                         credentials="hr@localhost/pdborcl"
                         charset="" session_mode=0 persistent=0 exclusive=0
    1380749137354121877: PHP oci8-connect-return
                         connection=0x7f453c087b68
    1380749137354326373: PHP oci8-sqltext
                         connection=0x7f453c087b68 client_id="Chris"
                         statement=0x7f453c086f08
                         sql="select city from locations where rownum < 5 order by 1"
    1380749137354467732: PHP oci8-execute-mode
                         connection=0x7f453c087b68 client_id="Chris"
                         statement=0x7f453c086f08 mode=0x20
    1380749137355886348: PHP oci8-connect-close
                         connection=0x7f453c087b68
    ^C
    

    The client identifier ("Chris") is a way for the application to tell the database which web user is executing statements, since commonly the database user ("hr") is the same for all web users.

    In addition to all the database monitoring that you do for a given client identifier, with DTrace you can use the client identifier to trace a particular user on the PHP side of the DB connection. To do this, your D script might contain::

    #!/usr/sbin/dtrace -Zqs
    
    php*:::oci8-sqltext
    / (char *)arg1 == "Chris" /
    {
        printf("%lld: PHP oci8-sqltext ", walltimestamp);
        printf("connection=0x%p ", (void *)arg0);
        printf("client_id=\"%s\" ", arg1 ? copyinstr(arg1) : "");
        printf("statement=0x%p ", (void *)arg2);
        printf("sql=\"%s\"\n", arg3 ? copyinstr(arg3) : "");
    }
    
    php*:::oci8-execute-mode
    / (char *)arg1 == "Chris" /
    {
        printf("%lld: PHP oci8-execute-mode ", walltimestamp);
        printf("connection=0x%p ", (void *)arg0);
        printf("client_id=\"%s\" ", arg1 ? copyinstr(arg1) : "");
        printf("statement=0x%p ", (void *)arg2);
        printf("mode=0x%x\n", arg3);
    }
    

    The probes will only fire when the specified client identifier was "Chris". This will be useful for debugging on a busy production machine (once UEK3 becomes "production"). You can trace only the statements that your test user is running.

Summary

DTrace is an 'always available' tracing utility useful for identifying performance and behavior issues in applications on Solaris and Oracle Linux. The PHP core and PHP OCI8 extensions have user probes that can be traced to efficiently identify PHP scripting problems.

Saturday Sep 21, 2013

Come and Join us in San Francisco this Week

Come and Join us in San Francisco this Week

It's the week of the huge Oracle OpenWorld, JavaOne and MySQL Connect conferences in San Francisco. The week will be a blast with so many things happening.

The easiest way to find sessions is to browse the Content Catalog

Followers of this blog might want to think about these sessions:

There are lots of interesting language talks at JavaOne worth considering - search the Content Catalog.

Finally, drop by the Oracle Database "Database Access APIs" demo pod 3591 in the Moscone South exhibition hall and say Hi.

About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Links: OTN Node.js Developer Center
OTN PHP Developer Center
Book: Free PHP Oracle book

Search

Archives
« May 2015
SunMonTueWedThuFriSat
     
1
2
3
4
5
6
7
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
      
Today