Oracle creating defunct processes against job Reply

queue parent processes

Posted: Nov 23, 2005 3:44 AM

OS: AIX 5.2

DB: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit
ERP: IFS

We have 6 job queues in oracle, numbered 0-5 as:

0-default
1-fast
2-system
3-report
4-background
5-edi

For the past week or two, we have on SOME days had a problem where
we have run out of server processes, stopping new connections to the
databases.

In our production DB we have 1250 (now changed to 2000) processes.

When we looked at the AIX processes there were 250 or so "defunct". In

AIX you cannot always kill these off, restarting is the only solution.

However, over night we restarted the DBs (as we always do) but not the
server. This morning no defunct processes at all.

Later in the morning, a few defuncts sprung up and as the morning has
progressed, this has increased slightly.

We currently have 13.

They ALL relate to parents processes which are JOB QUEUE process
(ORA_J00...)

oracle 41288 491302 8 00:00 <defunct> ora_j000_IFSL

oracle 99124 491302 7 00:00 <defunct> ora_j000_IFSL
oracle 182070 67152 9 00:00 <defunct> ora_j001_IFSL
oracle 362064 456194 9 00:00 <defunct> ora_j002_IFSL
oracle 408540 456194 7 00:00 <defunct> ora_j002_IFSL
oracle 413568 67152 10 00:00 <defunct> ora_j001_IFSL
oracle 464684 491302 7 00:00 <defunct> ora_j000_IFSL
oracle 485536 67152 12 00:00 <defunct> ora_j001_IFSL
oracle 490662 456194 7 00:00 <defunct> ora_j002_IFSL
oracle 494308 456194 7 00:00 <defunct> ora_j002_IFSL

We do not know why these are being created (I have checked a few
background jobs but see nothing unusual in the time frames)

Is there anything we can do to investigate in detail the cause of the

problem?
 Thanks

goryunov Re: Oracle creating defunct processes against Reply

job queue parent processes
Posts: 130 Posted: Nov 23, 2005 7:24 AM in response to: cubittm
From: Sydney, Australia
Registered: 11/10/04
Hi,
what PL/SQL code those jobs run? What is their interval? what are
the values of job* parameters of the instance?
Possibly you are experiencing problems using java stored procedures
(note 298048.1 on Metalink)

Andrey

cubittm Re: Oracle creating defunct processes against Reply

job queue parent processes
Posts: 270 Posted: Nov 23, 2005 7:34 AM in response to:
From: England goryunov
Registered: 4/16/03

Hi Andrey - thank you for replying.

Since it's a fairly large ERP system the PL/SQL will be large - we
have no visibility of it.

Intervals (execution plan):

0-Default: sysdate + 30/86400
1-Fast: sysdate + 2/86400
2-System: sysdate + 30/86400
3-Report: sysdate + 30/86400
4-Background: sysdate + 300/86400
5-EDI: sysdate + 300/86400

The value for job_queue_processes is 6.

I will now familiarise myself with the metalink note you kindly
provided.

regards

goryunov Re: Oracle creating defunct processes against job queue

parent processes
Posts: 130 Posted: Nov 23, 2005 8:13 AM in response to: cubittm
From: Sydney, Australia
Registered: 11/10/04
What is the value of hidden (since 9.2) parameter
_job_queue_interval?
Is it default value equals to 5 sec?
If so, then the fast job has no chance to work off all intervals since
their should happen every 2 sec.
 Andrey

cubittm Re: Oracle creating defunct processes against Reply

job queue parent processes
Posts: 270 Posted: Nov 23, 2005 8:16 AM in response to:
From: England goryunov
Registered: 4/16/03

I am sorry, I do not know where that value is held.

If I try show parameter job_queue_interval nothing is returned.

I also tried _job_queue_interval, no luck.

thanks

goryunov Re: Oracle creating defunct processes against Reply

job queue parent processes
Posts: 130 Posted: Nov 23, 2005 8:26 AM in response to: cubittm
From: Sydney, Australia
Registered: 11/10/04
If you have access to sys objects you can select value upon them
through

SELECT
a.ksppinm "Parameter",
a.ksppdesc "Description",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM
x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE
a.indx = b.indx
AND
a.indx = c.indx
AND
a.ksppinm = '_job_queue_interval'

but also select from user/dba_jobs (if you logged under job's owner
name or if you know job_id and have dba priviledges) can give a clue
of the value for that parameter
for instance:
oratest> create or replace procedure tj
2 as
3 d date;
4 begin
5 select sysdate into d from dual;
6 end;
7/

Procedure created.

Elapsed: 00:00:00.01
 oratest> var n number
oratest>
oratest> exec dbms_job.submit(:n, 'tj;', sysdate, 'sysdate + 2/86400')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
oratest> commit;

Commit complete.

Elapsed: 00:00:00.00

-- run the sql statement every sec to catch the changes

oratest> select LAST_DATE, THIS_DATE, NEXT_DATE, What from

user_jobs;

LAST_DATE THIS_DATE NEXT_DATE WHAT

----------------- ----------------- ----------------- ----------------------
11/23/05 19:22:48 11/23/05 19:22:50 tj;

Elapsed: 00:00:00.00
oratest> /

LAST_DATE THIS_DATE NEXT_DATE WHAT

----------------- ----------------- ----------------- ----------------------
11/23/05 19:22:53 11/23/05 19:22:55 tj;

Elapsed: 00:00:00.00

Andrey

cubittm Re: Oracle creating defunct processes against Reply

job queue parent processes
Posts: 270 Posted: Nov 23, 2005 8:29 AM in response to:
From: England goryunov
Registered: 4/16/03

Thank you once again for your continued support - it's nice to know
someone can help when Google fails!

The value for

_job_queue_interval
is
5

Thanks

cubittm Re: Oracle creating defunct processes against Reply

job queue parent processes
Posts: 270 Posted: Nov 23, 2005 8:32 AM in response to:
From: England goryunov
Registered: 4/16/03
 Now we know the value is indeed 5, can you explain how the Fast
Queue (2 seconds) is affected, by waay of demonstration?

ie if four jobs get submitted at the same time, what effect that has,
second by second.

Thanks

goryunov Re: Oracle creating defunct processes against Reply

job queue parent processes
Posts: 130 Posted: Nov 23, 2005 8:36 AM in response to: cubittm
From: Sydney, Australia
Registered: 11/10/04
How long does it take to execute your Fast job (1)?

Andrey

cubittm Re: Oracle creating defunct processes against Reply

job queue parent processes
Posts: 270 Posted: Nov 23, 2005 9:16 AM in response to:
From: England goryunov
Registered: 4/16/03

I queried the number of jobs run through Fast Queue today

(posted=sysdate).
Its 4.41pm in the UK so it's a fair day's work.

There are 3213 jobs from midnight (mind you, DB shut down fro
about 4am to 6am)

Looking down the list, the durations are (in seconds):

2
4
1
1
1
1
1
1
1
0
1
1
1
1
2
0
1
0
0
0
0
0
0
0
0
0
0
0
.
.
.
0
1
0
1
0
0
0
1
55
17
14
16
56
17
16
15
55
15
80
0
0
0
1
0
1
1
0

The average time is 2.042328 seconds

The highest value is 99 seconds

I ran the report for the past 7 days, to see a better indication:
Average: 1.907705
Highest: 4044 (!)

I remove the 4044 and a 4041, to leave a better average, which

became: 1.6697

The conclusion here is that there are many jobs which run under 2
seconds, but many that do not.

For the 2nd report, there were 33968 jobs. 2,946 were longer than 2
seconds.
goryunov Re: Oracle creating defunct processes against Reply
job queue parent processes
Posts: 130 Posted: Nov 23, 2005 9:51 AM in response to: cubittm
From: Sydney, Australia
Registered: 11/10/04
I have no UNIX box to experiment on right now,
but I think the problem is that job coordinator process loses control
over jobs with long execution and after 16 failures they become
zombie processes. And I think the job_queue_processes parameter
equals to the number of jobs intensifies the situation.
By the way, what is the reason to run the job 1 so often? What does it
do? Is there any java code?

Andrey

cubittm Re: Oracle creating defunct processes against Reply

job queue parent processes
Posts: 270 Posted: Nov 23, 2005 4:18 PM in response to:
From: England goryunov
Registered: 4/16/03

I am not sure what you mean "the job (1).

Do you mean the job queue 1 (Fast Queue) ?

The queue runs many different jobs which are supposed to be fast
finishers, such as stock checking, server polling, etc

It is possible I guess java code exists but I cannot be sure.

I think that you have highlighted a problem, if not necessary the

cause. With so many jobs taking more than 2 seconds. What
happens to the job if 2 or 5 seconds pass? Does it die? Do the other
that wait, die?

Is the job coordinator losing control specified in oracle documentation

that I can research?

Many thanks for your helpful advice, Andrey

goryunov Re: Oracle creating defunct processes against Reply

job queue parent processes
Posts: 130 Posted: Nov 25, 2005 6:11 AM in response to: cubittm
From: Sydney, Australia
Registered: 11/10/04
Hi,
I have not found interesting information on Metalink
just - Doc ID: Note:222180.1 Subject: What Does CJQ0 Process Do ?

but it does not explain your situation.

I saw a couple of topics about defunct job processes connected with
java stored procedures on Metalink.
Also I tried to reproduce the situation but with no luck.

Do the database generate trace files for job and job coordinator
 processes?

Andrey

cubittm Re: Oracle creating defunct processes against Reply

job queue parent processes
Posts: 270 Posted: Nov 25, 2005 6:22 AM in response to:
From: England goryunov
Registered: 4/16/03

The problem does clear upon the DB shutdown/restart.

This morning, there was already a defunct at 0820 when the DB had
been up since 0615. At 0823 another ocurred.

Our Development DB (on same machine) which is not being used

much at the moment actually spawned two defunct's yesterday.Its a
clone of LIVE.

I do have feeling that it is linked to integration with a web server, print

server and/or event server. Certainly the Event server is Java based.

AFAIK there are no trace files.

Thanks