Oracle Database 11gR2 Enhancements for PHP
By cj on Sep 01, 2009
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.
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.