0

My application currently polls database every n seconds to see if there are any new records.

To reduce network round trips, and CPU cycles of this polling i was thinking to replace it with CQN based approach where database will itself update subscribed application if there is any Commit to database.

The only problem is what if Oracle was NOT able to notify application due to any connection issue between oracle and subscribed application or if the application was crashed or killed due to any reason? ... Is there a way to know if application have missed any CQN notification?

Is polling database via application code itself the only way for mission critical applications?

Sana.91
  • 1,999
  • 4
  • 33
  • 52

1 Answers1

1

You didn't say whether every 'n' seconds means you're expecting data every few seconds, or you just need your "staleness" to as low as that. That has an impact on the choice of CQN, because as per docs, https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adfns/cqn.html#GUID-98FB4276-0827-4A50-9506-E5C1CA0B7778

"Good candidates for CQN are applications that cache the result sets of queries on infrequently changed objects in the middle tier, to avoid network round trips to the database. These applications can use CQN to register the queries to be cached. When such an application receives a notification, it can refresh its cache by rerunning the registered queries"

However, you have control over how persistent you want the notifcations to be:

"Reliable Option: By default, a CQN registration is stored in shared memory. To store it in a persistent database queue instead—that is, to generate reliable notifications—specify QOS_RELIABLE in the QOSFLAGS attribute of the CQ_NOTIFICATION$_REG_INFO object. The advantage of reliable notifications is that if the database fails after generating them, it can still deliver them after it restarts. In an Oracle RAC environment, a surviving database instance can deliver them. The disadvantage of reliable notifications is that they have higher CPU and I/O costs than default notifications do."

Connor McDonald
  • 10,418
  • 1
  • 11
  • 16
  • Yes 'n' seconds means very few seconds like 30 seconds – Sana.91 Apr 11 '22 at 05:02
  • What if application failed to receive notification that oracle sent? Will it get missed entirely? – Sana.91 Apr 11 '22 at 05:53
  • If you're using the "reliable" option they will queue up, so I think they'll be obtained next time your application asks if there are any notifications – Connor McDonald Apr 11 '22 at 06:05
  • are database queues scalable? I mean, can i have multiple instances of same process register to same database queues? .. Do they have concepts like Consumer groups in Redis streams? – Sana.91 Apr 11 '22 at 08:34
  • A single node will give you 2000 enqueue/dequeues per second, and you can go to ridiculous extremes if you need to. Ref: https://www.oracle.com/a/otn/docs/database/jmssq-db19c-technicalbrief.pdf – Connor McDonald Apr 12 '22 at 03:56
  • i explicitly tested a case where i crashed my application that created a CQN registration wit reliable option. once it restarted, it didnt receive any CQN notification that it missed while application was shutdown. What could be wrong? – Sana.91 May 12 '22 at 05:47