Improve PHP performance by caching database results

Welcome to the Oracle community forum and interact with 2 million technical staff. As we all know, the cache database query results 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 are ultimately

Welcome to the Oracle community forum and interact with 2 million technical staff> as we all know, the cache database query results 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 are ultimately

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

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" (a new Oracle database version 10g 2nd feature.

Getting started with "Database Change Notification"

The "Database Change Notification" feature is easy to use: Create a notification handler for notification execution? A pl/SQL stored procedure or 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

CREATE OR REPLACE PROCEDURE sendNotification(url IN VARCHAR2, tblname IN VARCHAR2, order_id IN VARCHAR2) ISreq   UTL_HTTP.REQ;resp  UTL_HTTP.RESP;err_msg VARCHAR2(100);tbl VARCHAR(60);BEGINtbl:=SUBSTR(tblname, INSTR(tblname, '.', 1, 1)+1, 60);BEGINreq := 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 THENerr_msg := SUBSTR(SQLERRM, 1, 100);INSERT INTO nfresults VALUES(SYSDATE, tblname, err_msg);END;COMMIT;END;/

As shown in "List 1", sendNotification sends a notification message to the client in the form of an HTTP request sent by the UTL_HTTP.BEGIN_REQUEST function. This URL contains the order_id of the changed row in the ORDERS table. Then, it 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. Instead, it only obtains a short message (describing the status code) stored in the reason_phrase field of the resp record ).

Create a notification Handler

Now you can create a notification handler that sends a change notification to the client using the sendNotification process described above. Let's take a look at the PL/SQL process orders_nf_callback in Listing 2.

Listing 2. Notification handler that processes the notifications for changes to the OE. ORDERS table

CREATE OR REPLACE PROCEDURE orders_nf_callback (ntfnds IN SYS.CHNF$_DESC) IStblname 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=';BEGINevent_type := ntfnds.event_type;numtables := ntfnds.numtables;IF (event_type = DBMS_CHANGE_NOTIFICATION.EVENT_OBJCHANGE) THENFOR i IN 1..numtables LOOPtblname := ntfnds.table_desc_array(i).table_name;IF (bitand(ntfnds.table_desc_array(i).opflags, DBMS_CHANGE_NOTIFICATION.ALL_ROWS) = 0) THENnumrows := ntfnds.table_desc_array(i).numrows;ELSEnumrows :=0;END IF;IF (tblname = 'OE.ORDERS') THENFOR j IN 1..numrows LOOProw_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 "List 2", this notification handler uses the SYS. CHNF $ _ DESC object as a parameter and uses its properties to obtain the details of the change. In this example, the notification handler only processes the notifications published by the database in response to DML or DDL changes to the registered object (that is, only when the notification type is EVENT_OBJCHANGE, and ignore notifications about other database events (such as instance startup or instance shutdown. From a later version, the handler can handle change notifications for each affected row in the OE. ORDERS table. In the "Add Table to existing registration" section after this article, you will add several lines of code to the handler so that it can process. notification of modified rows in the ORDER_ITEMS table.

Create registration for Change Notification

After creating a notification handler, you must create a query registration for it. In this example, you must query the OE. ORDER table during registration 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 a query registration for the orders_nf_callback notification handler.

Listing 3. Create query registration for the notification Handler

DECLAREREGDS SYS.CHNF$_REG_INFO;regid NUMBER;ord_id NUMBER;qosflags NUMBER;BEGINqosflags := 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 for the ORDERS table and uses orders_nf_callback as the notification handler. Now, if you use DML or DDL statements to modify the ORDERS table and submit transactions, the orders_nf_callback function is automatically called. For example, you may execute the following UPDATE statement for the ORDERS table and submit 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 has published a notification to respond to the preceding transactions, you can check the nfresults table:

SELECT TO_CHAR(operdate, 'dd-mon-yy hh:mi:ss') operdate, tblname, rslt_msg FROM nfresults;

The results should be as follows:

OPERDATE              TBLNAME     RSLT_MSG--------------------- ----------- ---------02-mar-06 04:31:28    OE.ORDERS   Not Found02-mar-06 04:31:29    OE.ORDERS   Not Found

From the above results, we can clearly see that orders_nf_callback is working normally, but no client script is found. This is not surprising in this example, because you have not created the dropResults. php script specified in the URL. For more information about the dropResults. php script, see build a client later in this article.

[1] [2] [3]