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

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.
>