This discussion is archived
2 Replies Latest reply: Nov 2, 2011 7:12 PM by meser RSS

Session "ALTER JAVA CLASS xxx COMPILE" hangs on "db sequential file read"

matt symes Newbie
Currently Being Moderated
Please can you help. We have many invalid Java Class objects in our E-Business Suite DB.

Here is one of them:

APPS./10a420ef_FuncExtElementAvaila

When we try to compile it (or any of the others for that matter):

alter java class "/10a420ef_FuncExtElementAvaila" resolve;

the session hangs. The server process is eating up CPU and the session is waiting on "db sequential file read". Here is some of my analysis.

Command:

alter java class "/10a420ef_FuncExtElementAvaila" resolve;

The SID is 20.

Identify The server process:

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id and s.sid = 20
WHERE s.type != 'BACKGROUND';

INST_ID     1
SID     20
SERIAL#     1023
SPID     27776
USERNAME     APPS
PROGRAM     plsqldev.exe

So - Server Process is 27776.

prstat -p 27776 gives:

PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
27776 oraprd 827M 767M cpu5 0 0 0:12:37 12% oracle/1

What's it hung on?

select * from v$session_wait where sid=20;

SID     20
SEQ#     538
EVENT     db file sequential read
P1TEXT     file#
P1     5
P1RAW     00000005
P2TEXT     block#
P2     75977
P2RAW     000128C9
P3TEXT     blocks
P3     1
P3RAW     00000001
WAIT_TIME     1
SECONDS_IN_WAIT     284
STATE     WAITED KNOWN TIME

Running select * from dba_extents x where file_id = 5 and 75977 between block_id and block_id + x.blocks gave:

OWNER     SYS
SEGMENT_NAME     JAVASNM$
PARTITION_NAME     
SEGMENT_TYPE     TABLE
TABLESPACE_NAME     SYSTEM
EXTENT_ID     9
FILE_ID     5
BLOCK_ID     75972
BYTES     507904
BLOCKS     62
RELATIVE_FNO     5

Can anyone give me some pointers in where I go with the analysis from here?

Thank you in advance,

- Matt Symes

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points