| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Fwd: Fwd: Undo Segment of 4GB for 1mn 4col update ?
Hmm.
Running the update with 100,000 records in the table :
Now I get different run-times :
1. 01hr:16min:41.55sec, 386 log-switches [10MB redologs] 2. 00hr:03min:39.76sec, 33 log-switches 3. 00h4:03min:37.56sec, 32 log-switches
What I can see is that the SMON was still busy when I started the first run after a SHUTDOWN ABORT and STARTUP [and also in the 5-hour failed attempt].
There doesn't seem to be any Row-Migration :
SQL> analyze table txn_user.txn_table compute statistics;
Table analyzed.
SQL> select * from dba_tables where table_name = 'TXN_TABLE';
OWNER TABLE_NAMEINITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS -------------- ----------- ----------- ----------- ------------ ---------- FREELIST_GROUPS LOG B NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT --------------- --- - ---------- ---------- ------------ ---------- ---------- AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE INSTANCES
------------------------------ ------------------------------
TABLESPACE_NAME CLUSTER_NAME
------------------------------ ------------------------------
IOT_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS ------------------------------ ---------- ---------- ---------- ----------
----------- ------------------------- ------------------- ---------- ----------
CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL PAR IOT_TYPE T S NES BUFFER_ ROW_MOVE
----- -------- ----------- --------- --- ------------ - - --- ------- --------
GLO USE DURATION SKIP_COR MON CLUSTER_OWNER DEPENDEN
--- --- --------------- -------- --- ------------------------------ --------
TXN_USER TXN_TABLE
SYSTEM
10 40 1 255
65536 1 2147483645 1
1 YES N 100000 1078 73 1433 0
70 7420 5 1 1
N ENABLED 100000 21-JAN-03 NO N N NO DEFAULT DISABLED
NO NO DISABLED NO DISABLED
SQL> exit
What AM I missing ? Some, silly error .. something I am overlooking ... Oviously, there was some other activity going on.. SMON had been cleaning up the earlier, failed, update. Was it also deleting freed extents in the Undo Tablespace ? [Locally-Managed, SYSTEM Allocation, AUTOMATIC SegmentSpaceManagement]
I am going to end up with egg on my face.
Hemant
----- Forwarded message from Hemant K Chitale <hkchital_at_singnet.com.sg> -----
Date: Mon, 20 Jan 2003 17:43:44 -0800
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
oops. The "cntr := cntr+1" is missing from the update.
The previous update round didn't have a counter, though.
I am re-running the update now.
Hemant
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).Received on Mon Jan 20 2003 - 21:59:35 CST
 |
 |