Tounderstand the memory involved for an Oracle processes.

For a quick refresher, in the last article, Oracle11g – Common Background Processes, we looked at some of the Oraclebackground processes. We used the Unix ps command to get a listing onthe operating system side and then looked inside Oracle to view these sameprocesses. Let’s quickly look at the smon Oracle background process withthe ps command and also through SQL/Plus:

[root@ludwig ~]# ps -afe | grep ora_smon
oracle    2587     1  0 11:54 ?        00:00:01 ora_smon_db11
SQL> SELECT spid,program,background 
       FROM v$process
      WHERE spid = 2587;
SPID  PROGRAM                                  B
----- ---------------------------------------- -
2587  oracle@ludwig.pinehorse.com (SMON)       1

If I lost you on these two commands, go back to that lastarticle for a quick refresher. The key here is to note the similarity(exactness) of the Unix process id and the Oracle SPID. It is with this numberwe can research processes from Oracle to Unix or Unix to Oracle.

When an Oracle instancestarts, or an application uses the Oracle database, the associated Oracleprocesses are allocated and consume memory. We can look at this memory usage ina number of ways. Firstly, the V$PROCESS view has some additionalcolumns that report on total memory usage. The V$PROCESS view has a setof columns that report on the PGA memory (Program Global Area) which is aprivate memory region that contains data and control information for theprocess. Use the following SQL to look at the smon background process weidentified in the previous SQL and see its PGA memory allocations.

SQL> SELECT spid, program,
            pga_max_mem      max,
            pga_alloc_mem    alloc,
            pga_used_mem     used,
            pga_freeable_mem free
       FROM V$PROCESS
      WHERE spid = 2587;
SPID  PROGRAM                                   MAX      ALLOC       USED       FREE
----- ---------------------------------- ---------- ---------- ---------- ----------
2587  oracle@ludwig.pinehorse.com (SMON)    3849704    3718632     954264    1507328

To get an idea for the different categories a process uses memory for, youcan query the V$PROCESS_MEMORY table. This table shows the dynamic PGA memoryusage for each Oracle processes and contains information, if used, for Java,PL/SQL, OLAP, and SQL areas. Key to understanding and dynamically monitoringthe PGA memory usage of processes are:

• Category – Valid categories include "SQL", "PL/SQL", "OLAP" and "JAVA".
• Allocated – Is the bytes of memory allocated to the process for a category.
• Used – Is the current bytes of memory used by the process for the category.
• Max_allocated – Is the maximum bytes of memory ever allocated for the category
• Freeable - Is a special category that memory that has been allocated to the process but not to a specific category. This memory is available to be given back to the operating system. Interesting to note, there is no max_allocated value.
• Other - Is a special category where memory has been allocated to the process but not to one of the default categories. Interesting to note, there is no used.

So, if you wanted to monitor the memory usage for our smon background processes in detail you could use the following SQL.

SQL> SELECT p.program,
            p.spid,
            pm.category,
            pm.allocated,
            pm.used,
            pm.max_allocated
       FROM V$PROCESS p, V$PROCESS_MEMORY pm
      WHERE p.pid = pm.pid
        AND p.spid = 2587;
PROGRAM                            SPID  CATEGORY   ALLOCATED    USED MAX_ALLOCATED
---------------------------------- ----- ---------- --------- ------- -------------
oracle@ludwig.pinehorse.com (SMON) 2587  SQL           185068   76120       2211796
oracle@ludwig.pinehorse.com (SMON) 2587  PL/SQL          2068     136          2068
oracle@ludwig.pinehorse.com (SMON) 2587  Freeable     1507328       0
oracle@ludwig.pinehorse.com (SMON) 2587  Other        2024168               2024168

You can also look at the memory allocation from a Unix operating system point of view. The pmap, with supplied process id(s), command reports on the memory map for a process. For our case of the smon process, with process id of 2587 issue the following command and observe the output: As this command produces quite a few lines of output, I am only displaying a small portion of the actual output. Just notice the mapped item and the memory (Kbytes) it takes. At the end of the listing, there is a total mapped memory usage. Unlike other Unix utilities that report on memory usage for a process, pmap separates out both private and sharable memory.

[root@ludwig ~]# pmap -d 2587
2587:   ora_smon_db11
Address   Kbytes Mode  Offset           Device    Mapping
00110000       4 rwx-- 0000000000110000 000:00000   [ anon ]
00111000     648 r-x-- 0000000000000000 0fd:00000 libhasgen11.so
001b3000       4 rwx-- 00000000000a1000 0fd:00000 libhasgen11.so
001b4000      12 rwx-- 00000000001b4000 000:00000   [ anon ]
001b7000      32 r-x-- 0000000000000000 0fd:00000 libocrutl11.so
001bf000       4 rwx-- 0000000000007000 0fd:00000 libocrutl11.so
001c0000       4 rwx-- 00000000001c0000 000:00000   [ anon ]
001c1000       4 r-x-- 0000000000000000 0fd:00000 libaio.so.1.0.1
001c2000       4 rwx-- 0000000000000000 0fd:00000 libaio.so.1.0.1
001c3000      76 rwx-- 00000000001c3000 000:00000   [ anon ]
001e2000    1552 r-x-- 0000000000000000 0fd:00000 libnnz11.so
00366000     148 rwx-- 0000000000184000 0fd:00000 libnnz11.so
0038b000       4 rwx-- 000000000038b000 000:00000   [ anon ]
0038c000     468 r-x-- 0000000000000000 0fd:00000 libocr11.so
00401000       4 rwx-- 0000000000074000 0fd:00000 libocr11.so
... 
48fc7000       8 rwx-- 0000000048fc7000 000:00000   [ anon ]
4922b000      28 r-x-- 0000000000000000 0fd:00000 librt-2.5.so
49232000       4 r-x-- 0000000000006000 0fd:00000 librt-2.5.so
49233000       4 rwx-- 0000000000007000 0fd:00000 librt-2.5.so
bfdd4000      96 rwx-- 00000000bfdd4000 000:00000   [ stack ]
mapped: 548640K    writeable/private: 6512K    shared: 417796K

Information about memory usage can also be seen in the /proc directory. Find the subdirectory for the process and just start looking around. Here I have just listed out the structure for the smon process we have been looking at.

[root@ludwig 2587]# ls -l /proc/2587
total 0
dr-xr-xr-x 2 oracle oinstall 0 Jun 19 13:01 attr
-r-------- 1 oracle oinstall 0 Jun 19 13:01 auxv
-r--r--r-- 1 oracle oinstall 0 Jun 19 11:57 cmdline
-r--r--r-- 1 oracle oinstall 0 Jun 19 13:01 cpuset
lrwxrwxrwx 1 oracle oinstall 0 Jun 19 13:01 cwd ->/opt/app/oracle/product/11.1.0/db_1/dbs
-r-------- 1 oracle oinstall 0 Jun 19 13:01 environ
lrwxrwxrwx 1 oracle oinstall 0 Jun 19 13:01 exe ->/opt/app/oracle/product/11.1.0/db_1/bin/oracle
dr-x------ 2 oracle oinstall 0 Jun 19 13:01 fd
-rw-r--r-- 1 oracle oinstall 0 Jun 19 13:01 loginuid
-r-------- 1 oracle oinstall 0 Jun 19 11:54 maps
-rw------- 1 oracle oinstall 0 Jun 19 13:01 mem
-r--r--r-- 1 oracle oinstall 0 Jun 19 13:01 mounts
-r-------- 1 oracle oinstall 0 Jun 19 13:01 mountstats
-rw-r--r-- 1 oracle oinstall 0 Jun 19 13:01 oom_adj
-r--r--r-- 1 oracle oinstall 0 Jun 19 13:01 oom_score
lrwxrwxrwx 1 oracle oinstall 0 Jun 19 13:01 root -> /
-r--r--r-- 1 oracle oinstall 0 Jun 19 13:01 schedstat
-r-------- 1 oracle oinstall 0 Jun 19 13:01 smaps
-r--r--r-- 1 oracle oinstall 0 Jun 19 11:54 stat
-r--r--r-- 1 oracle oinstall 0 Jun 19 13:01 statm
-r--r--r-- 1 oracle oinstall 0 Jun 19 11:57 status
dr-xr-xr-x 3 oracle oinstall 0 Jun 19 13:01 task
-r--r--r-- 1 oracle oinstall 0 Jun 19 13:01 wchan

Using V$PROCESS, V$PROCESS_MEMORY, pman, and the /proc directory we can gain a good understanding of the memory assigned to an Oracle process. It is important to understand what normal memory allocation for a process is so that we can detect and zero in on memory problems in the future. I would suggest you use the tools provided to monitor and occasionally make note of the memory usage. That way if you have a performance problem in the future you can eliminate or prove improper memory usage by an Oracle process. Items such as memory leaks or run-away processes are easily detected. More importantly, if you look at memory usage for those processes for the applications using Oracle, you can more easily predict future memory requirements when more users or applications are added.