Re: *Measuring sql performance (elapsed time and scalability) by number of logical reads

Hi Mladen,

When having memory shortage and fluctuating memory use on the server, then locking SGA could cause trouble as you said.

The reason why I mentioned locking SGA is: 1) Locked SGA can provide shared page table entries for SGA, thus reducing kernel VMM structure sizes and improving overall cache efficiency. This is different issue from disk-paging stuff.
2) Locked SGA can provide end-to-end direct (asynchronous) IO, which means when reading something from disk, we can read it directly to buffer cache instead of using an intermediate buffer and copying from there. This reduces memory bus traffic and possibly cache efficiency too 3) 100% of the Oracle database servers I've worked during last couple of years, haven't had a physical memory shortage at all.

Points 1,2 may vary by platform and some platforms might already implement some of the features without needing to lock SGA.

Tanel.

• Original Message ----- From: "Mladen Gogala" <gogala_at_sbcglobal.net> To: <tanel.poder.003_at_mail.ee> Cc: "ORACLE-L" <oracle-l_at_freelists.org> Sent: Wednesday, May 10, 2006 8:11 AM Subject: Re: *Measuring sql performance (elapsed time and scalability) by number of logical reads

>
> On 05/09/2006 11:34:22 AM, Tanel PÅ‘der wrote:
>
>> If anybody bothered to read until the end - there is little we can do to
>> fix
>> such memory latency related problems, but nevertheless, there are some.
>> First, using Oracle's buffer cache and direct IO instead of OS buffer
>> cache
>> to reduce system calls. Also using large memory pages and SGA locking
>> into
>> memory where available, to reduce the number of VM mapping entries,
>> sharing
>> them among processes and perhaps reducing number of levels in OS kernels
>> VM
>> mapping index structure - thus improving overall CPU cache efficiency.
>>
>> Kevin, did I get the last part right? ;)
>
> Well, I'm not Kevin, but I must disagree with the locking part. Contrary
> to
> popular belief, shared memory is "locked in memory" for most of the time.
> Page update and swapping processes do not treat all in memory pages as
> equals.
> Different types of pages are treated differently. Read only pages which
> are not
> used are typically thrown out first as they require only minimal handling
> since
> they already have backup on disk. After them, various data pages are first
> backed
> up and then freed. Shared pages are at the very end of the food chain and
> are
> accessed last, only if there is a severe memory shortage on the system.
> Locking page in memory means to set a flag which will prevent updater to
> throw it
> out when there is a severe memory shortage. That prevents page updater
> from freeing
> enough memory for system to operate normally, so page updater gives up and
> calls
> for his bully brother - the swapper. The swapper doesn't operate on pages,
> it
> operates on processes. Swapper goes through the process table, picks the
> lowest
> priority process in the "sleep" state, maps all of its segments as buffers
> and
> writes them to the swap partition. Only .u area remains in-core. Then it
> picks the
> next process until it ends up writing out processes with the status
> "runable".
> When swapper does its dirty work, you're likely to end up with essential
> services,
> like printer control process to end up in the swap partition. Of course,
> the very
> next "lp" will page "cupsd" back in and you are back on the beginning.
> Thus, locking of SGA may actually cause update process ("paged") to give
> up and
> call the big bad brother. That usually means trouble.
> How does "paged" select pages? The algorithm is called "two handed clock".
> The first
> hand sets the "invalid" flag for the candidate pages. The second hand
> comes in after a
> specified time interval, usually a parameter, except on Linux, and throws
> all the pages
> that still have the "invalid" flag set out of memory. If the page was used
> in the period
> between sweeps, the invalid flag would have been reset back to "valid".
> Therefore, a
> necessary condition for a page to get thrown out is not to be used for
> certain period of
> time. Shared global area of an active database is highly unlikely to
> satisfy that condition.
> Locking doesn't do anything. It may help you with scr..ing your system up,
> though.
> Kevin knows much more then me and I am interested in seeing his take in
> this.
>
>
>
> --
> Mladen Gogala
> http://www.mgogala.com
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l