Re: Performance issue while loading large amount of data

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:RdcV9.24793$jM5.65715_at_newsfeeds.bigpond.com...
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> news:EzZU9.24111$jM5.64133_at_newsfeeds.bigpond.com...
> OK, kids are in bed, time to do some investigating (and starting by
looking
> at a few block dumps is a good way to go).

Did I just die and go to Geek heaven?

> Couple of birds with one stone here. Firstly, an insert does generate
undo,
> each and every one of them and the amount of undo is proportional to the
> number of rows inserted as Howard states. However, the *issue* I have with
> the examples in this discussion and the reason why it generates so little
> undo per row is that they all use a subselect. By doing so, Oracle is
using
> an *array insert* mechanism and as such it dramatically reduces the amount
> of undo that gets generated as multiple rows being inserted into the same
> block can be recorded very efficiently within the *one* undo record.

Indeed, something that should be remembered for the purposes of the Original posters question (remember that) is that the type of insert you are performing can make performance vary considerably. Indeed we can do even better than the Bowie example.

SQL> create table insert_demo as select * from dba_objects where 'Black'='White';

Table created.

SQL> insert into insert_demo select * from dba_objects;

29524 rows created.

SQL> @show_transaction_stats; -- just the same select from v$transaction as before.

START_UBAFIL UBAFIL START_UBABLK UBABLK USED_UBLK USED_UREC ------------ ---------- ------------ ---------- ---------- ----------

           2 2 2281 2296 14 744

So I get pretty similar figures when doing insert ... select... to Richard. 112k of undo for 29000 records or 3.8 bytes per row. But we don't want to give you that...

SQL> rollback;

Rollback complete.

SQL> insert /*+ append */ into insert_demo select * from dba_objects;

29524 rows created.

• lets do a direct path load instead

SQL> @show_transaction_stats;

START_UBAFIL UBAFIL START_UBABLK UBABLK USED_UBLK USED_UREC ------------ ---------- ------------ ---------- ---------- ----------

           2 0 398 0 1 1

Now we really can't do much better than this. That is one block of undo and one undo record. If you are doing large data loads it looks like direct path is the way to go. You do have to bear in mind the impact on indexes though.

Curiosity of course kills the cat though, when in another session I issue

SQL> select object_id from dba_objects
  2 where object_name = 'INSERT_DEMO';

 OBJECT_ID

     30399

and then

alter system dump datafile 2 block 398 (which I read as being the undo block referred to above) I get a whole set of undo records *none* of which refer to objn 30399. Presumably I'm doing something daft here?

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************