Re: Cached blocks and performance

Hi Jonathon,

... so the Oracle block is the smallest unit that is read or written by Oracle. If you want one row in a block, the entire block is cached. Therefore if you (or anyone else) wants access to another row in the block, you are either 'lucky' and it's cached or you are 'unlucky' and it's not cached.

Note that blocks are stored in a Least Recently Used LRU chain. Therefore if you have just read in a block, it's regarded as the 'Most' recently used block. If you need to read in a new block from disk and store in memory, the poor little block that has been in memory the longest without having been accessed is the block that is given the chop and replaced.

Now if the actual rows you wish to access are stored in 'fewer' blocks (many of the rows stored together in the same blocks), rather than being dispersed and hence stored more different blocks, you *could* increase the likelihood that the required block has already been previously read into memory earlier in the query. The number of 'logical' reads would be the same but the number of 'physical' reads may be less, thereby improving performance. It depends but if the cache is big enough and if the block that contained a previously required row was access recently enough to still be cached, all well and good as it avoids a more expensive physical disk read.

Cheers

Richard
"Norman Dunbar" <Norman.Dunbar_at_lfs.co.uk> wrote in message news:E2F6A70FE45242488C865C3BC1245DA702935A68_at_lnewton.leeds.lfs.co.uk...
> Oracle caches blocks in either the DB_BUFFER_CACHE or the KEEP_POOL or
> the RECYCLE_POOL (depending on the version and any options you specified
> when creating the object of course).
>
> So if a read requires a single row from a table, Oracle has to check if
> it is already in the cache and if so this counts as a logical read. If
> not, go to the disc and read one block Oracle block that is) into the
> cache (= one physical read) then read from the cache (= one logical
> read).
>
> Does this help ?
>
> Regards,
> Norman.
>
> -------------------------------------
> Norman Dunbar
> Database/Unix administrator
> Lynx Financial Systems Ltd.
> mailto:Norman.Dunbar_at_LFS.co.uk
> Tel: 0113 289 6265
> Fax: 0113 289 3146
> URL: http://www.Lynx-FS.com
> -------------------------------------
>
>
>
> -----Original Message-----
> From: bliss_jonathan_at_hotmail.com (Jonathan Bliss)
> [mailto:bliss_jonathan_at_hotmail.com]
> Posted At: Thursday, August 29, 2002 4:55 PM
> Posted To: server
> Conversation: Cached blocks and performance
> Subject: Cached blocks and performance
>
>
> Please bear with me if this is a stupid question - it is more of a
> learning exercise than a real question although the train of thought
> was prompted by a real situation.
>
> <SNIP>
>
>
Received on Fri Aug 30 2002 - 01:38:54 CDT