Improve PHP performance by caching database results_PHP Tutorial

It is well 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 ultimately 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 machine 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 will 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 Notifications

Usage of the Database Change Notifications feature is very simple: Create a notification handler that executes against 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(as today's mainstream development language) application accesses the orders stored in the OE.ORDERS table and the 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 first 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 in order to receive notifications and respond to DML or DDL changes made to these two tables. . To do this, you can 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;
Make sure the init.ora parameter job_queue_processes is set to a non-zero value in order to receive PL/ SQL notification. 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 (
operdate DATE,
tblname VARCHAR2(60),
rslt_msg VARCHAR2(100)
);
In actual situations, you may need to create more tables to record information such as notification events and row IDs of changed rows, but for the purpose of 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 these stored procedures from the notification handler to implement a more Solutions for maintainability and flexibility. For example, you might want to create a stored procedure that implements 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. Using UTL_HTTP to send notifications to clients