Re: PL/SQL not as easy as it looks.

Calvin got it right. In fact, as I noted elsewhere in this thread, the problem was my code. There's an UPDATE with no WHERE clause, that's updating each of 6,000,000 or so rows, and doing it about 5,900 times all within one commit. No wonder my rollback segments can't handle the roughly 36 TRILLION rows.

So just allocating more space wasn't likely to help.

Thanks for paying attention, Calvin.

• kevin

Calvin Crumrine wrote:
>
> I don't like trial and error space allocation-that's reactive, not proactive. I thought that
> the rollback segment needs to be large enough to hold all the rows being modified between
> commits, but no larger. Since Kevin's rollback segement is large enough to accomodate a copy of
> the entire summary table it seems to me that it should be large enough, but it obviously isn't.
>
> Why not? What non-obvious factors am I missing in calculating my own rollback space?
>
> Andreas Michler wrote:
> >
> > The problem is your rollback space.
> > Create a rollback segment in an tablespace with enough disk space as:
> >
> > create rollback segment rbbig tablespace XXXXXXX storage(initial 10M next 10M
> > minextents 2 maxextents 200);
> > alter rollback segment rbbig online;
> >
> > The rollback segment will grow up to 200 MB.
> >
> > the first statement in your pl-sql must be
> >
> > set transaction use rollback segment rbbig;
> >
> > or put an commit in the fetch loop, which commits every transaction
> > immediately.
> >
> > Kevin O'Gorman wrote:
> >
> > > I'm trying my first PL/SQL program, to recode an update to a summary
> > > table that was originally three SQL commands. The result is a program
> > > that looks right, and works on very small inputs, but runs very
> > > slowly and crashes before finishing on real inputs (it fails in extending
> > > a rollback segment).
> > >
> > > I could sure use some pointers.
> > >
> > > I've got a modest sized (10K rows) delta table I'm using to update a
> > > large (6M rows) summary table. The SQL had an UPDATE, an INSERT and
> > > a DELETE command, and had cache behavior I thought I could improve by
> > > making the update/insert/delete decision on a row-by-row basis.
> > >
> > > Here's the code, modified to remove substitution variables which just
> > > allow me to use different table names. I use two cursors, the outer
> > > one (c1) scans the delta. The inner one (cview) looks up
> > > the corresponding record in the summary (if one exists) using the
> > > fields of its primary key. There will be at most one such record, so
> > > that part is not a loop. The code looks clean to my newbie eye, and
> > > I really don't know what I did wrong, but it must be something.
> > >
> > > The rollback segments are large enough to accomodate a copy of the
> > > entire summary table, so I have no clue what I'm doing that would
> > > exhaust that resource.
> > >
> > > HELP???
> > >
> > > -----------------------------------------------
> > >
> > > declare
> > > deltarec DELTA%ROWTYPE;
> > > cursor c1 is
> > > select * from DELTA;
> > > updates integer;
> > > deletes integer;
> > > inserts integer;
> > > begin
> > > updates := 0;
> > > deletes := 0;
> > > inserts := 0;
> > > for deltarec in c1 loop
> > > declare
> > > viewrec SUMMARY%ROWTYPE;
> > > cursor cview is
> > > select /*+ FIRST_ROWS */ * from SUMMARY w
> > > where
> > > w.w0_supp_nation = deltarec.w0_supp_nation and
> > > w.w0_cust_nation = deltarec.w0_cust_nation and
> > > w.w0_shipmonth = deltarec.w0_shipmonth and
> > > w.w0_ordermonth = deltarec.w0_ordermonth and
> > > w.w0_partkey = deltarec.w0_partkey and
> > > w.w0_suppkey = deltarec.w0_suppkey;
> > > begin
> > > open cview;
> > > fetch cview into viewrec;
> > > if cview%FOUND then
> > > if viewrec.w0_count = - deltarec.w0_count then
> > > deletes := deletes + 1;
> > > delete from SUMMARY w where
> > > w.w0_supp_nation = deltarec.w0_supp_nation and
> > > w.w0_cust_nation = deltarec.w0_cust_nation and
> > > w.w0_shipmonth = deltarec.w0_shipmonth and
> > > w.w0_ordermonth = deltarec.w0_ordermonth and
> > > w.w0_partkey = deltarec.w0_partkey and
> > > w.w0_suppkey = deltarec.w0_suppkey;
> > > else
> > > updates := updates + 1;
> > > update SUMMARY wu
> > > set
> > > wu.w0_quantity = wu.w0_quantity + deltarec.w0_quantity,
> > > wu.w0_volume = wu.w0_volume + deltarec.w0_volume,
> > > wu.w0_cost = wu.w0_cost + deltarec.w0_cost,
> > > wu.w0_count = wu.w0_count + deltarec.w0_count;
> > > end if;
> > > else
> > > inserts := inserts + 1;
> > > insert into SUMMARY
> > > (
> > > w0_supp_nation,
> > > w0_cust_nation,
> > > w0_shipmonth,
> > > w0_ordermonth,
> > > w0_partkey,
> > > w0_suppkey,
> > > w0_quantity,
> > > w0_volume,
> > > w0_cost,
> > > w0_count
> > > ) values (
> > > deltarec.w0_supp_nation,
> > > deltarec.w0_cust_nation,
> > > deltarec.w0_shipmonth,
> > > deltarec.w0_ordermonth,
> > > deltarec.w0_partkey,
> > > deltarec.w0_suppkey,
> > > deltarec.w0_quantity,
> > > deltarec.w0_volume,
> > > deltarec.w0_cost,
> > > deltarec.w0_count
> > > );
> > > end if;
> > > close cview;
> > > end; -- inner block
> > > end loop; -- c1 loop
> > > delete from counts;
> > > insert into counts
> > > values(inserts, deletes, updates);
> > > end; -- outer block
> > > /
> > >
> > >
> > > --

--
Kevin O'Gorman (805) 650-6274 kogorman_at_pacbell.net At school: kogorman_at_cs.ucsb.edu
Permanent e-mail forwarder: Kevin.O'Gorman.64_at_Alum.Dartmouth.org Received on Tue Dec 28 1999 - 20:59:16 CST