Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Session does not terminate when a connection drops while running PLSQL.

Re: Session does not terminate when a connection drops while running PLSQL.

From: Rauf Sarwar <>
Date: 5 Jul 2002 21:12:06 -0700
Message-ID: <>

Sybrand Bakker <> wrote in message news:<>...
> You aren't serious about that, aren't you?
> You just provided the number 1 recipe for a disaster database,
> requiring round the clock table maintenance.
> Problems like long-running statements should be *resolved* by tuning
> the statement.
> However, you preach symptom-fighting here, adding an extra layer of
> crap. I'm working for more than 40 hours per week now with such a
> system, where I officially work 32, and I can tell you it is
> definitely *NOT FUNNY*
> Regards
> Sybrand Bakker, Senior Oracle DBA
> To reply remove -verwijderdit from my e-mail address

Tuning should be the first option *BUT* *NOT* *THE* *ONLY* option. You can tune a process so much after which you reach a point of diminishing return. Some processes *WILL* take long time to run based on their complexity no matter how much you tune them. Unless there is a *magic tool* outside of standard SQL/PLSql that you can suggest to tune a 5 minute process down to 5 seconds...which I would be more then happy to have to start looking at other alternatives like multi tasking. I work with an ERP system where we have 100's of DML processes and after spending lots of time and money on tuning, still take anywhere from 15 seconds to 1 to 2 hours to run due to their complexity. If we did not heavily multi task those processes via background jobs...not even a single customer would like to use our time consuming product. I am proud to say that even with all the complexity...our architecture manages it pretty well.

Let's say there are 5 separate *fully tuned* processes each taking approximately 2 minutes to run. Would you rather have a user wait for 10 minutes to complete all process one at a time *OR* utilizing the multi-processor horse power of the server multi task all processes and let the server deal with running those and let the user move onto doing something else? Time is money...remember that. Bean counters don't care whether your application is fully tuned or not. Because they let you spend $100,000 to buy that powerfull Unix server few months back...they want to utilize their endusers to do 150% of work in 8 hours. It is your job to justify spending that $100,000 and to make sure that endusers don't waste their time hanging around waiting for a process to get done.

I am *very* surprised to see that a *Senior DBA* with your experience *Only* wants to look at one solution just because the alternative adds a few objects in the database. -:)

//Rauf Sarwar Received on Fri Jul 05 2002 - 23:12:06 CDT

Original text of this message