Re: File open wait
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 19 Jan 2006 13:30:51 -0600
File open waitHi,
If v$session_wait = WAITED UNKNOWN TIME , then it means you are not waiting
anymore, you WAITED. This means, as long as instrumentation is correct, you're
using CPU. If you don't have any trace entries and even processtate dump didnt
work (you did attach to the right process, right?), then maybe you're in some
loop or hang situation. Normal oradebug dumps work using sending a SIGUSR2
signal to the process IIRC, but if maybe even the signal handler isn't invoked
correctly or can't do its job.
If you do top or glance in OS, do you see your process continuously consuming
CPU?
Run pstack on this process few times and see whether it changes. If you don't
have pstack then you can use a debugger, gdb or tusc, attach to the process and
get call stack using "backtrace" from the process. If the process uses CPU and
stack trace doesnt change, youre in some kind of endless or very long loop
(caused by a bug).
Tanel.
----- Original Message -----
From: Luis Fernando Cerri
To: oracle-l@xxxxxxxxxxxxx
Sent: Thursday, January 19, 2006 11:45 AM
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
Other related posts:
