It is known that caching the results of database queries can significantly reduce script execution time and minimize the load on the database server. This technique works very well if the data you are processing is essentially static. This is because many data requests to the remote database can eventually be satisfied from the local cache, eliminating the need to connect to the database, execute the query, and obtain the results.
But caching the database result set is often a good idea when the database you are using is on a different computer than the Web server. However, determining the best caching strategy for your situation can be difficult. For example, for applications where it is important to use the latest database result set, a time-triggered caching approach (commonly used by caching systems that assume that the cache is regenerated every time the expiration timestamp is reached) may not be a satisfactory solution. In this case, you need a mechanism that will notify the application whenever the database data that the application needs to cache changes, so that the application can keep the cached expired data consistent with the database. In this case using "Database Change Notification" will be very convenient.
Getting Started with Database Change Notification
Usage of the Database Change Notification feature is very simple: Create a notification handler that executes for the notification – a PL/SQL stored procedure or client OCI callback function. Then, register a query against the database objects for which you want to receive change notifications, so that the notification handler is called whenever a transaction changes any object within it and commits. Typically, the notification handler sends the name of the table that was modified, the type of change made, and optionally the row ID of the changed row to the client listener so that the client application can perform appropriate actions in the response. deal with.
To understand how the Database Change Notification feature works, consider the following example. Assume that your PHP application accesses orders stored in the OE.ORDERS table and order items stored in OE.ORDER_ITEMS. Given that information about placed orders rarely changes, you might want your application to cache the result sets of queries against both the ORDERS and ORDER_ITEMS tables. To avoid accessing stale data, you can use Database Change Notifications, which allow your application to easily be notified of changes to the data stored in the two tables above.
You must grant the CHANGE NOTIFICATION system permission and the EXECUTE ON DBMS_CHANGENOTIFICATION permission to the OE user before you can register queries for the ORDERS and ORDER_ITEMS tables to receive notifications and respond to DML or DDL changes to these tables. To do this, execute the following command from a SQL command line tool such as SQL*Plus.
CONNECT/AS SYSDBA;
GRANT CHANGE NOTIFICATION TO oe;
GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO oe;
Ensure that the init.ora parameter job_queue_processes is set to a non-zero value in order to receive PL/SQL notifications. Alternatively, you can use the following ALTER SYSTEM command:
ALTER SYSTEM SET "job_queue_processes"=2; Then, after connecting as OE/OE, you can create a notification handler. But first, you must create the database object that will be used by the notification handler. For example, you might want to create one or more database tables into which the notification handler logs registry changes. In the following example, you create the nfresults table to record the date and time the change occurred, the name of the table that was modified, and a message indicating whether the notification handler successfully sent the notification message to the client.
CONNECT oe/oe;
CREATE TABLE nfresults (
operateDATE,
tblname VARCHAR2(60),
rslt_msg VARCHAR2(100)
);
In a real world scenario, you might need to create more tables to record information such as notification events and the row IDs of changed rows, but for the purposes of this article, the nfresults table will suffice.
Using UTL_HTTP to send notifications to clients
You may also create one or more PL/SQL stored procedures and call these stored procedures from the notification handler, thereby achieving a more maintainable and flexible solution. For example, you might want to create a stored procedure that implements sending notification messages to clients. "Listing 1" is the PL/SQL procedure sendNotification. This process uses the UTL_HTTPPL package to send change notifications to client applications.
Listing 1. Send notification to client using UTL_HTTPCREATE
OR REPLACE PROCEDURE sendNotification(url IN VARCHAR2,
tblname IN VARCHAR2, order_id IN VARCHAR2) IS
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
err_msg VARCHAR2(100);
tbl VARCHAR(60);
BEGIN
tbl:=SUBSTR(tblname, INSTR(tblname, '.', 1, 1)+1, 60);
BEGIN
req := UTL_HTTP.BEGIN_REQUEST(url||order_id||'&'||'table='||tbl);
resp := UTL_HTTP.GET_RESPONSE(req);
INSERT INTO nfresults VALUES(SYSDATE, tblname, resp.reason_phrase);
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION WHEN OTHERS THEN
err_msg := SUBSTR(SQLERRM, 1, 100);
INSERT INTO nfresults VALUES(SYSDATE, tblname, err_msg);
END;
COMMIT;
END;
/
As shown in "Listing 1", sendNotification sends a notification message to the client in the form of an HTTP request issued by the UTL_HTTP.BEGIN_REQUEST function. This URL contains the order_id of the changed row in the ORDERS table. It then uses UTL_HTTP.GET_RESPONSE to obtain the response information sent by the client. In fact, sendNotification does not need to process the entire response returned by the client, but only obtains a short message (describing the status code) stored in the reason_phrase field of the RESP record.
Creating a Notification Handler
Now you can create a notification handler that will send change notifications to clients with the help of the sendNotification procedure described above. Let's take a look at the PL/SQL procedure orders_nf_callback in "Listing 2".
Listing 2. Notification handler that handles notifications of changes to the OE.ORDERS table
CREATE OR REPLACE PROCEDURE orders_nf_callback (ntfnds IN SYS.CHNF$_DESC) IS
tblname VARCHAR2(60);
numtables NUMBER;
event_type NUMBER;
row_id VARCHAR2(20);
numrows NUMBER;
ord_id VARCHAR2(12);
url VARCHAR2(256) := 'http://webserverhost/phpcache/dropResults.php?order_no=';
BEGIN
event_type := ntfnds.event_type;
numtables := ntfnds.numtables;
IF (event_type = DBMS_CHANGE_NOTIFICATION.EVENT_OBJCHANGE) THEN
FOR i IN 1..numtables LOOP
tblname := ntfnds.table_desc_array(i).table_name;
IF (bitand(ntfnds.table_desc_array(i).opflags,
DBMS_CHANGE_NOTIFICATION.ALL_ROWS) = 0) THEN
numrows := ntfnds.table_desc_array(i).numrows;
ELSE
numrows :=0;
END IF;
IF (tblname = 'OE.ORDERS') THEN
FOR j IN 1..numrows LOOP
row_id := ntfnds.table_desc_array(i).row_desc_array(j).row_id;
SELECT order_id INTO ord_id FROM orders WHERE rowid = row_id;
sendNotification(url, tblname, ord_id);
END LOOP;
END IF;
END LOOP;
END IF;
COMMIT;
END;
/
As shown in "Listing 2", this notification handler takes the SYS.CHNF$_DESC object as a parameter and then uses its properties to get the details of the change. In this example, this notification handler will only handle notifications posted by the database in response to DML or DDL changes to registered objects (that is, only if the notification type is EVENT_OBJCHANGE), and ignore information about other database events such as instance startup or instance shutdown) notification. Starting with the above version, the handler can handle change notifications issued for each affected row in the OE.ORDERS table. Later in this article, in the "Adding a table to an existing registration" section, you will add a few lines of code to the handler so that it can handle notifications for modified rows in the OE.ORDER_ITEMS table.
Create a registration for change notifications
After you create a notification handler, you must create a query registration for it. For this example, you must perform a query on the OE.ORDER table during the registration process and specify orders_nf_callback as the notification handler. You also need to specify the QOS_ROWIDS option in the DBMS_CHANGE_NOTIFICATION package to enable ROWID-level granularity in notification messages. "Listing 3" is a PL/SQL block that creates the query registration for the orders_nf_callback notification handler.
Listing 3. Create query registrationDECLARE
for notification handler
REGDS SYS.CHNF$_REG_INFO;
regid NUMBER;
ord_id NUMBER;
qosflags NUMBER;
BEGIN
qosflags := DBMS_CHANGE_NOTIFICATION.QOS_RELIABLE +
DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS;
REGDS := SYS.CHNF$_REG_INFO ('orders_nf_callback', qosflags, 0,0,0);
regid := DBMS_CHANGE_NOTIFICATION.NEW_REG_START (REGDS);
SELECT order_id INTO ord_id FROM orders WHERE ROWNUM<2;
DBMS_CHANGE_NOTIFICATION.REG_END;
END;
/
This example creates a registration against the ORDERS table and uses orders_nf_callback as the notification handler. Now, if you use a DML or DDL statement to modify the ORDERS table and commit the transaction, the orders_nf_callback function is automatically called. For example, you might execute the following UPDATE statement against the ORDERS table and commit the transaction:
UPDATE ORDERS SET order_mode = 'direct' WHERE order_id=2421;
UPDATE ORDERS SET order_mode = 'direct' WHERE order_id=2422;
COMMIT;
To ensure that the database posted notifications in response to the above transaction, you can check the nfresults table:
SELECT TO_CHAR(operdate, 'dd-mon-yy hh:mi:ss') operdate,
tblname, rslt_msg FROM nfresults;
The result should look like this:
OPERDATE TBLNAME RSLT_MSG
--------------------- ---------- ---------
02-mar-06 04:31:28 OE.ORDERS Not Found
02-mar-06 04:31:29 OE.ORDERS Not Found
It is clear from the above results that orders_nf_callback is already working but the client script is not found. This is not unexpected in this example because you did not create the dropResults.php script specified in the URL. For instructions on the dropResults.php script, see the Building the Client section later in this article.
Adding a table to an existing registration
The previous section showed how to use the Change Notification Service to have the database notify you when a registration object (in the above example, the ORDERS table) changes. But from a performance perspective, the client application may prefer to cache the query result set of the ORDER_ITEMS table rather than the ORDERS table itself, because it has to retrieve only one row from the ORDERS table each time it accesses the order, but at the same time Multiple rows must be retrieved from the ORDER_ITEMS table. In reality, an order may contain dozens or even hundreds of line items.
Since you have already registered queries against the ORDERS table, you do not need to create a registration to register queries against the ORDER_ITEMS table. Instead, you can use an existing registration. To do this, you first need to retrieve the ID of an existing registration. You can execute the following query to accomplish this:
SELECT regid, table_name FROM user_change_notification_regs; The results may look like this:
REGID TABLE_NAME
----- --------------
241 OE.ORDERS
After obtaining the registration ID, you can add a new object to the registration using the DBMS_CHANGE_NOTIFICATION.ENABLE_REG function as follows:
DECLARE
ord_id NUMBER;
BEGIN
DBMS_CHANGE_NOTIFICATION.ENABLE_REG(241);
SELECT order_id INTO ord_id FROM order_items WHERE ROWNUM < 2;
DBMS_CHANGE_NOTIFICATION.REG_END;
END;
/
Done! From now on, the database will generate a notification in response to any changes made to ORDERS and ORDER_ITEMS, and call the orders_nf_callback procedure to handle the notification. Therefore, the next step is to edit orders_nf_callback so that it can handle notifications generated by DML operations on the ORDER_ITEMS table. But before re-creating the orders_nf_callback procedure, you need to create the following table type that will be referenced during the update process:
CREATE TYPE rdesc_tab AS TABLE OF SYS.CHNF$_RDESC; Then, return to Listing 2, after the following line of code:
IF (tblname = 'OE.ORDERS') THEN
FOR j IN 1..numrows LOOP
row_id := ntfnds.table_desc_array(i).row_desc_array(j).row_id;
SELECT order_id INTO ord_id FROM orders WHERE rowid = row_id;
sendNotification(url, tblname, ord_id);
END LOOP;
END IF;
Insert the following code:
IF (tblname = 'OE.ORDER_ITEMS') THEN
FOR rec IN (SELECT DISTINCT(o.order_id) o_id FROM
TABLE(CAST(ntfnds.table_desc_array(i).row_desc_array AS rdesc_tab)) t,
orders o, order_items d WHERE t.row_id = d.rowid AND d.order_id=o.order_id)
LOOP
sendNotification(url, tblname, rec.o_id);
END LOOP;
END IF;
After re-creating orders_nf_callback, you need to test that it works correctly. To do this, you can execute the following UPDATE statement against the ORDER_ITEMS table and commit the transaction:
UPDATE ORDER_ITEMS SET quantity = 160 WHERE order_id=2421 AND line_item_id=1;
UPDATE ORDER_ITEMS SET quantity = 160 WHERE order_id=2421 AND line_item_id=2;
COMMIT;
Then, check the nfresults table as follows:
SELECT TO_CHAR(operdate, 'dd-mon-yy hh:mi:ss') operdate,
rslt_msg FROM nfresults WHERE tblname = 'OE.ORDER_ITEMS'; The output may look like this:
OPERDATE RSLT_MSG
-----------------------------------
03-mar-06 12:32:27 Not Found
You may be wondering why only one row was inserted into the nfresults table – after all, you updated two rows in the ORDER_ITEMS table. In fact, the two updated rows have the same order_id - i.e. they belong to the same order. Here, we assume that the client application will use a single statement to select all line items of an order, so it does not need to know exactly which line items of an order have been changed. Instead, the client needs to know the order ID in which at least one line item was modified, deleted, or inserted.
Building the client
Now that you have created registrations for the ORDERS and ORDER_ITEMS tables, let's take a look at how change notifications are used by client applications that access orders and their line items stored in these tables. To do this, you can build a PHP application that will cache the results of queries against the above tables and take appropriate actions in response to notifications about changes to these tables (which are received from the database server). An easy way is to use the PEAR::Cache_Lite package, which provides you with a reliable mechanism to keep cache data up to date. In particular, you can use the Cache_Lite_Function class (part of the PEAR::Cache_Lite package), which allows you to cache function calls.
For example, you can create a function that performs the following tasks: establishes a database connection, executes a select statement against the database, obtains the search results, and finally returns the results as an array. You can then cache the result arrays returned by the function through the call method of the Cache_Lite_Function instance so that they can be read from the local cache rather than from the back-end database, which can significantly improve the performance of your application. Then, when you are notified of changes to the cached data, you will use the drop method of the Cache_Lite_Function instance to delete the expired data in the cache.
Going back to the example in this article, you might want to create two functions for your application to interact with the database: the first function will query the ORDERS table and return the orders with the specified ID, while the other function will query the ORDER_ITEMS table and return Returns the line items for this order. "Listing 4" shows the getOrderFields.php script that contains the getOrderFields function, which accepts an order ID and returns an associative array containing some of the fields of the retrieved order.
Listing 4. Get the fields of the specified order
＜?php
//File:getOrderFields.php
require_once 'connect.php';
function getOrderFields($order_no) {
if (!$rsConnection = GetConnection()){
return false;
}
$strSQL = "SELECT TO_CHAR(ORDER_DATE) ORDER_DATE, CUSTOMER_ID,
ORDER_TOTAL FROM ORDERS WHERE order_id =:order_no";
$rsStatement = oci_parse($rsConnection,$strSQL);
oci_bind_by_name($rsStatement, ":order_no", $order_no, 12);
if (!oci_execute($rsStatement)) {
$err = oci_error();
print $err['message'];
trigger_error('Query failed:' . $err['message']);
return false;
}
$results = oci_fetch_assoc($rsStatement);
return $results;
}
?＞
"Listing 5" is the getOrderItems.php script. The script contains the getOrderItems function, which accepts an order ID and returns a two-dimensional array containing rows representing the order's line items.
Listing 5. Get the line items of the specified order
＜?php
//File:getOrderItems.php
require_once 'connect.php';
function getOrderItems($order_no) {
if (!$rsConnection = GetConnection()){
return false;
}
$strSQL = "SELECT * FROM ORDER_ITEMS WHERE
order_id =:order_no ORDER BY line_item_id";
$rsStatement = oci_parse($rsConnection,$strSQL);
oci_bind_by_name($rsStatement, ":order_no", $order_no, 12);
if (!oci_execute($rsStatement)) {
$err = oci_error();
trigger_error('Query failed:' . $err['message']);
return false;
}
$nrows = oci_fetch_all($rsStatement, $results);
return array ($nrows, $results);
}
?＞
Note that both of the above functions require the connect.php script, which should contain the GetConnection function that returns the database connection. Listing 6 is the connect.php script:
Listing 6. Obtain database connection
<?php
//File:connect.php
function GetConnection() {
$dbHost = "dbserverhost";
$dbHostPort="1521";
$dbServiceName = "orclR2";
$usr = "oe";
$pswd = "oe";
$dbConnStr = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=".$dbHost.")
(PORT=".$dbHostPort."))(CONNECT_DATA=(SERVICE_NAME=".$dbServiceName.")))";
if(!$dbConn = oci_connect($usr,$pswd,$dbConnStr)) {
$err = oci_error();
trigger_error('Failed to connect ' .$err['message']);
return false;
}
return $dbConn;
}
?＞
Now that you have created all the functions needed to communicate with the database, let's take a look at how the Cache_Lite_Function class works. Listing 7 is the testCache.php script that uses the Cache_Lite_Function class to cache the results of the above function.
<?php
using PEAR::Cache_Lite
//File:testCache.php
require_once 'getOrderItems.php';
require_once 'getOrderFields.php';
require_once 'Cache/Lite/Function.php';
$options = array(
'cacheDir' => '/tmp/',
'lifeTime' => 86400
);
if (!isset($_GET['order_no'])) {
die('The order_no parameter is required');
}
$order_no=$_GET['order_no'];
$cache = new Cache_Lite_Function($options);
if ($orderfields = $cache->call('getOrderFields', $order_no)){
print "＜h3＞ORDER #$order_no＜/h3＞n";
print "<table>";
print "＜tr＞＜td＞DATE:＜/td＞＜td＞".$orderfields['ORDER_DATE']."＜/td＞＜/tr＞";
print "＜tr＞＜td＞CUST_ID:＜/td＞＜td＞".$orderfields['CUSTOMER_ID']."＜/td＞＜/tr＞";
print "＜tr＞＜td＞TOTAL:＜/td＞＜td＞".$orderfields['ORDER_TOTAL']."＜/td＞＜/tr＞";
print "</table>";
} else {
print "Some problem occurred while getting order fields!n";
$cache->drop('getOrderFields', $order_no);
}
if (list($nrows, $orderitems) = $cache->call('getOrderItems', $order_no)){
//print "＜h3＞LINE ITEMS IN ORDER #$order_no＜/h3＞";
print "<table border=1>";
print "<tr>n";
while (list($key, $value) = each($orderitems)) {
print "＜th＞$key＜/th＞n";
}
print "＜/tr＞n";
for ($i = 0; $i < $nrows; $i++) {
print "<tr>";
print "＜td＞".$orderitems['ORDER_ID'][$i]."＜/td＞";
print "＜td＞".$orderitems['LINE_ITEM_ID'][$i]."＜/td＞";
print "＜td＞".$orderitems['PRODUCT_ID'][$i]."＜/td＞";
print "＜td＞".$orderitems['UNIT_PRICE'][$i]."＜/td＞";
print "＜td＞".$orderitems['QUANTITY'][$i]."＜/td＞";
print "</tr>";
}
print "</table>";
} else {
print "Some problem occurred while getting order line items";
$cache->drop('getOrderItems', $order_no);
}
?＞
The testCache.php script in "Listing 7" should be called with the order_no URL parameter (representing the order ID stored in the OE.ORDER table). For example, to retrieve information related to the order with ID 2408, you would enter the following URL into your browser:
http://webserverhost/phpcache/testCache.php?order_no=2408 As a result, the browser will generate the following output:
ORDER #2408
DATE: 29-JUN-99 06.59.31.333617 AM
CUST_ID: 166
TOTAL: 309
ORDER_ID LINE_ITEM_ID PRODUCT_ID UNIT_PRICE QUANTITY
2408 1 2751 61 3
2408 2 2761 26 1
2408 3 2783 10 10Now
, if you click the reload button in the browser, the testCache.php script will not call the getOrderFields and getOrderItems functions again. Instead, it will read their results from the local cache. Therefore, every getOrderFields or getOrderItems call with order_no=2108 will be satisfied by the local cache within 24 hours from now (because lifeTime is set to 86400 seconds). Note, however, that the Cache_Lite_Function class does not provide an API to test whether a cache is available for a given function with given parameters. Therefore, it can be a bit tricky to determine whether the application actually reads the cache or still executes the function each time it is called with the same parameters. For example, in the above example, to ensure that the caching mechanism works properly, you can temporarily change the connection information specified in the connect.php script so that it cannot establish the database connection; for example, specify a wrong database server host name, and then use order_no= again 2108 Run the testCache.php script. If caching is working properly, the browser's output should be the same as before.
Additionally, you can check the cache directory that is passed to the constructor of the Cache_Lite_Function class as the value of the cacheDir option (/tmp in this example). In that directory you will find two cache files you just created with names similar to: cache_7b181b55b55aee36ad5e7bd9d5a091ec_3ad04d3024f4cd54296f75c92a359154. Note that if you are a Windows user, you may want to use the %SystemDrive%temp directory to save cache files. If so, the cacheDir option must be set to /temp/.
After verifying that the caching mechanism is working properly, you can then create a PHP to handle the change notifications received from the database server. "Listing 8" is the dropResult.php script. The database server will call this script in response to changes to the ORDERS and ORDER_ITEMS tables.
Listing 8. Handling change notifications received from the database server
<?php
//File:dropResults.php
require_once 'Cache/Lite/Function.php';
$options = array(
'cacheDir' => '/tmp/'
);
$cache = new Cache_Lite_Function($options);
if (isset($_GET['order_no'])&& isset($_GET['table'])) {
if($_GET['table']=='ORDER_ITEMS'){
$cache->drop('getOrderItems', $_GET['order_no']);
}
if ($_GET['table']=='ORDERS'){
$cache->drop('getOrderFields', $_GET['order_no']);
}
}
?＞
After creating the dropResult.php script, make sure that the URL specified in the notification handler (shown in Listing 2) is correct. Then, connect as OE/OE in SQL*Plus or similar tool and execute UPDATE statements that will affect the same orders accessed earlier in this section through the testCache.php script (here the order with ID 2408):
UPDATE ORDERS SET order_mode = 'direct' WHERE order_id=2408;
UPDATE ORDER_ITEMS SET quantity = 3 WHERE order_id=2408 AND line_item_id=1;
UPDATE ORDER_ITEMS SET quantity = 1 WHERE order_id=2408 AND line_item_id=2;
COMMIT;
In response to the above update, the notification handler described earlier in this article will run the dropResults.php script twice, using the following URLs: http://webserverhost/phpcache/dropResults.php?order_no=2408&table=ORDERS
http://webserverhost/phpcache/dropresults.php?order_no=2408&table=ORDER_ITEMS
From "Listing 8" you can clearly see that the dropResult.php script does not flush the cache after receiving the change notification from the database server. It just deletes cache files containing expired data. So if you check the cache directory now, you will see that the cache file created when running the testCache.php script with order_no=2408 has disappeared. What this essentially means is that the next time testCache.php requests data related to order ID 2408, it will get that data from the backend database instead of the local cache.
You may find this method useful in situations where the result set requested by the application is likely to change before the application uses it. For the purposes of this article's example, this means that the data related to a specific order may change multiple times before testCache.php accesses that order. In this way, the application does a lot of unnecessary work by flushing its cache immediately after receiving change notifications from the database server.
But if you want the dropResult.php script to flush the cache as soon as it is notified of the change, you can call the call method of the Cache_Lite_Function instance after calling the drop method, specifying the same parameters for both calls. In this case, you should also make sure to include the getOrderFields.php and getOrderItems.php scripts so that dropResults.php can call the getOrderFields and getOrderItems functions to refresh the cache. "Listing 9" is the modified dropResult.php script.
Listing 9. Flush cache immediately after receiving change notification
<?php
//File:dropResults.php
require_once 'Cache/Lite/Function.php';
require_once 'getOrderItems.php';
require_once 'getOrderFields.php';
$options = array(
'cacheDir' => '/tmp/',
'lifeTime' => 86400
);
$cache = new Cache_Lite_Function($options);
if (isset($_GET['order_no'])&& isset($_GET['table'])) {
if($_GET['table']=='ORDER_ITEMS'){
$cache->drop('getOrderItems', $_GET['order_no']);
$cache->call('getOrderItems', $_GET['order_no']);
}
if ($_GET['table']=='ORDERS'){
$cache->drop('getOrderFields', $_GET['order_no']);
$cache->call('getOrderFields', $_GET['order_no']);
}
}
?＞
The above approach may be useful if the data stored in the ORDERS and ORDER_ITEMS tables rarely changes and the application accesses it frequently.
Summary
If your PHP application interacts with Oracle Database 10g Release 2, you can take advantage of the Database Change Notification feature, which allows your application to receive notifications in response to DML changes to the object associated with the request made. Using this feature, you don't have to update the cache in your application during a specific period of time. Instead, the operation is performed only if the result set of the registered query has changed.