Re: external table access hangs

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

--
http://www.freelists.org/webpage/oracle-l