Re: How to tune insert opreation performance?

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 23 Dec 2004 03:45:31 +1100
Message-ID: <41c9a4ad$0$1078$afc38c87@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 Received on Wed Dec 22 2004 - 10:45:31 CST