Re: Full Oracle Block

wizofoz2k_at_yahoo.com.au (Noons) wrote in message news:<73e20c6c.0401151745.7442d545_at_posting.google.com>...
> wumutek_at_yahoo.com (Vincento Harris) wrote in message news:<2fa13ee7.0401150844.6bf0ce23_at_posting.google.com>...
>
>
> > The initrans is 1 and the maxtrans is 255.Is it possible that the
> > block is close to full and there is no room for creating a new ITL ?
>
> Assuming like you said that pctfree is default, I'd say not likely.
> I'm also assuming updates change data but do not significantly
> increase the size of a row.
>
> As usual it depends on the nature of the updates. It could also be
> caused by an update to a column that is indexed, in which case
> it could well be a problem in the respective index. Not the table.
>
> Easiest way to make sure of no problems:
> rebuild the table (and indexes!) with initrans equal to the potential
> number of concurrent update transactions on each block. Note:
> not the potential number of updaters on each block: the max number
> of CONCURRENT actual updaters. Which will not exceed the max number of
> rows per block. Which you can get easily with either a rowid
> group query or stats. Or you could use average rather than max.
>
>
> > As usual "nothing has changed"
>
> Ain't it always the case? :)
>
>
> > What would be the best way to investigate how full the block is?
>
>
> Which block? Are you sure you wanna deal with issues at the block
> level? May I suggest a strategy approach on a more global base?
> The above approach will nearly eliminate all possible instances of
> problems with ITL in every block. Apart from some obscure and
> unusual conditions that may well not ever be the case in your db.
> At very little cost in space.
>
> > 100% statistics are run nightly both on tables and indexes
>
> Do you subscribe to oracle-l? There was a lengthy discussion there
> not long ago about why NOT to analize stats 100% every night...
> (nothing to do with this problem, though)

Thank you for your response,you did actually bring up another issue we have been debating on for some time and if there are any ideas please feel free to share

Are there any known "big" advantages between estimating statistics to a full compute.At least from Oracle tuning books quite a few of the authors on life examples say how a run of full statistics made big changes

M Vincento Received on Fri Jan 16 2004 - 09:35:32 CST