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_NAME
------------------------------ ------------------------------
TABLESPACE_NAME CLUSTER_NAME
------------------------------ ------------------------------
IOT_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS
------------------------------ ---------- ---------- ---------- ----------
INITIAL_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
----------- ------------------------- ------------------- ---------- ----------
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 <[EMAIL PROTECTED]> -----
Date: Mon, 20 Jan 2003 17:43:44 -0800
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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: [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).
