Previous | Table of Contents | Next

Page 557

Select count ( *) From cust_ord ,cmpn_pmt_type Whe    13136493   12952619
re auth_stat ='Y' and cust_ord.pmt_mthd_id In ( Se
lect pmt_mthd_id From pmt_mthd Where pmt_mthd_typ
in ( `R' ,'D' ) ) and cust_ord.cmpn_id =cmpn_pmt_t

SELECT TITLE_ID,CNTRY_ID   FROM CUST_ORD_ADR  WHER    10469659   10453418
E RTRIM(UPPER(ORD_ID)) = RTRIM(UPPER(:b1))  AND RT
RIM(UPPER(ADR_ID)) = RTRIM(UPPER(:b2))

update cust_ord set ord_stat = `07', chng_user_id     24942696    3454225
= `stat19', chng_dt = sysdate where ord_id in
   (select c.ord_id         from cust_ord a, cust
_ord_line b, cust_ord_itm c         where a.ord_id

It's likely that most of these SQL statements are doing full table scans, causing a lot of reads to be done. The SQLs should be optimized by adding secondary performance indexes where possible or using other tuning techniques.

Resource: Memory

UNIX systems use virtual memory for memory management. With virtual memory processes, it is possible that the sum of the sizes of all the processes on the system exceeds the physical memory available on the system. Virtual memory is implemented by using paging and swapping, the two memory management policies on UNIX. Virtual memory makes it possible to allow the size of a process to be greater than the amount of physical memory available for it.

Main memory (RAM) stores data required for program execution. When data is no longer required for execution, this data is stored in secondary storage on the disk, making room for other active processes. This secondary storage area on disk is termed as swap space, to which entire processes are moved from main memory.

In paging, a single page of memory is transferred to the swap space—that is, a portion of the entire process. In swapping, the entire process is transferred to the swap space. So, paging frees a small amount of memory, and swapping frees a large amount of memory. The concept behind these memory management policies is that the most required pages of memory of a process are kept in the physical memory, and the rest is stored in the secondary storage area, commonly called the swap space.

When a process requires more memory, inactive pages in physical memory can be paged out and space is freed in physical memory. When space is freed on the physical memory side, pages that reside in the secondary memory can now be brought to the physical memory (RAM). Paging occurs when UNIX moves a single page of memory to the swap space; therefore, only a portion of the process gets moved to the swap space. Swapping, on the other hand, will move the entire process to the swap space. Thus, swapping will free up lots of physical memory, but it's more expensive than paging. The key indicator to an ideal memory situation is that under full load, the system is paging lightly and should be not swapping at all.

Page 558

On most systems, the following pattern is observed: When the system is lightly loaded, there is no paging or swapping; when the system is moderately loaded, paging starts; and when the system gets heavily loaded, paging stops and swapping begins on the system, on account of heavy memory requirement and low availability of physical memory. Swapping is the most expensive of the memory operations.

In a virtual memory system, the memory address used by processes on the system are not the physical memory address but one called the virtual address. This virtual address is translated by the Memory Management Unit (MMU) into the physical memory address.

When a process needs a page of memory, it references that page using the virtual address. This virtual address is translated by the MMU to locate the actual physical address. This translated address can map into an area in physical memory (RAM) or map onto the secondary storage (swap) address.

If the physical page is located on RAM, the process can use it immediately. If it is located on the disk, a page fault is generated by the MMU and the operating system will then read this page into a free page in physical memory; this is called a page in operation. If there is severe shortage of memory, the reading of this page could cause some other pages in physical memory to be written to the disk. This is commonly referred to as a page out. After the page is read in by the operating system, the new address of the page is conveyed to the MMU and the operating system will then restart the instruction. Paging causes only pages of physical memory to be moved to the disk; swapping causes the entire process to be moved to disk in order to free memory resources. If there is severe memory shortage on the system, swapping can become very high.

Both page outs and page ins are expensive with regard to the performance of the system, because a disk read/write is very expensive when compared to using data that is directly available on the cache. Therefore, if a process generates a lot of page faults due to frequent requests for new pages of data on a system where physical memory is scarce, the process will definitely run slowly.

In systems using virtual memory, only a portion of the memory used by a process resides in physical memory. This portion of memory is called the working set of the process. Because at any given time the process is not going to use all its pages, it's more economical to keep the currently used page in physical memory. During the course of operation of the process, the process may require pages that are currently not in physical memory. This causes a page fault, but it is perfectly normal to have some page fault during the course of operation of the process because it is definitely not feasible to have all the memory pages that the process requires to be located on the main memory. The operating system will always try to keep all the pages of the working set in main memory. This technique of using memory maximizes the memory throughput of a system, keeping in mind that many concurrent processes running on the system are actively making concurrent demands for memory.

The actual physical memory usage of a process is never constant, and it is very difficult to determine due to its variable nature. On the other hand, the virtual memory requirements of the process can be more easily ascertained. The actual physical memory consumed by a
process varies based on a number of factors such as system load, what the process is doing,

Page 559

and so forth. For example, if a process is running on a heavily loaded system because there are lots of other processes are requiring physical memory pages, the actual amount of the pages allocated in physical memory to this process will be very close to the working set size of the process. On the other hand, if the same process is run on a lightly loaded system where there are not many concurrently active processes are running, the actual number of physical pages allocated to the process will be much more than the working set requirement of the process. Similarly, during the course of operation, the working set of the process changes, so the amount of physical memory allocated also will change. All this makes it very difficult to predict programs that consume large physical memory, because it is completely situation-based. To simulate the physical memory requirement of a process, you must also simulate the load on the system, because the physical memory usage of a process running in isolation on the system will be much higher than that running on the peak load. Remember these key points:

  1. Load your system to the top or to the peak load.

  2. Check for the overall memory behavior (as shown in the next section) to see whether there are any signs of paging or swapping. Ignore a small amount of paging because it is natural for processes to page as the working set of the process changes. But, excessive signs of page outs or swapping indicate that the amount of physical memory available on the system is less or that you may have to reschedule the timing of some of your memory-intensive processes.

Before you explore monitoring memory usage, let's analyze how the memory usage of a single process is broken down.

Process Memory Breakup

Every process on a UNIX system is broken down into two broad regions: the text pages and the data pages. The text segment contains the machine instructions that the process will execute. The data segment contains all the other information that the process will require during its execution. The text pages are marked as read-only because they contain the machine instructions and are paged from the file system. Processes executing the same program will share the same text page, thus optimizing on memory usage. For example, if ten users work on Oracle Forms, all of them will share the executable or the text page of the Oracle Forms executable.

On the other hand, the data pages are marked read/write; they are private to the process and are paged from the paging area (swap)—that is, every process will have its own private data page and as the number of processes increases, the requirement for private pages will also increase—unlike the case with shared text pages. The fundamental difference is that if a text page is paged out because of memory shortage, the physical page of memory the text page is

Previous | Table of Contents | Next