Re: Oracle and Memory

Ed Bruce <bruce_at_ha.hac.com> wrote:

>
>How can it be faster to search for a physical block on a disk vs.
>searching for a logical block in RAM. Unless Oracle has a horrible
>design, locating data in RAM is always going to faster then loading in
>blocks from a disk and then processing them in RAM. That is why OSs and
>disk controllers use caches. In effect the SGA is a smart cache.
>
>So if your select index is in the SGA and all the data then no disk I/O
>is neccessary and the results are returned significantly faster.
>--
>Ed Bruce
>Systems Engineer
>Hughes Aircraft Company

It just depends on the internal structures of the tables/lists you're looking at. Without knowing anything about the internals of Oracle you can just think about the usage of a simple list structure which has the information about which data blocks are in memory. This works fine until it gets large with the searching time directly related to the size of the list.

At this point you need to restructure this into an indexed mechanism into the buffer pool, perhaps a B-tree such as you might already have to a disk system.

What wasn't really said was that the comment wasn't about loading time for the page/block of data, but about the total time to locate it and then load it. This is, I'm sure, why Oracle have had to restructure many of their internal structures for VLDBs.

Jon Diamond, Hoskyns Group plc, 130 Shaftesbury Avenue, London W1V 8HH Tel: +44 171 434 8348 Fax: +44 171 434 8549 Received on Wed Aug 28 1996 - 00:00:00 CEST