Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: *Measuring sql performance (elapsed time and scalability) by number of logical reads

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

From: Mladen Gogala <>
Date: Tue, 09 May 2006 20:11:29 -0400
Message-Id: <>

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 your system up, though. Kevin knows much more then me and I am interested in seeing his take in this.

Mladen Gogala

Received on Tue May 09 2006 - 19:11:29 CDT

Original text of this message