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    <- kponprp                             kponprp(384,6377417808,-139639086618572,0)

4    -> ktcCommitTxn                        ktcCommitTxn(6339045472,0,0,0)

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

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

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”.

Advertisements

5 thoughts on “A few words about query notification internals

  1. Hi Dmitry,

    I’m seeing the ktcccDeleteCommitCallbacks calls on a commit even when QCN is not setup on a 11.2.0.4.6 database:

    38 -> xctCommitTxn xctCommitTxn(4216228728,0,210175448,8988)

    38 -> ktcCommitTxn ktcCommitTxn(5077655968,0,0,0)

    38 -> ktcccDeleteCommitCallbacks ktcccDeleteCommitCallbacks(5077655968,5078540584,1,0)

    38 kcrf_commit_force kcrf_commit_force(-139639050513408,0,0,5078972368)

    38 <- kcrf_commit_force kcrf_commit_force(1163,0,-103599988759424,0)

    38 <- ktcCommitTxn ktcCommitTxn(1076,0,0,0)

    38 kcrf_commit_force kcrf_commit_force(218673632,1,100,4088)

    38 create table t (a number ) ;

    Table created.

    SQL> insert into t values (1) ;

    1 row created.

    SQL> commit ;

    Commit complete.

    The stack trace above is captured during commit .

    Like

    • Hi Nenad,

      Sorry for delayed response.

      It seems that callback mechanics is more generic than QCN purposes.
      I’ve personally seen it on tables with AUDIT set but not each time.

      I’ll try to investigate in more detals and blog again 🙂

      Thank you for the idea.

      Like

  2. Just checked on Oracle 12.2 (Solaris)

    Still see

    CPU FUNCTION
    4 -> ktcCommitTxn ktcCommitTxn(2366574360,0,0,0)

    4 xctCommitTxn xctCommitTxn(0,0,491774832,84)

    4 -> ktcCommitTxn ktcCommitTxn(2366571328,0,0,0)

    4 -> kcrf_commit_force_int kcrf_commit_force_int(-139639050501072,0,157,214)

    4 <- kcrf_commit_force_int kcrf_commit_force_int(1645,0,68,0)

    4 <- ktcCommitTxn ktcCommitTxn(90,0,-139639050494624,0)

    4 ktcCommitTxn ktcCommitTxn(2366571328,0,0,0)

    4 kcrf_commit_force_int kcrf_commit_force_int(-139639093998304,1,157,-139639093841472)

    4 <- kcrf_commit_force_int kcrf_commit_force_int(1645,1,32687,0)

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s