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

From: <dgoulet_at_vicr.com>
Date: Tue, 10 Dec 2002 06:14:21 -0800
Message-ID: <F001.0051607C.20021210061421@fatcity.com>

DBMS jobs are good things for stuff that can occur totally within the database and on a scheduled basis only. It sounds, RAJ, like your developers made a real mess. What would have been a better idea would have been to have the trigger update a table that the job on a scheduled basis checked for what it needed to do.

Dick Goulet

____________________Reply Separator____________________
Author: "Jamadagni; Rajendra" <Rajendra.Jamadagni_at_espn.com>
Date:       12/10/2002 4:38 AM

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!

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2654.45">
<TITLE>RE: a PL/SQL design question.</TITLE>
</HEAD>
<BODY>

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           &nbsp ;  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!


</BODY>
</HTML>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: dgoulet_at_vicr.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/basic attachment: MS-DOS text file

Received on Tue Dec 10 2002 - 08:14:21 CST