optimizing mass inserts and updates
Maija-Leena
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
sybr...@hccnet.nl
wrote:
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
Maija-Leena
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
<sybr...@hccnet.nl> wrote in message
news:kl9vb4d4s6ri5ap8t...@4ax.com...
joel garry
> 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
>
>
> news:kl9vb4d4s6ri5ap8t...@4ax.com...
>
>
>
> > On Thu, 04 Sep 2008 07:53:15 GMT, "Maija-Leena" <kangasm...@netti.fi>
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