Oracle Database 11gR2 Enhancements for PHP

Oracle Database 11g Release 2 is now available for download.

It's a great feeling to see this major release available to customers. Lots of hard work has gone into it and my thanks go to all those folk at Oracle who have made it possible.

Currently Linux 32 and 64 bit software is on the Oracle Database Software Downloads page.

There are many new database features. Various whitepapers and the documentation discuss them in detail.

In this post, I want to mention some of the new features useful for PHP OCI8 applications. They include enhancements that let online production applications be upgraded with minimal downtime, and also features improving existing important capabilities introduced in Oracle 11gR1 such as DRCP connection pooling and the Client Query Result Cache.

The new features in Oracle Database 11g Release 2 that I want to highlight are:

  • A new Database Resident Connection Pool (DRCP) dynamic performance view V$CPOOL_CONN_INFO. There is a GV$ counterpart for Oracle RAC.

    DRCP is a connection pooling solution that allows web applications to use database server connection resources very efficiently. At the upper end, it lets web applications scale to tens of thousands of connections on commodity hardware.

    The new view displays information about each connection to the DRCP Connection Broker. This gives more insight into client processes that are connected, making it easier to monitor and trace applications that are currently using pooled servers or are idle. (PHP/Apache processes that are idle retain a lightweight connection to the DRCP Connection Broker.)

    Existing DRCP views are unchanged from Oracle 11gR1. (Update: DBA_CPOOL_INFO now has NUM_CBROK and MAXCONN_CBROK columns, equivalent to the pool configuration option parameters of the same names)

  • The Oracle 11g Client Query Result Cache (CQRC) is supported with DRCP connections.

    The CQRC is a result cache storing the results of queries in the PHP/Apache process memory. When queries are executed repeatedly, the results can be retrieved directly from the cache, resulting in faster query response time. The results in the cache are automatically marked invalid when data in the database objects being accessed by the query is modified.

  • Table Annotations are supported for CQRC, making it easier for existing applications to get performance benefits from result caching. Previously queries had to be modified to include the "/*+ result_cache */" hint. In Oracle Database 11gR2 a DBA can now create or annotate a table as being a candidate for CQRC:

           ALTER TABLE sales RESULT_CACHE (MODE FORCE);  

    The application code does not need to change.

  • The prefetching of rows from REF CURSORs (aka Cursor Variables) is supported in Oracle Database 11gR2, greatly increasing performance when Oracle PL/SQL stored procedures and functions are used to encapsulate business logic.

    REF CURSORS are like pointers to result sets. Typically queries are performed in PL/SQL and a REF CURSOR is returned to PHP so the results can be processed.

    Prefetching minimizes database server round-trips by returning batches of rows to an Oracle-managed cache each time a request is made to the database. Prefetching was previously only supported for queries.

    With Oracle Database 11gR2, the default REF CURSOR prefetch row count size is the value of oci8.default_prefetch in php.ini, i.e. 100 in PHP OCI8 1.3. The size can be explicitly changed for a REF CURSOR. For example, to increase the prefetch size of a REF CURSOR to 200:

    	$stid = oci_parse($c, "call myproc(:rc)");
    	$refcur = oci_new_cursor($c);
    	oci_bind_by_name($stid, ':rc', $refcur, -1, OCI_B_CURSOR);
    	oci_execute($stid);
    	oci_set_prefetch($refcur, 200);
    	oci_execute($refcur);
            oci_fetch_all($refcur, $res);
      

    Setting the prefetch count on the "parent" resource $stid does not change the prefetch size for $refcur.

    REF CURSOR prefetching can be used when connected to previous database versions so taking advantage of the new feature can be as simple as relinking PHP with Oracle 11.2 client libraries.

    Prefetching also now works for nested cursor columns:

           select department_name, 
                  cursor(select manager_id 
                         from employees
                         where employees.employee_id = departments.department_id)
           from departments;
    

    Nested cursor prefetching requires both the Oracle client libraries linked with PHP and the database to be Oracle Database 11gR2.

Many other new Oracle Database 11gR2 features will benefit web applications. Here are just two of those features:

  • Edition-base redefinition makes it safer and easier to do web application upgrades. This feature enables database components of an application to be upgraded and tested while the old version continues to be used. When the changes are complete, an upgraded application using the new database objects can be made available to all users.

    I'll give an example of this in my next blog post since this is exciting stuff.

  • RAC One Node has been introduced. This bundling is perfect for sites that need high availability but currently run well with a single database server.

The PHP OCI8 1.2.5 and 1.3 extensions compile and run against Oracle Database 11gR2 without modification. PHP OCI8 1.3 is available with PHP 5.3. It is recommended to upgrade to OCI8 1.3 for PHP 5.2 and earlier versions of PHP. It can be installed from PECL.

Comments:

good news 11gR2 announcement. A CQRC is seem to great. I'll find a chance to test it.

Posted by Surachart Opun on September 01, 2009 at 11:45 AM PDT #

been waiting for that! while researching i found nice piece of news. http://bigdatamatters.com/bigdatamatters/2009/09/oracle-11g.html

Posted by Peter on September 03, 2009 at 10:42 PM PDT #

Well done!!! I was really waiting for prefetching of rows from REF CURSOR. Once again- well done!! This is definitely feature that I would like to test in practice and roll out to production asap. /PaweĊ‚

Posted by Paweł Barut on September 08, 2009 at 12:22 AM PDT #

Post a Comment:
Comments are closed for this entry.
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
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today