Oracle Parallel Processes ??

[Sundar_7]

We are experiencing this strange problem with one of our SDs. Though I could see number of indications pointing to memory pressure, I don't see any paging in the server.

Finally I figured, there are number of Oracle processes that are intriguing to me. Pardon my ignorance, but these processes appear to be doing nothing (literally nothing - Zero IO and CPU) but occupying memory upto 167.5 MB. I am not sure if this is typical.

Here is one of those processes.You can see that almost all the attributes are 0 except the RSS/VSS.

My DBA claims this is Oracle parallel process and is "NORMAL". Can someone who is familiar with Oracle and Parallel processing shed some light on this ? - It is beyond my comprehension that a process that apparently does no IO/CPU but occupies only memory is "NORMAL" :-).

# ps -fp 10393
UID PID PPID C STIME TTY TIME COMMAND
oracle 10393 1 3 19:07:57 ? 8:36 ora_p023_ADWP
#


Resources PID: 10393, ora_p023_ADWP PPID: 1 euid: 129 User: oracle
--------------------------------------------------------------------------------
CPU Usage (util): 0.0 Log Reads : 0 Wait Reason : new
User/Nice/RT CPU: 0.0 Log Writes: 0 Total RSS/VSS :167.5mb/167.5mb
System CPU : 0.0 Phy Reads : 0 Traps / Vfaults: 0/ 0
Interrupt CPU : 0.0 Phy Writes: 0 Faults Mem/Disk: 529/ 38
Cont Switch CPU : 0.0 FS Reads : 0 Deactivations : 0
Scheduler : HPUX FS Writes : 0 Forks & Vforks : 0
Priority : 156 VM Reads : 0 Signals Recd : 22680
Nice Value : 20 VM Writes : 0 Mesg Sent/Recd : 1/ 1
Dispatches : 379160 Sys Reads : 0 Other Log Rd/Wt: 0/ 0
Forced CSwitch : 126099 Sys Writes: 0 Other Phy Rd/Wt: 0/ 0
VoluntaryCSwitch: 253061 Raw Reads : 0 Proc Start Time
Running CPU : 8 Raw Writes: 0 Wed Aug 23 19:07:57 2006
CPU Switches : 0 Bytes Xfer: 0kb :


TIA

Sundar.

[spex]

Hi Sundar,

Parallel slave processes are used by parallel execution queries. You set the number of processes in init.ora.

# grep parallel initmysid.ora
parallel_min_servers=4
parallel_max_servers=40

parallel_min_servers servers will always run whenever your instance is running. When need be, that number can grow upward to parallel_max_servers. This is similar to OS soft and hard limits.

As you've discovered, each parallel process takes up memory, even though it's most likely doing nothing at all. After talking things over with your DBA, I would decrease parallel_min_servers (and possibly increase parallel_max_servers) and bounce the instance to make the change go into effect.

PCS

[Yogeeraj_1]

hi sundar,

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/tuningpe.htm#19664

excellent chapter to read.

kind regards
yogeeraj

[Volker Borowski]

Hi,
what oracle release is this ?
If it is 9.x and greater, check if your dba has configured pga = private global area.

This should reduce the static amount of memory for each oracle process significantly, because a global memory is used dynamicly by those processes that really need it !

Volker

[Steven E. Protter]

Shalom Sundar,

Few suggestions.

General tuning:
http://www2.itrc.hp.com/service/cki/search.do?docType=Security&docType=Patch&docType=EngineerNotes&docType=BugReports&docType=Hardware&docType=ReferenceMaterials&docType=ThirdParty&searchString=UPERFKBAN00000726&searchCrit=allwords&search.x=28&admit=-1335382922+1156621937982+28353475&category=c0&mode=id&search.y=8

Oracle SGA management

http://www.hpux.ws/system.perf.sh

Know what your issue is before trying to solve it.

SEP