11 Using Continuous Query Notification (CQN)

Guaranteed Mode

In guaranteed mode, there are no false positives: the database sends the application a QRCN only when the query result set is guaranteed to have changed.

For example, suppose that an application registered the query in Example 11-1 for QRCN, that employee 201 is in department 10, and that these statements are executed:

UPDATE EMPLOYEES
SET SALARY = SALARY + 10
WHERE EMPLOYEE_ID = 201;

UPDATE EMPLOYEES
SET SALARY = SALARY - 10
WHERE EMPLOYEE_ID = 201;

COMMIT;

Each UPDATE statement in the preceding transaction changes the query result set, but together they have no effect on the query result set; therefore, the database does not send the application a QRCN for the transaction.

For guaranteed mode, specify QOS_QUERY, but not QOS_BEST_EFFORT, in the QOSFLAGS attribute of the CQ_NOTIFICATION$_REG_INFO object.

Some queries are too complex for QRCN in guaranteed mode. For the characteristics of queries that can be registered in guaranteed mode, see "Queries that Can Be Registered for QRCN in Guaranteed Mode".

Best-Effort Mode

Some queries that are too complex for guaranteed mode can be registered for QRCN in best-effort mode, in which CQN creates and registers simpler versions of them.

For example, the query in Example 11-2 is too complex for QRCN in guaranteed mode because it contains the aggregate function SUM.

SELECT SUM(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 20;

In best-effort mode, CQN registers this simpler version of the query in Example 11-2:

SELECT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 20;

Whenever the result of the original query changes, the result of its simpler version also changes; therefore, no notifications are lost from the simplification. However, the simplification might cause false positives, because the result of the simpler version can change when the result of the original query does not.

In best-effort mode, the database:

• Minimizes the OLTP response overhead that is from notification-related processing, as follows:

  • For a single-table query, the database determines whether the query result has changed by which columns changed and which predicates the changed rows satisfied.

  • For a multiple-table query (a join), the database uses the primary-key/foreign-key constraint relationships between the tables to determine whether the query result has changed.

• Sends the application a QRCN whenever a DML statement changes the query result set, even if a subsequent DML statement nullifies the change made by the first DML statement.

The overhead minimization of best-effort mode infrequently causes false positives, even for queries that CQN does not simplify. For example, consider the query in Example 11-1 and the transaction in "Guaranteed Mode". In best-effort mode, CQN does not simplify the query, but the transaction generates a false positive.

Some types of queries are so simplified that invalidations are generated at object level; that is, whenever any object referenced in those queries changes. Examples of such queries are those that use unsupported column types or include subqueries. The solution to this problem is to rewrite the original queries.

For example, the query in Example 11-3 is too complex for QRCN in guaranteed mode because it includes a subquery.

SELECT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (
  SELECT DEPARTMENT_ID
  FROM DEPARTMENTS
  WHERE LOCATION_ID = 1700
);

In best-effort mode, CQN simplifies the query in Example 11-3 to this:

SELECT * FROM EMPLOYEES, DEPARTMENTS;

The simplified query can cause objects to be invalidated. However, if you rewrite the original query as follows, you can register it in either guaranteed mode or best-effort mode:

SELECT SALARY
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
  AND DEPARTMENTS.LOCATION_ID = 1700;

Queries that can be registered only in best-effort mode are described in "Queries that Can Be Registered for QRCN Only in Best-Effort Mode".

The default for QRCN mode is guaranteed mode. For best-effort mode, specify QOS_BEST_EFFORT in the QOSFLAGS attribute of the CQ_NOTIFICATION$_REG_INFO object.

Committed DML Transactions

When the notification type is OCN, any DML transaction that changes one or more registered objects generates one notification for each object when it commits.

When the notification type is QRCN, any DML transaction that changes the result of one or more registered queries generates a notification when it commits. The notification includes the query IDs of the queries whose results changed.

For either notification type, the notification includes:

• Name of each changed table

• Operation type (INSERT, UPDATE, or DELETE)

• ROWID of each changed row, if the registration was created with the ROWID option and the number of modified rows was not too large. For more information, see "ROWID Option".

Committed DDL Statements

For both OCN and QRCN, these data definition language (DDL) statements, when committed, generate notifications:

• ALTER TABLE

• TRUNCATE TABLE

• FLASHBACK TABLE

• DROP TABLE

When the notification type is QRCN:

• The notification includes:

  • Query IDs of the queries whose results have changed

  • Name of the modified table

  • Type of DDL operation

• Some DDL operations that invalidate registered queries can cause those queries to be deregistered.

  For example, suppose that this query is registered for QRCN:

  SELECT COL1 FROM TEST_TABLE
    WHERE COL2 = 1;
  

  Suppose that TEST_TABLE has this schema:

  (COL1 NUMBER, COL2 NUMBER, COL3 NUMBER)
  

  This DDL statement, when committed, invalidates the query and causes it to be removed from the registration:

  ALTER TABLE DROP COLUMN COL2;
  

Deregistration

For both OCN and QRCN, deregistration—removal of a registration from the database—generates a notification. The reasons that the database removes a registration are:

• Timeout

  If TIMEOUT is specified with a nonzero value when the queries are registered, the database purges the registration after the specified time interval.

  If QOS_DEREG_NFY is specified when the queries are registered, the database purges the registration after it generates its first notification.

• Loss of privileges

  If privileges are lost on an object associated with a registered query, and the notification type is OCN, the database purges the registration. (When the notification type is QRCN, the database removes that query from the registration, but does not purge the registration.)

  For privileges needed to register queries, see "Prerequisites for Creating CQN Registrations".

A notification is not generated when a client application performs an explicit deregistration.

Global Events

The global events EVENT_STARTUP and EVENT_SHUTDOWN generate notifications.

In an Oracle RAC environment, these events generate notifications:

• EVENT_STARTUP when the first instance of the database starts up

• EVENT_SHUTDOWN when the last instance of the database shuts down

• EVENT_SHUTDOWN_ANY when any instance of the database shuts down

The preceding global events are constants defined in the DBMS_CQ_NOTIFICATION package.

PL/SQL CQN Registration Interface

The PL/SQL CQN registration interface is implemented with the DBMS_CQ_NOTIFICATION package. You use the DBMS_CQ_NOTIFICATION.NEW_REG_START function to open a registration block. You specify the registration details, including the notification type and notification handler, as part of the CQ_NOTIFICATION$_REG_INFO object, which is passed as an argument to the NEW_REG_START procedure. Every query that you run while the registration block is open is registered with CQN. If you specified notification type QRCN, the database assigns a query ID to each query. You can retrieve these query IDs with the DBMS_CQ_NOTIFICATION.CQ_NOTIFICATION_QUERYID function. To close the registration block, you use the DBMS_CQ_NOTIFICATION.REG_END function.

For step-by-step instructions, see "Using PL/SQL to Register Queries for CQN".

CQN Registration Options

You can change the CQN registration defaults with the options summarized in Table 11-1.

^Footnote 1 Applies only when notification type is QRCN.

^Footnote 2 Applies only when notification type is OCN.

Topics:

• Notification Type Option

• QRCN Mode (QRCN Notification Type Only)

• ROWID Option

• Operations Filter Option (OCN Notification Type Only)

• Transaction Lag Option (OCN Notification Type Only)

• Notification Grouping Options

• Reliable Option

• Purge-on-Notify and Timeout Options

SELECT * FROM table_name_from_notification
WHERE ROWID = rowid_from_notification;

DBMS_CQ_NOTIFICATIONS.INSERTOP + DBMS_CQ_NOTIFICATIONS.DELETEOP

Prerequisites for Creating CQN Registrations

These are prerequisites for creating CQN registrations:

• You must have these privileges:

  • EXECUTE privilege on the DBMS_CQ_NOTIFICATION package, whose subprograms you use to create a registration

  • CHANGE NOTIFICATION system privilege

  • SELECT privileges on all objects to be registered

  Loss of privileges on an object associated with a registered query generates a notification—see "Deregistration".

• You must be connected as a non-SYS user.

• You must not be in the middle of an uncommitted transaction.

• The dml_locks init.ora parameter must have a nonzero value (as its default value does).

  (This is also a prerequisite for receiving notifications.)

Queries that Can Be Registered for Object Change Notification (OCN)

Most queries can be registered for OCN, including those executed as part of stored procedures and REF cursors.

Queries that cannot be registered for OCN are:

• Queries on fixed tables or fixed views

• Queries on user views

• Queries that contain database links (dblinks)

• Queries over materialized views

Queries that Can Be Registered for Query Result Change Notification (QRCN)

Some queries can be registered for QRCN in guaranteed mode, some can be registered for QRCN only in best-effort mode, and some cannot be registered for QRCN in either mode. (For information about modes, see "Guaranteed Mode" and "Best-Effort Mode".)

Topics:

• Queries that Can Be Registered for QRCN in Guaranteed Mode

• Queries that Can Be Registered for QRCN Only in Best-Effort Mode

• Queries that Cannot Be Registered for QRCN in Either Mode

SELECT SALARY FROM EMPLOYEES, DEPARTMENTS
  WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
    AND DEPARTMENTS.LOCATION_ID = 1700;

Using PL/SQL to Register Queries for CQN

To use PL/SQL to create a CQN registration, follow these steps:

1. Create a stored PL/SQL procedure to serve as the notification handler.

2. Create a CQ_NOTIFICATION$_REG_INFO object that specifies the name of the notification handler, the notification type, and other attributes of the registration.

3. In your client application, use the DBMS_CQ_NOTIFICATION.NEW_REG_START function to open a registration block.

4. Run the queries that you want to register. (Do not run DML or DDL operations.)

5. Close the registration block, using the DBMS_CQ_NOTIFICATION.REG_END function.

Topics:

• Creating a PL/SQL Notification Handler

• Creating a CQ_NOTIFICATION$_REG_INFO Object

• Identifying Individual Queries in a Notification

• Adding Queries to an Existing Registration

PROCEDURE schema_name.proc_name(ntfnds IN CQ_NOTIFICATION$_DESCRIPTOR)

DESC CQ_NOTIFICATION$_REG_INFO

DECLARE
  v_cn_addr CQ_NOTIFICATION$_REG_INFO;

BEGIN
  -- Create object:

  v_cn_addr := CQ_NOTIFICATION$_REG_INFO (
    'HR.dcn_callback',                 -- PL/SQL notification handler
    DBMS_CQ_NOTIFICATION.QOS_QUERY     -- notification type QRCN
    + DBMS_CQ_NOTIFICATION.QOS_ROWIDS, -- include rowids of changed objects
    0,                          -- registration persists until unregistered
    0,                          -- notify on all operations
    0                           -- notify immediately
    );

  -- Register queries: ...
END;
/

SELECT EMPLOYEE_ID, SALARY, DBMS_CQ_NOTIFICATION.CQ_NOTIFICATION_QUERYID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 10;

EMPLOYEE_ID     SALARY CQ_NOTIFICATION_QUERYID
----------- ---------- -----------------------
        200       4288                       0
 
1 row selected.

DECLARE
  v_cursor SYS_REFCURSOR;

BEGIN
  -- Open existing registration
  DBMS_CQ_NOTIFICATION.ENABLE_REG(21);
  OPEN v_cursor FOR
    -- Run query to be registered
    SELECT DEPARTMENT_ID
      FROM HR.DEPARTMENTS;  -- register this query
  CLOSE v_cursor;
  -- Close registration
  DBMS_CQ_NOTIFICATION.REG_END;
END;
/

Best Practices for CQN Registrations

For best CQN performance, follow these registration guidelines:

• Register few queries—preferably those that reference objects that rarely change.

  Extremely volatile registered objects cause numerous notifications, whose overhead slows OLTP throughput.

• Minimize the number of duplicate registrations of any given object, to avoid replicating a notification message for multiple recipients.

Troubleshooting CQN Registrations

If you are unable to create a registration, or if you have created a registration but are not receiving the notifications that you expected, the problem might be one of these:

• The JOB_QUEUE_PROCESSES parameter is not set to a nonzero value.

  This prevents you from receiving PL/SQL notifications through the notification handler.

• You were connected as a SYS user when you created the registrations.

  You must be connected as a non-SYS user to create CQN registrations.

• You changed a registered object, but did not commit the transaction.

  Notifications are generated only when the transaction commits.

• The registrations were not successfully created in the database.

  To check, query the static data dictionary view *_CHANGE_NOTIFICATION_REGS. For example, this statement displays all registrations and registered objects for the current user:

  SELECT REGID, TABLE_NAME FROM USER_CHANGE_NOTIFICATION_REGS;
  

• Run-time errors occurred during the execution of the notification handler.

  If so, they were logged to the trace file of the JOBQ process that tried to run the procedure. The name of the trace file usually has this form:

  ORACLE_SID_jnumber_PID.trc
  

  For example, if the ORACLE_SID is dbs1 and the process ID (PID) of the JOBQ process is 12483, the name of the trace file is usually dbs1_j000_12483.trc.

  Suppose that a registration is created with 'chnf_callback' as the notification handler and registration ID 100. Suppose that 'chnf_callback' was not defined in the database. Then the JOBQ trace file might contain a message of the form:

  ****************************************************************************
     Run-time error during execution of PL/SQL cbk chnf_callback for reg CHNF100.
     Error in PLSQL notification of msgid:
     Queue :
     Consumer Name :
     PLSQL function :chnf_callback
     Exception Occured, Error msg:
     ORA-00604: error occurred at recursive SQL level 2
     ORA-06550: line 1, column 7: 
     PLS-00201: identifier 'CHNF_CALLBACK' must be declared
     ORA-06550: line 1, column 7:
     PL/SQL: Statement ignored
  ****************************************************************************
  

  If run-time errors occurred during the execution of the notification handler, create a very simple version of the notification handler to verify that you are actually receiving notifications, and then gradually add application logic.

  An example of a very simple notification handler is:

  REM Create table in HR schema to hold count of notifications received.
  CREATE TABLE nfcount(cnt NUMBER);
  INSERT INTO nfcount (cnt) VALUES(0);
  COMMIT;
  CREATE OR REPLACE PROCEDURE chnf_callback
    (ntfnds IN CQ_NOTIFICATION$_DESCRIPTOR)
  IS
  BEGIN
    UPDATE nfcount SET cnt = cnt+1;
    COMMIT;
  END;
  /
  

• There is a time lag between the commit of a transaction and the notification received by the end user.

Interpreting a CQ_NOTIFICATION$_DESCRIPTOR Object

When a CQN registration generates a notification, the database passes a CQ_NOTIFICATION$_DESCRIPTOR object to the notification handler. The notification handler can find the details of the database change in the attributes of the CQ_NOTIFICATION$_DESCRIPTOR object.

In SQL*Plus, you can list these attributes by connecting as SYS and running this statement:

DESC CQ_NOTIFICATION$_DESCRIPTOR

Table 11-4 summarizes the attributes of CQ_NOTIFICATION$_DESCRIPTOR.

Interpreting a CQ_NOTIFICATION$_TABLE Object

The CQ_NOTIFICATION$_DESCRIPTOR type contains an attribute called TABLE_DESC_ARRAY, which holds a VARRAY of table descriptors of type CQ_NOTIFICATION$_TABLE.

In SQL*Plus, you can list these attributes by connecting as SYS and running this statement:

DESC CQ_NOTIFICATION$_TABLE

Table 11-5 summarizes the attributes of CQ_NOTIFICATION$_TABLE.

Interpreting a CQ_NOTIFICATION$_QUERY Object

The CQ_NOTIFICATION$_DESCRIPTOR type contains an attribute called QUERY_DESC_ARRAY, which holds a VARRAY of result set change descriptors of type CQ_NOTIFICATION$_QUERY.

In SQL*Plus, you can list these attributes by connecting as SYS and running this statement:

DESC CQ_NOTIFICATION$_QUERY

Table 11-6 summarizes the attributes of CQ_NOTIFICATION$_QUERY.

Interpreting a CQ_NOTIFICATION$_ROW Object

If the ROWID option was specified during registration, the CQ_NOTIFICATION$_TABLE type has a ROW_DESC_ARRAY attribute, a VARRAY of type CQ_NOTIFICATION$_ROW that contains the ROWIDs for the changed rows. If ALL_ROWS was set in the OPFLAGS field of the CQ_NOTIFICATION$_TABLE object, then ROWID information is not available.

Table 11-7 summarizes the attributes of CQ_NOTIFICATION$_ROW.

Creating a PL/SQL Notification Handler

Create a a server-side stored PL/SQL procedure to process notifications as follows:

1. Connect to the database AS SYSDBA.

2. Grant the required privileges to HR:

   GRANT EXECUTE ON DBMS_CQ_NOTIFICATION TO HR;
   GRANT CHANGE NOTIFICATION TO HR;
   

3. Enable the JOB_QUEUE_PROCESSES parameter to receive notifications:

   ALTER SYSTEM SET "JOB_QUEUE_PROCESSES"=4;
   

4. Connect to the database as a non-SYS user (such as HR).

5. Create database tables to hold records of notification events received:

   -- Create table to record notification events.
   DROP TABLE nfevents;
   CREATE TABLE nfevents (
     regid      NUMBER,
     event_type NUMBER
   );
   
   -- Create table to record notification queries:
   DROP TABLE nfqueries;
   CREATE TABLE nfqueries (
     qid NUMBER,
     qop NUMBER
   );
   
   -- Create table to record changes to registered tables:
   DROP TABLE nftablechanges;
   CREATE TABLE nftablechanges (
     qid             NUMBER,
     table_name      VARCHAR2(100),
     table_operation NUMBER
   );
   
   -- Create table to record ROWIDs of changed rows:
   DROP TABLE nfrowchanges;
   CREATE TABLE nfrowchanges (
     qid        NUMBER,
     table_name VARCHAR2(100),
     row_id     VARCHAR2(2000)
   );
   

6. Create the procedure HR.chnf_callback, as shown in Example 11-6.

CREATE OR REPLACE PROCEDURE chnf_callback (
  ntfnds IN CQ_NOTIFICATION$_DESCRIPTOR
)
IS
  regid           NUMBER;
  tbname          VARCHAR2(60);
  event_type      NUMBER;
  numtables       NUMBER;
  operation_type  NUMBER;
  numrows         NUMBER;
  row_id          VARCHAR2(2000);
  numqueries      NUMBER;
  qid             NUMBER;
  qop             NUMBER;

BEGIN
  regid := ntfnds.registration_id;
  event_type := ntfnds.event_type;

  INSERT INTO nfevents (regid, event_type)
  VALUES (chnf_callback.regid, chnf_callback.event_type);

  numqueries :=0;

  IF (event_type = DBMS_CQ_NOTIFICATION.EVENT_QUERYCHANGE) THEN
    numqueries := ntfnds.query_desc_array.count;

    FOR i IN 1..numqueries LOOP  -- loop over queries
      qid := ntfnds.query_desc_array(i).queryid;
      qop := ntfnds.query_desc_array(i).queryop;

      INSERT INTO nfqueries (qid, qop)
      VALUES(chnf_callback.qid, chnf_callback.qop);

      numtables := 0;
      numtables := ntfnds.query_desc_array(i).table_desc_array.count;

      FOR j IN 1..numtables LOOP  -- loop over tables
        tbname :=
          ntfnds.query_desc_array(i).table_desc_array(j).table_name;
        operation_type :=
          ntfnds.query_desc_array(i).table_desc_array(j).Opflags;

        INSERT INTO nftablechanges (qid, table_name, table_operation) 
        VALUES (
          chnf_callback.qid,
          tbname,
          operation_type
        );

        IF (bitand(operation_type, DBMS_CQ_NOTIFICATION.ALL_ROWS) = 0) THEN
          numrows := ntfnds.query_desc_array(i).table_desc_array(j).numrows;
        ELSE
          numrows :=0;  -- ROWID info not available
        END IF;

        -- Body of loop does not run when numrows is zero.
        FOR k IN 1..numrows LOOP  -- loop over rows
          Row_id :=
 ntfnds.query_desc_array(i).table_desc_array(j).row_desc_array(k).row_id;

          INSERT INTO nfrowchanges (qid, table_name, row_id)
          VALUES (chnf_callback.qid, tbname, chnf_callback.Row_id);

        END LOOP;  -- loop over rows
      END LOOP;  -- loop over tables
    END LOOP;  -- loop over queries
  END IF;
  COMMIT;
END;
/

Registering the Queries

After creating the notification handler, you register the queries for which you want to receive notifications, specifying HR.chnf_callback as the notification handler, as in Example 11-7.

DECLARE
  reginfo   CQ_NOTIFICATION$_REG_INFO;
  mgr_id    NUMBER;
  dept_id   NUMBER;
  v_cursor  SYS_REFCURSOR;
  regid     NUMBER;

BEGIN
  /* Register two queries for QRNC: */
  /* 1. Construct registration information.
        chnf_callback is name of notification handler.
        QOS_QUERY specifies result-set-change notifications. */

  reginfo := cq_notification$_reg_info (
    'chnf_callback',
    DBMS_CQ_NOTIFICATION.QOS_QUERY,
    0, 0, 0
  );

  /* 2. Create registration. */

  regid := DBMS_CQ_NOTIFICATION.new_reg_start(reginfo);

  OPEN v_cursor FOR
    SELECT dbms_cq_notification.CQ_NOTIFICATION_QUERYID, manager_id
    FROM HR.EMPLOYEES
    WHERE employee_id = 7902;
  CLOSE v_cursor;

  OPEN v_cursor FOR
    SELECT dbms_cq_notification.CQ_NOTIFICATION_QUERYID, department_id
    FROM HR.departments
    WHERE department_name = 'IT';
  CLOSE v_cursor;

  DBMS_CQ_NOTIFICATION.reg_end;
END;
/

View the newly created registration:

SELECT queryid, regid, TO_CHAR(querytext)
FROM user_cq_notification_queries;

Result:

QUERYID REGID                               TO_CHAR(QUERYTEXT)
------- ----- ------------------------------------------------
     22    41 SELECT HR.DEPARTMENTS.DEPARTMENT_ID
                FROM HR.DEPARTMENTS
                  WHERE HR.DEPARTMENTS.DEPARTMENT_NAME  = 'IT'

     21    41 SELECT HR.EMPLOYEES.MANAGER_ID
                FROM HR.EMPLOYEES
                  WHERE HR.EMPLOYEES.EMPLOYEE_ID  = 7902

Run this transaction, which changes the result of the query with QUERYID 22:

UPDATE DEPARTMENTS
SET DEPARTMENT_NAME = 'FINANCE'
WHERE department_name = 'IT';
COMMIT;

The notification procedure chnf_callback (which you created in Example 11-6) runs.

Query the table in which notification events are recorded:

SELECT * FROM nfevents;

Result:

REGID EVENT_TYPE
----- ----------
   61          7

EVENT_TYPE 7 corresponds to EVENT_QUERYCHANGE (query result change).

Query the table in which changes to registered tables are recorded:

SELECT * FROM nftablechanges;

Result:

REGID     TABLE_NAME TABLE_OPERATION
----- -------------- ---------------
   42 HR.DEPARTMENTS               4

TABLE_OPERATION 4 corresponds to UPDATEOP (update operation).

Query the table in which ROWIDs of changed rows are recorded:

SELECT * FROM nfrowchanges;

Result:

REGID     TABLE_NAME              ROWID
----- -------------- ------------------
   61 HR.DEPARTMENTS AAANkdAABAAALinAAF