Re: UNTO TBS behavior in 9i
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: "Ram Raman" <veeeraman@xxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 25 Jan 2007 20:52:05 +0800
As Daniel has pointed out, if you have any ON INSERT Triggers on
the target table, those triggers
would also be generating Undo.
Have you been able to monitor the undo generation by querying V$TRANSACTION ?
-- eg if you could re-write the SELECT statement to fetch only
about 10% of the rows
and then see how many Undo Records and Undo Blocks does the
V$TRANSACTION entry show ?
Finally, why not use APPEND hint to generate Direct Path loading ?
alter table target_table nologging ;
insert /*+ APPEND */ into target_table SELECT .. < or try with
only a subset of rows>
<finally> commit ; alter table target_table logging;
If you are really just trying to investigate the perplexing
behaviour, you shouldn't really
need to do a 5.5GB insert and wait to hit 30GB Undo. Just try your
insert with a 10% subset
but monitor the operation in V$UNDOSTAT {assuming that no other
transactions are running
at the same time} or, better, V$TRANSACTION -- which you can sample
every second !
Hemant
At 01:19 AM Thursday, Ram Raman wrote:
Thanks for your replies.
1. We are not doing multiple commits. Just one INSERT INTO SELECT
statement with one commit at the end. There were no other
transactions when we started this one.
2. The space for both data and index is about 5.5Gb.
Hemant K Chitale
http://web.singnet.com.sg/~hkchital
and
http://hemantoracledba.blogspot.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
