Re: UNTO TBS behavior in 9i
- From: "Paul Drake" <bdbafh@xxxxxxxxx>
- To: veeeraman@xxxxxxxxx
- Date: Wed, 24 Jan 2007 13:14:24 -0500
On 1/24/07, Ram Raman <veeeraman@xxxxxxxxx> 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.
Last time we ran the process, the 30GB UNDO TBS was close to 100% free
when we started the process and the UNDO TBS became 100% full and then erred
out because of lack of space. Why would the undo TBS grow out to be almost
30GB causing the process to fail esp. when no other process is running in
the database. The only other active processes are Oracle background
processes. Oracle is 9206.
We do have the option of using Direct path insert and turning the logging
off, but this behavior is perplexing.
Ram,
Is it possible for you to load this data from outside the database via
sqlldr?
You would be surprised at how much less undo is generated via direct path,
as no other session can see the written data blocks until the load has
completed (as they are above the datafile high water mark at that time).
Paul
On 1/23/07, Hemant K Chitale <hkchital@xxxxxxxxxxxxxx> wrote:
>
>
> 1. If you are doing multiple commits during the insert run, each
> committed batch remains in UNDO for the duration of 2 hours. You should
> reduce UNDO_RETENTION.
>
> 2. UNDO is maintained for both Table Rows and Index updates -- eg
> when you INSERT,
> you are adding entries to indexes on the table as well. So UNDO is
> maintained for that
> as well.
>
> 3. You can monitor the size of your transaction by querying
> V$TRANSACTION -- that
> would show the number of undo "records" (table+index entries) and
> undo blocks used
> between every commit.
>
> 4. You could avoid undo and redo both -- eg
> alter table target_table nologging ;
> insert /*+ APPEND */ into target_table ....
> <finally> commit ; alter table target_table logging;
>
>
>
> At 02:19 PM Tuesday, Ram Raman wrote:
> >Hi all,
> >
> >We are running a batch process to insert data from one table to
> >another. The data inserted is over than 10 million rows. We had
> >about 20GB of UNDO tablespace. UNDO_RETENTION is 7200. When we start
> >the insert process no other process runs and all the time during the
> >insert process nothing else runs. We insert about 5.5 Gb of data
> >including indexes.
> >
>
>
> Hemant K Chitale
> http://web.singnet.com.sg/~hkchital<http://web.singnet.com.sg/%7Ehkchital>
> and
> http://hemantoracledba.blogspot.com
>
>
--
-- ALTER SESSION SET EVENTS 'immediate trace name hanganalyze level 4';
Other related posts:
