Re: Table Fragmentation

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 - 06:39:12 CDT