Re: INSERT /*+ append */ hint

From: vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com>
Date: Sat, 8 May 2010 16:02:13 -0700 (PDT)
Message-ID: <3935964c-1145-4ca4-ad5d-09fef7b88e69_at_u3g2000prl.googlegroups.com>

I doubt that 'modifying most inserts to use /*+ APPEND */ hint' is the right thing to do:
- The hint is supposed to be used only for bulk load, in other words
in situation where very large number of records is being inserted by INSERT AS SELECT statement
- You shouldn't use it for single-row INSERT (INSERT INTO TABLE VALUES
())
- INSERT WITH /*+ APPEND */ hint locks table in exclusive mode so no
other insert/update/delete/(select for update) can be executed
- INSERT WITH /*+ APPEND */ hint inserts only above the high water
mark, so you may end up with fragmented table

To answer your question: obviously something is being executed in parallel here, either SELECT part or INSERT part. There are two things you can do:
- Increase limit on sessions - normally this is done by setting
parameter processes, however this parameter isn't dynamic, to change it the database needs to be restarted
- Reduce number of parallel servers. As far as a remember parameter
parallel_max_servers is dynamic, if you reduce the value then Oracle will use serial execution and you won't needs as many sessions. Received on Sat May 08 2010 - 18:02:13 CDT