Re: Massive update troubles

"should" being the operative word....they "should" be doing the transforms prior to or during load (in my opinion), since they're file-filter or file-merge operations mostly.

Bart Cortenbach wrote:

> "...the SAP requirements are slowly mutating...",
> "...the developers are still tweaking the process..."
>
> If the developpers are still tweaking the process, and the requirements
> still evolving, it means that they are still in a test phase.
> If they are still in a test phase, they shouldn't use production files but
> test files...smaller files.
>
> Don Jerman <djerman_at_dot.state.nc.us>@fatcity.com on 2002-08-01 17:14:40
>
> Please respond to ORACLE-L_at_fatcity.com
>
> Sent by: root_at_fatcity.com
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc:
>
> 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).
>
> (See attached file: djerman.vcf)

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