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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Reply via email to