The process size that an utility like top shows is not the actual size of heap (data). It is a combination of (a) SGA size + (b) oracle executable being used by the process + (c) the actual heap (data). On sun boxes, you can verify the actual size of the heap portion of the process through the use of pmap command. For example :

On a solaris10 box, here's the output from top:

PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
9177 ora9i 1 1 0 1597M 1569M sleep 45:04 0.03% oracle

As you can see, the process size is listed by top at almost 1.6GB.

Doing pmap :

$ pmap -x 9177

9177: ora_pmon_QADB
Address Kbytes RSS Anon Locked Mode Mapped File
00010000 49744 48896 - - r-x-- oracle <<- Executable
030B2000 496 344 56 - rwx-- oracle <<- Executable
0312E000 16 8 8 - rwx-- oracle <<- Executable
03132000 1512 632 632 - rwx-- [ heap ] <<- Private Data
20000000 1568768 1568768 - 1568768 rwxsR [ ism shmid=0x55 ] <<- SGA
FE720000 240 216 - - r-x-- libresolv.so.2
FE76C000 16 16 - - rwx-- libresolv.so.2
FE780000 2392 2096 - - r-x-- libvas.so.4.2.0
..............
..............

From PMAP, the actual private heap / data size is only 1.4 MB.

Translating the SGA address from the pmap output (ism shmid=0x55 ): 0x55 translates to 85

$ ipcs -m

Shared Memory:
m 85 0xdc126148 --rw-r----- ora9i software

ipcs -a should tell you as to how much oracle's allocated for SGA.

As you can see, the process size of top is not a good indication of the heap portion of the process.

You are better off relying on the UGA and PGA statistics from v$sesstat to see if you are using excessive sql work area memory.

Until 9i the automatic tuning of work areas was disabled when you use shared servers since most of the work area allocations that are part of the run time memory were handled within SGA for shared servers. Starting 10G work area allocations that are part of the run time memory is handled within PGA and so large sort operation or hash joins or windowing operation on resultsets can potentially cause your private heap size to grow.

Good luck.......

http://www.dbaxchange.com