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 technique is very effective if the data to be processed is static. This is because

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 technique is very effective if the data to be processed is static. This is because a lot of data requests to the remote database can be satisfied from the local cache, so that you do not have to connect to the database, perform queries and obtain results.

However, when the database of your application and the Web server are on different computing machines, the cache database result set is usually a good method. However, it is difficult to determine the best cache policy based on your situation. For example, the time-triggered cache method (a common method used by the cache system, which assumes that the cache is automatically cached 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 exploitation program of changes to the database data to be cached by the program, so that the exploitation program can keep the cached expired data consistent with the database. In this case, it is very convenient to apply the "database Change Notification" (a new Oracle Database 10g 2nd feature.

"Database change notification"

The usage of the "database change notification" feature is very simple: Create a notification handler for notification fulfillment-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, the modified type, and the row ID (optional) to the client listener, so that the client can perform corresponding processing in the response.

To understand how the "database change notification" feature works, consider the following example. Assume that your PHP program visits 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 expect that the application caches the query result set for the ORDERS and ORDER_ITEMS tables at the same time. To avoid visiting expired data, you can apply the "database change notification", which allows your application to conveniently learn the changes to the data stored in the preceding two tables.

You must first grant the change notification system permission and the execute on DBMS_CHANGENOTIFICATION permission to the OE user to register 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 execute 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 applied 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 create an nfresults table to record the following information: change the date and time of generation, the name of the modified table, and a message (specify whether the notification handler successfully sends the notification message to the client ).