| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Table Fragmentation
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
Agreed but I think your post gives the impression that full table scans ("hundreds of fetches") are affected by chaining - and I thought that a full scan just went from start block to hwm - and thus wasn't affected by chaining...
Cheers
Connor
--
"Some days you're the pigeon, and some days you're the statue." Received on Thu Jul 01 1999 - 09:38:05 CDT
 |
 |