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

From: Antoine BRUNEL <antoinebrunel/yahoo.fr>
Date: Fri, 23 May 2003 00:10:40 +0200
Message-ID: <3ecd4ae5$0$27235$79c14f64@nan-newsreader-03.noos.net>

more investigations....

I noticed from perfstat reports that "redo size" without append is 454,613,876 and with append: 501,660 .

So, here are wait events:

without append

Event                                               Waits    Time (s) Ela

Time

-------------------------------------------- ------------ ----------- ------
--
CPU time                                                          124
86.02
free buffer waits                                      63          12
8.03
log file parallel write                               712           3
1.74
control file parallel write                           163           2
1.53
db file scattered read                                859           2

1.12

with "append" hint

Event                                               Waits    Time (s) Ela

Time

-------------------------------------------- ------------ ----------- ------
--
CPU time                                                           10
80.76
control file parallel write                            24           2
12.26
db file scattered read                                752           1
6.47
db file sequential read                                12           0
.45
log file sync                                           1           0

.04

So I think you should do new tests....

"Antoine BRUNEL" <antoinebrunel/yahoo.fr> a écrit dans le message de news:3ecd4290$0$27233$79c14f64_at_nan-newsreader-03.noos.net...
> 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 - 17:10:40 CDT