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

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 - 04:49:06 CDT