Previous | Table of Contents | Next

Page 563

NOTE
The SZ column refers to text plus data plus stack memory usage. This size of the text page of the Oracle executable can be determined by using the size command on the Oracle executable. After the text page size of the executable is obtained, then, theoretically, if you subtract the value obtained from the virtual memory size using the ps -ael command, you would get the actual per-process virtual memory usage of the process. But sometimes the SZ column in the ps -el command does not report the actual text page size; it reports only the page required by the process. This happens because of the demand-paging algorithm, wherein only the most required pages of text are used by the process, and the rest are brought as required from the file system.

You could use the size column report in the top command because the top command reports the total text-page size rather than the actual used-page size when it reports on the virtual memory in the SIZE column. In Listing 22.21, the size of the virtual memory used by the process ID 25623 is 8640K, which includes the complete text page.

If I try to find the size value using the ps command, the following is obtained:

$ ps -ael | grep 256,23
 1 R   105 25623 25622 254 241 20  3909b00
1524        - ?         9:20 oracle

The ps command shows that the size of the virtual memory is 1524*4K =6096K, which is much less than the top command 8640K value because the text page size used is actually much lower than the total text page size.

Keep this in mind when computing actual memory usage of the individual processes.n

Listing 22.21 Obtaining the Virtual Memory Size by Using the top Command Output
System: arms1                                         Tue Sep  9 19:41:51 1997
Load averages: 4.87, 4.74, 4.44
211 processes: 204 sleeping, 7 running
Cpu states:
CPU  LOAD   USER   NICE    SYS   IDLE  BLOCK  SWAIT   INTR   SSYS
0    4.98  88.1%   0.0%  11.9%   0.0%   0.0%   0.0%   0.0%   0.0%
1    4.75  79.2%   0.0%  20.8%   0.0%   0.0%   0.0%   0.0%   0.0%
--   ----  -----  -----  -----  -----  -----  -----  -----  -----
avg  4.87  83.2%   0.0%  16.8%   0.0%   0.0%   0.0%   0.0%   0.0%

Memory: 53592K (15892K) real, 72336K (25548K) virtual, 11848K free  Page# 1/20

CPU  TTY  PID USERNAME  PRI NI   SIZE   RES  STATE   TIME %WCPU  %CPU  COMMAND
0     ? 25623 arms      240 20  8640K   548K run     7:34 43.47 43.39 oracleor
1     ? 18128 oracle    241 20  9184K  1024K run   177:07 31.29 31.23 oracleor
0     ? 19523 oracle    240 20  9644K  1552K run    60:48 26.19 26.15 oracleor

Page 564

Using the output in Listing 22.22, you can determine the pid of the Oracle processes. When the pid is obtained using the SQL statements in the previous sections, the sid of the processes can be found and then further investigation can be done at the session level.

Listing 22.22 ps Command Used for finding the Memory Usage by Processes
$ ps -eal | grep oracle | sort -n -r -k 10
 1 R   129 28060 28058 248 240 20  3932f00 2400       - ?  15:24 oracle
 1 R   104 28049     1 246 239 20  3941080 2001       - ?  10:55 oracle
 1 S   104 25751     1  0 156 20  38e1f80 1939   461b34 ?  19:02 oracle
 1 S   104 15018     1  0 154 20  37c2980 1915  3d90e38 ? 198:25 oracle
 1 S   104 25861     1 59 148 20  3807d00 1711  2194bac ?  32:42 oracle
 1 S   104 25743     1  0 156 20  3e38380 1658   461b1c ?  23:52 oracle
 1 S   104 14103     1  0 154 20  2829900 1653  3d4e338 ? 109:12 oracle
 1 R   104 25739     1 255 241 20  37c2d80 1623       - ? 142:21 oracle
 1 S   104 27772     1  0 156 20  372ba80 1603   461b04 ?   9:11 oracle
 1 S   105 22744 22743  0 154 20  3941e80 1578  3d61da2 ?  38:59 oracle
 1 S   129 25731 25727  0 154 20  335f700 1538  38c47a2 ?   0:00 oracle
 1 S   104 19722     1  0 154 20  2845380 1525  3d6cb38 ?   0:05 oracle
 1 S   129 28055 28053  0 154 20  3780f00 1522  3dda0a2 ?   0:00 oracle
 1 S   125 25629     1  0 156 20  3738680 1511   461aa4 ?   0:01 oracle
 1 S   104  7258     1  0 154 20  38e6100 1498  391e838 ?   0:00 oracle
 1 S   125 25623     1  0 156 20  294f600 1493   461a8c ?   0:38 oracle
 1 S   125 25621     1  0 156 20  2576080 1490   461a84 ?   1:15 oracle
 1 S   125 25627     1  0 156 20  3807300 1483   461a9c ?   0:03 oracle
 1 S   125 25625     1  3 156 20  3937000 1483   461a94 ?   2:00 oracle
 1 S   125 25649     1  0 156 20  2958980 1478   461af4 ?   0:10 oracle
 1 S   125 25647     1  0 156 20  3903b00 1478   461aec ?   0:10 oracle
 1 S   125 25645     1  0 156 20  2845a80 1478   461ae4 ?   0:11 oracle
 1 S   125 25643     1  0 156 20  3354400 1478   461adc ?   0:13 oracle
 1 S   125 25641     1  0 156 20  391d700 1478   461ad4 ?   0:13 oracle
 1 S   125 25639     1  0 156 20  3739b80 1478   461acc ?   0:13 oracle
 1 S   125 25637     1  0 156 20  2852780 1478   461ac4 ?   0:14 oracle
 1 S   125 25635     1  0 156 20  2888580 1478   461abc ?   0:15 oracle
 1 S   125 25633     1  0 156 20  3663e80 1478   461ab4 ?   0:15 oracle
 1 S   125 25631     1  0 156 20  3e40c80 1478   461aac ?   0:15 oracle
 1 S   125 25619     1  0 156 20  27fb500 1477   461a7c ?   0:01 oracle

The memory usage of individual processes can also be monitored using the V$SESSTAT system view. To understand which part of memory we are looking at, we will now look into how memory is organized in the Oracle SGA. The SGA is a shared segment that comprises the shared pool, the block buffers, and the redo log buffers. The shared pool is the area in the SGA that contains constructs such as shared SQL areas and the data dictionary cache.

For every SQL that is executed on the server, there is a shared part (shared SQL area) and a private part (private SQL area). Two users executing the same SQL will use the same shared SQL area, but each user will have an individual private SQL area.

The shared SQL area contains the parse tree and the execution plan for every SQL statement. The size of the area depends on the complexity of the statement. The size of every such SQL

Previous | Table of Contents | Next