We have implemented solution using oracle Data Change Notification(DCN) and it works well.But some times we are having heavy load i.e about 50-100k records insert,which is causing too much current notification and CPU starvation.This in turn causing other java process to hung.
Is there any way to control or limit the number of concurrent DCN notification or is there any other solution solution..
When I was using Oracle DCN (in the Oracle 11 database), the documentation specifically mentioned that the functionality is meant for tables which are mostly read-only, ie. only rarely modified. It probably wasn't designed for the heavy updates you're experiencing in your DB. We had much lower volume of updates and still decided to ditch DCN, as it wasn't practical for us.
Are the inserts made by a single, or a few large transactions, or are there lots and lots of small transactions inserting a few rows each? The latter would quite certainly be more problematic, as it would trigger lots and lots of notifications. I'm afraid you might have to choose a different solution to the entire problem.
posted 2 years ago
Thanks Martin,Ours is having 50 to 100k small transactions.,commit for every transactions...I think we have to go for different solution..
Arockia Raj wrote:Is there any way to control or limit the number of concurrent DCN notification or is there any other solution solution..
As mentioned by Martin, according to the documentation it is mentioned as one of the best practices to use it only when your tables are mostly read-only (and modifications are the exception rather than the rule).
Best Practices wrote:For best performance of change notification, the following guidelines are presented. Registered objects are few and mostly read-only and that modifications to those objects are the exception rather than the rule. If the object is extremely volatile, then it will cause a large number of invalidation notifications to be sent, and potentially a lot of storage in the invalidation queue on the server. If there are frequent and a large number of notifications, it can slow down OLTP throughput due to the overhead of generating the notifications.It is also a good idea to keep the number of duplicate registrations on any given object low (ideally one) in order to avoid the same notification message being replicated to multiple recipients.