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.

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
Book: Free PHP Oracle book
Download: PHP Linux RPMs with the OCI8 extension
Links: OTN PHP Developer Center

Search

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