Re: Looking for Statspack documentation

"Noons" <nsouto_at_optusnet.com.au.nospam> wrote in message news:Xns931AF3C3A4B89mineminemine_at_210.49.20.254...
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in
> news:3e422da2$0$247$ed9e5944_at_reading.news.pipex.net and I quote:
>
> > at all. "if all your sql is tuned" is a hell of a caveat as well.
>
>
> Hehehe! I *really* love that one. As if...
> :D
>
>
> > of the database (db_block_size). For your object choose a prime number
> > for the number of freelists as the processes are hashed by Oracle PID
> > over the freelists.
>
>
> That is news. Prime?

Jonathan's been saying this for years. You get 'popular' freelists if the number of freelists isn't prime, which rather defeats the purpose of multiple freelists in the first place.

>
> > are accessing the same rows in a block/buffer. So reducing the number of
> > rows per block will help to reduce the contention on the buffer.
>
>
> Might be useful to explain how. SOme folks out there don't,
> believe me.

Surely not ;-) I might be missing something, but it's basic mathematics and probability theory, isn't it? If there are fewer rows in a block, then, ceteris paribus, the block is less likely to be the target of the next select/update/whatever. On avergae, and with randomised selects/updates/whatever (ie, nothing will save you if you have a deeply popular row!) a table with 100 rows in 100 blocks will get 1 hit per block in time X. But if the same hundred rows are stored in 10 blocks, then you will get 10 hits per block in time X.

PCTFREE is a very handy tool in this regard. When you are confronted with Steve Adam's evidence that you do NOT have a free choice in the matter of block size, and are accordingly constrained to pick an enormous block size, you can use PCTFREE to deliberately 'waste' space at the top of each block, thereby getting back to something more 'reasonable' as far as contention issues are concerned.

Regards
HJR Received on Fri Feb 07 2003 - 17:59:36 CST