Re: optimizing mass inserts and updates

Many thanks for your kind answer,
it answered the question that the undo cannot be turned off.

I guess this assured me, that there isn't much to do anymore, I have optimized all that I can but there are so many databases (number of databases depending on the environment and that's why I need dynamic sql) and so many tables (hundreds) and so many rows (millions per table) that have to be converted to a new table that it cannot be done in an hour or two. And that 'garbage bin' -table is very important because it gathers the most important change of data to one table where it is easily found and stored. After testing the data this table can be removed (or moved to some idle instance).

Best regards,

Maija-Leena

<sybrandb_at_hccnet.nl> wrote in message
news:kl9vb4d4s6ri5ap8tesutvpattir979o8t_at_4ax.com...
> 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
Received on Thu Sep 04 2008 - 04:54:13 CDT