Re: optimizing mass inserts and updates

On Thu, 04 Sep 2008 07:53:15 GMT, "Maija-Leena" <kangasmaki_at_netti.fi> wrote:

>Hi,
>
>I'm converting data from Oracle7 to Oracle10 and the tables and the data
>change a lot. The main conversion is done by insert-select (dynamic plsql)
>from imported old tables and that works fine and pretty fast even for big
>tables (more than 1 million rows), but extra upates that are often needed
>are a little bit slow. So I'm wondering how to speed them up. I'm going to
>cache my sequences and I create temporary indexes and also cache some small
>tables. Archiving and flashback are of course off. Is there a way to avoid
>using undo tablespace ? I don't need to rollback, if something goes wrong, I
>have to start over. I have one huge table where I insert rows for certain
>changes, so that table grows fast and is _really_ big.
>
>Any tips ?
>
>Regards,
>
>Maija-Leena
>

Dynamic pl-sql is a big no no, as it requires extra parses. For the rest your post is so deliberately vague (refer to 'extra updates ... are a little bit slow'. This doesn't tell anything. You did look at the EXPLAIN PLAN for those statements?) that no advice is possible.

Obviously it is NOT possible to turn off undo. You require undo for read consistency and for crash recovery. Usually when people are asking this question they are not looking to optimize something (they don't know how), but rather to workarounds and hacks.
Rest assured: these do not resolve anything. Also your last sentence rises suspicions you are treating the database like a bit bucket and a garbage bin. This doesn't work either.

-- 
 
Sybrand Bakker
Senior Oracle DBA