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
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, 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,
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.
