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

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.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu May 22 2003 - 15:20:51 CDT