"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
news:41c9a4ad$0$1078$afc38c87_at_news.optusnet.com.au...
> Access wrote:
> > "Michael" <michael.lohmar_at_gmail.com> wrote in message
> > news:1103721310.598627.305450_at_f14g2000cwb.googlegroups.com...
> >
> >>Hello Pankej,
> >>
> >>
> >>>how to tune the insert opreation performance...
> >>
> >>Hm ... Where is your problem with your insert operations?
> >>
> >>In general it depend on the count of indices you have created on your
> >>table, trigger which are maybe fired during the insert, a bad space
> >>management for this table and/or what is often a problem ... on a wrong
> >>setting of the inittrans value for this table. Many time a slow insert
> >>operation is forced due to concurrent inserts which do block each
> >>other. Not to forget I/O bottlenecks of the system and concurrent
> >>read/writes on the same disk.
> >>
> >>As you can see there are a lot of reasons and I'm sure I forgot about
> >>to mention a few in my list ...
> >>Maybe you should ask a little bit more concrete.
> >>
> >>Greetings,
> >>Michael
> >>
> >
> >
> >
> > Considering INITRANS : I always wondered why the default value = 1 ? Are
> > there any disadvantages in setting it for example to 10 for all
> > tables/indices ?
> >
> > Matthias
>
>
> Yes. INITRANS reserves bytes in the header of each block for transaction
> slots. Lots of pre-allocated transaction slots are great if you know in
> advance that there will be a high level of concurrent access to the
> blocks. But reserving bytes for them also means the header is much
> larger than it would be if only one slot was pre-allocated. And if the
> header is bigger, that must mean less usable space in the block for
> storing your data. And that in turn must mean bigger tables and indexes
> -meaning longer table scans or index range scans.
>
> It's also worth pointing out that indexes have an INITRANS of 2 by
> default -it's tables that have the default of 1. This is sound thinking
> on Oracle's part. Because the "rows" in an index are generally much
> smaller than the corresponding rows in a table, you're bound to be able
> to fit lots more of them into a block. And if you can fit more "rows" in
> a block, then the probability that two users will want different index
> entries and yet collide onto the same block goes up. Recognising this,
> Oracle makes the default level of minimum concurrency on an index double
> that for tables.
>
> Bear in mind, too, that INITRANS is only the INITIAL number of
> transaction slots. New ones are dynamically created (and the header
> grows dynamically, as a result, "downwards" into the block) as needed
> -provided only that there's actually some space in the block to allow it
> to do so.
>
> So why not bung INITRANS to 10 for everything? (1) because you'd waste
> space unnecessarily for many segments (2) because the number of
> transaction slots can grow dynamically anyway (3) because doing so would
> lose the subtlety of indexes usually needing higher concurrency than
> tables (4) because segments would become unnecessarily larger, and (5)
> that will slow down segment scans.
>
> And I might add, by way of analogy, why do we insist on screws, nails,
> rivets, glue and velcro as ways of sticking things together. Why not
> just make it rivets for everything? Because knowing how to use the right
> tool for the job is important! INITRANS is a tool. Use it wisely.
>
> Regards
> HJR
Great explanation - thanks !
Received on Wed Dec 22 2004 - 12:05:55 CST
