"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<vcqja2a2ue9352_at_corp.supernews.com>...
> "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.
thank you
Received on Fri May 23 2003 - 06:10:34 CDT
