RE: Undo Segment of 4GB for 1mn 4col update ?

At the next set of runs, I did increment the counter variable. However as it is in a cursor and loop, the loop should still loop through all the records in the table and then error out with sql%notfound after the last record ?

Anyway, see my next mail on the timings when I ran it thrice with 100,000 row updates.
Hemant

At 07:33 PM 20-01-03 -0800, you wrote:
>It seems like you are in an infinte loop. Your counter cntr
>never gets incremented.
>
>-----Original Message-----
>Sent: Monday, January 20, 2003 8:19 PM
>To: Multiple recipients of list ORACLE-L
>
>
>
>I have been trying to run a "benchmark" of a server
>[9iRel2 on HPUX]
>The database is 9.2.0.2 with Extent Management Local and
>an Undo Tablespace.
>
>This is my table :
>create table txn_table
> (setrange number(2) not null,
> col1 varchar2(6),
> col2 varchar2(255),
> col3 number,
> col4 varchar2(45),
> update_date date
> );
>
>create index txn_table_setnumber_n1
> on txn_table(setrange);
>
>
>create index txn_table_update_dt_n1
> on txn_table(update_date);
>
>{SETRANGE will have values 0 to 5 and I am deliberately indexing this column
>to see if the database uses the index or does a FTS)
>
>The INSERT of 1 million records took 02:21.86 [2min] :
>
>DECLARE
>
>BEGIN
>FOR i IN 1..100000 LOOP
> IF MOD(i,5) = 0 THEN -- multiple of 5
> INSERT INTO txn_table VALUES (mod(i,5), to_char(i), 'the quick brown
>fox jumps over the lazy dog',i*2.4,'multiple of 5 ',sysdate);
> ELSE
> INSERT INTO txn_table VALUES (mod(i,5),to_char(i)||'
>','zxcv.,mnbasdfgf;lkjhjqwertpoiuyu',i*3-4,'not a multiple',sysdate);
> END IF;
>
>END LOOP;
>COMMIT;
>END;
>/
>
>However, my Update initially ran out of Undo Tablespace which had grown to
>2GB. I found that UNDO_RETENTION was 10800, reduced it to 30 and even
>bounced the Instance before re-running the Update. [I had also added 2 more
>files to the Undo Tablespace].
>During the first round of testing, another user was testing a WebMethods
>application. However, during the second round I was supposed to be the only
>person on the instance [with OEM connecting as DBSNMP, other than my SQLPlus
>session]. Yet, the update failed and all three Undo Tablespace files had
>grown to 2GB each.
>
>Why should the update take 5hours ? Why should it take some much undo ? Is
>the logic of the update plsql block wrong ? [I haven't put a c1%notfound ;
>I am using rowid
>from the fetch to go back to the table and update it]
>
>17:13:00 SQL> set serveroutput on size 50000;
>17:13:00 SQL>
>17:13:00 SQL> DECLARE
>17:13:00 2
>17:13:00 3 CURSOR C1 is
>17:13:00 4 SELECT SETRANGE,COL1,COL2, rowid
>17:13:00 5 from TXN_TABLE;
>17:13:00 6
>17:13:00 7
>17:13:00 8 P_SN number;
>17:13:00 9 P_C1 varchar2(6);
>17:13:00 10 P_C2 varchar2(255);
>17:13:00 11 P_D varchar2(15);
>17:13:00 12 P_Row rowid;
>17:13:00 13
>17:13:00 14 cntr number;
>17:13:00 15
>17:13:00 16 BEGIN
>17:13:00 17 cntr := 0;
>17:13:00 18 OPEN C1;
>17:13:00 19 loop
>17:13:00 20 FETCH C1 into P_SN, P_C1, P_C2, P_Row ;
>17:13:00 21 update txn_table set col4 =
>'updated'||to_char(mod(p_sn,5)),
>17:13:00 22 update_date = sysdate
>17:13:00 23 where rowid = P_Row ;
>17:13:00 24
>17:13:00 25 if cntr = 1000000 then
>17:13:00 26 dbms_output.put_line('exiting ...');
>17:13:00 27 exit;
>17:13:00 28 end if;
>17:13:00 29 end loop;
>17:13:00 30
>17:13:00 31 COMMIT;
>17:13:00 32 END;
>17:13:00 33 /
>DECLARE
>*
>ERROR at line 1:
>ORA-30036: unable to extend segment by 8192 in undo tablespace 'UNDOTBS1'
>ORA-06512: at line 21
>
>
>Elapsed: 05:31:09.32
>22:44:09 SQL>
>22:44:09 SQL> spool off
>
> 1* select usn, extents, rssize, xacts, writes, gets, optsize, hwmsize
>SQL> /
>
> USN EXTENTS RSSIZE XACTS WRITES GETS OPTSIZE
>---------- ---------- ---------- ---------- ---------- ---------- ----------
> HWMSIZE
>----------
> 0 7 450560 0 8460 2052885
> 450560
>
> 1 2 122880 0 230880 2056248
> 7462912
>
> 2 2 122880 0 1844253864 2815995
>4234141696
>
> 3 3 8511488 0 292502 2061328
> 8511488
>
> 4 3 1171456 0 270352 2057293
> 1171456
>
> 5 3 1171456 0 235868 2056307
> 2220032
>
> 6 3 1171456 0 239234 2056328
> 2220032
>
> USN EXTENTS RSSIZE XACTS WRITES GETS OPTSIZE
>---------- ---------- ---------- ---------- ---------- ---------- ----------
> HWMSIZE
>----------
>
> 7 3 1171456 0 350606 2058513
> 2220032
>
> 8 3 1171456 0 285134 2056422
> 1171456
>
> 9 2 122880 0 237370 14800561
>2416041984
>
> 10 2 122880 0 237826 2056313
> 67231744
>
>
>11 rows selected.
>
>SQL> exit
>
>SQL> show parameter undo
>
>NAME TYPE VALUE
>------------------------------------ -----------
>------------------------------
>undo_management string AUTO
>undo_retention integer 30
>undo_suppress_errors boolean FALSE
>undo_tablespace string UNDOTBS1
>
>
>Hemant K Chitale
>http://hkchital.tripod.com
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Hemant K Chitale
> INET: hkchital_at_singnet.com.sg
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>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).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Richard Ji
> INET: richard.ji_at_mobilespring.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>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).

Hemant K Chitale
My web site page is : http://hkchital.tripod.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).