Take the 2-minute tour ×

I have a process wherein a program running in an application server must access a table in an Oracle database server whenever at least one row exists in this table. Each row of data relates to a client requesting some number crunching performed by the program. The program can only perform this number crunching serially (that is, for one client at a time rather than multiple clients in parallel).

Thus, the program needs to be informed of when data is available in the database for it to process. I could either

  1. have the program poll the database, or
  2. have the database trigger the program.

QUESTION 1: Is there any conventional wisdom why one approach might be better than the other?

QUESTION 2: I wonder if programs have any issues "running" for months at a time (would any processes in the server stop or disrupt the program from running? -- if so I don't know how I'd learn there was a problem unless from angry customers). Anyone have experience running programs on a server for a long time without issues? Or, if the server does crash, is there a way to auto-start a (i.e. C language executable) program on it after the server re-boots, thus not requiring a human to start it specifically?

Any advice appreciated.

UPDATE 1: Client is waiting for results, but a couple seconds additional delay (from polling) isn't a deal breaker.

share|improve this question
Far as I'm concerned, "depending on how well the program is written" should be "if the program is written correctly or not". A memory leak is a bug. You shouldn't make your design decisions on the basis of potential bugs. –  Code Bling Mar 14 '12 at 22:03
2) makes no sense. There's no such thing as a "C program"; there are just programs. C is a language in which you can author a program, but once compiled, a program is a program. If your OS can run programs, then it can run "C programs", too, and if it tells you it will kill them after 30 minutes, then it'll kill them no matter whether they're written in C or not. That said, if you write terrible code, you can make programs die after they run a certain time by consuming too many resources. –  Kerrek SB Mar 14 '12 at 22:03
@Kerrek just edited to remove reference to C since the question isn't really language-specific –  Code Bling Mar 14 '12 at 22:06
OK, perhaps I focused too much on the fact that I'm coding in C. Thank you Code Bling for the edits. The question boils down to, "is it better to run a program that continuously polls a database, or have the database trigger the execution of the program?" –  ggkmath Mar 14 '12 at 22:08
ggkmath, I guess it depends on how frequent the updates to the database may be, but you it doesn't necessarily have to run continously. You have to weigh the advantages of polling vs event-driven. Usually event-driven is more responsive and lightweight (memory and CPU), but there are other costs. For one thing, maintaining two linked code bases in different languages (one is your main program, the other runs on the database, usually) that are interdependent can be a nightmare. All that being said, I'm sure you'll get some great actual answers below! :) –  Code Bling Mar 14 '12 at 22:25
add comment

5 Answers

up vote 1 down vote accepted

1) have the program poll the database, since you don't want your database to be able to start host programs (because you'd have to make sure that only "your" program can be started this way).

The classic (and most convenient IMO) way for doing this in Oracle would be through the DBMS_ALERT package.

The first program would signal an alert with a certain name, passing an optional message. A second program which registered for the alert would wait and receive it immediatly after the first program commits. A rollback of the first program would cancel the alert.

Of cause you can have many sessions signaling and waiting for alerts. However, an alert is a serialization device, so if one program signaled an alert, other programs signaling the same alert name will be blocked until the first one commits or rolls back.

Table DBMS_ALERT_INFO contains all the sessions which have registered for an alert. You can use this to check if the alert-processing is alive.

2) autostarting or background execution depends on your host platform and OS. In Windows you can use SRVANY.EXE to run any executable as a service.

share|improve this answer
add comment

I recommend using a C program to poll the database and a utility such as monit to restart the C program if there are any problems. Your C program can touch a file once in a while to indicate that it is still functioning properly, and monit can monitor the file. Monit can also check the process directly and make sure it isn't using too much memory.

For more information you could see my answer of this other question:

When a new row in database is added, an external command line program must be invoked

Alternatively, if people aren't sitting around waiting for the computation to finish, you could use a cron job to run the C program on a regular basis (e.g. every minute). Then monit would be less needed because your C program will start and stop all the time.

share|improve this answer
Thanks David, in my case the client is waiting for the results. Your link is stating basically what I'm trying to do. The C program will process the first row, then delete this row when done, making the previous 2nd row now the first row, which the C program will then process, etc. I was going to have the program update some field in the database periodically (e.g. a heartbeat field) just to show it's running fine, but using an external file should also work. –  ggkmath Mar 14 '12 at 22:18
Thanks for the monit link. –  ggkmath Mar 14 '12 at 22:36
Yeah, a file is nice because you don't have to change your database schema, and it can be easily monitored by monit. –  David Grayson Mar 14 '12 at 22:43
add comment

There are simple job managers like gearman that you can use to send a job message from the database to a worker. Gearman has among others a MySQL user defined function interface, so it is probably easy to build one for oracle as well.

share|improve this answer
add comment

You might want to look into Oracle's "Change Notification":


I don't know how well this integrates with a "regular" C program though.

It's also available through .Net and Java/JDBC


share|improve this answer
add comment

I would like to give a more generic answer...

There is no right answer that applies every time. Some times you need a trigger, and some times is better to poll.

But… 9 out of 10 times, polling is much more efficient, safe and fast than triggering.

It's really simple. A trigger needs to instantiate a single program, of whatever nature, for every shot. That is just not efficient most of the time. Some people will argue that that is required when response time is a factor, but even then, half of the times polling is better because:

1) Resources: With triggers, and say 100 messages, you will need resources for 100 threads, with 1 thread processing a packet of 100 messages you need resources for 1 program.

2) Monitoring: A thread processing packets can report time consumed constantly on a defined packet size, clearly indicating how it is performing and when and how is performance being affected. Try that with a billion triggers jumping around…

3) Speed: Instantiating threads and allocating their resources is very expensive. And don’t get me started if you are opening a transaction for each trigger. A simple program processing a say 100 meessage packet will always be much faster that initiating 100 triggers…

3) Reaction time: With polling you can not react to things on line. So, the only exception allowed to use polling is when a user is waiting for the message to be processed. But then you need to be very careful, because if you have lots of clients doing the same thing at the same time, triggering might respond LATER, than if you where doing fast polling.

My 2cts. This has been learned the hard way ..

share|improve this answer
add comment

Your Answer


