| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Undo Segment of 4GB for 1mn 4col update ?
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),
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;
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 /
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 servicesReceived on Mon Jan 20 2003 - 19:19:01 CST
---------------------------------------------------------------------
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).
 |
 |