Re: DB Block Buffers - Too Much ???

From: Gaja Krishna Vaidyanatha <gajav_at_yahoo.com>
Date: Wed, 21 Jun 2000 20:21:04 -0700 (PDT)
Message-Id: <10535.110097@fatcity.com>

Sean,

The answer to your question is yes. One can and should lock the SGA in memory to prevent even preemptive paging (let alone swapping or process deactivation) from occuring on the pages belonging to the SGA. This however should to be done, only after the memory allocation for the SGA is done in a prudent fashion, keeping in mind total memory, memory needed for PGAs (based on sizing of sort_area_size, hash_area_size etc.), and memory needed for the O-S and other applications (if applicable).

Waleed Khedr has already talked about how to do it on Solaris using the USE_ISM parameter, which basically uses an algorithm that "touches" the entries in the page-table periodically, to basically fake usage of all memory pages. There are however a number of bugs logged with respect to this feature on Solaris, so use it after you have determined whether the bug is relevant on your version of Oracle, version of Solaris, and your platform. There were some severe ISM-related bugs on the E-10K platform, so watch out.

On other platforms like HP-UX, you need to grant the MLOCK privilege to dba:oracle by executing the userprivgrp command or something to that effect ( do a man -k priv and you will see the command I am talking about). After granting that privilege, you will then need to set the MLOCK_SGA, LOCK_SGA or relevant parameter in the init.ora. Please check what the actual parameter is on your system - by doing a "show parameter lock" or "show parameter sga" inside svrmgrl.

On some of the systems that I have worked with, I have seen paging and swapping actually drop to negligible levels after locking the SGA in memory. If the memory sizing and allocation for a system is done right, locking the SGA in memory will provide a definite boost to overall system performance.

Hope that helps,

Gaja.

sean.hull_at_pobox.com wrote:
>
> On Wed, 21 Jun 2000, Gaja Krishna Vaidyanatha wrote:
>
> > If overallocation is done, and if a significant portion of
> your
> > SGA is getting paged/swapped out, then you will experience a
> > system-wide degradation in performance. Goes to prove that
> > cache hit ratios by themselves cannot validate that Oracle
> is
> > running and functioning optimally. They are just one of the
> > indicators for performance. You really have to look at the
> > "amount of work done" or "throughput" on the system and
> measure
> > the success of your tuning efforts.
>
> Gaja:
>
> Your comments on buffer cache hitratios vs OS statistics such
> as swapping
> are excellent as usual Gaja.
>
> It brings up a question in my mind though. As I recall, one
> very good
> system administrator who I used to work with explained to me
> that certain
> segments of the SGA seemed to be marked as not-swappable by
> Oracle. This
> would certainly leave plenty of room for important components
> of the
> Oracle or OS software to be swapped out of memory.
>
> The question: Can segments of shared memory be marked by an
> application
> as non-swappable, and does Oracle do this? Anybody know, or
> have pointers
> to good documents?
>
> Thanks,
> Sean
>
> --
> Author:
> INET: sean.hull_at_pobox.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> San Diego, California -- Public Internet access /
> Mailing Lists
>

> To REMOVE yourself from this mailing list, send an E-Mail
> message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).
> You may
> also send the HELP command for other information (like
subscribing).

Gaja Krishna Vaidyanatha | gajav_at_yahoo.com Brio Technology | (972)-304-1170

"Opinions and views expressed are my own and not of Brio Technology"

Received on Wed Jun 21 2000 - 22:21:04 CDT