Re: Table Fragmentation

A copy of this was sent to John Higgins <JH33378_at_deere.com> (if that email address didn't require changing) On Thu, 01 Jul 1999 22:26:38 -0500, you wrote:

>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?
>

maybe, maybe not -- thats why I said your mileage will vary.

if every row starts life at 50bytes and eventually grows to 2000 bytes -- then probably you can suggest a pctfree. But then again, it only takes 1 200 byte row inserted to really mess things up. All you need to do is insert a couple of 200 byte rows and bamm- you've wasted enormous amounts of space AND the system runs slower then one with chained rows since full table scans have 4 times the data to read.

When I see the word "approx" I say "your mileage may vary".

>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.
>

but only if eacy row starts at exactly 50 bytes AND you never update a row on a block until there are 4 rows on the block!!

As soon as you grow a row from 50 to 2000 bytes, if its one of less then 4 rows on the block -- that block will not get the requisite 4 rows we are hoping to achieve. What you want to do is say "put 4 rows on a block". We don't have that syntax -- we control it using pctfree which will be thrown off by rows that do not fit the 'approx' and by updates before we get 4 rows on there.

In Oracle8i, release 8.1 there is a syntax

SQL> alter table T minimize records_per_block;

that scans the existing table, finds the max rows in a block and makes it so that no more then that number can be inserted on a block.

I need to play around with that to see if it can apply in a case like this. What we would do is create the table, put 4 very small rows in it, alter the table and delete the rows. We should have set the max rows per block to 4. Now, you can set pctfree to some very low number, allowing us to put 4 rows on a block (and then stopping) without worrying about what happens when we insert a 200 byte row.

<quote from 8i doc>
records_per_block_clause

determines whether Oracle restricts the number of records that can be stored in a block. This clause ensures that any bitmap indexes subsequently created on the table will be as small (compressed) as possible.

Restrictions:
o You cannot specify either MINIMIZE or NOMINIMIZE if a bitmap index has already been defined on table. You must first drop the bitmap index.

o You cannot specify this clause for an index-organized table or nested table.

MINIMIZE
instructs Oracle to calculate the largest number of records in any block in the table, and limit future inserts so that no block can contain more than that number of records.
Restriction: You cannot specify MINIMIZE for an empty table.

NOMINIMIZE
disables the MINIMIZE feature. This is the default. </quote>

>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

--
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 Fri Jul 02 1999 - 06:54:19 CDT