Re: A transaction profile question - block growth always groups of 5?

Hey folks.. I'm pretty sure this has nothing to do with extent growth, but I wouldn't swear to it. I'm going to be doing some more transactions tomorrow, but I'm pretty sure in neither case is a new extent allocated. I'm going to capture that tomorrow (Friday) I don't think the blocks column in user_tables reflects the high water mark, but hey, let's find out. I'll post some more results tomorrow.. Just wanted to keep a little interest going. Appeciate the ideas.

• D

On Thu, 3 May 2001 19:49:06 +1000, "Howard J. Rogers" <howardjr_at_www.com> wrote:

>From memory, the old 5 block trick was an Oracle 7 thing... but it also
>takes place in Oracle 8 (and presumably 8i) UNLESS you specify a MINIMUM
>EXTENT clause for the tablespace. If that's specified, then the increment
>is in MINIMUM EXTENT-sized chunks. If it's not, then it's the *table's*
>NEXT clause, rounding up to the nearest 5. In 8i, if you use locally
>managed extents, the 5 block roundup is also redundant.
>
>So, I'll take a wild stab in the dark and suggest that your first tablespace
>doesn't have MINIMUM EXTENT set. And your second one (with 39 block
>extents) does. (Feel free to correct me!!)
>
>Be aware, too, that having carefully rounded up extent sizes to a multiple
>of 5 blocks, full table scans then take place as per the multiblock read
>count parameter anyway-and that's invariable a *binary* multiple (typically,
>8). So if you don't watch out, you end up having to do extra i/os that
>careful extent sizing would have obviated. The standard advice is: in 8i,
>use locally managed extents. In 8, use MINIMUM EXTENT. And in 7, make sure
>your extent sizes are a multiple of 5 AND the multiblock read count
>parameter.
>
>Regards
>HJR
>
>
>
>"Joe Blow" <dcowles_at_i84.net> wrote in message
>news:cao1ft8h5t7i26212lm6l6b68fgkav6vbf_at_4ax.com...
>> I've been doing some transaction profiles of a custom app.
>> I use the monitoring facility which is turned on before the transaction.
>> Also, before this the entire database is analyzed with compute option.
>>
>> So, after this one transaction.. I go this on a particular table from the
>> monitoring..
>>
>> 20 inserts on oneofmytables.
>>
>> Also, comparing the statistics on this table before and after
 (re-calculated
>> after transaction)..
>>
>> I get this:
>>
>> Row count change : +20 (makes sense)
>> Block count change: +5 (seems a little high.. I'll explain)
>> Total bytes for table: 1,520 increase
>> Avg size of row: 76 .. unchanged.
>>
>> Block size is 4K
>> Pct Free and Pct Used are Oracle defaults..
>> even so..
>> Under what cirumstances would an increase of 1,520 bytes ask for 5 blocks?
>>
>> Now, I've heard that things are often increased in groups of 5 blocks..
>> So is this true? 5 block boundaries?
>> (this is 8.1.5 on AIX 4.3.3)
>>
>> If that's the case.. how about example B:
>> monitoring results: 1101 inserts 8 updates...
>> Different table:
>> Row count change : +1101
>> block count change: +39 (not a multiple of 5)
>> Total bytes for table: 132,120 increase
>> Avg size of row: 120 .. unchanged.
>>
>>
>> So in this case, did it get 40 blocks for the inserts and put one back on
 the
>> freelist after the updates which probably shrunk some of the rows?
>>
>>
>> Thanks in advance,
>> D
>>
>>
>
Received on Thu May 03 2001 - 21:15:20 CDT