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

"Ryan" <rgaffuri_at_cox.net> wrote in message 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
>
>

As to tracing
alter session set sql_trace = true
is equivalent to
alter session set events="10046 trace name context forever, level 1" The other levels for this event are:
4 enable bind variable dumps
8 enable wait file info
12 would get you both
For I/O related wait events:
p1 the fileid
p2 the blockid
p3 the number of blocks affected
(These parameters are identical to the parameters in v$session_events)

Trace file names on most Unixes are
of the form
ora_<sid>_<pid of the server process>
To find the id of your serverprocess
select spid from v$process where addr =
(select paddr from v$session where sid = (select sid from v$mystat where rownum = 1))

The best option sofar is
to use the following sequence of commands in sqlplus
oradebug setospid <pid of your process>
oradebug event 10046 trace name context forever, level 12 -- NOte: without quotes
oradebug unlimit
-- to overcome any maxdumpfile size you may have set and
oradebug tracefile_name to get you the exact name of your tracefiles.

The events are described in the file
oraus.msg in $ORACLE_HOME/rdbms/msg

Metalink will without doubt have information about this and Oracle Education has a 1 day special on this topic.

-- 
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address