|
Looking at Memory Usage for Oracle Processes
By James Koopmann, DatabaseJournal, July 3, 2008
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.
|
