Re: Performance issue while loading large amount of data

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3e257155$0$235$ed9e5944_at_reading.news.pipex.net...

< cutty roo>

> 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.

Hi Niall,

Excellent point. The amount of undo with a direct load certainly is not going to put much stress on any modern disk systems (or any ancient ones for that matter).

One thing that seems to always cause confusion though is the amount of *redo* that gets generated. The answer is of course that it depends (on logging option) but I've argued this one a few times.

>
> 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?
>

Not sure ? The undo typically has some dd stuff ...

Cheers

Richard Received on Thu Jan 16 2003 - 07:00:20 CST