Improve PHP performance by caching database results

As we all know, the results of cache database queries can significantly shorten the script execution time and minimize the load on the database server. This technology is very effective if the data to be processed is basically static. This is because many data requests to the remote database can finally be satisfied from the local cache, so that you do not have to connect to the database, execute queries, and obtain results.

However, when the database you are using is located on a different computer from the Web server, it is always a good way to cache the database result set. However, it is difficult to determine the best cache policy based on your situation. For example, for applications that use the latest database result set, the time-triggered cache method (a common method used by the cache system, which assumes that the cache is regenerated every time the expiration timestamp is reached) it may not be a satisfactory solution. In this case, you need to adopt a mechanism that notifies the application whenever the database data to be cached by the application changes, so that the application can keep the cached expired data consistent with the database. In this case, it is very convenient to use "Database Change Notification.

Getting started with "Database Change Notification"

The usage of the "Database Change Notification" feature is very simple: Create a notification handler for notification execution-a PL/SQL stored procedure or a client OCI callback function. Then, register a query for the database object whose change notification is to be received so that the notification handler is called whenever the transaction changes any of the objects and commits them. Generally, the notification handler sends the modified table name, modified type, and row ID (optional) to the client listener, so that the client application can perform corresponding processing in the response.

To learn how the "Database Change Notification" feature works, consider the following example. Assume that your php application accesses the ORDERS stored in the OE. ORDERS table and the order items stored in OE. ORDER_ITEMS. Since the order information is rarely changed, you may want the application to cache query result sets for both ORDERS and ORDER_ITEMS tables. To avoid access to expired data, you can use the "Database Change Notification", which allows your application to conveniently learn the changes to the data stored in the preceding two tables.

You must grant the change notification system permission and the execute on DBMS_CHANGENOTIFICATION permission to the OE user before registering the query for the ORDERS and ORDER_ITEMS tables, to receive notifications and respond to DML or DDL changes made to these two tables. Therefore, you can run the following commands from SQL command line tools (such as SQL * Plus.

CONNECT/as sysdba;
Grant change notification to oe;
Grant execute on DBMS_CHANGE_NOTIFICATION TO oe;
Make sure that the init. ora parameter job_queue_processes is set to a non-zero value to receive PL/SQL notifications. Alternatively, you can use the following alter system command:

Alter system set "job_queue_processes" = 2; then, after connecting with OE/OE, you can create a notification handler. First, you must create a database object that will be used by the notification handler. For example, you may need to create one or more database tables to notify the handler to record registry changes. In the following example, you will create an nfresults table to record the following information: the date and time when the change occurred, the name of the modified table, and a message (indicating whether the notification handler successfully sends the notification message to the client ).

CONNECT oe/oe;

Create table nfresults (
Operdate DATE,
Tblname VARCHAR2 (60 ),
Rslt_msg VARCHAR2 (100)
);
In practice, you may need to create more tables to record notification events and the row ID of the modified row. However, in this article, the nfresults table can fully meet your needs.

Use UTL_HTTP to send notifications to clients

You may also create one or more PL/SQL stored procedures and call them from the notification handler to implement a more maintainability and flexibility solution. For example, you may want to create a stored procedure to send notification messages to the client. "Listing 1" is the sendNotification for PL/SQL processes. In this process, the UTL_HTTPPL package is used to send a change notification to the client application.

Listing 1. Use UTL_HTTP to send notifications to the client