Re: external table access hangs
- From: Mladen Gogala <mgogala@xxxxxxxxxxx>
- To: Dave.Herring@xxxxxxxxxx
- Date: Tue, 27 Feb 2007 10:25:50 -0500
On 02/27/2007 07:19:18 AM, Herring Dave - dherri wrote:
> Mladen,
>
> V$SESSION_WAIT shows the following:
>
> SID SEQ# EVENT
> ------------------ ------------------
> ----------------------------------------------------------------
> P1TEXT
> P1 P1RAW
> ----------------------------------------------------------------
> ------------------ ----------------
> P2TEXT
> P2 P2RAW
> ----------------------------------------------------------------
> ------------------ ----------------
> P3TEXT
> P3 P3RAW WAIT_TIME
> ----------------------------------------------------------------
> ------------------ ---------------- ------------------
> SECONDS_IN_WAIT STATE
> ------------------ -------------------
> 212 123 db file sequential read
> file#
> 1 0000000000000001
> block#
> 2300 00000000000008FC
> blocks
> 1 0000000000000001 1
> 197 WAITED KNOWN TIME
>
> file#1 is /u02/oradata/ntrndb1/system01.dbf, which matches the output I
> gave from event 10046 where it appears to be waiting on the query:
>
> SELECT PATH, READ, WRITE FROM LOADER_DIR_OBJS WHERE NAME =
> 'COLLECT_DATA_DIR';
>
> I can run this query interactively and it returns immediately.
>
> As for "hang" being in question, from a front-end the process does hang,
> as in performs no noticable work. From the O/S level, I see the
> associated Oracle process consume considerable amounts of CPU, grabbing
> 95%+ on the CPU its assigned to.
Doing what? Can you run truss on the process? The question is what exactly
is the process doing. If it's burning CPU, it's unlikely that it's reading.
The "WAITED KNOWN TIME" status indicates that the process is not waiting,
it is doing something. Now, it needs to be seen what is it doing. The
tools accessible to mere mortals are strace and ltrace, thy will show you
system services used by the process and library calls used by the process.
That may allow you to go to the heart of darkness and fix the problem. You
can try it on your own time, but the best thing to do is to attach to
the process by using oradebug
oradebug setospid <system PID>
and then do:
oradebug dump processstate 12
followed by:
oradebug dump systemstate 12
The output of both commands will be files in udump directory. You may want to
unlimit the size of the trace file. Then, you open a TAR and send this to
Oracle. They have to resolve the problem.
>
> And no, I have not opened an SR with Oracle yet. I was hoping that
> perhaps someone on this list might have run into something similar and
> provide would be willing to share.
Oracle Support is your best and only bet, trust me. Here, you may waste
your time with childish types replying to your posts by using ASCII art.
--
Mladen Gogala
http://www.mladen-gogala.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
