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

"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 Sun May 25 2003 - 21:30:02 CDT