Monday, October 03, 2005

Watching the Watcher-Watchers

I've just found a process that uses on-update triggers to insert rows into a table:
Queue_Monitor_Table
( source_table_name varchar2(30)
  rowid             varchar2(16) /*lovely,  but not the point*/
  enqueued_yn       char(1)
  dequeued_yn       char(1) )
This is processed by a pl/sql "queue_monitor_listener" that is started in a background process. A "queue_monitor_listener" if you can't guess, repeatedly scans the table for rows where enqueued_yn = 'N' and then it enqueues the rowid to an AQ queue, called the "row_update_queue". When it's happy that a row has been enqueued, it updates the enqueued_yn flag to "Y". As you might fear, there is also dequeue process. This also runs in a background job. When this has successfully dequeued from the row_update_queue, and done its task ( which could be the subject of a new WTF ), it updates the Queue_Monitor_Table to set dequeued_yn = 'Y'. Nice. The fun really seems to have begun when, in order to improve performance, it was decided that several "queue_monitor_listener" processes should be started in parallel background processes.

No comments: