Re: Table Fragmentation

I agree that he will need to monitor his chained rows over time to see if he has the PCTFREE about right.

But, can't we calculate a starting point based on his statement that the rows are inserted as 50 bytes and are updated into thhe 2000 byte range?

I am assuming a block size of 8K. Even with block overhead, he should be able to fit 4 2000 byte rows into the 8K blocks.

I think this means that PCTFREE needs to be set to a value that causes Oracle to go to the next block as soon as 4 50 byte rows have been inserted! I compute this as (8192 - 107 - 4*50) / 8192 = 96%. PCTUSED would be 3%.

These are extreme values, but growth from 50 bytes at insert to 2000 bytes is extreme row growth!

Thomas Kyte wrote:

> A copy of this was sent to pauldb <luapdb_at_yahoo.com>
> (if that email address didn't require changing)
> On Thu, 01 Jul 1999 02:39:31 -0800, you wrote:
>
> >I have a table where there is always a large percentage of
> >updating on the table. And the change of row size is from
> >approx 50bytes to 2000 bytes.
> >
> >So I need to check that the values PCTFREE and PCTUSED are
> >optimal.
> >
> >The question is:
> >1) how can I check if there are chained blocks?
>
> analyze table list chained rows does this
>
> >2) Is there a way to check if these values are optimal?
>
> if the analyze comes up with few rows -- you know at least that pctfree is high
> enough. you could then analyze the table to find the empty space on a block to
> see if that is too high for your comfort and adjust it down -- but then you
> might get more chained rows.
>
> >3)any suggestions for an optimal setting?
> >
>
> your mileage may vary.
>
> depends on how you use the data too.
>
> If you do keyed reads all of the time and just get single rows from the table --
> you may never notice the impact of chained rows (if it takes 10 times as long
> to get a chained row (it DOESN'T btw) and it took 0.01 seconds to get an
> unchained row, then a chained row would take 0.1 seconds and your end users
> would never be able to tell the difference).
>
> on the other hand, if you regularly fetch hundreds of rows at a time, you very
> well might notice. Instead of taking 1 second to get data -- it takes 10
> seconds and now your end users notice. It all depends.
>
> >
> >
> >**** Posted from RemarQ - http://www.remarq.com - Discussions Start Here (tm) ****
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Thu Jul 01 1999 - 22:26:38 CDT