Re: Massive update troubles

Thanks for the pointers, Jared -- the beatings are being scheduled now :)

Jared.Still_at_radisys.com wrote:

> Don,
>
> The biggest problem you have is you are working with duhvelopers.
>
> There are ways to write your code to allow testing without ridiculous
> actions like 'alter system kill session' every time your program doesn't
> do what you want during development.
>
> Such as:
>
> * Using a variable commit point that is set differently for development
> than for production.
>
> * Using a 'Development flag' for critical section of the code that allow
> you to check for
> actions to take. For instance, you may have a one row table that contains
> instructions
> such as 'STOP RIGHT NOW!', that are only checked when the development flag
> is on.
>
> Coupled with a lower commit point in devekopment, this makes stopping your
> program
> somewhat simpler, and is a bit more elegant than 'alter system kill
> session'.
>
> * Outputting diagnostic information to a DBMS PIPE when the DEBUG_FLAG is
> set, so
> that realtime diagnostics can be read from another process reading the
> pipe.
>
> etc, etc, etc.
>
> Get your company to buy them the following books, and make them read them:
>
> Code Complete http://www.amazon.com/exec/obidos/ASIN/1556154844
> This is written for C, but is full of good habits for any language.
>
> Writing Solid Code: http://www.amazon.com/exec/obidos/ASIN/1556155514
> Ignore the reviews on Amazon for this book. It's not as good as Code
> Complete,
> but still pretty good.
>
> Oracle PL/SQL Programming
> http://www.amazon.com/exec/obidos/ASIN/1565923359
>
> Jared
>
> PS. I'm not being cynical, really I'm not. :)
>
> Don Jerman <djerman_at_dot.state.nc.us>
> Sent by: root_at_fatcity.com
> 08/01/2002 02:14 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc:
> Subject: Massive update troubles
>
> I have a data conversion team working on our financial data,
> prepping it for load into SAP. My concept for the conversion
> process was to download the flat files, run programs written in C
> or Perl to transform the data, then use SQL*Loader to load them
> into relational tables for constraint testing and general
> analysis, prior to export to the SAP system.
>
> At some point early on I lost control, and this mutated into:
> download the flat files, load them into Oracle, run stored
> procedures to transform the data through 2 or 3 stages to a new
> schema for SAP. Still ok, you're thinking.
>
> But -- now we're running more than a couple hundred thousand rows
> at the time, and the developers are still tweaking the process
> (because the SAP requirements are slowly mutating -- another
> issue). Frequently the developers will stop a long running query
> with ALTER SESSION KILL -- this is working but often takes a very
> long time to roll back. Well, there's the rub -- this is a PC
> system, and the developers frequently want to make a tweak to
> their program and re-run it. This puts a tremendous load on what
> the PC isn't good at -- I/O.
>
> So I wind up with a frantic developer on the phone "the Oracle
> server's locked up!" and sure enough, he's right -- there's so
> much going on in there you can't do anything that requires a disk
> access..... The evolved response is shutdown abort, startup
> mount, recover, open. This always works and always takes about 3
> minutes. Naturally, I've moved them to their Very Own server, so
> that this doesn't disrupt other work.
>
> Can anyone turn their diagonstic eyes on this situation and
> suggest a better method for me to either limit the damage or
> recover from the problem? Or even a good method for analyzing
> the problem, given that we haven't the downtime to wait for all
> processes to complete (once in this state, a weekend can pass
> without successfully ending whatever the database is doing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Don Jerman
  INET: djerman_at_dot.state.nc.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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/x-vcard attachment: Card for Don Jerman