Re: optimizing mass inserts and updates

On Sep 4, 2:54 am, "Maija-Leena" <kangasm..._at_netti.fi> wrote:
> 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
>
> <sybra..._at_hccnet.nl> wrote in message
>
> news:kl9vb4d4s6ri5ap8tesutvpattir979o8t_at_4ax.com...
>
>
>
> > On Thu, 04 Sep 2008 07:53:15 GMT, "Maija-Leena" <kangasm..._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.
>

Please don't top-post.

See the optimal_logfile_size in v$instance_recovery. Be aware though, that it will keep telling you to make your logfiles bigger and bigger, as you make them bigger and bigger, because it doesn't really take into account what the db is really waiting for. Given your question though, I'll bet the logfiles are sized way off, and at least the first time it may help (it didn't for me, but I started with something reasonable).

As far as the "extra updates," there may be a lot you can do, likely by changing the coding, but that may take longer than just waiting for them to work on a one-time conversion. There is also the possibility that you haven't left enough pctfree in those tables. See the docs for how to tell if there has been row migration or chaining. Get Cary Milsap's book for how to tune when you are done with this migration, and Tom Kyte's books for how you should have coded the updates. And as Sybrand alluded, you need to know how to read traces and explain plans. Surf around for posts by Jonathan Lewis and Charles Hooper for some examples of how to figure that stuff out. Also there is some good stuff on asktom.oracle.com for how to speed up updates and the type of stuff you may be doing wrong, as Sybrand noted.

jg

--
@home.com is bogus.
"Why is Chelsea Clinton so ugly?  Janet Reno is her father." - John
McCain, 1998