Monday Dec 08, 2014

Video: Best Practices for Application Performance, Scalability, and Availability

Nancy Ikeda nails it in a great Oracle OpenWorld recording of her Best Practices for Application Performance, Scalability, and Availability session now viewable on the Oracle Call Interface page

The session covered:

Best practice coding samples and techniques show how to resolve connection management, statement execution, and data fetching inefficiencies in applications using APIs such as JDBC, OCI, ODBC, ODP.Net, or higher-level scripting languages. This session shows how the Automatic Workload Repository feature of Oracle Database and Automatic Database Diagnostic Monitor profiling tools help diagnose application design and coding issues. Specific solutions show how to resolve these and other issues to enhance applications for scalability and resilience. Among the solutions discussed are Oracle Database 12c’s new client configuration file. Developers or DBAs can use it to tune and configure applications without modifying code. Examples use JDBC and OCI but are applicable to all APIs.

Nancy is one of Oracle's senior developers working in the call interface group.

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");
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

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

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

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

Monday Sep 09, 2013

Using PHP DTrace on Oracle Linux

This post shows PHP and DTrace "dynamic tracing" in action on Oracle Linux. It follows my previous post on recent PHP patches to stabilize DTrace support.

Install Oracle Linux and the UEK3 Kernel

  1. The starting point is to install Oracle Linux 6.4 from Oracle eDelivery. Wim Coekaerts blogged about the UEK3 release. I'm going to quote a paragraph from Wim here because it is fundamental to understanding Oracle Linux's direction:

    Oracle Linux is freely downloadable from Oracle Linux is free to use on as many systems as you want, is freely re-distributable without changing the CD/ISO content (so including our cute penguin), provides free security errata and bugfix errata updates. You only need to pay for a support subscription for those systems that you want/need support for, not for other systems. This allows our customers/users to run the exact same software on test and dev systems as well as production systems without having to maintain potentially two kinds of repositories. All systems can run the exact same software all the time.

  2. Once OL 6.4 is installed, add the Beta repo with:

    # cd /etc/yum.repos.d
    # mv public-yum-ol6.repo public-yum-ol6.repo.disabled
    # wget
  3. Enable the UEK3 Beta channel by editing public-yum-ol6-beta.repo and setting "enabled" to 1.

  4. Install the UEK3 kernel, which supports DTrace:

    # yum update
  5. Install the DTrace utilities:

    # yum install dtrace-utils
  6. Reboot to the new UEK3 3.8.13 kernel

Install PHP

[Update: see DTrace PHP Using Oracle Linux 'playground' Pre-Built Packages for some pre-built PHP RPMs.]

  1. Download a PHP snapshot (or PHP 5.4.20 or PHP 5.5.4, when they become available) from and extract it:

    $ tar -xJf php5.5-201309042230.tar.xz
    $ cd php5.5-201309042230
  2. Configure PHP:

    $ ./configure \
      --prefix=$HOME/p55 \
      --enable-dtrace \
      --disable-all --disable-cgi \
      --with-pear --enable-xml --enable-libxml --with-zlib

    This builds a minimal command line PHP with DTrace enabled. All unwanted extensions are disabled. You can include other extensions as needed. Currently PHP DTrace testing is limited to command-line use because a UEK3 DTrace fix for forked environments wasn't available at the time of the UEK3 Beta 1 release.

    The --prefix option puts the installation into a local directory, which makes it easy to see the files installed. It is easy to cleanup this directory when finished with the snapshot.

    The PEAR, XML and Zlib options allow the use of the 'pecl' command.

  3. Make the PHP binary and install it:

    $ make
    $ make install
  4. Copy php.ini-development to $HOME/p55/lib/php.ini and edit it to set the timezone, for example:

    date.timezone = America/Los_Angeles

Install PHP OCI8 for Oracle Database

To connect to Oracle Database, add PHP OCI8 as a "shared" extension:

  1. Download Oracle Instant Client "basic" and "devel" RPMs from ULN (for ULN subscribers) or OTN. You can use the 10g, 11g or 12c versions.

  2. Install Instant Client as root:

    # rpm -Uvh oracle-instantclient12.1-basic-
    # rmp -Uvh oracle-instantclient12.1-devel-
  3. As a normal user, set PATH so PHP is found:

    $ export PATH=$HOME/p55/bin:$PATH
  4. Set a PEAR proxy, if needed for access to

    $ pear config-set http_proxy
  5. Set an environment variable PHP_DTRACE to enable DTrace, and install PHP OCI8:

    $ PHP_DTRACE=yes pecl install oci8-2.0.2

    The DTrace probes definitions used later in this article are based on PHP OCI8 2.0.2, so that explicit version is installed. If you install any future, later version review the probes and their arguments for differences. Note PHP OCI8 2.0 is in "development" status so changes are likely.

    When prompted for the ORACLE_HOME directory, hit return without entering text. The installation will autodetect the Instant Client RPMs. Configuration will continue and the output will contain something like:

    [ . . . ]
    checking for Oracle Database OCI8 support... yes, shared
    checking PHP version... 5.5.4, ok
    checking OCI8 DTrace support... yes
    [ . . . ]
    configure: WARNING: OCI8 extension: ORACLE_HOME is not set,
        looking for default Oracle Instant Client instead
    checking Oracle Instant Client directory...
    checking Oracle Instant Client SDK header directory...
    checking Oracle Instant Client library version compatibility... 12.1
    [ . . . ]
  6. Edit php.ini again and add PHP OCI8:
  7. Confirm the installation:

    $ php --ri oci8
    OCI8 Support => enabled
    OCI8 DTrace Support => enabled
    OCI8 Version => 2.0.2-dev
    Revision => $Id: b30fb4bef45d9f5ce8a56b736f1546ea0cff08ef $
    Oracle Run-time Client Library Version =>
    Oracle Compile-time Instant Client Version => 12.1
    Directive => Local Value => Master Value
    oci8.max_persistent => -1 => -1
    oci8.persistent_timeout => -1 => -1
    oci8.ping_interval => 60 => 60
    oci8.privileged_connect => Off => Off
    oci8.statement_cache_size => 20 => 20
    oci8.default_prefetch => 100 => 100
    oci8.old_oci_close_semantics => Off => Off
    oci8.connection_class => no value => no value => Off => Off
    Statistics =>  
    Active Persistent Connections => 0
    Active Connections => 0

PHP OCI8 Installation Notes

For DTrace support, PHP OCI8 2.0 needs to be installed from PECL because PHP 5.4 and PHP 5.5 have PHP OCI8 1.4, which doesn't have DTrace probes. In future, when PHP 5.6 (or whatever comes after 5.5) is released, you will be able to configure a DTrace-enabled PHP OCI8 while building PHP.

You can, of course, install PHP OCI8 with Instant Client ZIP files, or simply use an existing ORACLE_HOME install.

You can DTrace-enable PHP OCI8 on a version of PHP that doesn't have DTrace available or configured. This includes older versions of PHP. You will be able to trace the PHP OCI8 probes but not any core PHP probes. Similarly you can install a DTrace-disabled PHP OCI8 on DTrace-enabled PHP.

If you install PHP OCI8 2.0 from PECL using 'phpize' and 'configure' (instead of 'pecl'), you will still need to set PHP_DTRACE=yes. This is because the --enable-dtrace option will be ignored by the limited 'configure' script of a PECL bundle.

The PHP OCI8 2.0 configuration script is suitable for "real" DTrace use but Linux SystemTap will not trace the extension.

Note that DTracing optimized binaries might give output that is not quite expected from code observation.

Verify the PHP DTrace Probes

  1. As root, enable DTrace and allow normal users to record trace information:

    # 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.

  2. As a normal user, run php without any options. It will start and wait for input:

    $ php
  3. As root, list the DTrace probes that are available. Both PHP core and PHP OCI8 probes are listed:

    # dtrace -l -m php -m
     4 php9559     php              dtrace_compile_file compile-file-entry
     5 php9559     php              dtrace_compile_file compile-file-return
     6 php9559     php                       zend_error error
     7 php9559     php ZEND_CATCH_SPEC_CONST_CV_HANDLER exception-caught
     8 php9559     php    zend_throw_exception_internal exception-thrown
     9 php9559     php                dtrace_execute_ex execute-entry
    10 php9559     php          dtrace_execute_internal execute-entry
    11 php9559     php                dtrace_execute_ex execute-return
    12 php9559     php          dtrace_execute_internal execute-return
    13 php9559     php                dtrace_execute_ex function-entry
    14 php9559     php                dtrace_execute_ex function-return
    15 php9559     php             php_request_shutdown request-shutdown
    16 php9559     php              php_request_startup request-startup
    17 php9559  php_oci_dtrace_check_connection oci8-check-connection
    18 php9559               php_oci_do_connect oci8-connect-entry
    19 php9559        php_oci_persistent_helper oci8-connect-expiry
    20 php9559            php_oci_do_connect_ex oci8-connect-lookup
    21 php9559 php_oci_pconnection_list_np_dtor oci8-connect-p-dtor-close
    22 php9559 php_oci_pconnection_list_np_dtor oci8-connect-p-dtor-release
    23 php9559               php_oci_do_connect oci8-connect-return
    24 php9559            php_oci_do_connect_ex oci8-connect-type
    25 php9559                    php_oci_error oci8-error
    26 php9559        php_oci_statement_execute oci8-execute-mode
    27 php9559             php_oci_create_spool oci8-sesspool-create
    28 php9559           php_oci_create_session oci8-sesspool-stats
    29 php9559           php_oci_create_session oci8-sesspool-type
    30 php9559         php_oci_statement_create oci8-sqltext

    The core PHP probes are documented here. ThePHP OCI8 probes are described below.

  4. In your user terminal, stop the php executable with Ctrl-C.

    $ php

PHP OCI8 2.0 DTrace Probe Overview

The static PHP OCI8 2.0 probes can be categorized as "user" probes and "maintainer" probes. The latter that are more useful for PHP OCI8 maintainers to verify functionality during development of the extension itself. All the probes return data in arguments.

User Probes are:

  • oci8-connect-entry - initiated by oci_connect(), oci_pconnect() and oci_new_connect(). Fires before database connection is established.
    • char *username - the connection username
    • char *dbname - the database connection string
    • char *charset - the character set specified
    • long session_mode - A binary "or" of OCI_SYSDBA (0x2), OCI_SYSOPER (0x4) and OCI_CRED_EXT (1<<31, or -2147483648 on the platform I was using). Set to 0 by default.
    • int persistent - set to 1 if oci_pconnect() was called, 0 otherwise
    • int exclusive - set to 1 if oci_new_connect() was called, 0 otherwise
  • oci8-connect-return - fires at the end of connection.
    • void *connection - the address of the connection structure
  • oci8-check-connection - initiated if an Oracle error might have caused the connection to become invalid
    • void *connection - the address of the connection structure
    • int is_open - will be 0 if the errcode or server_status indicate the connection is invalid and must be recreated.
    • long errcode - the Oracle error number
    • unsigned long server_status - an indicator from the Oracle library if the connection is considered invalid. If is_open is 0 because errcode indicated the connection was invalid, then server_status will be its default of 1.
  • oci8-sqltext - initiated when oci_parse() is executed
    • void *connection - the address of the connection structure
    • char *sql - text of the SQL statement executed
  • oci8-error - initiated if an Oracle error occurs
    • int status - the Oracle return status of the failing Oracle library call, such as -1 for Oracle's OCI_ERROR or 1 for Oracle's OCI_SUCCESS_WITH_INFO. See Oracle's oci.h for all definitions.
    • long errcode - the Oracle error number
  • oci8-execute-mode - indicates the commit state of an oci_execute() call
    • void *connection - the address of the connection structure
    • unsigned int mode - the mode passed to the Oracle library such as OCI_NO_AUTO_COMMIT (0x00), OCI_DESCRIBE_ONLY (0x10) or OCI_COMMIT_ON_SUCCESS (0x20)

Maintainer probes are below. Refer to the PHP OCI8 source code for the argument descriptions:

  • oci8-connect-p-dtor-close
    • void *connection
  • oci8-connect-p-dtor-release
    • void *connection
  • oci8-connect-lookup
    • void *connection
    • int is_stub
  • oci8-connect-expiry
    • void *connection
    • int is_stub
    • long idle_expiry
    • long timestamp
  • oci8-connect-type
    • int persistent
    • int exclusive
    • void *connection
    • long num_persistent
    • long num_connections
  • oci8-sesspool-create
    • void *session_pool
  • oci8-sesspool-stats
    • unsigned long free
    • unsigned long busy
    • unsigned long open
  • oci8-sesspool-type
    • int type
    • void *session_pool

The PHP OCI8 probes are highly likely to be extended prior to PHP OCI8 2.0 being marked "production". The PHP OCI8 documentation will be updated only at that time, but you can check the oci8_dtrace.d file in the PHP OCI8 source code to see the probe arguments for your version. (Update: The documentation is here).

The probes in PHP OCI8 2.0 replace PHP OCI8 1.4's use of oci_internal_debug() tracing. This function has become a no-op.

Using PHP OCI8 and DTrace

Follow these steps.

  1. Create a simple PHP file, oci8.php, to query the database:

    ini_set('display_errors', 'Off');
    function do_query($c, $sql)
        $s = oci_parse($c, $sql);
        if (!$s)
        $r = oci_execute($s);
        if (!$r)
        while (($row = oci_fetch_row($s)) != false) {
            foreach ($row as $item) {
                echo $item . " ";
            echo "\n";
    $c = oci_new_connect('hr', 'welcome', 'localhost/pdborcl');
    do_query($c, "select city from locations where rownum < 5 order by 1");
    do_query($c, "select something from does_not_exist");
  2. Create a D script, user_oci8.d, to probe the execution of oci8.php:

    #!/usr/sbin/dtrace -Zs
        printf("PHP connect-entry\n");
        printf("\t   username      %s\n", arg0 ? copyinstr(arg0) : "");
        printf("\t   dbname        %s\n", arg1 ? copyinstr(arg1) : "");
        printf("\t   charset       %s\n", arg2 ? copyinstr(arg2) : "");
        printf("\t   session_mode  %ld\n", (long)arg3);
        printf("\t   persistent    %d\n", (int)arg4);
        printf("\t   exclusive     %d\n", (int)arg5);
        printf("PHP oci8-connect-return\n");
        printf("\t   connection    0x%p\n", (void *)arg0);
        printf("PHP oci8-connect-close\n");
        printf("\t   connection    0x%p\n", (void *)arg0);
        printf("PHP oci8-error\n");
        printf("\t   status        %d\n", (int)arg0);
        printf("\t   errcode       %ld\n", (long)arg1);
        printf("PHP oci8-check-connection\n");
        printf("\t   connection    0x%p\n", (void *)arg0);
        printf("\t   is_open       %d\n", arg1);
        printf("\t   errcode       %ld\n", (long)arg2);
        printf("\t   server_status %lu\n", (unsigned long)arg3);
        printf("PHP oci8-sqltext\n");
        printf("\t   connection    0x%p\n", (void *)arg0);
        printf("\t   sql           %s\n", arg0 ? copyinstr(arg1) : "");
        printf("PHP oci8-execute-mode\n");
        printf("\t   connection    0x%p\n", (void *)arg0);
        printf("\t   mode          0x%x\n", arg1);
  3. As root, start the D script. It will pause, waiting for probes to be fired:

    # chmod +x user_oci8.d
    # ./user_oci8.d

    (Later, this terminal can be Ctrl-C'd when you have finished experimenting with PHP)

  4. Run command-line PHP in another window. The output from the successful query is displayed:

    $ php oci8.php 
  5. In the root terminal running the D script, the probes firing during execution of PHP will be displayed:

    # ./user_oci8.d
    dtrace: script 'user_oci8.d' matched 0 probes
    CPU  ID                    FUNCTION:NAME
    1    18 php_oci_do_connect:oci8-connect-entry PHP connect-entry
            username      hr
            dbname        localhost/pdborcl
            session_mode  0
            persistent    0
            exclusive     0
    0    23 php_oci_do_connect:oci8-connect-return PHP oci8-connect-return
            connection    0x7f64e112cff0
    0    31 php_oci_statement_create:oci8-sqltext PHP oci8-sqltext
            connection    0x7f64e112cff0
            sql           select city from locations where rownum < 5 order by 1
    0    27 php_oci_statement_execute:oci8-execute-mode PHP oci8-execute-mode
            connection    0x7f64e112cff0
            mode          0x20
    0    31 php_oci_statement_create:oci8-sqltext PHP oci8-sqltext
            connection    0x7f64e112cff0
            sql           select something from does_not_exist
    0    27 php_oci_statement_execute:oci8-execute-mode PHP oci8-execute-mode
            connection    0x7f64e112cff0
            mode          0x20
    0    26 php_oci_error:oci8-error PHP oci8-error
            status        -1
            errcode       942
    0    17 php_oci_dtrace_check_connection:oci8-check-connection PHP oci8-check-connection
            connection    0x7f64e112cff0
            is_open       1
            errcode       942
            server_status 1
    0    25 php_oci_connection_close:oci8-connection-close PHP oci8-connect-close
             connection    0x7f64e112cff0

    (Adding "-q" to the /usr/sbin/dtrace arguments in user_oci8.d will suppress the CPU and ID details.)

    On multi-CPU machines the probe ordering might not appear sequential, depending on which CPU was processing the probes. Displaying probe timestamps will help reduce confusion, for example:

        printf("PHP connect-entry at %lld\n", walltimestamp);

    From the user_oci8.d DTrace output, you can see

    • The connection being initiated (oci8-connect-entry). The user 'hr' connected to the 'localhost/pdborcl' database. It was an oci_connect() call because both 'exclusive' and 'persistent' were 0. No explicit character set was requested. The default session mode (the optional fifth parameter to oci_connect) was requested.

    • Two SQL statements being parsed (oci8-sqltext) and executed (oci-execute-mode) with mode 0x20 aka OCI_COMMIT_ON_SUCCESS.

    • An Oracle error ORA-942 "table or view does not exist" was generated (oci8-error)

    • The error causing the connection status to be verified (oci8-check-connection). The value of is_open is 1, indicating that the connection is OK.

    With this information you can trace problematic statement execution and connection issues.


This is just a morsel about using DTrace, which is a very powerful utility. Following on from the example above, you could integrate PHP OCI8 tracing with core PHP tracing. Bryan Cantrill posted some examples of core PHP tracing in DTrace and PHP, demonstrated (Note that blog platform upgrades have caused single backslashes to display as double backslashes in his post. Also you no longer need the separate PHP DTrace extension). To explore more DTrace power look on the web for example scripts. There are various blogs too.

Remember that the intent of DTrace is that its functionality is enabled all the time, suitable for development and ready for when you need it most: in production. The design of DTrace means that the probes have zero overhead when nothing is monitoring them.

Finally, as I write this article, I can already see how the PHP OCI8 probes can be enhanced (perhaps to display the connection client identifier to aid end-to-end tracing through the Oracle stack.) Oracle Linux support for DTrace should be improving all the time, too. The power of DTrace on Linux is growing and it's time to think about incorporating it into your application life cycle.

Updated 26 Sep 2013 to mention using an ACL rule for /dev/dtrace/helper

Thursday May 16, 2013

Offline Processing in PHP with Advanced Queuing

Offloading slow batch tasks to an external process is a common method of improving website responsiveness. One great way to initiate such background tasks in PHP is to use Oracle Streams Advanced Queuing in a producer-consumer message passing fashion. Oracle AQ is highly configurable. Messages can queued by multiple producers. Different consumers can filter messages. From PHP, the PL/SQL interface to AQ is used. There are also Java, C and HTTPS interfaces, allowing wide architectural freedom. Oracle Advanced Queuing is included in all editions of the database.

The following example simulates an application user registration system where the PHP application queues each new user's street address. An external system monitoring the queue can then fetch and process that address. In real life the external system might initiate a snail-mail welcome letter, or do further, slower automated validation on the address.

The following SQL*Plus script qcreate.sql creates a new Oracle user demoqueue with permission to create and use queues. A payload type for the address is created and a queue is set up for this payload.

-- qcreate.sql

connect / as sysdba
drop user demoqueue cascade;

create user demoqueue identified by welcome;
grant connect, resource to demoqueue;
grant aq_administrator_role, aq_user_role to demoqueue;
grant execute on dbms_aq to demoqueue;
grant create type to demoqueue;

connect demoqueue/welcome@localhost/orcl

-- The data we want to queue
create or replace type user_address_type as object (
  name        varchar2(10),
  address     varchar2(50)

-- Create and start the queue
   queue_table        =>  'demoqueue.addr_queue_tab',
   queue_payload_type =>  'demoqueue.user_address_type');

   queue_name         =>  'demoqueue.addr_queue',
   queue_table        =>  'demoqueue.addr_queue_tab');

   queue_name         => 'demoqueue.addr_queue',
   enqueue            => true);

The script qhelper.sql creates two useful helper functions to enqueue and dequeue messages:

-- qhelper.sql
-- Helpful address enqueue/dequeue procedures

connect demoqueue/welcome@localhost/orcl

-- Put an address in the queue
create or replace procedure my_enq(name_p in varchar2, address_p in varchar2) as
  user_address       user_address_type;
  enqueue_options    dbms_aq.enqueue_options_t;
  message_properties dbms_aq.message_properties_t;
  enq_id             raw(16);
  user_address := user_address_type(name_p, address_p);
  dbms_aq.enqueue(queue_name         => 'demoqueue.addr_queue',
                  enqueue_options    => enqueue_options,
                  message_properties => message_properties,
                  payload            => user_address,
                  msgid              => enq_id);
show errors

-- Get an address from the queue
create or replace procedure my_deq(name_p out varchar2, address_p out varchar2) as
  dequeue_options    dbms_aq.dequeue_options_t;
  message_properties dbms_aq.message_properties_t;
  user_address       user_address_type;
  enq_id             raw(16);
  dbms_aq.dequeue(queue_name         => 'demoqueue.addr_queue',
                  dequeue_options    => dequeue_options,
                  message_properties => message_properties,
                  payload            => user_address,
                  msgid              => enq_id);
  name_p    :=;
  address_p := user_address.address;
show errors

The script newuser.php is the part of the PHP application that handles site registration for a new user. It queues a message containing their address and continues executing:

// newuser.php

$c = oci_connect("demoqueue", "welcome", "localhost/orcl");

// The new user details
$username = 'Fred';
$address  = '500 Oracle Parkway';

// Enqueue the address for later offline handling
$s = oci_parse($c, "begin my_enq(:username, :address); end;");
oci_bind_by_name($s, ":username", $username, 10);
oci_bind_by_name($s, ":address",  $address,  50);
$r = oci_execute($s);

// Continue executing
echo "Welcome $username\n";


It executes an anonymous PL/SQL block to create and enqueue the address message. The immediate script output is simply the echoed welcome message:

Welcome Fred

Once this PHP script is executed, any application can dequeue the new message at its leisure. For example, the following SQL*Plus commands call the helper my_deq() dequeue function and displays the user details:

-- getuser.sql

connect demoqueue/welcome@localhost/orcl

set serveroutput on
  name varchar2(10);
  address varchar2(50);
  my_deq(name, address);
  dbms_output.put_line('Name     : ' || name);
  dbms_output.put_line('Address  : ' || address);

The output is:

Name     : Fred
Address  : 500 Oracle Parkway

If you instead want to check the queue from PHP, use getuser.php:

// getuser.php

$c = oci_connect("demoqueue", "welcome", "localhost/orcl");

// dequeue the message
$sql = "begin my_deq(:username, :address); end;";
$s = oci_parse($c, $sql);
oci_bind_by_name($s, ":username", $username, 10);
oci_bind_by_name($s, ":address", $address, 50);
$r = oci_execute($s);

echo "Name     : $username\n";
echo "Address  : $address\n";


If the dequeue operation is called without anything in the queue, it will block waiting for a message until the queue wait time expires. This is configurable by setting a zero wait time dequeue_options.wait := 0; before calling dbms_aq.dequeue.

The PL/SQL API has much more functionality than shown in this overview. For example you can enqueue an array of messages, or listen to more than one queue. Queuing is highly configurable and scalable, providing a great way to distribute workload for web or mobile applications. More information about AQ is in the Oracle Streams Advanced Queuing User's Guide.

Bootnote: The basis for this blog post comes from the Underground PHP and Oracle Manual

This post was updated to show setting a zero wait time.

Thursday Mar 21, 2013

Python cx_Oracle and Oracle 11g DRCP Connection Pooling

The topic of Oracle 11g DRCP connection pooling in Python cx_Oracle came up twice this week for me. DRCP is a database tier connection pooling solution which is great for applications run in multiple processes. There is a whitepaper on DRCP that covers a lot of background and talks about configuration. This whitepaper is ostensibly about PHP but is good reading for all DRCP users.

The first DRCP and cx_Oracle scenario I dealt with was a question about mod_python.

To cut a long story short, I created a handler and requested it 1000 times via Apache's 'ab' tool. In my first script, and despite having increased the default pool parameters, there were a high number of NUM_WAITS. Also NUM_AUTHENTICATIONS was high. Performance wasn't the best. Querying V$CPOOL_CC_STATS showed:

------------ ------------ ---------- ---------- ---------- -------------------
HR.CJDEMO1           1000        992          8        478                1000

At least the session information in each DRCP server was reused (shown via a high NUM_HITS).

Results were better after fixing the script to look like:

from mod_python import apache
import cx_Oracle
import datetime

# Example: Oracle 11g DRCP with cx_Oracle and mod_python

# These pool params are suitable for Apache Pre-fork MPM
mypool = cx_Oracle.SessionPool(user='hr', password='welcome',
         dsn='localhost/orcl:pooled', min=1, max=2, increment=1)

def handler(req):
    global mypool

    req.content_type = 'text/html'
    n =
    req.write (str(n) + "<br>");

    db = cx_Oracle.connect(user='hr', password='welcome',
            dsn='localhost/orcl:pooled', pool=mypool, cclass="CJDEMO1",

    cur = db.cursor()
    cur.execute('select * from locations')
    resultset = cur.fetchall()
    for result in resultset:
        for item in result:
            req.write (str(item) + " ")
        req.write ("<br>")

    return apache.OK

The 'ab' benchmark on this script ran much faster and the stats from V$CPOOL_CC_STATS looked much better. The number of authentications was right down about to about 1 per Apache (ie. mod_python) process:

------------ ------------ ---------- ---------- ---------- -------------------
HR.CJDEMO1           1000        977         23         13                  26

The NUM_HITS was high again, because the DRCP purity was ATTR_PURITY_SELF. If I hadn't wanted session information to be reused each time the handler was executed, I could have set the purity to ATTR_PURITY_NEW. If I'd done this then NUM_HITS would have been low and NUM_MISSES would have been high.

If you're testing this yourself, before restarting the DRCP pool don't forget to shutdown Apache to close all DB connections. Otherwise restarting the pool will block. Also, if you're interpreting your own V$CPOOL_CC_STATS stats don't forget to account for the DRCP "dedicated optimization" that retains an association between clients (mod_python processes) and the DB. The whitepaper previously mentioned discusses this.

The second place where DRCP and python came up this week was on the cx_Oracle mail list. David Stanek posed a question. He was seeing application processes blocking while waiting for a DRCP pooled server to execute a query. My variant of David's script is

import os
import time
import cx_Oracle

# Example: Sub-optimal connection pooling with Oracle 11g DRCP and cx_Oracle

def do_connection():
    print 'Starting do_connection ' + str(os.getpid())
    con = cx_Oracle.connect(user=user, password=pw, dsn=dsn, cclass="CJDEMO2",
    cur = con.cursor()
    print 'Querying ' + str(os.getpid())
    cur.execute("select to_char(systimestamp) from dual")
    print cur.fetchall()
    print 'Sleeping ' + str(os.getpid())
    print 'Finishing do_connection ' + str(os.getpid())
user = 'hr'
pw = 'welcome'
dsn = 'localhost/orcl:pooled'
for x in range(100):
    pid = os.fork()
    if not pid:

This script forks off a bunch of processes - more than the number of pooled DRCP servers (see MAXSIZE in the DBA_CPOOL_INFO view). The first few processes grab a DRCP server from the pool and do their query. But they don't release the DRCP server back to the DRCP pool until after the sleep() when the process ends. The other forked processes are blocked waiting for those DRCP servers to become available. This isn't optimal pool sharing.

My suggestion was to use an explicit cx_Oracle session pool like

import os
import time
import cx_Oracle

# Example: Connection pooling with Oracle 11g DRCP and cx_Oracle
def do_connection():
    print 'Starting do_connection ' + str(os.getpid())
    mypool = cx_Oracle.SessionPool(user=user,password=pw,dsn=dsn,min=1,max=2,increment=1)
    con = cx_Oracle.connect(user=user, password=pw,
          dsn=dsn, pool = mypool, cclass="CJDEMO3", purity=cx_Oracle.ATTR_PURITY_SELF)
    cur = con.cursor()
    print 'Querying ' + str(os.getpid())
    cur.execute("select to_char(systimestamp) from dual")
    print cur.fetchall()
    print 'Sleeping ' + str(os.getpid())
    print 'Finishing do_connection ' + str(os.getpid())

user = 'hr'
pw = 'welcome'
dsn = 'localhost/orcl:pooled'
for x in range(100):
    pid = os.fork()
    if not pid:

The mypool.release(con) call releases the DRCP server back to the DRCP pool prior to the sleep. When this second script is run, there is a smoothness to the output. The queries happen sequentially without noticeably being blocked.

Like with any shared resource, it is recommended to release DRCP pooled servers back to the pool when they are no longer needed by the application.

Update August 2014: Oracle submitted a cx_Oracle patch which Anthony has merged but not yet released. This patch now allows to work efficiently. For the fork scenario the non-explicit session pool solution in is likely to be better than, since it does fewer DRCP requests. For the mod_python example, continuing to use a session pool as shown in can minimize the number of authentications, which helps scalability.

Friday Mar 15, 2013

Using PHP 5.5's New "OPcache" Opcode Cache

Zend have contributed their Zend Optimizer+ opcode cache to PHP - thanks Zend!!! (Update 19 March 2013: the renaming to "Zend OPcache" is complete)

"The Zend OPcache provides faster PHP execution through opcode caching and optimization."

The new OPcache extension can be seen as substitute for the venerable APC cache, the maintenance of which had become an issue. Note: although OPcache is now readily available, there is currently nothing preventing you from using any available (working!) opcode cache in PHP 5.5.

A few minutes ago Dmitry Stogov did the physical merge to the PHP 5.5 source's ext/opcache directory. The current PHP 5.5 snapshot has the code. Future Alpha or Beta (and Production) releases will include it too.

Please test OPcache. It is not a panacea for all performance problems. There are a lot of settings which may need adjusting. Understanding how it works and identifying issues during the stabilization phase of PHP 5.5 release process will greatly help.

To build Zend OPcache for PHP 5.5:

When you configure PHP, add the option --enable-opcache like:

./configure ... --enable-opcache

Then make and make install, as normal. This will build OPcache shared extension. It's not possible to build it statically.

Find the shared library in your installation directory with a command like find /home/cjones/php55 -name

Edit php.ini and add the extension with its full path:


Update (25 March 2013): Dmitry merged a PHP 5.5 change so that the full path is not required for zend_extension libraries in the extension_dir directory. You can now simply do

You'll want to enable OPcache too:


The ext/opcache/README is the current source of documentation, and lists all the other php.ini parameters.

Problems can be reported in the Github issue tracker

Update (18 March 2013): In a commit over the weekend, the build option --enable-opcache is On by default. You will still need to update php.ini.

To build Zend OPcache for older versions of PHP:

You should be able to build OPcache with PHP 5.2 onwards

Install it by getting the source from Github. There is also a PECL repository; this is slightly out of date so I recommend using Github. Follow the README instructions to install it.

User-data cache:

The new opcode cache does not include a user-data cache. Joe Watkins recently started the APCu project to extract the user-data cache code from APC. Test this too!

Update 19 March 2013: Xinchen Hui is working on a lockless user-data cache, see and his blog about it here (in Chinese).

Tuesday Dec 11, 2012

Excitement! Updated Underground PHP and Oracle Manual is Available for Download

We're thrilled to have a major update of the free Underground PHP and Oracle Manual released on OTN.

The Underground PHP and Oracle Manual is designed to bridge the gap between the many PHP scripting language and the many Oracle Database books available. It contains unique material about PHP's OCI8 extension for Oracle Database, and about other components in the PHP-Oracle ecosystem. It shows PHP developers how to use PHP and Oracle together, efficiently and easily.

The book has been completely refreshed. It has been updated for Oracle XE 11g and the latest PHP OCI8 extension. There are new chapters about using PHP with Oracle TimesTen, NetBeans and Oracle Tuxedo. There is also a new chapter about installing PHP on Oracle Solaris. The book now clocks in at 347 pages of great content.

Acknowledgements are due to all those who have helped with this and previous editions of the book. Thanks to the product teams that assisted with brand new content. In particular Craig Mohrman contributed the chapter about PHP on Solaris. Jeffry Rubinoff contributed the base text for the chapter on PHP and NetBeans.

Thursday Dec 06, 2012

Adding DTrace Probes to PHP Extensions

The powerful DTrace tracing facility has some PHP-specific probes that can be enabled with --enable-dtrace.

DTrace for Linux is being created by Oracle and is currently in tech preview. Currently it doesn't support userspace tracing so, in the meantime, Systemtap can be used to monitor the probes implemented in PHP. This was recently outlined in David Soria Parra's post Probing PHP with Systemtap on Linux.

My post shows how DTrace probes can be added to PHP extensions and traced on Linux. I was using Oracle Linux 6.3.

Not all Linux kernels are built with Systemtap, since this can impact stability. Check whether your running kernel (or others installed) have Systemtap enabled, and reboot with such a kernel:

# grep CONFIG_UTRACE /boot/config-`uname -r`
# grep CONFIG_UTRACE /boot/config-*

When you install Systemtap itself, the package systemtap-sdt-devel is needed since it provides the sdt.h header file:

# yum install systemtap-sdt-devel

You can now install and build PHP as shown in David's article. Basically the build is with:

$ cd ~/php-src
$ ./configure --disable-all --enable-dtrace
$ make

(For me, running 'make' a second time failed with an error. The workaround is to do 'git checkout Zend/zend_dtrace.d' and then rerun 'make'. See PHP Bug 63704)

David's article shows how to trace the probes already implemented in PHP. You can also use Systemtap to trace things like userspace PHP function calls. For example, create test.php:


$c = oci_connect('hr', 'welcome', 'localhost/orcl');
$s = oci_parse($c, "select dbms_xmlgen.getxml('select * from dual') xml from dual");
$r = oci_execute($s);
$row = oci_fetch_array($s, OCI_NUM);
$x = $row[0]->load();
echo $x;


The normal output of this file is the XML form of Oracle's DUAL table:

$ ./sapi/cli/php ~/test.php
<?xml version="1.0"?>

To trace the PHP function calls, create the tracing file functrace.stp:

probe process("sapi/cli/php").function("zif_*") {
    printf("Started function %s\n", probefunc());

probe process("sapi/cli/php").function("zif_*").return {
    printf("Ended function %s\n", probefunc());

This makes use of the way PHP userspace functions (not builtins) like oci_connect() map to C functions with a "zif_" prefix.

Login as root, and run System tap on the PHP script:

# cd ~cjones/php-src
# stap -c 'sapi/cli/php ~cjones/test.php' ~cjones/functrace.stp
Started function zif_oci_connect
Ended function zif_oci_connect
Started function zif_oci_parse
Ended function zif_oci_parse
Started function zif_oci_execute
Ended function zif_oci_execute
Started function zif_oci_fetch_array
Ended function zif_oci_fetch_array
Started function zif_oci_lob_load
<?xml version="1.0"?>
Ended function zif_oci_lob_load
Started function zif_oci_free_descriptor
Ended function zif_oci_free_descriptor

Each call and return is logged. The Systemtap scripting language allows complex scripts to be built. There are many examples on the web.

To augment this generic capability and the PHP probes in PHP, other extensions can have probes too. Below are the steps I used to add probes to OCI8:

  1. I created a provider file ext/oci8/oci8_dtrace.d, enabling three probes. The first one will accept a parameter that runtime tracing can later display:

    provider php {
    	probe oci8__connect(char *username);
    	probe oci8__nls_start();
    	probe oci8__nls_done();
  2. I updated ext/oci8/config.m4 with the PHP_INIT_DTRACE macro. The patch is at the end of config.m4. The macro takes the provider prototype file, a name of the header file that 'dtrace' will generate, and a list of sources files with probes. When --enable-dtrace is used during PHP configuration, then the outer $PHP_DTRACE check is true and my new probes will be enabled. I've chosen to define an OCI8 specific macro, HAVE_OCI8_DTRACE, which can be used in the OCI8 source code:

    diff --git a/ext/oci8/config.m4 b/ext/oci8/config.m4
    index 34ae76c..f3e583d 100644
    --- a/ext/oci8/config.m4
    +++ b/ext/oci8/config.m4
    @@ -341,4 +341,17 @@ if test "$PHP_OCI8" != "no"; then
    +  if test "$PHP_DTRACE" = "yes"; then
    +     AC_CHECK_HEADERS([sys/sdt.h], [
    +       PHP_INIT_DTRACE([ext/oci8/oci8_dtrace.d],
    +                       [ext/oci8/oci8_dtrace_gen.h],[ext/oci8/oci8.c])
    +         AC_DEFINE(HAVE_OCI8_DTRACE,1,
    +         [Whether to enable DTrace support for OCI8 ])
    +     ], [
    +       AC_MSG_ERROR(
    +         [Cannot find sys/sdt.h which is required for DTrace support])
    +     ])
    +   fi
  3. In ext/oci8/oci8.c, I added the probes at, for this example, semi-arbitrary places:

    diff --git a/ext/oci8/oci8.c b/ext/oci8/oci8.c
    index e2241cf..ffa0168 100644
    --- a/ext/oci8/oci8.c
    +++ b/ext/oci8/oci8.c
    @@ -1811,6 +1811,12 @@ php_oci_connection *php_oci_do_connect_ex(char *username, int username_len, char
    +#ifdef HAVE_OCI8_DTRACE
    +		DTRACE_OCI8_CONNECT(username);
    +	}
     	/* Initialize global handles if they weren't initialized before */
     	if (OCI_G(env) == NULL) {
    @@ -1870,11 +1876,22 @@ php_oci_connection *php_oci_do_connect_ex(char *username, int username_len, char
     		size_t rsize = 0;
     		sword result;
    +#ifdef HAVE_OCI8_DTRACE
    +		}
     		PHP_OCI_CALL_RETURN(result, OCINlsEnvironmentVariableGet, (&charsetid_nls_lang, 0, OCI_NLS_CHARSET_ID, 0, &rsize));
     		if (result != OCI_SUCCESS) {
     			charsetid_nls_lang = 0;
     		smart_str_append_unsigned_ex(&hashed_details, charsetid_nls_lang, 0);
    +#ifdef HAVE_OCI8_DTRACE
    +		}
     	timestamp = time(NULL);

    The oci_connect(), oci_pconnect() and oci_new_connect() calls all use php_oci_do_connect_ex() internally. The first probe simply records that the PHP application made a connection call. I already showed a way to do this without needing a probe, but adding a specific probe lets me record the username. The other two probes can be used to time how long the globalization initialization takes.

    The relationships between the oci8_dtrace.d names like oci8__connect, the probe guards like DTRACE_OCI8_CONNECT_ENABLED() and probe names like DTRACE_OCI8_CONNECT() are obvious after seeing the pattern of all three probes.

    I included the new header that will be automatically created by the dtrace tool when PHP is built. I did this in ext/oci8/php_oci8_int.h:

    diff --git a/ext/oci8/php_oci8_int.h b/ext/oci8/php_oci8_int.h
    index b0d6516..c81fc5a 100644
    --- a/ext/oci8/php_oci8_int.h
    +++ b/ext/oci8/php_oci8_int.h
    @@ -44,6 +44,10 @@
     #  endif
     # endif /* osf alpha */
    +#ifdef HAVE_OCI8_DTRACE
    +#include "oci8_dtrace_gen.h"
     #if defined(min)
     #undef min
  4. Now PHP can be rebuilt:

    $ cd ~/php-src
    $ rm configure && ./buildconf --force
    $ ./configure --disable-all --enable-dtrace \
    $ make

    If 'make' fails, do the 'git checkout Zend/zend_dtrace.d' trick I mentioned.

  5. The new probes can be seen by logging in as root and running:

    # stap -l 'process.provider("php").mark("oci8*")' -c 'sapi/cli/php -i'
  6. To test them out, create a new trace file, oci.stp:

    global numconnects;
    global start;
    global numcharlookups = 0;
    global tottime = 0;
    probe process.provider("php").mark("oci8-connect") {
        printf("Connected as %s\n", user_string($arg1));
        numconnects += 1;
    probe process.provider("php").mark("oci8-nls_start") {
        start = gettimeofday_us();
    probe process.provider("php").mark("oci8-nls_done") {
        tottime += gettimeofday_us() - start;
    probe end {
        printf("Connects: %d, Charset lookups: %ld\n", numconnects, numcharlookups);
        printf("Total NLS charset initalization time: %ld usecs/connect\n", 
                            (numcharlookups > 0 ? tottime/numcharlookups : 0));

    This calculates the average time that the NLS character set lookup takes. It also prints out the username of each connection, as an example of using parameters.

  7. Login as root and run Systemtap over the PHP script:

    # cd ~cjones/php-src
    # stap -c 'sapi/cli/php ~cjones/test.php' ~cjones/oci.stp
    Connected as cj
    <?xml version="1.0"?>
    Connects: 1, Charset lookups: 1
    Total NLS charset initalization time: 164 usecs/connect

    This shows the time penalty of making OCI8 look up the default character set. This time would be zero if a character set had been passed as the fourth argument to oci_connect() in test.php.

Update: To use real (non-SystemTap) DTrace, the extension binary needs to be built slightly differently than shown above in step 2. Instead of modifying config.m4 to reuse PHP_INIT_DTRACE, an OCI8-specific variant of that macro is created. To see how this is done, look at the new OCI8_INIT_DTRACE macro definition and way it is used in PHP OCI8 2.0's ext/oci8/config.m4.

Tuesday Feb 02, 2010

Facebook's HPHP: Initial Comments

[Read More]

Tuesday Jan 19, 2010

Tracing PHP Oracle Applications, part 1

[Read More]

Monday Oct 27, 2008

Oracle 11g Result Caching and PHP OCI8

[Read More]

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Links: OTN Node.js Developer Center
OTN PHP Developer Center
Book: Free PHP Oracle book


« March 2015