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. |
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 OutputSystem: 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