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