OTN Appreciation Day : Oracle Reports Server Job Queue Monitoring

Before I start, what’s this all about you might be thinking?  Well, Tim Hall recently floated the idea of an unofficial “OTN Appreciation Day” so everyone could create a short post about their favourite Oracle features.  So I figured I’d join in…and he thought it would help me “get back on the horse” with my blogging!

I actually really struggled with this post.  Not because I couldn’t think or anything, or don’t know anything 🙂 …but simply because when I started thinking about what features I’ve loved over the years, there are quite a lot of them, and trying to pick just one is pretty difficult.  Anyway, the feature I chose isn’t necessarily my favourite of all time, but hopefully it’s something a bit different, yet simple, and relates to Oracle Forms & Reports services.  I get the impression not too many people actually know about this, even though it’s been around for an age (since 6i), but it’s very easy to setup, and I’ve found to be quite invaluable at times.

I’m talking about the RW_SERVER_JOB_QUEUE table and it’s PL/SQL API, which once configured, will start populating a database with Reports Server job information.  Without this, all you have a is binary .dat persistence file on the Reports Server machine, leaving you with a browser as pretty much the only means of viewing the job queue/history details.

I’ve blogged about the setup process here, to keep this post short 😉  Configuring Oracle Reports Server Job Queue Monitoring

Why do I like this?  It’s easy enough to setup, and it’s helped me out a few times recently when monitoring the job queue processing.  Recently I’ve hit issues where a particular Report Server would occasionally stall, then get to a point where jobs would refuse to process, and eventually hit resource exhaustion issues at the OS level, to the point where the server would crash – not good!  So whilst the root cause is investigated, I created a simple function based on this feature, to email out an alert when a “Current” jobs count threshold was breached, and scheduled it to run from the database using DBMS_SCHEDULER every 10 mins.

Implementing this check monitored the queue counts, and triggered pro-active alerts if the job queue stalled: cr_function_rs_job_check

So that’s just a quick example of how I’ve made use of the feature for monitoring purposes, but you could also use it to summarize performance/success rates etc. over a period of time.

The table also holds data like start/finish/elapsed times and records the full command line executed at submission – perfect for keeping a detailed audit trail of processed reports, and useful when troubleshooting issues after the event.  A list of columns and information stored in the table can be found here: Structure of the RW_SERVER_JOB_QUEUE Table

Finally, here are a couple of other cool Oracle features I thought were just worth a quick mention in the spirit of OTN Appreciation Day…

Real-Time SQL Monitoring in Enterprise Manager Grid/Cloud Control
RMAN Automatic Block Recovery

#ThanksOTN! …and Tim@oraclebase for the idea!