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

From: Jack Silvey <depifster_at_yahoo.com>
Date: 26 May 2003 16:46:43 -0700
Message-ID: <25c7944d.0305261546.69341aad@posting.google.com>

ok.

You might also try appending in parallel, like so:

alter session enable parallel dml; --I forget to do this all the time and then wonder why my session did not go parallel

insert /*+ parallel(a,12) */ into tab a.......

commit;

since the parallel hint implies the append hint (each parallel process simply starts making its own temp segment) and has its own sort and hash area size.

To be honest, I can't remember if each parallel process attaches to a particular partition or if this will work on a non-partitioned table - think it will.

Here is a query to show current parallel processes in use:

select se.username,

ps.qcsid par_sid, 
ps.qcserial# par_ser,
pr.server_name,
pr.sid child_pq_sid,

substr(sq.sql_text,1,1000) par_sql_text
from v$px_process pr,

v$px_session ps, 
v$session se, 
v$process vp, 
v$sqlarea sq

where pr.sid = ps.sid

and ps.qcsid = se.sid
and ps.qcserial# = se.serial#
and se.paddr = vp.addr
and se.sql_address = sq.address(+)

order by 1,2,3,4,5
/

and here is a query to verify that your are inserting nologging (shows redo):

select se.sid, s.username, round(se.value/1048576,2) redo_megs from v$session s, v$sesstat se, v$statname sn where se.statistic# = sn.statistic#
and sn.name = 'redo size'
and se.value != 0
and s.sid = se.sid
order by 1;

which if you go parallel/nologging you shouldn't be if your table is set to NOLOGGING (however, if I remember correcly, all index mods are logged, since there is no APPEND on an index - it is just an ordered list, and the values have to be inserted in the correct place.)

Here is a query to show rollback generated:

select	s.sid
	,s.osuser
	,s.username
	,r.segment_name
 	,t.used_ublk
	,t.used_urec
from v$session s,
     v$transaction t,
     dba_rollback_segs r

where s.taddr=t.addr
and t.xidusn=r.segment_id(+)
order by 1;

which, since each parallel process creates a temp segment, and if the insert crashes just drops it, should be low.

"Ryan" <rgaffuri_at_cox.net> wrote in message news:<K_eAa.99524$823.50552_at_news1.east.cox.net>...
> "Jack Silvey" <depifster_at_yahoo.com> wrote in message
> news:25c7944d.0305251037.500d78c4_at_posting.google.com...
> > Ryan,
> >
> > I discovered an undocumented feature in Oracle using append - the
> > insert processes have a tendancy to sort.
> >
> > It came to my attention when I had the exact question you have - why
> > is append so slow?
> >
> > I discovered this problem on a distributed parallel insert, like so:
> >
> > alter session enable paralle dml;
> >
> > insert /*+ append parallel(a,12) */ into tab a
> > select /*+ parallel(b,12) full(b) */ * from b_at_dblink b;
> >
> > The issue here was that the PQ co-ordinators are tied up talking
> > across the database link to each other, so the insert processes aren't
> > being told what to do with their data. Each parallel process therefore
> > stores a copy of all of its data into sort segments until the pq
> > co-ordinator got free. Then, once the co-ord was avaiable to tell the
> > PQ processes what to do with the data, they were able to insert it
> > quickly.
> >
> > If you want to check this, open two sessions - one for the insert, one
> > for the monitoring. Run your insert in one, and run this in the other:
> >
> > select t1.tablespace
> > , extents
> > , sum( t1.blocks * to_number( t3.value ) ) / 1024 / 1024 mb_used
> > , sum( t1.extents ) tot_extents
> > , t2.username
> > , t2.osuser
> > , t2.SID ||','|| t2.serial# SID_PID
> > , t4.spid
> > , t5.sql_text
> > , t1.segtype
> > from
> > v$sort_usage t1
> > ,v$session t2
> > ,v$parameter t3
> > ,v$process t4
> > ,v$sqlarea t5
> > where t1.SESSION_ADDR = t2.SADDR
> > and t3.name = 'db_block_size'
> > and (t2.PROCESS=t4.SPID or t2.paddr = t4.addr)
> > and t2.sql_address=t5.address
> > and t2.status = 'ACTIVE'
> > group by
> > t1.tablespace
> > , t2.username
> > , t2.osuser
> > , t2.machine
> > , t2.schemaname
> > , t2.program
> > , t2.SID ||','|| t2.serial#
> > , t4.spid
> > , t5.sql_text
> > , t1.segtype
> > , segfile#
> > , extents
> > /
> >
> >
> > and see if you are sorting.
> >
> > Also, bump up your sort_area_size and sort_area_retained_size and see
> > if that helps.
> >
> > hth,
> >
> > Jack Silvey
> > senior warehouse DBA and all around Oracle monkey, somewhere in the
> > great nation of Texas
> >
> >
> > >
> > > 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.
> > >
> > <snip>
>
> thats probably it. Ill check it on Tuesday. Im doing a hash_aj as part of my
> insert... thanks.
Received on Mon May 26 2003 - 18:46:43 CDT