Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id gBAJ4Xt13790 for ; Tue, 10 Dec 2002 13:04:33 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id gBAJ4X313782 for ; Tue, 10 Dec 2002 13:04:33 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id HAA87643; Tue, 10 Dec 2002 07:46:41 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0051607C; Tue, 10 Dec 2002 06:14:21 -0800 Message-ID: Date: Tue, 10 Dec 2002 06:14:21 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: dgoulet@vicr.com Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: dgoulet@vicr.com Subject: Re:RE: a PL/SQL design question. Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="IMA.Boundary.90692593010" --IMA.Boundary.90692593010 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Content-Description: cc:Mail note part 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" 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! RE: a PL/SQL design question.

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!

--IMA.Boundary.90692593010 Content-Type: text/basic; name="ESPN_Disclaimer.txt" Content-Transfer-Encoding: 7bit Content-Description: MS-DOS text file Content-Disposition: inline; filename="ESPN_Disclaimer.txt" ********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************2 --IMA.Boundary.90692593010-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: dgoulet@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@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).