depifster_at_yahoo.com (Jack Silvey) wrote in message news:<25c7944d.0305261546.69341aad_at_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.
we are not using partitioning and the tables being joined are on the
same I/O mount, so I assumed that parallel wouldn't help because of
the I/O waits. Ill check it out though.
Received on Tue May 27 2003 - 07:28:47 CDT
