Re: Massive update troubles

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).

Content-Type: text/plain; name="ReadMe.txt"; charset="us-ascii" Content-Transfer-Encoding: 7bit

The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail.

This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact Postmaster_at_fatcity.com for clarification.

--=_mixed 007D0A3188256C09_=--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still_at_radisys.com

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).



application/octet-stream attachment: djerman.vcf