Oracle processes

[Deborah Weatherspoon]

Hi all,

All of sudden we seeing a lot of oracle processes starting??? These processes do not belong to anyone(oracle is the user),so I spent a lot of time killing these processes.They are causing the system to be very slow. Do anyone know what I can change or look at to help this matter.
We're running Oracle 9i on a L3000 11i system.

[Bill Hassell]

Not enough information here. First, I would not be killing processes owned by oracle unless I knew exactly what the process is doing and that killing it will not damage the database. Secondly, Oracle just does not start a bunch of do-nothing processes by itself. Your DBA will have to tell you what these processes are doing and why they are running.

To see how the processes are related, use this command:

$ UNIX95=1 ps -fHu oracle

This will show the child processes indented to diagram the parent-child relationship. Show this list to your DBA.

[Jonathan Fife · Accepted solution]

Do the names look like:
oraclesid (LOCAL=NO)

If you have Oracle configured to use dedicated server connections, it will fork a process for each incoming connection request. If someone is flooding the DB with connection requests you will get a lot of these processs.

Try the following query to see where the connections are originating from:
select machine, program, count(*) from v$session group by machine, program;

If the names don't look like that, what do they look like?

[Deborah Weatherspoon]

Thanks for the infor,here's the output.

SQL> select machine, program, count(*) from v$session group by machine, program;


MACHINE
----------------------------------------------------------------
PROGRAM COUNT(*)
------------------------------------------------ ----------
adm2
oracle@adm2 (ARC0) 1

adm2
oracle@adm2 (ARC1) 1

adm2
oracle@adm2 (CKPT) 1


MACHINE
----------------------------------------------------------------
PROGRAM COUNT(*)
------------------------------------------------ ----------
adm2
oracle@adm2 (DBW0) 1

adm2
oracle@adm2 (LGWR) 1

adm2
oracle@adm2 (PMON) 1


MACHINE
----------------------------------------------------------------
PROGRAM COUNT(*)
------------------------------------------------ ----------
adm2
oracle@adm2 (RECO) 1

adm2
oracle@adm2 (SMON) 1

adm2
RPEDISB@adm2 (TNS V1-V3) 1


MACHINE
----------------------------------------------------------------
PROGRAM COUNT(*)
------------------------------------------------ ----------
adm2
fgractg@adm2 (TNS V1-V3) 1

adm2
gurinso@adm2 (TNS V1-V3) 1

adm2
gurjobs@adm2 (TNS V1-V3) 2


MACHINE
----------------------------------------------------------------
PROGRAM COUNT(*)
------------------------------------------------ ----------
adm2
sfrpipe@adm2 (TNS V1-V3) 10

adm2
shrtrtc@adm2 (TNS V1-V3) 1

adm2
smrcmpl@adm2 (TNS V1-V3) 2


MACHINE
----------------------------------------------------------------
PROGRAM COUNT(*)
------------------------------------------------ ----------
adm2
sqlplus@adm2 (TNS V1-V3) 2

adm2
tgrmisc@adm2 (TNS V1-V3) 1

adm2
tgrrcpt@adm2 (TNS V1-V3) 1


MACHINE
----------------------------------------------------------------
PROGRAM COUNT(*)
------------------------------------------------ ----------
BANNER7\SSB
Apache.exe 46

BANNER7\INB7
ifweb90.exe 19

BANNER7\SSB2
Apache.exe 11


MACHINE
----------------------------------------------------------------
PROGRAM COUNT(*)
------------------------------------------------ ----------
BANNER7\INB7B
ifweb90.exe 31

BANNER7\INB7C
ifweb90.exe 33

WORKGROUP\3E99EFDF7B084F8
MSACCESS.EXE 2


MACHINE
----------------------------------------------------------------
PROGRAM COUNT(*)
------------------------------------------------ ----------
WORKGROUP\ADM211TG870852C
checks.exe 1


25 rows selected.

SQL> SQL>

[Deborah Weatherspoon]

Jonathan,

Do the names look like:
oraclesid (LOCAL=NO) Yes

[Jonathan Fife]

Server Program # connections
BANNER7\SSB Apache.exe 46
BANNER7\INB7 ifweb90.exe 19
BANNER7\SSB2 Apache.exe 11
BANNER7\INB7B ifweb90.exe 31
BANNER7\INB7C ifweb90.exe 33

These are the servers and programs that are initiating the majority of oracle connections. I would check with whoever administers Apache and the ifweb90 application if those are reasonable numbers of connections, or if they recently bumped up a connection limit.

If the number of connections is valid and it is still creating an unacceptable number of processes you could look into changing Oracle to MTS, where it would pool incoming connections into a single process.

[Deborah Weatherspoon]

Thanks Bill and Jonathan,

Using Bill's command, I'm killing 10 processes at one time. But 20 new processes appear without a parent (PPID).

oracle 15981 1 110 11:05:28 ? 00:13 oraclePROD (LOCAL=NO)
oracle 16047 1 119 11:05:49 ? 00:12 oraclePROD (LOCAL=NO)
oracle 16069 1 115 11:06:00 ? 00:11 oraclePROD (LOCAL=NO)
oracle 16054 1 109 11:05:56 ? 00:12 oraclePROD (LOCAL=NO)
oracle 16052 1 0 11:05:55 ? 00:01 oraclePROD (LOCAL=NO)
oracle 15910 1 105 11:05:04 ? 00:13 oraclePROD (LOCAL=NO)
oracle 15859 1 112 11:04:31 ? 00:13 oraclePROD (LOCAL=NO)
oracle 15951 1 1 11:05:21 ? 00:02 oraclePROD (LOCAL=NO)
oracle 15801 1 0 11:04:08 ? 00:00 oraclePROD (LOCAL=NO)
oracle 16067 1 117 11:06:00 ? 00:12 oraclePROD (LOCAL=NO)
oracle 17050 1 0 11:12:48 ? 00:00 oraclePROD (LOCAL=NO)
oracle 15543 1 105 11:02:07 ? 00:25 oraclePROD (LOCAL=NO)
oracle 15876 1 0 11:04:46 ? 00:02 oraclePROD (LOCAL=NO)
oracle 16034 1 97 11:05:47 ? 00:12 oraclePROD (LOCAL=NO)
oracle 16022 1 0 11:05:42 ? 00:00 oraclePROD (LOCAL=NO)
oracle 15788 1 113 11:04:04 ? 00:17 oraclePROD (LOCAL=NO)

I'm turn it over to the network/ssb administrator.

[Yogeeraj_1]

hi Deborah,

Instead of "fire-fighting", i would prefer to identify these processes and reach the source the problem.

Try:
select b.sid SID,b.serial# "Serial#", c.spid "srvPID", b.osuser, b.username, b.status, b.client_info, machine from v$session b, v$process c where b.paddr = c.addr and c.sPID = &OSPID

(where OSPID is the OS PID that you want to "kill")

e.g.
[root@myserver]# ps -ef|grep 18366
oracle 18366 1 0 07:52 ? 00:00:05 oraclemydb (LOCAL=NO)
root 2781 22350 0 09:25 pts/2 00:00:00 grep 18366
[root@myserver]#



me@mydb.mu>@check_process
Enter value for ospid: 18366
SID : 1345
Serial# : 1794
srvPID : 18366
OSUSER : oracle
USERNAME : ST06
STATUS : INACTIVE
CLIENT_INFO : MyApps - Operations Definitions
MACHINE : myserver
-----------------
SID : 1368
Serial# : 31947
srvPID : 18366
OSUSER : oracle
USERNAME : ST06
STATUS : INACTIVE
CLIENT_INFO : MyApps - Order Followup
MACHINE : myserver
-----------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
me@mydb.mu>

If you need further assistance, please let us know.

hope this helps!
kind regards
yogeeraj