RE: File open wait - the end

First of all, I'd like to thank you all who contributed with this thread.

After some tests, last Monday I found the problem: there was a bug in the stored procedure that was leading to a endless loop.

The most important things I have learned: - the difference between -2, 0 e >0 in v$session_wait.wait_time for online monitoring;
- probably you are not hitting a bug;
- don't trust developers when they say they have debugged the code and it's still freezing without explanation. Debug it yourself.

Best regards,
Luis  

> -----Original Message-----
> From: Luis Fernando Cerri
> Sent: quinta-feira, 19 de janeiro de 2006 14:46
> To: oracle-l_at_freelists.org
> Subject: File open wait
>
> Hello, list!
>
> OS: HPUX 11.11
> Oracle 8.1.7.4 EE
>
>
>
> I'm facing right now a "file open" wait for a specific session as follows:
>
> This select is returning the same result for like one hour:
>
> select substr(event,1,35) event,TOTAL_WAITS
> from v$session_event
> where sid= 40;
>
> EVENT TOTAL_WAITS
> ----------------------------------- -----------
> log file sync 1
> db file sequential read 12
> file open 2
> SQL*Net message to client 38
> SQL*Net message from client 38
>
>
>
> Current active wait event:
>
> SQL> select seq#,event,wait_time,seconds_in_wait,state
> 2 from v$session_wait
> 3 where sid=40
> 4 /
>
> SEQ# EVENT WAIT_TIME SECONDS_IN_WAIT STATE
> ----- ------------ ---------- --------------- -------------------
> 91 file open -2 4751 WAITED UNKNOWN TIME
>
>
>
>
> No join information between v$session and dba_data_files:
>
> SQL> select s.ROW_WAIT_FILE#,f.file_name
> 2 from v$session s, dba_data_files f
> 3 where s.ROW_WAIT_FILE# = f.file_id
> 4 and s.sid=40
> 5 /
>
> no rows selected
>
> SQL> select s.ROW_WAIT_OBJ#,s.ROW_WAIT_FILE#
> ,s.ROW_WAIT_BLOCK#,s.ROW_WAIT_ROW#
> 2 from v$session s
> 3 where s.sid=40;
>
> ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
> ------------- -------------- --------------- -------------
> -1 0 0 0
>
>
>
> Enabling 10046 trace through dbms_support didn't generated a trace file on
> UDUMP destination. The ORADEBUG DUMP PROCESSSTATE didn't return usable
> information (or I couldn't find it).
>
>
> Any guesses on determining which file is the session waiting for? Do P1,
> P2 and/or P3 mean anything usable? Oracle states that this information can
> be used only by development team.
>
> Thanks in advance for your collaboration.
>
> Best regards,
> Luis
>

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