A few words about query notification internals

There is a good alternative to the database “polling” anti-pattern. It is a database/query change notification available for OCI clients for a while and for JDBC thin drivers quite recently. I decided to go deep into the OS level to see how smart the underlying   implementation in Oracle code itself and what is a related overhead for regular DML operations.

A little bit about test environment: Oracle 11.2.0.4/Solaris 11 on VirtualBox.

The idea is pretty simple: to compare the process of a simple insert with/and with out QCN.

Here is a Java code snippet to set up a Query Change Notification:

OracleConnection dataSourceListenConnection = (OracleConnection) dataSourceListen.getConnection();
instrumentOracleConnection(dataSourceListenConnection);
Properties prop = new Properties();

prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "true");
prop.setProperty(OracleConnection.DCN_NOTIFY_CHANGELAG,"0");
prop.setProperty(OracleConnection.NTF_LOCAL_TCP_PORT,"40000");

DatabaseChangeRegistration dcr = dataSourceListenConnection.registerDatabaseChangeNotification(prop);
Thread.currentThread().sleep(1000* DELAY);
dcr.addListener(new DatabaseChangeListener() {
    @Override
    public void onDatabaseChangeNotification(DatabaseChangeEvent databaseChangeEvent) {
        System.out.println("=============================================================");
        System.out.println("=================="+ "notified:" + databaseChangeEvent.getEventType().name() + "   " + Thread.currentThread().getId() + "===========================================");
        System.out.println("=============================================================");
        QueryChangeDescription[] qChangeDescriptions = databaseChangeEvent.getQueryChangeDescription();
        if (qChangeDescriptions != null) {
            for (int i = 0; i < qChangeDescriptions.length; i++) {
                System.out.print(qChangeDescriptions[i].getQueryChangeEventType().name());
            }
        }
        TableChangeDescription[] tableChangeDescription = databaseChangeEvent.getTableChangeDescription();
        if (tableChangeDescription !=null) {
            for (int i = 0; i < tableChangeDescription.length; i++) {
                System.out.print("tblCD:" + tableChangeDescription[i]);
            }
        }
    }
});
Thread.currentThread().sleep(1000* INTDELAY);
Statement stmt = dataSourceListenConnection.createStatement();
// associate the statement with the registration:
((OracleStatement) stmt).setDatabaseChangeRegistration(dcr);
Thread.currentThread().sleep(1000* INTDELAY);
ResultSet rs = stmt.executeQuery("select * from QN1 where f1=4");

I started with a pretty simple dtrace script:

dtrace -n 'profile-497/pid == $target/ { @[ustack(40, 2000)] = count(); } tick-20s { exit(0); }' -p 1876 > insout_no.out

and soon found that the main difference is on-commit process described very well in Frits Hoogland’s blog

Here is my final dtrace script that shows the main difference between “QCN inserts” and “simple inserts”.

#!/usr/sbin/dtrace -CFZs

pid$1::kpon*:entry,
pid$1::kcrf_commit*:entry,
pid$1::ktcccDeleteCommitCallbacks:entry,
pid$1::xctCommitTxn:entry,
pid$1::ktcCommitTxn:entry
{
 printf("%s(%d,%d,%d,%d) \n",probefunc,arg0,arg1,arg2,arg3);
}


pid$1::kpon*:return,
pid$1::kcrf_commit*:return,
pid$1::ktcccDeleteCommitCallbacks:return,
pid$1::xctCommitTxn:return,
pid$1::ktcCommitTxn:return
{
 /*ustack();*/
 printf("%s(%d,%d,%d,%d) \n",probefunc,arg0,arg1,arg2,arg3);
}

and finally my results:

“Simple” insert:

  3  -> xctCommitTxn                          xctCommitTxn(0,0,216432960,59)

  3    -> ktcCommitTxn                        ktcCommitTxn(6339042896,0,0,0)

  3      -> kcrf_commit_force                 kcrf_commit_force(-139639050494144,0,0,6340341424)

  3      <- kcrf_commit_force                 kcrf_commit_force(1379,0,-69268715635744,0)

  3    <- ktcCommitTxn                        ktcCommitTxn(1146,0,-139639050491408,0)

  3  <- xctCommitTxn                          xctCommitTxn(755,29720,221535760,0)

 

“QCN” inserts

4  -> xctCommitTxn                          xctCommitTxn(0,0,216432960,59)

4    -> kponprp                             kponprp(1,-139639086618624,2,23)

4     ktcCommitTxn                        ktcCommitTxn(6339045472,0,0,0)

4      -> kcrf_commit_force                 kcrf_commit_force(-139639050508016,0,0,6340341424)

4       ktcccDeleteCommitCallbacks        ktcccDeleteCommitCallbacks(6339045472,6339991856,1,0)

4        -> kponcmtcbk                      kponcmtcbk(6376984944,6339045472,-1,1)

4          -> kponpst                       kponpst(6377401960,8,23,6377413256)

4          <- kponpst                       kponpst(1239,0,-139639050500336,0)

4        <- kponcmtcbk                      kponcmtcbk(347,1,0,0)

4      <- ktcccDeleteCommitCallbacks        ktcccDeleteCommitCallbacks(1670,1,6339991984,0)

4    <- ktcCommitTxn                        ktcCommitTxn(1146,0,0,0)

4  <- xctCommitTxn

 

You can easily see that setting up query/database change notification establish a kind of Callback into commit process (call to ktcccDeleteCommitCallbacks), kponpst function inside seems related to AQ (guessed based on bugs review on MOS) and may mean “post into a queue”.