Re: Performance issue while loading large amount of data

"Nuno Souto" <nsouto_at_optushome.com.au> wrote in message news:dd5cc559.0301121531.6b0149b6_at_posting.google.com...
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:<UUdU9.22027$jM5.59228_at_newsfeeds.bigpond.com>...
>
> > Then you must have been focusing too much on your wood carving and not
> > enough on the database ;)
>
> Heh! That too. Thank God!!! :D
>
> > I have to admit I have not performed an undo block dump in 9i but an
insert
> > took somewhere in the order of 80-100 bytes *per undo record* in 8.0
>
>
> Hmmmm, very strange indeed. That doesn't tally with what I got.
> Hang on: "per undo record"... What exactly does that mean in
> terms of inserted rows? Is it one per row inserted?
> Or one per block written?
>
>
> >
> > The rollback of an insert does not and has not reset the HWM. For the
simple
> > reason that it can't as other transactions could have got in there,
there's
> > no way for Oracle to know. Even a failed insert (say due to insufficient
> > space) will *not* reset the HWM.
>
>
> Yup, quite right. Sorry, mixed up on that one.
>
>
> > >
> > > I used to load 10 million rows and hardly use
> > > any rollback at all (<10M). That was in 8.0, no LMT.
> >
> > No way !! Need to see it to believe it (unless it we are talking some
form
> > of direct load ??)
>
>
> IIRC, we tested it with/without direct load (APPEND hint)
> and it still used bugger all rollback. Partitioned table,
> loading one partition per day. 8.0.5. AFAIK, they still running it.
>
> Same on V7.3, where I used to load 100Gb tables for a
> big data conversion with bugger rollback use.
> As soon as we did UPDATE/DELETE of course we had to be careful.
> But with INSERT, never an issue. Wouldn't use more than
> one extent in a rollback segment. According to v$rollstat
> anyways. No direct load.

Bear in mind that SQL Loader can/does do array inserts with an implied commit after each array is inserted. Likewise the 'rows' parameter could be specified, meaning that a commit is issued after the given number of rows is inserted in conventional mode. Which means that the amount of *outstanding* (ie, un-over-writeable) undo might well be quite small. But nevertheless quite a large amount of undo *in total* would be generated.

Whatever: here's a little demo which makes my point for me, I think:

SQL> insert into emp (empno) values (9362); 1 row created.

SQL> select used_ublk from v$transaction;  USED_UBLK

         1

SQL> show parameter db_block_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------
---
db_block_size                        integer     4096

So a single insert of just a 4-digit number causes an entire 4K block of rollback to be 'used'. And to prove it's this insert doing the 'using':

SQL> commit;
Commit complete.

SQL> select used_ublk from v$transaction; no rows selected

...so once the transaction's been committed, the undo disappears (ergo, it was this transaction generating it).

Or try this one:

SQL> create table objectstbl as select * from dba_objects where 1=2; Table created. [with no rows]

SQL> insert into objectstbl select * from dba_objects; 29359 rows created. [now its got rows inserted]

SQL> select used_ublk from v$transaction;  USED_UBLK

        40
[using 40 blocks of rollback, or 40*4K=160ish K of rollback]

SQL> analyze table objectstbl compute statistics; Table analyzed.
SQL> select num_rows, avg_row_len from dba_tables where table_name='OBJECTSTBL';
  NUM_ROWS AVG_ROW_LEN
---------- -----------

     29359 96 [=2,818,464 or 2.8MB of row insertion]

So, yes, the amount of rollback generated is relatively small (in this case, 163840/29359 about 6 bytes per row). But it's not nothing.

And just for the sake of completeness, how about this?

SQL> truncate table objectstbl;
Table truncated.

SQL> insert /*+APPEND*/ into objectstbl select * from dba_objects; 29359 rows created.

SQL> select used_ublk from v$transaction;  USED_UBLK

         1

Ooo-err. Do an append insert, and bugger-all rollback (TM Noons!) is generated. Which makes me think that your SQL Loads were indeed using the direct load method.

Regards
HJR [snip] Received on Sun Jan 12 2003 - 20:15:03 CST