Looking at Memory Usage for Oracle Processes

To understand the memory involved for an Oracle processes.

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

[[email protected] ~]# 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  [email protected] (SMON)       1

If I lost you on these two commands, go back to that last article 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 number we can research processes from Oracle to Unix or Unix to Oracle.

When an Oracle instance starts, or an application uses the Oracle database, the associated Oracle processes are allocated and consume memory. We can look at this memory usage in a number of ways. Firstly, the V$PROCESS view has some additional columns that report on total memory usage. The V$PROCESS view has a set of columns that report on the PGA memory (Program Global Area) which is a private memory region that contains data and control information for the process. Use the following SQL to look at the smon background process we identified 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
      WHERE spid = 2587;
SPID  PROGRAM                                   MAX      ALLOC       USED       FREE
----- ---------------------------------- ---------- ---------- ---------- ----------
2587  [email protected] (SMON)    3849704    3718632     954264    1507328

To get an idea for the different categories a process uses memory for, you can query the V$PROCESS_MEMORY table. This table shows the dynamic PGA memory usage for each Oracle processes and contains information, if used, for Java, PL/SQL, OLAP, and SQL areas. Key to understanding and dynamically monitoring the 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,
      WHERE p.pid = pm.pid
        AND p.spid = 2587;
---------------------------------- ----- ---------- --------- ------- -------------
[email protected] (SMON) 2587  SQL           185068   76120       2211796
[email protected] (SMON) 2587  PL/SQL          2068     136          2068
[email protected] (SMON) 2587  Freeable     1507328       0
[email protected] (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.

[[email protected] ~]# 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.

[[email protected] 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.

» See All Articles by Columnist James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles