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

From: Antoine BRUNEL <antoinebrunel/yahoo.fr>
Date: Sat, 24 May 2003 00:25:01 +0200
Message-ID: <3ece9fbd$0$5799$79c14f64@nan-newsreader-03.noos.net>

from the Oracle 9i Performance Tuning Guide and Reference:

" ...........

In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. As a result, direct-path INSERT can be considerably faster than conventional INSERT.

append_hint::=

................"

and obviously, this result also in generating much less redo blocks from insert.

"Ryan Gaffuri" <rgaffuri_at_cox.net> a écrit dans le message de news:1efdad5b.0305230310.566022bb_at_posting.google.com... > "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

begin 666 append_hint.gif

M1TE&.#=A\ $8`/<`````````/P``?P``P ``_P`J```J/P`J?P`JP `J_P!5
M``!5/P!5?P!5P !5_P!_``!_/P!_?P!_P !__P"J``"J/P"J?P"JP "J_P#5
M``#5/P#5?P#5P #5_P#_``#_/P#_?P#_P #__RH``"H`/RH`?RH`P"H`_RHJ
M`"HJ/RHJ?RHJP"HJ_RI5`"I5/RI5?RI5P"I5_RI_`"I_/RI_?RI_P"I__RJJ
M`"JJ/RJJ?RJJP"JJ_RK5`"K5/RK5?RK5P"K5_RK_`"K_/RK_?RK_P"K__U4`
M`%4`/U4`?U4`P%4`_U4J`%4J/U4J?U4JP%4J_U55`%55/U55?U55P%55_U5_
M`%5_/U5_?U5_P%5__U6J`%6J/U6J?U6JP%6J_U75`%75/U75?U75P%75_U7_
M`%7_/U7_?U7_P%7__W\``'\`/W\`?W\`P'\`_W\J`'\J/W\J?W\JP'\J_W]5
M`']5/W]5?W]5P']5_W]_`']_/W]_?W]_P']__W^J`'^J/W^J?W^JP'^J_W_5
M`'_5/W_5?W_5P'_5_W__`'__/W__?W__P'___ZH``*H`/ZH`?ZH`P*H`_ZHJ
M`*HJ/ZHJ?ZHJP*HJ_ZI5`*I5/ZI5?ZI5P*I5_ZI_`*I_/ZI_?ZI_P*I__ZJJ
M`*JJ/ZJJ?ZJJP*JJ_ZK5`*K5/ZK5?ZK5P*K5_ZK_`*K_/ZK_?ZK_P*K__]4`
M`-4`/]4`?]4`P-4`_]4J`-4J/]4J?]4JP-4J_]55`-55/]55?]55P-55_]5_
M`-5_/]5_?]5_P-5__]6J`-6J/]6J?]6JP-6J_]75`-75/]75?]75P-75_]7_
M`-7_/]7_?]7_P-7___\``/\`/_\`?_\`P/\`__\J`/\J/_\J?_\JP/\J__]5
M`/]5/_]5?_]5P/]5__]_`/]_/_]_?_]_P/]___^J`/^J/_^J?_^JP/^J___5
M`/_5/__5?__5P/_5____`/__/___?___P/____\``/\`/_\`?_\`P ```"HJ
M*E5557]_?ZJJJM75U?___RP`````\ $8```(_P#_"1Q(L*#!?P#]"1PH$. _
M_at_0,)$@3H3^! @@4-'D28L"# ?P('$BSHSY\_@/\$#B18T.!!A D5+F38T.%#

MB!$E3J18T>)%C!DU;L3H;U^^?/D`ZA,X,%^^?/P`_A,XD. _@/H$#B18T.!! MA D)YO,'\)_ @03][0.83^! ??P`_A,XD&!!@P<1)E2XD&%#AP\A1I0XD6)%

MBQ<Q9M2XD>*^?/S\*>RG+U^_A/K\<9RHSU]"??GX^2O(+U\^?QPY<N3(D2-'
MCAPY<N3(D>- ??H>]LNW#Z$^?QPGZO-WT%^^?0KYY>O'D2-'CAPY<N3(D2-'
MCAPY_M.W+Z*_?/T.ZO_SQW&B/G\']>UCV"^?/XX<.7+DR)$C1XX<.7+DN)%?
M/H/^_NE3V"_?07W^#N[;-S!?OGS]!.;+EZ^?P'SY\N7S]R_?P'S_\N7+Q^^@
M/G\&^>DKJ(^@OX'\\G'DR)$C1XX<.7+DR)$CQXWY^A7LY\]?OGT*]>TSJ,_?
MP7SY!N83F$]@/H'Y!.8KF*^?P'S_\@G<U\^@/G\&\_DCZ"_?/H$`^_G[]^_?

MOWS]`/X3.)!@08,'$294N)!A0X</(4:4.)%B18L7,6;4N!%AOW[_^N4SR$_@

M/GX*^^4;R,_?/WW^#/K;I\^?P'P"\PG,)S"?P'P%\_$3F.]?/H'^]!G4YT__
M(#]___CI,[B/GT!^!?GM&\B/(T>.'#ERY,B1(T>.'#E.U->/'S^#_ SF0YC/
MGT!_^?SI\V>07S]^_ 3F$YA/8#Z!^03F*YC/'[]_^?[E&YC/H#Y_`OWE\[>/
MGT)^!?WE&^A/'T>.'#ERY,B1(T>.'#ER1 C0G\"!!/WIR\</X#^!`_L-')AO

MX$"!^?KY\^>O7SY]__at_8.U/?/GSZ!^?+EXR<P7[Y\_ 3FRY<OG\!\__3]R_<O MW\!\`P?JZ^?/G[]^^?3U&SAPX+]^`P?F\^?/G[]^^@#^$SB08$&#!Q$F5+B0 M84.'#R%&E#B18D6+%S%FU$BQ'T!^`@<2U)>/'\!__P(%]ALHD%^^?/GT#1RH M#R __at_0+UZ?,'\)_ @?GRY<LG,-] @?D&"LPW4&"^?_WXY?N7;Z"^@0/U`>0G

M4* ^??X`_A,X<& _@@/S`>0G4&"^?@#_"1Q(L*#!@P_at_3*ES(L*'#AQ C2IQ(
ML:+%BQ_at_S:MPH<-\^?OP*\CN8#V$^?P/W[=/GKR _?@+W]?N7KV"^@OD*YA.8
M+]^_? +W\3.HS]_ ??WT\4O(SZ"_? 3U]>/(D2-'CAPY<N3(D2-'CA#Y[?O'
M3U]!?@?S'?27;R"_??_T^2NXSY_ ?OS^Y2N8KV"^?/GR^?N73R"_?/_RY<O'

M[Z ^?P+Y]?O';U]"?@;[Z?\;J*\?1XX<.7+DR)$C1XX<.7*LF*\BOWT&]?GC M.%&?OX+^\D'4QX\C1XX<.7+DR)$C1XX<.6K<MX]B/G\&]?GC.%&?/X/Z^#GT MEX\C1XX<.7+DR)$C1XX<.7+,UT^BOGT']?GC.%&?/X/^\O5KF(\?1XX<<<01 M1QQQQ!%''''$$4<`]<O'#^ __at_0,)%ORG3Y]!@?K\&31HT*!!@P8+ZO-G\%^_ M?/T,$O27;Q_ ?P('$BQH\"#"A H7,FSH\"'$B!(G4JQH\2+&C!HW0O273Y\_ MAOSRZ4NHSQ_'B?K$>R7;Y^_@_SR[>/(D2-'CAPY<N3(D2-'CAP+[LN73Q] M?@+*!_+;IR]?OGX`_PD<2%#?/H#\! XD6-#@080)!>;S!_"?P($$]^7+IX\? MOWWZ\NGS!_"?P($$"QH\B#"APH4,&SI\"#&BQ(D4*UJ\B#&CQHT6^P'D)W @ M07\`_PD<2%!@/X#\! XD6-#@080)"0+$W @P8']`/(3*!#@/X$#"18T>!!A M0H4+&39T^!!B1(D3*5:T>!%C1HT;.7;T^!%D2)$C298T>1)E2I4K6;9T^1)F <3)DS:=:T>1-G3IT[>?;T^1-H4*%#B58," `[.P`` `
end Received on Fri May 23 2003 - 17:25:01 CDT