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

"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 - 15:55:16 CDT