Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jul 3, 2008

Looking at Memory Usage for Oracle Processes

By James Koopmann

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:

[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 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  oracle@ludwig.pinehorse.com (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;
---------------------------------- ----- ---------- --------- ------- -------------
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.

» See All Articles by Columnist James Koopmann

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM