Re: Oracle and Memory

On Mon, 26 Aug 1996 14:49:00 GMT, zog_at_zog.ColumbiaSC.NCR.COM (Bill Hertzog) wrote:

>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)).
>

I over generalized to make a point, sorry.

did you see any performance increase going from 1 to 2 gig?

did you try backing off the size of the sga until you saw a performance degradation. Perhaps you were using a lot of memory for nothing. Memory is not the only answer.

Are you sure everything in the sga was even being used?

You may also want to look at the param: DB_BLOCK_LRU_EXTENDED_STATISTICS. It will help you determine if more memory will save disk accesses or not. You can look at using DB_BLOCK_LRU_STATISTICS to see what using less block buffers might do for you as well.

You actually back up the linear points somewhat. You get N hash buckets. So optimally you will have M/N blocks in a list (where M is the number of db_block_buffers). A hash bucket is the head of a list. The list will grow as the SGA grows. Yes, with proper tuning you can get around some of the overhead. Also, I would like to point out that parms starting with '_' are not documented and you really can't tell what will happen by using them (if anything at all in many cases, some are just 'leftover'). I don't recommend using '_' parameters unless support tells you to in general. This tuning parameter is not used in general. I pretend they don't exist as most people will not be using them.

Don't forget, all of these memory structures are accessed by all concurrent users. Concurrency issues are also present when accessing these shared structures. Not only do you need to take into account the access time, but the overhead of having to share and mediate access to these structures by many users. Your starting to lump a couple of blocks together in a shared structure.

By the time you are getting to VLM you are getting into the 5,6,10,15 gig of memory range. Completely different things to consider in that environment (checkpoints, recovery, among others).

><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

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Tue Aug 27 1996 - 00:00:00 CEST