The notification is "semi-synchronous" lack of a better term.

On the *commit* of the transaction, a little bit of extra (internal) DML occurs to log the fact that a notification is required.

Notifications then are handled asynchronously via AQ mechanisms and the job scheduler infrastructure. If (in your test environment) you turn off jobs (via job_queue_processes) you will see the jobs appear - enable jobs again and then notifications flow through as required.

Check figure 15.2 at http://docs.oracle.com/database/121/ADFNS/adfns_cqn.htm#ADFNS1015

for a nice schematic.

But notifications on rarely updated tables just makes sense - if the table is updated many times per second, then why bother with notifications - you would just query it regularly to refresh your view of it. If its updated (say) every few seconds, then perhaps standard client result cache technology is sufficient. If the update frequency is less than that, then notifications start to make more sense.

In terms of notification latency, its pretty good - a simple demo below

SQL> create table mcdonac.CQN as select * from all_objects;

Table created.

SQL> create index mcdonac.CQN_IX on mcdonac.CQN ( owner, object_name);

Index created.

SQL> drop table NOTIFY_LOG purge;

Table dropped.

SQL> create table NOTIFY_LOG(t timestamp default systimestamp, m varchar2(100));

Table created.

SQL> CREATE OR REPLACE PROCEDURE mcdonac.callback(ntfnds IN CQ_NOTIFICATION$_DESCRIPTOR) IS
  2   BEGIN
  3    insert into NOTIFY_LOG ( m ) values ('Got a notification');
  4    commit;
  5  END;
  6  /

Procedure created.

SQL> DECLARE
  2    reginfo   CQ_NOTIFICATION$_REG_INFO;
  3    v_cursor  SYS_REFCURSOR;
  4    regid     NUMBER;
  5
  6  BEGIN
  7    reginfo := cq_notification$_reg_info (
  8      'callback',
  9      DBMS_CQ_NOTIFICATION.QOS_QUERY,
 10      0, 0, 0
 11    );
 12
 13    regid := DBMS_CQ_NOTIFICATION.new_reg_start(reginfo);
 14
 15    OPEN v_cursor FOR
 16      SELECT dbms_cq_notification.CQ_NOTIFICATION_QUERYID, owner, object_name, object_type
 17      FROM mcdonac.cqn
 18      WHERE owner = 'SCOTT';
 19    CLOSE v_cursor;
 20
 21    DBMS_CQ_NOTIFICATION.reg_end;
 22  END;
 23  /

PL/SQL procedure successfully completed.


SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
05-OCT-15 01.09.26.459000 PM +08:00

1 row selected.

SQL> delete from mcdonac.cqn WHERE owner = 'SCOTT' and rownum = 1;

1 row deleted.

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
05-OCT-15 01.09.32.503000 PM +08:00

1 row selected.

SQL> commit;

Commit complete.

SQL> select * from notify_log;

T
---------------------------------------------------------------------------
M
----------------------------------------------------------------------------------------------------
05-OCT-15 12.55.29.533000 PM
Got a notification