Re: Oracle and Memory

drsql_at_tir.com wrote:
>
> Richard
>
> I have seen cases where the sga is too large. Yes I did said too large
> 8-). (Not very often) I have also see cases that increasing the sga
> does not improve performance.
>
> It does take a certain amount of time to go through the SGA looking for
> data. Oracle has to maintain memory structures that contain data about
> what is in the sga. As the sga grows so do the structures and so does
> the time it takes to review the internal structures. Therefore there is
> a point of demising return where the SGA is so large then it is quicker
> to go read the data off a disk using a selective index then reading
> millions of data blocks.

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
bruce_at_ha.hac.com