Oracle-L: RE: a PL/SQL design question.

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Tue, 10 Dec 2002 04:38:46 -0800
Message-ID: <F001.00515ED0.20021210043846@fatcity.com>

Jeremy, as I wholeheartedly agree that dbms_job is a good thing, here is my recent experience ...

One of the development group recently posed us the question "Can we use a db trigger to fire a dbms_job to be executed only once?" we reluctantly agreed only on the condition that this job will not be a repeating job as 9012 has had its own problems with dbms_job (the server sometimes forgets that there a jobs to run ...).

The dev team tested it in ACPT and okayed it to go. That night I was on call and computer room called me to say that the system is very slow and one of the support person called to say that they were getting ora-4030 errors on simple selects.

Well I logged on, looked at the system, it showed some load, but then I looked at dba_job queue and boy there were 14000 jobs sitting waiting to be run.

bottom line: I shut off job_queue_processes to zero, disabled the triggers on the tables that submitted these jobs, gave all the details to the developer and his manager after waking them up at 2am and received a promise that they will fix the code tomorrow before 12noon. They did.

The reason, the development team "didn't anticipate" that there will be so many changes so they didn't optimize their code.

I am all for AQ solution ... though I like dbms_job and they do work as advertised unless of course you are using 901x where thee are some bugs.

My $0.02

Raj

Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



text/plain attachment: ESPN_Disclaimer.txt

Received on Tue Dec 10 2002 - 06:38:46 CST