Re: Why would an Insert /*+ append */ be slower than a straight insert?

Hi from Paris

your results are strange... from tests I made on 9.2.0.3 linux:

23:14:43 SQL> create table test2 as select * from test where 1=2;

Table created.

Elapsed: 00:00:01.02
23:14:56 SQL> truncate table test2;

Table truncated.

Elapsed: 00:00:01.03
23:15:05 SQL> insert into test2 select * from test;

1269760 rows created.

Elapsed: 00:03:05.06
23:18:35 SQL> truncate table test2;

Table truncated.

Elapsed: 00:00:01.05
23:18:40 SQL> insert /*+ append*/ into test2 select * from test;

1269760 rows created.

Elapsed: 00:00:14.08
23:19:33 SQL> truncate table test2;

Table truncated.

Elapsed: 00:00:00.02
23:21:57 SQL> insert into test2 select * from test;

1269760 rows created.

Elapsed: 00:02:25.02
23:24:28 SQL> truncate table test2;

Table truncated.

Elapsed: 00:00:00.08
23:24:43 SQL> insert /*+ append*/ into test2 select * from test;

1269760 rows created.

Elapsed: 00:00:13.09
23:25:04 SQL> Are you sure of the validity of your test ???

by the way, you must know that setting tables NOLOGGING won't change anything with insert statements.

"Ryan" <rgaffuri_at_cox.net> a écrit dans le message de news:UOaza.52438$823.38070_at_news1.east.cox.net...
>
> "Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> wrote in message
> news:m4cqcvkhg10ubofeq738bko4ns4nunaqj9_at_4ax.com...
> > On 22 May 2003 12:45:55 -0700, rgaffuri_at_cox.net (Ryan Gaffuri) wrote:
> >
> > >Here is the situation. Im testing a load process and I need to show
> > >results between the old method and a newer method to management.
> > >
> > >Here are the details.
> > >I did an
> > >
> > >Insert /*+ append */ into Master
> > >select * from stage;
> > >
> > >Also did it without append and the regular insert was about 40%
> > >faster. Here are the details.
> > >
> > >Oracle 8.1.7
> > >Solaris v5.8
> > >Each table in a seperate tablespace
> > >Same I/O device for everything
> > >Both tablespaces are taken offline to flush the DB_BUFFER_CACHE
> > >ALTER SYSTEM FLUSH SHARED_POOL; issued as well.
> > >2.5m rows inserted.
> > >Master table is truncated between loads
> > >There is a primary key with 7 columns that is enabled(yes I know
> > >append is faster with disabled, but Im testing all possibilities)
> > >Both TABLESPACSE are set to NOLOGGING
> > >Both Tables are set to NOLOGGING.
> > >Only one session is active on the server. Nothing else is going on
> > >
> > >any ideas? Id assume at worst Append would be the same speed?
> >
> >
> > The sensible approach would have been to enable event 10046 level 12
> > for the affected sessions, so you could investigate *what* it was
> > waiting for. Now you are calling for a Niemic-like advice, like
> > increase the buffer cache, increase the shared pool etc.
>
> Im in the learning process. I do not know much of anything about event
> tracing. I have posted that before.
> Ive started reading metalink documents on statspack.
>
> do you know any good documents on sqltrace? Any you recommend? How many
> trace events are there?
> I dont even know how to tell which trace file this created.
>
> I was wondering whether there are known conditions where append might be
> slower.
>
>
> >
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > To reply remove -verwijderdit from my e-mail address
>
>
Received on Thu May 22 2003 - 16:35:12 CDT