Re: Oracle and Memory

In article <3218a565.15276997_at_dcsun4> tkyte_at_us.oracle.com writes:

• major snipage-
  >Also, having too many block buffers can harm performance. If my entire database
  >was cached, it would HURT and could be painfully slow. Data on disk is easyly
  >addressable (eg: rowid = file/block/row on block). Data in memory is a little
  >less organized (read: sequentially searched). We can spend quite a bit of time
  >(elaps and cpu time) searching RAM for something that may or may not be there OR
  >we can do 1 or 2 io's and just get it. In many cases, doing the io's will be
  >faster.
  >
  >In order to support very large memory databases, you have to go about memory
  >organization a little differently. Oracle with the VLM (Very Large Memory)
  >option on 64 bit machines for example can handle gigabytes of information.
  >
  >
  >Thomas Kyte
  >Oracle Government
  >tkyte_at_us.oracle.com
  >
  >statements and opinions are mine and do not necessarily
  >reflect the opinions of Oracle Corporation
  

Say it ain't so, please! While I'm dreadfully short of formal Oracle training, a sequential search of in-memory buffers certainly seems to be a Very Silly Thing. Considering your domain and despite your disclaimer, are you sure that memory is searched to determine that the desired block is already in memory and its current virtual address? Can you provide a pointer to some documentation describing the in-memory searching?

From the "_db_block_hash_buckets" initialization parameter (among others) I'ld presumed a hashing algorithm probably based on the rowid and then a linear search of the collision chain for the desired block. With proper tuning (and the correct version of Oracle) the collision chain should not be excessively long. Even with over a million buffers, the chain should not contain more than 10 - 20 buffers and should scan pretty durn fast.

I've done some runs with large SGA and haven't seen any degradation when going from (about) 1 Gb SGA to 2 Gb SGA (this is on a 32 bit architecture and is not using the VLM option (whatever it may be composed of)).

<standard disclaimers apply>

                                -zog-

-- 
--------------------------------------------------------------------------
Bill Hertzog, PSS                         Bill.Hertzog_at_ColumbiaSC.NCR.COM
NCR-       .*     -DataBaseEngineering    Voice: 803-939-6443 (VP 632-6443)
3325 Platt Springs Rd, W. Columbia, SC 29170              Fax: 803-939-7317