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"

    mjsymes
      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